VBA Code to get UNDO function back

[pchugh]

Hi Everyone,

I have a code running in my worksheet and I am not able to use undo function when the code is running. Undo button does not work on rows that are not even part of the code. I heard of adding Application.Undo in the VBA code but I am not sure how will that work as i tried adding it and VBA doesnt work then. Below is the VBA code that I am using. Can anyone help to get me Undo function back atleast on rows that are not part of the VBA.

Please Login or Register  to view this content.

[MrShorty]

When a VBA Sub or event procedure is run, it clears the undo stack. To my knowledge, there is no way to preserve the undo stack. Use of the Application.OnUndo method allows you to specify a separate VBA procedure that will execute if/when you execute the undo command -- effectively starting a new undo stack.

It is one of those programming decisions. If undo is important to the way you use this spreadsheet, then you should probably find a non-VBA, non event procedure way to accomplish the task. If a VBA event procedure is the only way to accomplish what you want, then you are effectively giving up on reliably using undo -- especially with a selection change event, since the selection will change very frequently.

[pchugh]

This VBA is to run the drop down list down to the end of the rows in the sheet. And Undo works on the drop down list which is being run by this VBA. On the cells which are free text and are not controlled by VBA why are those not being able to do get UNDO. Is this how VBA works. I am new to this.

[MrShorty]

I am having a hard time visualizing what is going on in your spreadsheet. It might be best if you upload a small sample file that illustrates what you are doing, so we can look at it and offer some alternatives that will keep the undo stack intact.

[pchugh]

I just have this below small VBA. This is helping me to run Dependent Data Validation Lists in 2 columns across one of the sheet in workbook. But the problem is, the rows which are free text or not having data validation lists cannot also have the undo function. Since I am new to VBA, I assume the VBA code should just be restricted to 2 columns in the sheet which have the data validation and should not interfere how rest of the columns in the sheet are dealt with. If my understanding is correct then I should be able to have undo function in every other column.
Is there way I can achieve this by restriction the VBA to just 2 columns in the sheet and not affecting other columns.

[CK76]

Nope. Undo cache is cleared when VBA code runs. It's just the way it is.

In order to get around that, you'd need custom class module to trap previous state.

Have read of below.
http://www.jkp-ads.com/Articles/UndoWithVBA00.asp