Undo Option Lost After Event Macro Code

Re: Macro Has Removed Undo Option

Whenever you change anything using VBA it clears the undo stack and VBA doesn't support an undo, the only way is to create a macro that reverses the effect of the previous macro or copy the worksheet and hide it and upon close delete it or have a button with macro to re-instate it!

Re: Undo Option Lost After Event Macro Code

JoFo,

Simon Lloyd is correct. Here is a link to an example of what it takes to create your own macro undo:
http://www.j-walk.com/ss/excel/tips/tip23.htm
One difficulty is trying to keep track of everything to undo - values, formulas, column widths, hidden rows, colors, borders, objects, etc. etc.

Re: Undo Option Lost After Event Macro Code

To be honest the copy sheet method is better as you don't have to do all that tracking just copy the sheet and then use the following:
Create a macro that copies the worksheet and call it MyUnDoMacro, then at the bottom of your original macro add this line

Application.OnUndo "Undo My Macro", "MyUnDoMacro"

the Undo My Macro is what the user would see and the latter part calls the macro, now after your macro has run you just click on the undo as you would normally and hey presto macro reversed!

Re: Undo Option Lost After Event Macro Code

JoFo,

Curiosity got the better of me and I tried to come up with a better Undo solution. I won't guarantee the following code in all situations because Sendkeys can sometimes produce unexpected results but I think this will work in your situation. By using Sendkeys it does not wipe out the Undo commands. The nice thing about this is it will allow you to undo all the Undo commands, not just the last one which would be the situation if you wrote your own Undo. It puts in the date (not the date and time as before - the VBA "Now" function produced odd results when pasting) by sending the ctrl+; keys to the cell. I'm open to criticisms - improvements from others since I may have missed something.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim Newc As Range
    
    Application.EnableEvents = False
    For Each c In Target
        If c.Column = 11 Then
            If c.Value = "100 - Purchase Order In" Then
                 MsgBox "Is the Month In correct?"
            End If
        End If
        If c.Column > 1 And c.Column < 18 Then
            Set Newc = ActiveCell
            Cells(c.Row, 1).Select
            SendKeys "^;~", True
            Newc.Select
        End If
    Next c
    Application.EnableEvents = True
End Sub

Edit Note - After trying in different workbooks it sometimes does all the undos, sometimes does one, and sometimes does none. I'll look into it more....

Re: Undo Option Lost After Event Macro Code

I would just copy the whole sheet to a hidden one.

Re: Undo Option Lost After Event Macro Code

But if you copy the whole sheet you only have one level of undo. You can only go back to the time just before your macro ran.

Re: Undo Option Lost After Event Macro Code

I would consider a whole sheet copy as an unlimited number of undos for that sheet.

Re: Undo Option Lost After Event Macro Code

Dave your dead right, it is a simplicity all of its own as i mentioned in post 5, in fact you could copy the sheet everytime a change is made naming the with the sheet name and time perhaps then upon close delet all the copied sheets. Bert01 great effort but as you say can be unstable so if the document is important you can't rely on it however you can rely on a direct copy of the worksheet.

Re: Undo Option Lost After Event Macro Code

Guys,

Well, I have to give in to your suggestions. Sendkeys sometimes worked 100% of the time, sometimes 0%, and usually inbetween. I looked at some API's but couldn't get anywhere with them. Creating new sheets and copying the information then deleting the pages when closing would be adequate performance in a lot of cases. I would insert a new sheet and copy/paste the contents rather than copy the whole sheet because the copy sheet is slower and there is a bug that limits the sheets copied. I was hoping for a moment of glory but instead I'm eating humble pie. Still, I did learn a few things...

Re: Undo Option Lost After Event Macro Code

Simon,
No need to make multiple copies of the production sheet. The undo stack copies along with the copied sheet. Delete the previously copied sheet and make a fresh copy. Keep the copied sheet the same name so a button can load that sheet back in if requested.

Bill