How to know what line of code clears the Undo cache

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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.
 
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top