Microsoft Office Tutorials and References
In Depth Information
Another popular use of CheckBoxes is to
provide the user with several options at the
same time. Figure 19-10 shows a UserForm
that asks for users to indicate which regions a
company report should include. When the OK
button is clicked, you can assign variables to
each CheckBox that was checked, and
incorporate those variables later in a VBA decision
process that recognizes only the checked
regions. One way to accomplish that is to loop
through each CheckBox and identify the
selected CheckBox(es), as shown in the
Private Sub cmdOK_Click()
‘Declare an Integer type variable for the five CheckBoxes.
Dim intCheckBox As Integer
‘Declare a String type variable for the list of selected Checkboxes.
Dim strCheckBoxNames As String
‘Open a For next loop to examine each of the 5 CheckBoxes.
For intCheckBox = 1 To 5
‘If the CheckBox is selected, meaning its value is True,
‘build the strCheckBoxNames string with the caption of the
‘selected CheckBox, followed by a Chr(10) new line character
‘for readability in the confirming MsgBox.
If Controls(“CheckBox” & intCheckBox).Value = True Then
strCheckBoxNames = strCheckBoxNames & _
Controls(“CheckBox” & intCheckBox).Caption & Chr(10)
‘Continue the loop until all 5 CheckBoxes have been examined.
‘Display a Message Box to advise the users what they selected.
MsgBox strCheckBoxNames, , “Regions that were checked:”
An OptionButton is used when you want the user to select one choice from a group of optional
choices. You would use a group of OptionButtons to show the single item that was selected among
the group’s set of choices. For example, on a college application form, in the gender section, an
applicant could select only Male or Female.
In Figure 19-11, a menu for running a financial report might ask the user to select the month of
activity upon which the report should be based. A group of 12 OptionButtons limits the user to only
one selection. Each OptionButton’s caption property was filled in with the name of a month.