Undo operation on a macro VBA

[zazass8]

Hello,

I have already created a macro to do some operations on a spreadsheet, and I also want to be able bring this spreadsheet back to its original format and restore all changes (UNDO).

I do know, that in Excel you can't undo a Macro directly so probably I was thinking if there's an alternative solution that I can write code to create this Undo Macro.

I was initially thinking of "reverse engineering" the original code from the first Macro.

Although, somewhere online I found an alternative solution that does it in a way where it stores the original spreadsheet in a variable, then by calling a Boolean if it's true to return the variable that we have stored our original spreadsheet. And this was coded in the code of our original Macro. But I am not sure on how to use it within the first macro, or if it's necessary to create a separate macro to complete my task. If this is the case, do I have to store the variable of the spreadsheet as a global variable?

What should I do about this?

 

[Michael M]

I'd suggest making a copy of the original sheet in your current macro.
Then, Your "UNDO" macro could simply open the original AND delete to latest copy.

 

[MCLIFTO8]

Yes you would modify the second macro to take an argument.
You would call it twice, once at the beginning of macro 1 to back up the sheet and once when you need to restore it.

It would look something like this:
Macro1 0 'backup sheet
Macro1 1 'Restore sheet

Sub statement (VBA)

Office VBA reference topic

learn.microsoft.com

Here's a small function I made that copies your active sheet, hides it and restores it to illustrate an example.

Sub backupSheet(strSheetName As String, boolBackup As Boolean)
    'simple macro to copy a sheet, rename it and hide it.
    'also retores the original sheet
    'call this with
    'backupSheet "Sheet1", 1
    'to backup and
    'backupSheet "Sheet1", 0
    'to restore
    
    Dim strNewSheetName
    
    Application.DisplayAlerts = False
    On Error GoTo ErrB
    strNewSheetName = strSheetName & "_original"
    If boolBackup Then
    
        'backup sheet
        ThisWorkbook.Sheets(strSheetName).Copy Before:=Sheets(1)
        On Error GoTo ErrB
        Sheets(1).Name = strNewSheetName
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
        
    Else
        'restore sheet
        On Error GoTo ErrR
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetVisible
        ThisWorkbook.Sheets(strSheetName).Delete
        ThisWorkbook.Sheets(strNewSheetName).Name = strSheetName
        Application.DisplayAlerts = True
        Exit Sub
    End If
ErrB:
    ThisWorkbook.Sheets(strNewSheetName).Delete
    Sheets(1).Name = strNewSheetName
    ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
    Exit Sub
ErrR:
    Application.DisplayAlerts = True
    MsgBox "Cannot restore sheet that has not been backed up", vbExclamation, "Warning"
End Sub

'You don't need these subs they're just here to illustrate how you would call them in your macro. 
Sub backup()
    backupSheet "Sheet1", 1
End Sub

Sub restore()
    backupSheet "Sheet1", 0
End Sub

 

[zazass8]

I'd suggest making a copy of the original sheet in your current macro.
Then, Your "UNDO" macro could simply open the original AND delete to latest copy.

Can you please clarify how I would have written this? I will create a copy of the original sheet and then call it on the undo macro.. but which one do you mean I should delete afterwards?

 

[MCLIFTO8]

Can you please clarify how I would have written this? I will create a copy of the original sheet and then call it on the undo macro.. but which one do you mean I should delete afterwards?

I've already written you some VBA and included an example on how to use it if you would like to use mine.
Just place my code in the same module as your macro and "backupSheet "Sheet1", 1" wherever you want to call the backup code in your main macro and "backupSheet "Sheet1", 0" wherever you want to restore it.

The code works as follows:
Backup: Choose the sheet you want to back up and it will copy the sheet and add "_original" to it's name. It will also hide the sheet.
Restore: Choose the sheet you want to restore and it will delete the sheet you were just working on. Then it will rename your backed up sheet to it's original name and unhide it.

 

[zazass8]

Yes you would modify the second macro to take an argument.
You would call it twice, once at the beginning of macro 1 to back up the sheet and once when you need to restore it.

It would look something like this:
Macro1 0 'backup sheet
Macro1 1 'Restore sheet

Sub statement (VBA)

Office VBA reference topic

learn.microsoft.com

Here's a small function I made that copies your active sheet, hides it and restores it to illustrate an example.

Sub backupSheet(strSheetName As String, boolBackup As Boolean)
    'simple macro to copy a sheet, rename it and hide it.
    'also retores the original sheet
    'call this with
    'backupSheet "Sheet1", 1
    'to backup and
    'backupSheet "Sheet1", 0
    'to restore
   
    Dim strNewSheetName
   
    Application.DisplayAlerts = False
    On Error GoTo ErrB
    strNewSheetName = strSheetName & "_original"
    If boolBackup Then
   
        'backup sheet
        ThisWorkbook.Sheets(strSheetName).Copy Before:=Sheets(1)
        On Error GoTo ErrB
        Sheets(1).Name = strNewSheetName
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
       
    Else
        'restore sheet
        On Error GoTo ErrR
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetVisible
        ThisWorkbook.Sheets(strSheetName).Delete
        ThisWorkbook.Sheets(strNewSheetName).Name = strSheetName
        Application.DisplayAlerts = True
        Exit Sub
    End If
ErrB:
    ThisWorkbook.Sheets(strNewSheetName).Delete
    Sheets(1).Name = strNewSheetName
    ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
    Exit Sub
ErrR:
    Application.DisplayAlerts = True
    MsgBox "Cannot restore sheet that has not been backed up", vbExclamation, "Warning"
End Sub

'You don't need these subs they're just here to illustrate how you would call them in your macro.
Sub backup()
    backupSheet "Sheet1", 1
End Sub

Sub restore()
    backupSheet "Sheet1", 0
End Sub

I don't really understand how you assigned the xlSheetHidden and xlSheetVisible variables the other way around in your code. And I can't understand how ErrB and ErrR contribute.

 

[zazass8]

I've already written you some VBA and included an example on how to use it if you would like to use mine.
Just place my code in the same module as your macro and "backupSheet "Sheet1", 1" wherever you want to call the backup code in your main macro and "backupSheet "Sheet1", 0" wherever you want to restore it.

The code works as follows:
Backup: Choose the sheet you want to back up and it will copy the sheet and add "_original" to it's name. It will also hide the sheet.
Restore: Choose the sheet you want to restore and it will delete the sheet you were just working on. Then it will rename your backed up sheet to it's original name and unhide it.

So this code would work perfectly even for my case, there's no need to modify it.. Also, if I will put everything in the same module, how would I be able to create a button separetely to do the undo operation? After I code it, how would I be able to utilise it within the Excel sheet?

 

[MCLIFTO8]

So this code would work perfectly even for my case, there's no need to modify it.. Also, if I will put everything in the same module, how would I be able to create a button separetely to do the undo operation? After I code it, how would I be able to utilise it within the Excel sheet?

Yes this code would work perfectly even for your case, there's no need to modify it.

You can put everything in the same module, however if you want separate buttons for saving and backing up you need to call the operations in separate subs and assign those subs to separate buttons. The bottom of my example illustrates this:

Sub backup()
    backupSheet "Sheet1", 1
End Sub

Sub restore()
    backupSheet "Sheet1", 0
End Sub

So all you need to do is copy my example into the same module as your code.
Create 2 shapes
Assign macro on shape 1 to 'Backup'
Assign shape on macro 2 to 'Restore"

 

[zazass8]

Yes this code would work perfectly even for your case, there's no need to modify it.

You can put everything in the same module, however if you want separate buttons for saving and backing up you need to call the operations in separate subs and assign those subs to separate buttons. The bottom of my example illustrates this:

Sub backup()
    backupSheet "Sheet1", 1
End Sub

Sub restore()
    backupSheet "Sheet1", 0
End Sub

So all you need to do is copy my example into the same module as your code.
Create 2 shapes
Assign macro on shape 1 to 'Backup'
Assign shape on macro 2 to 'Restore

 

[zazass8]

Okay thanks very much! I will try it and let you know how it goes.