Undo Stack gets cleared running VBA - Possible Solution to fix this?

[kellman]

I came across this post on the Microsoft Answers web site and wanted to share it here with everyone.

Could this suggested VBA code actually work to allow the Undo button to work after VBA macro has run?

Person Asks Question:

I have code that runs a sort of Column A after I hit the Enter key. But, I've noticed that if I want to undo what I just did or beyond that, it doesn't let me. Is there VBA code that can be used to enable the Undo?


Answer or Solution:

Yes, you can use the Application.EnableEvents property to enable undoing actions after running an event in VBA code.

By default, when an event is triggered in Excel (such as by pressing the Enter key), the EnableEvents property is set to False, which prevents undoing actions. However, you can set EnableEvents to True in your VBA code to allow undoing actions.

The VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False ' Disable events to prevent infinite loop
    If Target.Column = 1 Then ' Sort column A only
        Range("A1"). Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
    End If
    Application.EnableEvents = True ' Enable events to allow undoing actions
End Sub

In this example, the Worksheet_Change event is triggered whenever a cell in the worksheet is changed. The code checks if the changed cell is in column A and if it is, it sorts column A in ascending order.

To enable undoing actions, the Application.EnableEvents property is set to False at the beginning of the code to prevent an infinite loop (since sorting the column would trigger the Worksheet_Change event again), and then set back to True at the end of the code to allow undoing actions.

My Question:

In my case I have a small table with 6 Columns and I'd like to have the ability to "Undo" mistakes made in Columns E and F
How might this code be modified to work in my case / example attached below:

My current VBA code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

 
   Application.ScreenUpdating = False
 
   'Clear the color of all cells
   Sh.Cells.Interior.ColorIndex = 0
   With Target
      'Highlight row and column of the selected cell
      .EntireRow.Interior.ColorIndex = 24
      .EntireColumn.Interior.ColorIndex = 24
   End With
 
   Application.ScreenUpdating = True

End Sub

 

[6StringJazzer]

Yes, you can use the Application.EnableEvents property to enable undoing actions after running an event in VBA code.

By default, when an event is triggered in Excel (such as by pressing the Enter key), the EnableEvents property is set to False, which prevents undoing actions. However, you can set EnableEvents to True in your VBA code to allow undoing actions.

That answer is nonsense. It reads like an AI answer. The EnableEvents property is unrelated to Undo. Triggering an event does not set it to False. Setting it to False does not prevent undoing actions. Setting it to True will not allow undoing actions.

If VBA code makes any change in a worksheet, it will clear the Undo stack.

If the code you show here makes a change to the fill color because of a selection the user made, the user cannot use Undo after this code runs.

It looks like you are highlighting the row and column of the selected cell. If the user changes a cell, selects a different cell, and then realizes they made a mistake in the first cell, the change cannot be undone.

An alternative to your solution is to combine VBA with conditional formatting, which does not change the content of the sheet and therefore does not wipe out the Undo stack.

In the module for the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Me.Calculate
End Sub

 

[kellman]

Here is the URL to the Article I'm referring to - there are more details that I believe I had omitted.

Original Article can be found at the following URL below - Click to follow the link:

Redirecting

 

[Peter_SSs]

Here is the URL to the Article I'm referring to - there are more details that I believe I had omitted.

Never-the-less, if you start a fresh workbook, enter the code suggested in that article and then enter anything in column A you will find that the Undo function is not available.


Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.


@6StringJazzer
Jeff, you may possibly have missed a couple of points in relation to the OP's code.

• It highlights multiple columns/rows if that is what is selected
• It is workbook code so any CF solution could need to be implemented in multiple worksheets

 

[6StringJazzer]

Here is the URL to the Article I'm referring to - there are more details that I believe I had omitted.

Original Article can be found at the following URL below - Click to follow the link:

Redirecting

I found and read the original article when I read your post. It's still nonsense.

Jeff, you may possibly have missed a couple of points in relation to the OP's code.

• It highlights multiple columns/rows if that is what is selected
• It is workbook code so any CF solution could need to be implemented in multiple worksheets

Yes, I missed that. My solution is easily adapted to cover the second bullet by using Workbook_SheetSelectionChange in ThisWorkbook. However, it will only highlight the row and column of the upper-left cell in a multi-cell selection. I don't have an off-the-shelf solution for that. But OP's comment says "Highlight row and column of the selected cell" so I wonder if it's really needed.

 

[kellman]

Thank you for your suggestion - very much appreciated