Page 281 - Excel Progamming Weekend Crash Course

n540629 ch20.qxd 9/2/03 9:35 AM Page 256

256 Saturday Evening

Figure 20-1 Making a selection from a ComboBox

The CommandButton Control
The CommandButton control displays a button on a form. The main use for this control is
to carry out some action when the user clicks the button. To perform an action, put the
required code in the Click event procedure for the control. An example of this was pre-
sented earlier in this session in the section on the ComboBox control.
It is sometimes desirable to have default and cancel buttons on a form. The default but-
ton is automatically selected (as if it had been clicked) when the user presses Enter (as long
as no other command button has the focus), and the cancel button is automatically selected
when the user presses Esc.

To make a command button the default, set its Default property to True.
To make a command button the cancel button, set its Cancel property to True.
A user form can have only one default button and one cancel button on it.
In many cases, a dialog box has a command button with the caption OK or something
similar. This button closes the form and tells the calling program to accept the data from
the form. Likewise, the cancel button (usually with the Cancel caption) also closes the form,
but tells the program to ignore data from the form. But how does the calling program know
which button was selected to close the form?
The trick lies in creating a global Boolean variable within the form. This is a variable
declared outside of any event procedure, and is therefore accessible to code outside the
form. To create such a variable, open the code-editing window for the user form and then
select (General) in the list at the top left of the window. Enter the variable declaration,
using the Public keyword in place of Dim:

Public Cancelled As Boolean
Here’s how to use this variable:

1. Code in the Click event procedure for the form’s OK button sets this variable to
False and then hides the form.

2. Code in the Click event procedure for the form’s Cancel button sets this variable to
True and then hides the form.
3. Code in the calling program checks the value of this variable and retrieves data
from the form only if it is False. If it is True, data from the form is ignored.

The following program demonstrates this technique. Here are the steps required: