The Module VBA module

The Module1 VBA module contains the declarations, a simple procedure that kicks off the utility, and a procedure that handles the undo operation.


Following are the declarations at the top of the Module1 module:

Public Const APPNAME As String = "Text Tools Utility" Public Const PROGRESSTHRESHOLD = 2000

Public UserChoices(1 To 8) As Variant 'stores user's last choices Public UndoRange As Range ' For undoing Public UserSelection As Range 'For undoing

I declare a Public constant containing a string that stores the name of the application. This string is used in the UserForm caption and in various message boxes.

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. This is a convenience feature I added 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 follows:

Sub ShowTextToolsDialog()

Dim InvalidContext As Boolean If Val(Application.Version) < 12 Then

MsgBox "This utility requires Excel 2007 or later.", vbCritical Exit Sub End If

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


UserForm1.Show vbModeless End If End Sub

As you can see, it's rather simple. 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.


It's certainly possible to design this utility so it also works with previous versions. For simplicity, I made this an Excel 2007-only application.

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 of these is not 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 is displayed).

Figure 16-4: This message is displayed if no workbook is active or if the active sheet is not a worksheet.

Notice that the code does not ensure that a range is selected. This additional error handling is included in the code that's executed when the Apply button is clicked.

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 the shortcut key combination.


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 ").

0 0

Post a comment