Adding eventhandler procedures

Here's how to add an event-handler procedure for the Cancel and OK buttons:

1. Double-click the Cancel button.

VBE activates the Code window for the UserForm and inserts an empty procedure:

Private Sub CancelButton_Click()

The procedure named CancelButton_Click is executed when the Cancel button is clicked, but only when the dialog box is displayed. In other words, clicking the Cancel button when you're designing the dialog box won't execute the procedure. Because the Cancel button's Cancel property is set to True, pressing Esc also triggers the CancelButton_Click procedure.

2. Insert the following statement inside the procedure (before the End Sub statement):

Unload UserForml

This statement simply closes the UserForm when the Cancel button is clicked.

3. Press Shift+F7 to return to the UserForm.

4. Double-click the OK button.

VBE activates the code window for the UserForm and inserts an empty Sub procedure called

Private Sub OKButton_Click()

Clicking OK executes this procedure. Because this button has its Default property set to True, pressing Enter also executes the OKButton_Click procedure.

5. Enter the following code inside the procedure:

Private Sub OKButton_Click()

Dim cell As Range

' Uppercase

If OptionUpper Then

For Each cell In Selection

cell.Value = UCase(cell

.Value)

Next cell

End If

' Lowercase

If OptionLower Then

For Each cell In Selection

cell.Value = LCase(cell

.Value)

Next cell

End If

' Proper case

If OptionProper Then

For Each cell In Selection

cell.Value = _

Application.WorksheetFunction.Proper _

(cell.Value)

Next cell

End If

' Unload the dialog box

Unload UserForml

End Sub

The preceding code is an enhanced version of the original ChangeCase macro that I presented at the beginning of the chapter. The macro consists of three separate blocks of code. This code uses three If-Then structures; each one has a For Each loop. Only one block is executed, according to which OptionButton the user selects. The last statement unloads (closes) the dialog box after the work is finished.

Notice that VBA has a UCase function and an LCase function, but not a function to convert text to proper case. Therefore, I use Excel's PROPER worksheet function (preceded by Application.WorksheetFunction) to do the actual conversion. Another option is to use the VBA StrConv function. (See the Help system for details.) The StrConv function is not available in all Excel versions, so I used the PROPER worksheet function instead.

Was this article helpful?

0 0

Post a comment