'Last Updated' Worksheet_Change event disables Undo

[rabley]

I’m trying to add a “Last Time Updated” column to my spreadsheet of product information. I got it to work (it updates Cell BQ every time any cell in that row is changed), but it has “broken” the Undo function in Excel. I just get an error beep and nothing is undone. If I can’t fix that, I’ll have to take this out and rely on the user to manually update the cell – unlikely! Any ideas on how to get around this? Can I somehow combine it with a Before Close event, so that only the cells in changed rows are updated at the end of each session?

Right now, my Worksheet code looks like this:

Private Sub Worksheet_Change(ByVal areaOfInterest As Range)
      	‘ Rows 1-3 are header info
If areaOfInterest.Row > 3 Then
          Worksheets("Sheet1").Cells(areaOfInterest.Row, "BQ") = Now
      End If
End Sub

 

[Fr33dan]

Some built in functions clear out data such as the clipboard or undo history. I don't know how to fix it but you might wanna watch to make sure that your user doesn't get caught in an infinite loop (BQ gets modified called the macro which again modifies BQ)

Private Sub Worksheet_Change(ByVal areaOfInterest As Range)
    ' Rows 1-3 are header info
    If (areaOfInterest.Row > 3) And (areaOfInterest.Column <> 69) Then
          Worksheets("Sheet1").Cells(areaOfInterest.Row, "BQ") = Now
    End If
End Sub

 

[PHV]

What about this ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row > 3 Then
  Application.EnableEvents = False
  Cells(Target.Row, "BQ") = Now
  Application.EnableEvents = True
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rabley]

Thank you for your replies. Unfortunately, that gives me the same error beep and disabled Undo, PH. (Also, it updates the column even when all the user has done is click on a cell). Even changing it from SelectionChange to Change didn't fix it, though. Any other ideas in your hat?

 

[Fr33dan]

Well one idea would be to create a hidden copy of the worksheet in Workbook_Open then check for changes in Workbook_BeforeClose or Workbook_BeforeSave and modified the times then. I'd be a very inefficient method but it should work.

 

[VRoscioli]

I'd imagine that altering data on the sheet in anyway will negate the ability to use Undo. You could try keeping track of which rows have been updated (in an array, for example) but this may not work either (and even if it does there can be some issues).


-V

 

[Fr33dan]

It occurred to me that you could also override the keyboard shortcuts for undo and redo to your own versions. It wouldn't make it work in the edit menu but you would get some of the functionality. It would look something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    temp = Target.FormulaArray
    Application.Undo
    I_am_Worksheet = ActiveSheet.Name
    I_am_Address = ActiveCell.Address
    
    If ActiveSheet.Index - 1 > 0 Then
        If (Sheets(ActiveSheet.Index - 1).Name = "Before") Then
            Sheets(ActiveSheet.Index - 1).Delete
        End If
    End If
    
    ActiveSheet.Copy Before:=ActiveSheet
    ActiveSheet.Name = "Before"
    ActiveSheet.Visible = 0
    
    Worksheets(I_am_Worksheet).Activate
    Range(I_am_Address).Select
    Target.FormulaArray = temp
    If (Target.Row > 3) Then
          Cells(Target.Row, "BQ") = Now
    End If
    Application.EnableEvents = True
End Sub

That code in the worksheet

Sub Undo()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    If ActiveSheet.Index <> 1 Then
    If (Sheets(ActiveSheet.Index - 1).Name = "Before") Then
        I_am_Worksheet = ActiveSheet.Name
        I_am_Address = ActiveCell.Address
        
        If ActiveSheet.Index + 1 <= Sheets.Count Then
            If (Sheets(ActiveSheet.Index + 1).Name = "After") Then
                Sheets(ActiveSheet.Index + 1).Delete
            End If
        End If
    
        ActiveSheet.Copy After:=ActiveSheet
        ActiveSheet.Name = "After"
        ActiveSheet.Visible = 0
            
        Sheets(Sheets(I_am_Worksheet).Index - 1).Visible = 1
        Sheets(Sheets(I_am_Worksheet).Index - 1).Activate
        Sheets(I_am_Worksheet).Delete
        ActiveSheet.Name = I_am_Worksheet
    End If
    End If
End Sub
Sub Redo()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    If ActiveSheet.Index + 1 <= Sheets.Count Then
    If (Sheets(ActiveSheet.Index + 1).Name = "After") Then
        I_am_Worksheet = ActiveSheet.Name
        I_am_Address = ActiveCell.Address
        
        If ActiveSheet.Index - 1 > 0 Then
            If (Sheets(ActiveSheet.Index - 1).Name = "Before") Then
                Sheets(ActiveSheet.Index - 1).Delete
            End If
        End If
        
        ActiveSheet.Copy Before:=ActiveSheet
        ActiveSheet.Name = "Before"
        ActiveSheet.Visible = 0
            
        Sheets(Sheets(I_am_Worksheet).Index + 1).Visible = 1
        Sheets(Sheets(I_am_Worksheet).Index + 1).Activate
        Sheets(I_am_Worksheet).Delete
        ActiveSheet.Name = I_am_Worksheet
    End If
    End If
End Sub

and that code in a module. then just assign the macros to crtl-z and crtl-y

 

[rabley]

Thank you both for your suggestions, Fr33dan and VRoscioli. I'll mess around some more and see what I can patch together.