Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
Installing an add-in
To install an add-in, including the text tools.xlam add-in, follow these steps:
1. Choose File➜ Options.
2. In the Excel Options dialog box, click the Add-Ins tab.
3. In the drop-down list labeled Manage, select Excel Add-Ins and then click Go to display the
Add-Ins dialog box.
4. If the add-in that you want to install is listed in the Add-Ins Available list, place a check
mark next to the item.
If the add-in isn’t listed, click Browse to locate the XLAM or XLA add-in file.
5. Click OK, and the add-in will be installed.
It will remain installed until you deselect it from the list.
In the preceding instructions, you can skip Steps 1 through 3 and press Alt+TI, which is the
preExcel 2007 keyboard sequence to display the Add-Ins dialog box.
The file also contains some manual modifications that I made in order to get the
command to display on the Ribbon. See “Adding the RibbonX code,” later in this chapter.
Unfortunately, you can’t modify Excel’s Ribbon using only VBA.
How the Text Tools utility works
The Text Tools add-in contains some RibbonX code that creates a new item in the Ribbon:
Home➜Utilities➜Text Tools. Selecting this item executes the StartTextTools procedure,
which calls the ShowTextToolsDialog procedure.
To find out why this utility requests both the StartTextTools procedure and the
ShowTextToolsDialog procedure, see “Adding the RibbonX code,” later in this
The user can specify various text modifications and click the Apply button to perform them. The
changes are visible in the worksheet, and the dialog box remains displayed. Each operation can
be undone, or the user can perform additional text modifications. Clicking the Help button
displays a Help window, and clicking the Close button dismisses the dialog box. Note that this is a
modeless dialog box. In other words, you can keep working in Excel while the dialog box is
displayed. In that sense, a modeless dialog box is similar to a toolbar.
The UserForm for the Text Tools utility
When I create a utility, I usually begin by designing the user interface. In this case, it’s the dialog
box that’s displayed to the user. Creating the dialog box forces me to think through the project
one more time.