How to know what line of code clears the Undo cache

[mcomp72]

In some of the sheets of my workbook, whenever I type in a value into a cell and press Enter, the Undo button is greyed out. So I assume the Undo cache is being cleared because a macro is running. However, I cannot figure out what macro is running.

As a test, I went into the ThisWorkbook module and commented out all code within the Workbook_SheetSelectionChange sub.

In the module for the sheet, I went into the sub for Worksheet_SelectionChange and commented out all the code in it.

The issue still occurs. I can't think of any other subs that might be running when I type a value into a cell. Yet, the Undo button is greyed out after I do.

Is there any way to create a listing in the Watch window where I can watch the Undo cache? I could set that to break when the value changes, so maybe I could see the line of code that is causing the change. But I don't know what exactly I would put in the Watch window to watch the Undo cache.

 

[6StringJazzer]

When you commented out code, did you comment out only the code inside the sub, or did you also comment out the Sub statement itself?

Your question is unclear because I don't know if you are expecting a specific line of code to be causing this problem, or you just don't know what code is running. The Undo stack is cleared when any VBA code runs. So any event handler is going to do it, even if there is no code inside it.

 

[mcomp72]

When you commented out code, did you comment out only the code inside the sub, or did you also comment out the Sub statement itself?

Your question is unclear because I don't know if you are expecting a specific line of code to be causing this problem, or you just don't know what code is running. The Undo stack is cleared when any VBA code runs. So any event handler is going to do it, even if there is no code inside it.

It's interesting that you say that. That's exactly what I thought, too. But as I've been doing some testing, I have discovered that when VBA code runs, it does not always clear the Undo stack. It seems it will only do it if the code that runs makes a change to a sheet. For instance, if the only code that runs is Application.ScreenUpdating = TRUE, it does not clear the Undo stack. Also, if I have an IF statement and the IF statement evaluates as False and therefore doesn't do anything else, it also doesn't clear the Undo stack. Maybe this is something new that Microsoft has added recently? I'm running the latest Excel from Microsoft 365.

And just moments ago, I stumbled upon an answer on StackOverflow that essentially gave me what I needed in terms of the Undo stack. I typed this into the Watch window:

Application.CommandBars("Standard").Controls("&Undo").List(1)

This tracks the last item added to the Undo stack. So I was able to set this to "Break when the Value Changes" and discovered where my problem was. There was code in the Worksheet_Calculate sub on another worksheet that was running when I would make a change to my main worksheet. Now I think if I add an IF statement in that sub, I can get it to not always trigger the code, and then hopefully not always clear the Undo stack. Haven't tried it yet. Fingers cross.

 

[Alex Blakenburg]

My understanding is also that the undo stack only gets cleared when the macro writes to the sheet. Bill Jelen talks about it in the video below.
2 min 40 and @ 1:37 "it turns out that the rule is: the undo stack is cleared when your macro writes something to the worksheet".

@mcomp72 Your watch window tip looks to be very useful.

 

[6StringJazzer]

when VBA code runs, it does not always clear the Undo stack. It seems it will only do it if the code that runs makes a change to a sheet.

My understanding is also that the undo stack only gets cleared when the macro writes to the sheet.

I apologize for the misleading response. I have never checked this out in that kind of detail so I did not realize this. Thanks for clarifying this.