Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Undo & ErrorHandler
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Undo & ErrorHandler
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Undo & ErrorHandler
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 "Tom Ogilvy" wrote in message ... 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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Instead of one by one Undo | Excel Discussion (Misc queries) | |||
Undo | Excel Discussion (Misc queries) | |||
can't undo | Excel Discussion (Misc queries) | |||
UNDO - how many times can I UNDO? | Excel Worksheet Functions | |||
Why is my undo function in Excel only can undo the last 1 or 2 ch. | Excel Worksheet Functions |