Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
To reactivate the original active sheet after the sorting was performed, I wrote code that
assigned the original sheet to an object variable ( OldActiveSheet ) and then activated that
sheet when the routine was finished. Here’s the statement that assigns the variable:
Set OldActive = ActiveSheet
This statement activates the original active worksheet:
Pressing Ctrl+Break normally halts a macro, and VBA usually displays an error message. But
because one of my goals was to avoid VBA error messages, I inserted a command to prevent this
situation. From the VBA Help system, I discovered that the Application object has an
EnableCancelKey property that can disable Ctrl+Break. So I added the following statement at
the top of the routine:
Application.EnableCancelKey = xlDisabled
Be very careful when you disable the Cancel key. If your code gets caught in an infinite
loop, you can’t break out of it. For best results, insert this statement only after you’re
sure that everything is working properly.
To prevent the problem of accidentally sorting the sheets, I added the following statement to the
procedure, before the Ctrl+Break key is disabled:
If MsgBox(“Sort the sheets in the active workbook?”, _
vbQuestion + vbYesNo) <> vbYes Then Exit Sub
When the user executes the SortSheets procedure, he sees the message box in Figure 9-12.
Figure 9-12: This message box appears before the sheets are sorted.