Assing key to undo all changes at once

[Lux Aeterna]

Hey everyone!

I have created an excel in which I insert blood exam results. I insert every patient manually.

What I want to do is to assign a key (for example F5) to undo all changes made to the sheet at once. I know I can do it using undo button, but I'd like to have a specific key for that.

 

[AFPathfinder]

VBA can only undo the last change by the user. However, if you understand the nature of the undo, you can mimic the action by planning the interval of changes, normally by using the Save option when you want to commit to your changes. If you make a bunch of changes you need to undo, you can just exit the file without saving and reopen.

Another option would be to copy the sheet as a save-point to which you can revert. This can be programmatic, but you'd need to control when to commit to those changes.

 

[Lux Aeterna]

Thank you for your answer!

The second solution seems to suit me better. To be precise, I need to revert to the original sheet just after I have printed my data (so I can enter new data, print again and so on). How can I do it?

 

[AFPathfinder]

Make sure you test this out with a copy of your workbooks as these aren't tested and will delete sheets. The solution is two pronged, one macro to save your backup when the workbook is printed (should work on printing anything within the workbook) and another for when you want to revert back to the last backup.

The first macro should work automatically once placed in your "ThisWorkbook" object and the other can be attached to a button or keyboard shortcut (F5 is already taken by Excel, so it would usually be a Ctrl+Shift+[n] type of shortcut).

Workbook_BeforePrint macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim oWs As Worksheet
    
    Set oWs = ThisWorkbook.ActiveSheet
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Sheets("Backup").Delete
    On Error GoTo 0
    
    oWs.Copy After:=Sheets.Count
    ActiveSheet.Name = "Backup"
    oWs.Activate
    
    Application.DisplayAlerts = True

    Set oWs = Nothing
End Sub

Undo Changes macro:

Sub UndoSheetChanges()
    Dim strSheetName As String
    Dim x As Integer
    
    strSheetName = ActiveSheet.Name
    x = ActiveSheet.Index - 1
    
    On Error GoTo NoBackup
    Sheets("Backup").Copy After:=x
    On Error GoTo 0
    
    Sheets(strSheetName).Activate
    
    Application.DisplayAlerts = False
    
    ActiveSheet.Delete
    Sheets("Backup (2)").Name = strSheetName
    
NoBackup:
    Application.DisplayAlerts = True
End Sub

 

[Lux Aeterna]

Thanks a lot. I'll try them as soon as I can and I'll let you know!