Undo is greyed out

[sspatriots]

I have a workbook that will not allow the Undo feature to work. I'm guessing it has to do with having a workbook sheet change event in it. Anyone else have this issue? If so, is there a workaround? Or could this be due to another issue in the workbook, a setting or something?

Thanks SS

Dim oldValue As String
Dim oldAddress As String

Private Sub Workbook_Open()
Sheets("LogDetails").Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
ShowHideLogSheet
'Target.Offset(1, 1).Select
Target.Offset(0, 0).Select

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
'' add these defintiions
'Dim temparr(1 To 1, 1 To 9) As Variant
Dim temparr(1 To 1, 1 To 8) As Variant

'A, B, C, E, G, I, J, K & L  column numbers
'A, B, E, G, I, K, L, M & N  column numbers
'A, E, G, I, K, L, M & N  column numbers


'colnos = Array(1, 2, 3, 5, 7, 9, 10, 11, 12)
'colnos = Array(1, 2, 5, 7, 9, 11, 12, 13, 14)
colnos = Array(1, 5, 7, 9, 11, 12, 13, 14)


'''''''''''''''''''''''''
'sSheetName = "2022"
sSheetName = ActiveSheet.Name

If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
'' this is not very good code, it could be much faster
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now


'Put back later, SPS, 03/09/22
Sheets("LogDetails").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress


Sheets("LogDetails").Columns("A:D").AutoFit
'' add this bit.
rowno = Target.Row
'inarr = Worksheets(sSheetName).Range(Cells(rowno, 1), Cells(rowno, 12))
inarr = Worksheets(sSheetName).Range(Cells(rowno, 1), Cells(rowno, 14))

'For I = 1 To 9
For i = 1 To 8

 temparr(1, i) = inarr(1, colnos(i - 1))
Next i
With Sheets("LogDetails")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
 '.Range(.Cells(LastRow, 7), .Cells(LastRow, 15)) = temparr
 .Range(.Cells(lastRow, 7), .Cells(lastRow, 14)) = temparr
End With

''''
Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'oldValue = Target.Value
If Target.Count > 1 Then Exit Sub
If Target.Count = 1 Then
oldValue = Target.Value
End If



oldAddress = Target.Address
End Sub

 

[SanjayGMusafir]

I have a workbook that will not allow the Undo feature to work.

What I have understood from your post is that after you have run a macro it doesn't allow to undo it...

If that's the case -
Excel doesn't allow undo macro actions or after you have saved a file...

 

[sspatriots]

All I'm saying is that I have no undo option at all. Wasn't sure if the change events were triggering and preventing the undo option from working. Or, what else could cause it? Worksheet is not protected and I have no undo ability on either worksheet.

 

[Jaafar Tribak]

The Undo list is lost whenever a change is made to excel via code.

 

[sspatriots]

I understand that a macro disables the undo, but after a macro has run and I do something else to the worksheet I still don’t have an undo option.

 

[sspatriots]

I'm adding the following line to my Workbook_SheetChange event.

ActiveWorkbook.Save

Then I'm adding the following code with a button that will allow me close and reopen the saved file before the even ran. For some reason this code doesn't re-open the file though. Can anyone see why it might not be doing this?

Sub UndoLastChange()

X = ActiveWorkbook.Name

Workbooks(X).Close savechanges:=False
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & X

End Sub

 

[Jaafar Tribak]

I understand that a macro disables the undo, but after a macro has run and I do something else to the worksheet I still don’t have an undo option.

As long as you have the Workbook_SheetChange in place and there is code in it that modifies cell values , the Undo list will stay greyed out.

Then I'm adding the following code with a button that will allow me close and reopen the saved file before the even ran. For some reason this code doesn't re-open the file though. Can anyone see why it might not be doing this?

If the ActiveWorkbook is the workbook executing the UndoLastChange macro then it won't reopen because it will be already closed before the Workbooks.Open Filename:=ThisWorkbook.Path & "\" & X code line gets a chance to be executed.

Try this and see if it does what you want :

Sub UndoLastChange()
    Application.OnTime Now, "ReOpen"
    ThisWorkbook.Close SaveChanges:=False
End Sub

Sub ReOpen()
    '
End Sub

Edit : the code above is assumed to be added in a normal module (not in the ThisWorkbook module)

 

[sspatriots]

As long as you have the Workbook_SheetChange in place and there is code in it that modifies cell values , the Undo list will stay greyed out.


If the ActiveWorkbook is the workbook executing the UndoLastChange macro then it won't reopen because it will be already closed before the Workbooks.Open Filename:=ThisWorkbook.Path & "\" & X code line gets a chance to be executed.

Try this and see if it does what you want :

Sub UndoLastChange()
    Application.OnTime Now, "ReOpen"
    ThisWorkbook.Close SaveChanges:=False
End Sub

Sub ReOpen()
    '
End Sub

Edit : the code above is assumed to be added in a normal module (not in the ThisWorkbook module)

This is genius. Thanks. Exactly what I needed.