Undo & ErrorHandler

[Otto Moehrbach]

Excel 2002, Win XP
I have noticed in these NGs that when an Undo command is issued (in
code) that an Error Handler routine is often used. That is, a statement
like:
On Error GoTo ErrHandler
is put before the Undo command.
Or something like:
If Sheets(xlsName).CanUndo Then.....
I can understand doing this if the Undo capability is in question.
My question regards the need for the above in the specific situation where
the code containing the Undo command is triggered by a Worksheet_Change
event. When and why would the Undo not be available in this situation?
Thanks for your help. Otto

 

[Tom Ogilvy]

Undo is almost always at risk in a macro. Many macro commands clear the
undo history.

I rarely see undo code in this workgroup and I don't recall seeing it
encased in error handlers as a noteworthy thing.

If you do it immediately in a change event, it should work. I did a search
on canundo in the newsgroup and your cite for it is the only one I found.
As far as I know, there no such command as canundo. As to my use of an
error handler it is to ensure events are enabled if an error does occur
regardless of the source. Has nothing to do with undo specifically.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If Not Intersect(Target, Range("B9:F20")) Is Nothing Then
vVal = Target.Value
Application.EnableEvents = False
Application.Undo
vVal1 = Target.Value
res = MsgBox("Do you want to replace " & vVal1 & _
vbNewLine & "with " & vVal, vbQuestion + vbYesNo)
If res = vbYes Then
Target.Value = vVal
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Regards,
Tom Ogilvy

 

[Otto Moehrbach]

Tom
As always, you took the time to explain and I appreciate that. I now
have a better understanding of why the ErrHandler should be used. The
CanUndo code was in my HowTo file notes and I had to have gotten it from the
NGs. I'll do a quick validation check of it and straighten out my notes.
Thanks again for your help. Otto