VBA detect when the user has performed an Undo or a Redo

Question

Is there a way in VBA, in the Worksheet Change event, to know that the user has done an Undo or a Redo and more specifically a Redo. Bearing in mind that <Ctrl + z> and <Ctrl + y> can be also used.

I have used a method comparing the opposite evolution of both Undo and Redo stack heights which works fine EXCEPT for 1 irreducible case:

When the Redo stack height goes from 1 to 0 it can be for 2 reasons:

• Either the user has performed the last Redo
• or the user has performed a simple change in the Worksheet that has emptied the Redo stack.

I haven't found anything to distinguish these 2 cases.

.OnAction does not work for Builtin CommandBar Controls and no other property provides useful info.

The 2 CommandBar Controls used are:

1. Undo -> Application.CommandBars("Standard").FindControl(ID:=128)
2. Redo -> Application.CommandBars("Standard").FindControl(ID:=129)

The stack height count comes with the property .ListCount (protect with On Error if 0)

Thanks in advance for any clue.

Answer 1

I have also tried Application.OnUndo but it's completely unusable. It requires a text, kills the stack and an Application.Undo in the procedure generates an error. And anyway Application.OnRepeat does not seem to be the counterpart of Application.OnUndo. It only reacts to a <Ctrl + y>, does not display the text (!) and blocks the Redo button and a VBA Redo (Application.CommandBars("Standard").FindControl(ID:=129).Execute)