Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
‘ Move the sheets
For i = 1 To SheetCount
‘ Reactivate the original active sheet
Because the SortSheets macro is stored in the Personal Macro Workbook, it’s available
whenever Excel is running. At this point, you can execute the macro by selecting the macro’s name
from the Macro dialog box (Alt+F8 displays this dialog box) or by pressing Ctrl+Shift+S. Another
option is to add a command to the Ribbon.
To add a command, follow these steps:
Right-click any area of the Ribbon and choose Customize the Ribbon.
In the Customize Ribbon tab of the Excel Options dialog box, choose Macros from the
drop-down list labeled Choose Commands From.
Click the item labeled PERSONAL.XLSB!SortSheets.
Use the controls in the box on the right to specify the ribbon tab and create a new group.
(You can’t add a command to an existing group.)
I created a group named Worksheets in the View tab, and renamed the new item to Short Sheets
(see Figure 9-13).
Evaluating the project
So there you have it. The utility meets all the original project requirements: It sorts all sheets in
the active workbook, it can be executed easily, it’s always available, it seems to work for any
workbook, and I have yet to see it display a VBA error message.
The procedure still has one slight problem: The sorting is strict and may not always be
“logical.” For example, after sorting, Sheet10 is placed before Sheet2 . Most would
want Sheet2 to be listed before Sheet10 . Solving that problem is the beyond the
scope of this introductory exercise.