Excel 2003 - Multiple Undo and redo of Macro actions

[Vijaya Bhaskara Prasanna]

Hi! Seasons Greetings!
In MS-excel 2003, I have written macros for different actions on a work sheet. For every action I am storing the previous and current values of the affected range in an array for a later undo and/or redo of the macro actions. At the end of every macro that are invoked by an event on the sheet I am invoking the Application.Onundo method like

Application.Onundo "undo .... action", "undo_procedure<UNDO name procedure>"

to enable the Edit-> undo command and it works fine.


And at the end of <UNDO procedure>I am calling same method like

Application.Onundo "undo .... action", "undo_procedure<UNDO name procedure>"

to enable the Edit-> undo command for the second time so that I can undo the previous actions of macros but it doesn't work. The Edit -undo command is not enabled again. Could anyone help me here please?

Also I would like to know how to enable the Edit-> redo command.

P.S.: The procedure, undo_procedure, <UNDO procedure>reads the array to restore the old values in the affected range and writes them to the respective places.

Thanks,
Vijaya.

 

[Commander238]

<undo procedure="">Hi,

This is a good question

I solved the same problem today. I found a little trick using Application.OnTime. Now you can use Undo repeatedly.
I am using Excel 2003.
As far as I know, it is not possible to enable the Redo button, but you can enable the repeat button - at the top of the Edit menu.
Put the code inside ThisWorkbook module.
Here is my working solution:

Dim Undos As New Collection


Sub Change()
  ' push previous cell values to the end of your undo array
  Undos.Add ActiveCell.Value
  ' change the cell values as you wish
  ActiveCell.Value = "(" + ActiveCell.Value + ")"
  
  PlanUndo
  PlanRepeat
End Sub


Sub Undo()
  ' make sure the undo array is not empty
  If (Undos.Count > 0) Then
    ' pop previous cell values from the end of your undo array
    Dim Value
    Value = Undos.Item(Undos.Count)
    Undos.Remove Undos.Count
    ' revert the cell values
    ActiveCell.Value = Value
  End If
  
  If (Undos.Count > 0) Then
    PlanUndo
  End If
  PlanRepeat
End Sub


Function PlanUndo()
  Application.OnTime Now, "ThisWorkbook.SetUndo"
End Function


Sub SetUndo()
  Application.OnUndo "Undo last change", "ThisWorkbook.Undo"
End Sub


Function PlanRepeat()
  Application.OnTime Now, "ThisWorkbook.SetRepeat"
End Function


Sub SetRepeat()
  Application.OnRepeat "Repeat last change", "ThisWorkbook.Change"
End Sub

</undo>