. 53
( 62 .)


of the mouse. The properties to work with are the following:
Caption: This determines what text is displayed in the
Picture: This allows you to display a graphic image on
the button.
TakeFocusOnClick: When something in Excel is ready to
receive input from a mouse or keyboard, that object has

On-Screen Controls 291

˜˜focus.™™ This property is best set to False for command
buttons, so that other Excel objects can be accessed during a
click of the command button.
Since there is usually a routine associated with the com-
mand button, once the button is set on the sheet and while it
is still in the Design mode, click on the View Code icon in the
Control Toolbox. This will show you the Visual Basic Editor, and
it will show you where the code associated with the command
button is located. Since no code has been written, at the start all
you will see will be the lines:
Private Sub CommandButton1_Click()
[Empty. Macro goes here]
End Sub
CommandButton1 is the default name if this is the first
command button control that you are setting up. The
CommandButton1_Click means that with a click of the mouse
on this button, the macro that appears here will run. If you
have recorded a macro, you can copy that macro into the
marked space.

Option Button
The option button also has the name ˜˜radio button.™™ Like the
check box, an option button gives either a selected or unselected
state. However, unlike the check box, a group of option buttons
can be linked together and work in a mutually exclusive way:
if one option button is selected, then the others automatically
unselect themselves.
By default, all the option buttons on a sheet will work
together in this way. However, you can create different groups
of option buttons so that they are mutually exclusive only within
those groups that you have defined. There is no limit on the
number of option buttons that can work together in a group.
The properties to work with are the following:
Caption: This is the text that appears next to the option

Chapter 17

GroupName: Enter a string to identify which mutually
exclusive group the option button belongs in. By default,
the group name is the name of the worksheet.
Value: TRUE means that it is selected; FALSE means that
it is not.
LinkedCell: This can be a cell address or a range name and
is the cell that holds the state of the option button. Each
option button should have its own LinkedCell. You can then
set your worksheet formulas to follow the setting estab-
lished by the group of option buttons.
If you have three option buttons, what is the best way of
translating the settings of the three LinkedCells into a value that
can be used by, say, an IF formula? One way is to multiply the
value of the first LinkedCell by 1, the second by 2, and the third by
3. Column D below shows the results of doing this. (Column E is
used to illustrate the formulas used in column D.) Remember that
TRUE has a value of 1 and FALSE has a value of 0 and they can be
used in formulas. So in this set of TRUE/FALSE values from the
selection of option button 1, we would have TRUE*1 ¼ 1,FALSE*2
¼ 0, and FALSE*3 ¼ 0. We sum these three calculations columns
and get the value of 1. The selection of Option 2 and Option 3
would give 0 þ 2 þ 0 ¼ 2 and 0 þ 0 þ 3 ¼ 3, respectively. So in this
way, we can use the SUM cell as the driver for any IF formulas that
need to derive their condition from the LinkedCells. This approach
would work for an unlimited number of LinkedCells.

1 LinkedCell1 TRUE 1 1 =B1*C1
2 LinkedCell2 FALSE 2 0 =B2*C2
3 LinkedCell3 FALSE 3 0 =B3*C3
4 1 =SUM(D1:D3)

List Box
The list box control allows you to select an item from a list. The
control can reference a large range of choices, and you can access
the entire list through the arrow buttons that appear on the right
side of the list box. The number of items that appear on the list is
a function of the vertical size for the list box, which can be easily

On-Screen Controls 293

changed by manipulating the sizing handles. The properties to
work with are the following:
ListFillRange: This is a range on the worksheet to which the
list in the list box is referenced. This can be an address or
range name.
LinkedCell: This is the cell that stores the selection from the
list. The selection is also the Value property of the control.
MatchEntry: This determines how you can select from the
list. In addition to using the scroll bar and then selecting
from the list, you can also just type into the list box. The
settings here will allow different ways of selecting the item
you want. Suppose you have the names ˜˜New Jersey,™™
˜˜New Mexico,™™ and ˜˜New York™™ in the list, and you want
to select ˜˜New York.™™ This is what you need to type to get
to ˜˜New York™™ quickly:

Type ˜˜N™™ three times.
Type ˜˜New Y.™™

Combo Box
The combo box is similar to the list box. However, it begins as
only a one-line entry, but with a drop-down list box that is set by
the ListRows property and not just sizing the control like the list
box. Additionally, you can set the combo box to accept an entry
that is not on the list of items linked to the combo box. In effect,
this gives you a way to specify a new item on the fly. However,
that new item remains excluded from the list of items. The
properties to work with are the following:
ListFillRange: This is a range on the worksheet to which the
list in the list box is referenced. This can be an address or
range name.
ListRows: This is the number of items that will appear in
the drop-down list.
LinkedCell: This is the cell that stores the selection from the
list. The selection is also the Value property of the control.

Chapter 17

MatchEntry: This property is the same as for the list box.
Style: Select FmStyleDropDownList and the combo box
will behave in the same way as a list box. Select
FmStyleDropDownCombo for the combo box to accept a
user entry into the control. This is assigned to the Value
property, but does not become part of the list.

Toggle Button
Although the toggle button looks like a command button, it is
most similar in function to the check box. The toggle can show
an on (TRUE) state and an off (FALSE) state. When it is on, the
button appears as if depressed. The properties to work with are
the following:
Caption: This is the text that is displayed on the toggle
Value: Sets or returns the state of the control. TRUE
indicates depressed; FALSE indicates up.
LinkedCell: This is the cell that stores the Value. The
setting here also determines the state of the toggle button.
Here is a little bit of fun in macro writing that you can put
behind a toggle button. This assumes that ToggleButton1 is the
name of the control you are working with:

Private Sub ToggleButton1_Click()
With ToggleButton1
If .Value ¼ True Then
.Caption ¼ “Turn OFF toggle”
.Caption ¼ “Turn ON toggle”
End If
End With
End Sub

We have not gone over any VBA code, so I will just ask you
to enter the above by clicking on the View Code icon while in the
Design mode for this control. When you get out of the Design

On-Screen Controls 295

mode and start clicking on the toggle button, you will see the
caption change, depending on the state.

Spin Button and Scroll Bar
The spin button and scroll bar are similar. They are controls to
give you a quick way to vary values over a particular range. The
spin button is less visually informative, because it is a set of
buttons with up and down arrows. There is no indication of
the range of values that you are working in if you are not already
familiar with it. The scroll bar, however, is a bar with a slider, so
that you can see the range. Moreover, the scroll bar allows you to
adjust the value you want by either manipulating the slider bar,
or by clicking on the end of the scroll bar that you want to move
toward. For both of these, the properties to work with are the
Max: The maximum value. This must be a positive integer
or zero.
Min: The minimum value. This must be a positive integer or
zero. It must be less than the value set in Max.
Value: The current value of the scroll bar or the spinner.
LinkedCell: The cell that stores the Value property.

Label is used for displaying static text, which is specified in the
Caption property.

Image is used to displaying images. You can have the image
appear in this control by going to the Picture property in the
Properties window. Click on that and a small button with three
dots ˜˜. . .™™ appears. That is the ˜˜browse™™ button that will give
you the Explorer to find the image you want to insert.

Chapter 17

Frame is a control that is actually used only on user forms, which
we are not covering. To draw a frame around controls just to
visually group them together, use the group box control in the
old Forms toolbar.


. 53
( 62 .)