Excel 2010 Bible

Chapter 41: Creating UserForms

853

Creating event-handler procedures

This section explains how to create two event-handler procedures: one to handle the Click event

for the CancelButton CommandButton and the other to handle the Click event for the

OKButton CommandButton. Event handlers for the OptionButton controls are not necessary. The

VBA code can determine which of the three OptionButton controls is selected.

Event-handler procedures are stored in the UserForm code module. To create the procedure to

handle the Click event for the CancelButton, follow these steps:

1. Activate the UserForm1 form by double-clicking its name in the Project window.


2. Double-click the CancelButton control. The VB Editor activates the code module for
   the UserForm and inserts an empty procedure.


3. Insert the following statement before the End Sub statement:

Unload UserForm1

That’s all there is to it. The following is a listing of the entire procedure that’s attached to the

Click event for the CancelButton:

Private Sub CancelButton_Click()

Unload UserForm1

End Sub

This procedure is executed when the CancelButton is clicked. It consists of a single statement that

unloads the UserForm1 form.

The next step is to add the code to handle the Click event for the OKButton control. Follow

these steps:

1. Select OKButton from the drop-down list at the top of the module. The VB Editor
   begins a new procedure called OKButton_Click.


2. Enter the following code. The first and last statements have already been entered for
   you by the VB Editor.
   Private Sub OKButton_Click()
   Application.ScreenUpdating = False
   ‘ Exit if a range is not selected
   If TypeName(Selection) <> “Range” Then Exit Sub
   ‘ Upper case
   If OptionUpper Then
   For Each cell In Selection
   If Not cell.HasFormula Then
   cell.Value = StrConv(cell.Value, vbUpperCase)