Any way to preserve "Undo"?

[mrMadCat]

As I read all over the Internet running a macro clears all undo history, which as sad as understandable. But, is there a way to get around this at least for my situation?

I have a macro that stores info about user sessions in excel file. This info is put on a separate sheet. May be there is a way to ignore this sheet in the undo history of the file but preserve it for other sheets?
Finally it can be even OK to put this info into separate file but to preserve the undo functionality. Is there any way?

If not - all this macro is useless because it will spoil users' work experience with the file and I'm going to cry after spending 2 days into making it. ;(

 

[mole999]

I use this (or a form of) on a sheet I choose to hide from most

In the ThisWorkBook Sheet

Private Sub Workbook_Open()
    On Error Resume Next
    'Sheets("risk register").Range("C2").Select
    'ActiveWindow.FreezePanes = True
    Sheets("risk register").Range("C2").FreezePanes = True
    Sheets("tracker").Visible = xlVeryHidden
    Dim Lastrow As Long
    Lastrow = Sheets("tracker").Range("A100000").End(xlUp).Row
    Lastrow = Lastrow + 1
    With Sheets("tracker")
        .Range("A" & Lastrow) = Now()
        .Range("B" & Lastrow) = Environ("USERNAME")
        .Range("C" & Lastrow) = Environ("COMPUTERNAME")
        .Range("D" & Lastrow) = Environ("LOGONSERVER")
        .Range("E" & Lastrow) = Environ("USERDNSDOMAIN")
        .Range("F" & Lastrow) = "=TRIM(RIGHT(SUBSTITUTE(TRIM('Risk Register'!R1), "" "", REPT("" "", LEN(TRIM('Risk Register'!R1)))), LEN(TRIM('Risk Register'!R1))))"
    End With
End Sub

In the sheet I am watching changes to

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Lastrow As Long
    Dim vOldVal
    Application.EnableEvents = False
    Application.Undo
    vOldVal = Target.Value
    Application.Undo
    Application.EnableEvents = True
    On Error Resume Next
    Lastrow = Sheets("tracker").Range("A100000").End(xlUp).Row + 1
    ActiveWorkbook.Sheets("Tracker").Cells(Lastrow, 1) = "Data Input " & ActiveCell.Address & " changed to: " & Target.Value
    ActiveWorkbook.Sheets("Tracker").Cells(Lastrow + 1, 1) = "Data Change " & ActiveCell.Address & " changed from: " & vOldVal
End Sub

As UNDO (I believe) monitors user changes, these codes aren't, so a change and undo are captured as on-going changes and don't get UNDO applied as its been done by the programme

 

[mrMadCat]

mole999, Thank you for your answer. I tried your code but only 1 undo operation is available after running the macro. I did something wrong? You really can see all the undo history after running this macro?

 

[mole999]

This will capture the changes, it doesn't give the ability to undo undo undo, but you can see what was changed, when and who was logged onto the machine. I deliberately use a sheet called tracker that when the book opens is assigned Sheets("tracker").Visible = xlVeryHidden, can be recovered via the vba window and setting to visible (can't be seen from the tab bar for unhide)

Undo would be visible on the tracked sheet where a cell was changed and then changed back., nothing more technical than that

 

[mrMadCat]

The thing is I don't need to capture changes by the macro. It is living separately, and session log is living with it. Let if be without undo possibility, it's ok.
What I need is make it not to delete other sheets' undo stock. Hoped that making the log sheet verryhidden can make it be separate, but no. Looks not possible.