Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
Figure 16-4: This message is displayed if no workbook is active or if the active sheet isn’t a worksheet.
While I was developing this utility, I assigned a keyboard shortcut (Ctrl+Shift+T) to the
ShowTextToolsDialog procedure for testing purposes. That’s because I saved the
Ribbon modification task for last, and I needed a way to test the utility. After I added
the Ribbon button, I removed the keyboard shortcut.
To assign a keyboard shortcut to a macro, press Alt+F8 to display the Macro dialog
box. Type ShowTextToolsDialog in the Macro Name box and then click Options. Use the
Macro Options dialog box to assign (or unassign) the shortcut key combination.
The UndoTextTools procedure in the Module1 VBA module
The UndoTextTools procedure is executed when the user clicks the Undo button (or presses
Ctrl+Z). This technique is explained later in this chapter (see “Implementing Undo”).
The UserForm1 code module
All the real work is done by VBA code contained in the code module for UserForm1 . Here, I
briefly describe each of the procedures in this module. The code is too lengthy to list here, but
you can view it by opening the text tools.xlam file on the companion CD-ROM.
The UserForm_Initialize procedure in the UserForm1 code module
This procedure is executed before the UserForm is displayed. It sizes the UserForm and retrieves
(from the Windows Registry) the previously selected values for the controls. It also adds the list
items to the ComboBox (named ComboBoxOperation ) that determines which operation will be
performed. These items are
h Change case
h Add text
h Remove by position
h Remove spaces
h Delete characters