VBA Undo - Excel

[Just Shan]

Dear Experts,

Can i please have the code to undo an action in VBA. (EXCEL)

For example:

i have run a macro, and i want to undo the action.
I only need one Undo but Please tell me if more than one undo is possible ?

The macro to be undone is already available with me, if you could please provide me the code in this structure, would be most appreciated.


Sub Undo()

Undo Code here

my code here. (i will paste my code accordingly)

End sub..

Thanks in anticipation,

S h a n

[si_the_geek]

Unfortunately running a macro cancels all of the Undo events of Excel.

You can put your own event on to this list (cant remember how at the moment!), but it must be your own method.. ie: you need a macro which reverses the effect of the first macro.

[Just Shan]

Do you mean that the undo macro will vary according to the macro which is to be undone ?

Is there no common code for this ?

Best Regards,

S h a n

[si_the_geek]

That's right, if your macro only alters one sheet then you could take a copy of the sheet & hide it (probably in the same workbook), and the "undo macro" would just copy the data back over the original.

Alternatively you could save a copy of the entire workbook (I'm not sure if this is a viable option - it might cause problems for saving the original later on).

In either of these cases, it could slow down dramatically if there is lots of data.


I have never seen any standard code for this, and cant the version I wrote a few years ago either

[si_the_geek]

ah-ha! I've found it (suddenly remembered where it was likely to be).

This is the code to set the Undo command:

Application.OnUndo "Undo the ZeroRange macro", "undozero"

Where undozero is the name of the macro to be run which un-does the original macro, and the other bit is the text to show the user.

If you need more info, the best place is in VBA help

[RobDog888]

Cool tip si_the_geek!

[si_the_geek]

cheers

It's always nice to remember something from a few years ago that actually worked.

It's just a shame that VBA is far too complex for the Undo macro to be created automatically (and you cant keep Undo's from before the macro)

[Just Shan]

Hi,

I could not help my self achieving the code given by you to undo my macro, Considering the below is my macro, could you please amend it to allow undo.

Overview of the below Code:

This macro is attached to another macro which is for making new sheets. once sheets are created, you can now double click any row, a userform appears asking where you want to transfer this row (to which sheet) & (to which location). I want when it is double clicked and the row is sent to the created sheet, it should allow undo of the transferred row.


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Sh.CustomProperties.Count = 0 Then frmTransfer.Show
End Sub

---------------------------------------------------------------------------------
Option Explicit

Private Sub cmdCopy_click_Click()
Dim i As Long, strSheetName As String, shtOriginalSheet As Worksheet

If ListBox1.ListIndex = -1 Then
MsgBox "You have to select an item in the list"
Exit Sub
End If

strSheetName = ListBox1.List(ListBox1.ListIndex)

Select Case MsgBox("Do you want to transfer this row after the Last Used Row?", vbYesNoCancel)

Case vbYes

i = Worksheets(strSheetName).Range("d65536").End(xlUp).Row + 1


Case vbNo
Set shtOriginalSheet = ActiveSheet
Application.ScreenUpdating = False
Worksheets(strSheetName).Activate
i = ActiveCell.Row
shtOriginalSheet.Activate
Set shtOriginalSheet = Nothing
Application.ScreenUpdating = True

Case vbCancel
Unload Me
Exit Sub

End Select

ActiveSheet.Rows(ActiveCell.Row).EntireRow.Copy Destination:=Worksheets(strSheetName).Rows(i)

Unload Me

End Sub

------------------------------------------------------------------------------

Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
If sht.CustomProperties.Count > 0 Then
If sht.CustomProperties.Item(1).Name = "Sheet" Then Me.ListBox1.AddItem (sht.Name)
End If
Next
End Sub




Best Regards,

S h a n

[si_the_geek]

First of all, when you post code on this site, please use the VBCode tags (or the VB button above the typing area), it makes it much easier to read!

ok.. from what I can see you are basically pasting into the range
Worksheets(strSheetName).Rows(i) (where strSheetName and i are set earlier in the sub).

In order to undo this action you need to store the values of strSheetName and i. The code you have over-writes the existing row in the target location, so I guess you will want to store that too (but I am not sure how you would do it).

As the form is unloaded as part of your macro, you cannot put the Undo code (or variables) in the form, so you will need to put it in a module instead.

'** Code for the module:  **
Public undo_SheetName As String
Public undo_Row As Long
 
Sub undomacro()
'Undo the effects of cmdCopy_click_Click
 
  If (undo_SheetName = "") Or (undo_Row < 1 Or undo_Row > 65536) Then
    MsgBox "Sorry, couldnt find the pasted row.", vbExclamation
  Else
            'We have valid sheet/row, so undo!
    Worksheets(undo_SheetName).Rows(undo_Row).Clear
    'Instead of a .Clear you could reset the old values (if you store them in your macro!)
  End If
 
End Sub
 
 
'** Changes to your macro:  **
...
  End Select
 
     'Store undo info
  undo_SheetName = strSheetName
  undo_Row = i
  'you could (somehow) store the current values of the target row here
 
  ActiveSheet.Rows(ActiveCell.Row).EntireRow.Copy Destination:=Worksheets(strSheetName).Rows(i)
 
     'enable Undo
   Application.OnUndo "Undo the macro", "undomacro"
 
  Unload Me
...