Undo action after running code

[tiredofit]

I would like to do the following:

1. Enter some data on a worksheet manually
2. Click on a button to run some code
3. Be able to undo action 1.

I came across this article:

Excel: Enabling Undo With Excel VBA

jkp-ads.com: Undo With Excel VBA

jkp-ads.com

but I can't implement it.

I copied the two class modules and created my own simple subroutine:

Sub Macro1()

Msgbox "Hi"

End Sub

then I entered a value on the worksheet and ran Macro1.

What I expected to happen is if I now clicked undo, that the value I entered on the worksheet to disappear.

Am I mistaken the code on the website is not for this purpose?

Thanks

 

[shknbk2]

The code won't work for what you want. The code's purpose is to undo certain actions performed by the VBA. In the implementation example on the website, cells A1:A10 have their color changed. The undo code allows that action to be undone. However, as soon as the first cell has its color changed, the previous undo actions you manually performed would get cleared, leaving the undo list at the end with just the ability to undo the color change. In your example, 1 would get cleared, but you could undo 2 in theory (as long as the actions are supportable like you can't undelete a sheet if the code deletes it).

To implement the code, you'd have to use something like the implementation example. In your code above, you don't take any advantage of having included the classes. You simply show a message box, which shouldn't change the undo history I think since it's not an "undoable" action like deleting something or setting the value of a cell, etc.

 

[tiredofit]

The code won't work for what you want. The code's purpose is to undo certain actions performed by the VBA. In the implementation example on the website, cells A1:A10 have their color changed. The undo code allows that action to be undone. However, as soon as the first cell has its color changed, the previous undo actions you manually performed would get cleared, leaving the undo list at the end with just the ability to undo the color change. In your example, 1 would get cleared, but you could undo 2 in theory (as long as the actions are supportable like you can't undelete a sheet if the code deletes it).

To implement the code, you'd have to use something like the implementation example. In your code above, you don't take any advantage of having included the classes. You simply show a message box, which shouldn't change the undo history I think since it's not an "undoable" action like deleting something or setting the value of a cell, etc.

Thanks for your reply.

I'm a little confused. Are you saying in my example, it is IMPOSSIBLE to undo action 1 manually, (after a macro has been run) or are you saying it IS POSSIBLE but I have amended the code correctly?

 

[shknbk2]

It is impossible to undo action 1. The undo list that grows when you manually do things on the sheet before running the macro gets cleared when the code starts manipulating things.

 

[tiredofit]

It is impossible to undo action 1. The undo list that grows when you manually do things on the sheet before running the macro gets cleared when the code starts manipulating things.

Thanks for clarifying.