Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
The PROGRESSTHRESHOLD constant specifies the number of cells that will display the progress
indicator. When this constant is 2,000 , the progress indicator will be shown only if the utility is
working on 2,000 or more cells.
The UserChoices array holds the value of each control. This information is stored in the
Windows Registry when the user closes the dialog box and is retrieved when the utility is
executed again. I added this convenience feature because I found that many users tend to perform
the same operation every time they use the utility.
Two other Range object variables are used to store information used for undoing.
The ShowTextToolsDialog procedure in the Module1 VBA module
The ShowTextToolsDialog procedure follows:
Dim InvalidContext As Boolean
If Val(Application.Version) < 12 Then
MsgBox “This utility requires Excel 2007 or later.”, vbCritical
If ActiveSheet Is Nothing Then InvalidContext = True
If TypeName(ActiveSheet) <> “Worksheet” Then InvalidContext = True
If InvalidContext Then
MsgBox “Select some cells in a range.”, vbCritical, APPNAME
The procedure starts by checking the version of Excel. If the version is prior to Excel 2007, the
user is informed that the utility requires Excel 2007 or later.
You can certainly design this utility so that it also works with previous versions. For
simplicity, I made this utility an application for Excel 2007 or later.
If the user is running the appropriate version, the ShowTextToolsDialog procedure checks to
make sure that a sheet is active, and then it makes sure that the sheet is a worksheet. If either
one isn’t true, the InvalidContext variable is set to True . The If-Then-Else construct
checks this variable and displays either a message (see Figure 16-4) or the UserForm. Notice that
the Show method uses the vbModeless argument, which makes it a modeless UserForm (that
is, the user can keep working in Excel while it’s displayed).
Notice that the code doesn’t ensure that a range is selected. This additional error handling is
included in the code that’s executed when the Apply button is clicked.