How to pass Argument to an Undo handler in VBA

Question

Hello!

My first attempt at creating an Excel VBA.

The macros below essentially change the background on the interior of a cell-range.

GenerateMarkerOnSheet sets interior to black. ResetMarkerOnSheet is meant to roll-back the above change on Undo.

Is it possible to pass the previous interior as an argument to the Undo hander (ResetMarkerOnSheet) via Application.OnUndo?

If so, How should I go about it?

Sub GenerateMarkerOnSheet()
'
' GenerateMarkerOnSheet Macro
' Macro recorded 29/01/2010 by Everyone
'

'

StartIndex = 99

RangeGap = 100

StartCell = "A"

EndCell = "BU"

PreviousBackground = 1

Do While StartIndex < 65536
    For Each c In Worksheets(ActiveSheet.Name).Range(StartCell & StartIndex & ":" & EndCell & StartIndex)
        If PreviousBackground = 1 Then
            PreviousBackground = c.Interior.ColorIndex
        End If
        c.Interior.Color = RGB(0, 0, 0)
    Next
    StartIndex = StartIndex + RangeGap
Loop
' How to pass PreviousBackground to the call below
Application.OnUndo "Undoing", "ResetMarkerOnSheet"


End Sub


Sub ResetMarkerOnSheet()
'
' ResetMarkerOnSheet Macro
' Macro recorded 29/01/2010 by Everyone
'

'

StartIndex = 99

RangeGap = 100

StartCell = "A"

EndCell = "BU"

Do While StartIndex < 65536
    For Each c In Worksheets(ActiveSheet.Name).Range(StartCell & StartIndex & ":" & EndCell & StartIndex)
        c.Interior.ColorIndex = PreviousBackground
    Next
    StartIndex = StartIndex + RangeGap
Loop


End Sub

Answer 1

I don't believe it is possible to undo actions that have been executed through VBA code. For example, if I manually overtype a value in a cell, then Edit > Undo will allow me to go back to the previous value. However if I use code that changes the value (Range("a1") = "New Value"), then I cannot Undo.

What you might consider doing is adding code to your GenerateMarkerOnSheet code that stores the addresses or ranges of the cells affected each time you run it. You could perhaps store the addresses/ranges in a hidden worksheet or in a named range.

Then your ResetMarkerOnSheet routine would just change the background for the addresses/ranges stored.

Depending on how you code it, you could store multiple levels of "Undo".