UserForm Controls - CheckBox, OptionButton and ToggleButton
UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls on a Worksheet have been illustrated in detail, in the separate section of "Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet".
Also refer "2. UserForm and Controls - Properties" for properties common to the UserForm and most Controls
Note: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise.
--------------------------
Contents:
--------------------------
The Value property of a CheckBox indicates whether it is selected or not. A True value indicates that the CheckBox is selected, False indicates that it is cleared; and the Null value indicates that it is neither selected nor cleared, and the CheckBox will appear shaded in this case. The value of TripleState property should be set (can be set either in the Properties window or by using a macro or vba code) to True for the CheckBox to have a Null value.
Example 1: Display value of CheckBox, indicating if it is selected, cleared or in a Null state
Private Sub UserForm_Initialize()
'set CheckBox properties on activation of UserForm
With Me.CheckBox1
.TextAlign = fmTextAlignCenter
'will enable CheckBox to have three possible values, including the Null value
.TripleState = True
End With
End Sub
Private Sub CheckBox1_Change()
'MsgBox will display value of CheckBox, indicating if it is selected, cleared or in a Null state
If CheckBox1.Value = True Then
MsgBox "True"
ElseIf CheckBox1.Value = False Then
MsgBox "False"
Else
MsgBox "Null"
End If
End Sub
Example 2: User can enter in TextBox only if CheckBox is selected
Private Sub UserForm_Initialize()
'set TextBox properties on activation of UserForm
Me.TextBox1.Enabled = False
End Sub
Private Sub CheckBox1_Click()
'user can enter in TextBox only if CheckBox is selected
If CheckBox1.Value = True Then
TextBox1.Enabled = True
Else
TextBox1.Enabled = False
End If
End Sub
--------------------------------------------------------------------------------------------------------------
OptionButton is used to make one selection from multiple options. It is also referred to as a Radio Button, which chooses one option from a group of mutually exclusive options. If OptionButtons are not grouped, selecting one OptionButton in a UserForm will de-select all other OptionButtons in the form. All OptionsButtons within a specific Group become mutually exclusive and self-contained within that group and do not affect selection of OptionButtons outside that group. Selecting an OptionButton in one group will de-select all other OptionButtons of only that group.
OptionButtons can be grouped by the following methods: (i) using the GroupName property: OptionButtons can first be added to a UserForm and then their GroupName property can be set either in the Properties window or by using a macro or vba code. OptionButtons having the same GroupName become mutually exclusive and selecting one OptionButton will de-select all other OptionButtons of that group; and (ii) using a Frame Control: First add the Frame in a UserForm and then add OptionButtons to the Frame. A Frame can contain other Controls as well. Each Frame will separately group two or more OptionButtons, irrespective of whether all OptionButtons across other Frames, also have the same GroupName. However, it might be preferable to group OptionButtons using the GroupName property than using a Frame only for this purpose, to save space and reduce the UserForm size. Note: A MultiPage control is also a container and is used to group or organize related controls within each of its pages. Each page of a MultiPage control will separately group two or more OptionButtons, irrespective of whether all OptionButtons (across all pages) have the same GroupName.
The Value property of an OptionButton indicates whether it is selected or not. A True value indicates that the OptionButton is selected, False is the default value and indicates that it is not selected.
Examples - Determine selected OptionButton or CheckBoxes within a Frame:
Example 3: Determine which controls (CheckBox & OptionButton) within a Frame, are selected:
Private Sub CommandButton1_Click()
'Determine which Controls within a Frame (containing both OptionButton and CheckBoxes), are selected.
Dim ctrl As Control
For Each ctrl In Frame1.Controls
If ctrl.Value = True Then
MsgBox ctrl.Caption
End If
Next
End Sub
Example 4: Determine which OptionButton within a Frame, is selected:
Private Sub CommandButton1_Click()
'Determine which OptionButton within a Frame (containing both OptionButton and CheckBoxes), is selected.
Dim ctrl As Control
For Each ctrl In Frame1.Controls
If TypeOf ctrl Is msforms.OptionButton Then
If ctrl.Value = True Then
MsgBox ctrl.Caption & " is selected", vbOKOnly, "Selected OptionButton"
End If
End If
Next ctrl
End Sub
Example 5: Determine which CheckBoxes within a Frame, are selected:
Private Sub CommandButton1_Click()
'Determine which CheckBoxes within a Frame (containing both OptionButton and CheckBoxes), are selected.
Dim ctrl As Control
'The test for CheckBoxes (viz. If TypeOf ctrl Is msforms.CheckBox Then) might not be conclusive - the OptionButton and ToggleButton controls also implement the Checkbox interface and meet this condition.
For Each ctrl In Frame1.Controls
If TypeOf ctrl Is msforms.CheckBox Then
If Not TypeOf ctrl Is msforms.OptionButton Then
If ctrl.Value = True Then
MsgBox ctrl.Caption & " is selected", vbOKOnly, "Selected CheckBoxes"
End If
End If
End If
Next ctrl
End Sub
Example 6: Determine which OptionButton, in a UserForm, with a particular GroupName, is selected:
Private Sub CommandButton1_Click()
'Determine which OptionButton, in a UserForm, with a particular GroupName, is selected.
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.OptionButton Then
If ctrl.GroupName = "GroupA" Then
If ctrl.Value = True Then
MsgBox ctrl.Caption & " is selected", vbOKOnly, "Selected OptionButton"
End If
End If
End If
Next ctrl
End Sub
Difference between CheckBox and OptionButton
With CheckBox, user can select multiple options, by selecting each CheckBox against which an option is available. OptionButton is used when user is allowed to select only a single option.
CheckBox can be used in a singular manner viz. a single CheckBox can be selected or deselected (by clicking itself) indicating whether an option is to be exercised or not. OptionButton on the other hand are used in multiples viz. two or more, to indicate selection of mutually exculsive options. OptionButton gets deselected only on selection of another OptionButton and not by clicking itself.
Example 7: A Practical Example of creating and using a UserForm with CheckBox, OptionButtons and other Controls:
Step 1: refer Image 23a - clicking on Command Button (Get UserForm) runs the following macro in Sheet4, which Loads the UserForm:
Sub getUserForm()
frmGetData.Show
End Sub
Step 2: refer Image 23b - UserForm gets Loaded.
Step 3: refer Image 23c - enter UserForm fields for each candidate.
Step 4: refer Image 23d - save UserForm and data gets posted in worksheet, sorted alphabetically, by name.
Set of VBA codes/macros entered in the Code Module of the UserForm: VBE editor -> Forms -> frmGetData (name of the UserForm):
Private Sub UserForm_Initialize()
'Set properties of Controls on initialization of UserForm. The UserForm gets loaded on clicking the command button on worksheet which uses the UserForm Show method.
Dim i As Integer
Dim myArray As Variant
'to select from a large number of options, use a ListBox or ComboBox; CheckBoxes might provide better visibility and can be used when selection is to be made from few options, hence these are used to select user "Interests".
With Me.lstAge
For i = 1 To 100
.AddItem i & " yrs"
Next i
End With
'ComboBox has been preferred over ListBox, for selecting country, for 2 reasons: (i) allow customized entries from user even if country name not in list; and (ii) to facilitate selection from list because typing in the first letter(s) in text area of ComboBox will display matching options.
myArray = Array("Argentina", "Australia", "Brazil", "China", "France", "Germany", "Greece", "HongKong", "India", "Italy", "Japan", "Russia", "Singapore", "Spain", "Switzerland", "UK", "USA")
Me.cmbCountry.List = myArray
Me.txtSpouse.Enabled = False
Me.txtStartDate.Enabled = False
Me.txtEndDate.Enabled = False
'all OptionsButtons within a specific GroupName become mutually exclusive and self-contained within that group and do not affect selection of OptionButtons outside that group.
'selecting an OptionButton in one group will de-select all other OptionButtons of only that group.
'if OptionButtons are not grouped (in a container viz. specific GroupName, MultiPage Control or Frame Control), selecting one OptionButton in a UserForm will de-select all other OptionButtons in the form.
optMale.GroupName = "Gender"
optFemale.GroupName = "Gender"
optStartDate.GroupName = "Availability"
optEndDate.GroupName = "Availability"
End Sub
Private Sub clearForm()
'clear all fields in UserForm
'TextBox to accept name
txtName.Value = ""
'OptionButton which selects "Male"
optMale.Value = False
'OptionButton which selects "Female"
optFemale.Value = False
'ListBox to select Age
lstAge.ListIndex = -1
'ComboBox to select Country
cmbCountry.Value = ""
'CheckBox to select "Reading"
chkReading.Value = False
'CheckBox to select "Music"
chkMusic.Value = False
'CheckBox to select "Movies"
chkMovies.Value = False
'CheckBox to select "Sports"
chkSports.Value = False
'CheckBox to select if Married
chkMarried.Value = False
'TextBox to enter Spouse Name
txtSpouse.Value = ""
'TextBox to enter Start Date
txtStartDate.Value = ""
'TextBox to enter End Date
txtEndDate.Value = ""
'OptionButton to select Start Date
optStartDate.Value = False
'OptionButton to select End Date
optEndDate.Value = False
End Sub
Private Sub Calendar1_Click()
'To add Calendar control in a ToolBox and then to UserForm: In the VBE editor -> select ToolBox -> right-click in ToolBox OR select Tools from top menu bar -> click Additional Controls -> select Calendar Control ("Calendar Control 10.0", "Calendar Control 12.0")
'manual entry has been disabled in the TextBoxes txtStartDate and txtEndDate. Select relevant OptionButton and click on calendar to post.
If optStartDate = True Then
txtStartDate.Text = Format(Calendar1.Value, "mm/dd/yyyy")
ElseIf optEndDate = True Then
txtEndDate.Text = Format(Calendar1.Value, "mm/dd/yyyy")
End If
End Sub
Private Sub chkMarried_Click()
'user can enter in TextBox only if CheckBox is selected
If chkMarried.Value = True Then
txtSpouse.Enabled = True
Else
txtSpouse.Enabled = False
End If
End Sub
Private Sub cmdClear_Click()
clearForm
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSave_Click()
'save UserForm data in worksheet
Dim totalRows As Long
Dim str As String
Dim endDate As String, startDate As String
'error check - blank UserForm fields not permitted
If txtName.Text = "" Then
MsgBox "Please Enter Name", vbOKOnly, "Name Error!"
Exit Sub
ElseIf optMale.Value = False And optFemale.Value = False Then
MsgBox "Please Select Gender", vbOKOnly, "Gender Error!"
Exit Sub
ElseIf lstAge.ListIndex = -1 Then
MsgBox "Please Select Age", vbOKOnly, "Age Error!"
Exit Sub
ElseIf cmbCountry.Value = "" Then
MsgBox "Please Select Country", vbOKOnly, "Country Error!"
Exit Sub
ElseIf chkReading.Value = False And chkMusic.Value = False And chkMovies.Value = False And chkSports.Value = False Then
MsgBox "Please Select atleast one Interest", vbOKOnly, "Interests Error!"
Exit Sub
ElseIf chkMarried.Value = True And txtSpouse.Text = "" Then
MsgBox "Please Enter Spouse Name", vbOKOnly, "Spouse Error!"
Exit Sub
ElseIf txtStartDate.Text = "" Then
MsgBox "Please Enter Start Date", vbOKOnly, "Date Error!"
Exit Sub
ElseIf txtEndDate.Text = "" Then
MsgBox "Please Enter End Date", vbOKOnly, "Date Error!"
Exit Sub
End If
'error check - start date cannot be later than end date
endDate = Right(txtEndDate.Text, 4) & Left(txtEndDate.Text, 2) & Mid(txtEndDate.Text, 4, 2)
startDate = Right(txtStartDate.Text, 4) & Left(txtStartDate.Text, 2) & Mid(txtStartDate.Text, 4, 2)
If endDate < startDate Then
MsgBox "Start Date cannot be later than End Date!", vbOKOnly, "Date Error!"
Exit Sub
End If
'determine worksheet row to post data
totalRows = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row
If totalRows < 3 Then
totalRows = 3
Else
totalRows = totalRows
End If
'Post Data in worksheet:
Sheet4.Cells(totalRows + 1, 1) = txtName.Text
If optMale.Value = True Then
Sheet4.Cells(totalRows + 1, 2) = "Male"
ElseIf optFemale.Value = True Then
Sheet4.Cells(totalRows + 1, 2) = "Female"
End If
Sheet4.Cells(totalRows + 1, 3) = lstAge.Value
If txtSpouse.Text <> "" Then
Sheet4.Cells(totalRows + 1, 4) = txtSpouse.Text
Else
Sheet4.Cells(totalRows + 1, 4) = "Unmarried"
End If
Sheet4.Cells(totalRows + 1, 5) = cmbCountry.Value
If chkReading.Value = True Then
str = "Reading, "
End If
If chkMusic.Value = True Then
str = str & "Music, "
End If
If chkMovies.Value = True Then
str = str & "Movies, "
End If
If chkSports.Value = True Then
str = str & "Sports, "
End If
'deleting comma and space, the 2 characters at the end of str
str = Left(str, Len(str) - 2)
Sheet4.Cells(totalRows + 1, 6) = str
Sheet4.Cells(totalRows + 1, 7) = txtStartDate.Value
Sheet4.Cells(totalRows + 1, 8) = txtEndDate.Value
'sort the data stored in worksheet, by name
Sheet4.Range("A4:H" & totalRows + 1).Sort Key1:=Sheet4.Range("A4"), Order1:=xlAscending
'run the macro - Private Sub clearForm(), to clear all fields in UserForm
clearForm
End Sub
----------------------------------------------------------------------------------------------------------
ToggleButton is a control which executes one action when clicked first and a different action on the second click. It has two states, On and Off, wherein the button alternates (or toggles) between the two. It can have a value of True (where it appears as pressed) when the button is selected or the value False (appears unpressed) when unselected. ToggleButton seems a cross between a CheckBox (toggle functionality) and a CommandButton (clickable and similar appearance till clicked). You can toggle On and Off by selecting or deselecting a CheckBox. ToggleButton uses a single button whereas OptionButtons use two separate buttons to determine an on/off (select/deselect) status. However, most users find CheckBox or OptionButtons easier to use. Note: ToggleButton can also have a Null value (ie. neither selected nor unselected) wherein it will appear shaded, if the TripleState property is set to True.
Example 8: Using a ToggleButton control to alternate between sorting as per Ascending order or Descending order of a worksheet range. Refer Images 24a and 24b:
Private Sub UserForm_Initialize()
'Set properties of Controls on initialization of UserForm.
Dim totalrows As Long
'determine total rows in column A:
totalrows = Sheet7.Cells(Rows.Count, "A").End(xlUp).Row
'ToggleButton is pressed, data in column A & B gets sorted in ascending order and the button appears in green BackColor and displays the alternate sort option:
Me.ToggleButton1.Value = True
Me.ToggleButton1.Caption = "Get Descending Order"
Me.ToggleButton1.Font.Bold = True
Me.ToggleButton1.BackColor = RGB(0, 255, 0)
Sheet7.Range("A2:B" & totalrows).Sort Key1:=Sheet7.Range("A2"), Order1:=xlAscending
End Sub
Private Sub ToggleButton1_Click()
Dim totalrows As Long
'determine total rows in column A:
totalrows = Sheet7.Cells(Rows.Count, "A").End(xlUp).Row
'if ToggleButton is pressed, data in column A & B gets sorted in ascending order and the button appears in green BackColor and displays the alternate sort option:
If Me.ToggleButton1.Value = True Then
Me.ToggleButton1.Caption = "Get Descending Order"
Me.ToggleButton1.BackColor = RGB(0, 255, 0)
Sheet7.Range("A2:B" & totalrows).Sort Key1:=Sheet7.Range("A2"), Order1:=xlAscending
'if ToggleButton value is False (appears unpressed), data in column A & B gets sorted in descending order and the button appears in red BackColor and displays the alternate sort option:
ElseIf Me.ToggleButton1.Value = False Then
Me.ToggleButton1.Caption = "Get Ascending Order"
Me.ToggleButton1.BackColor = RGB(255, 0, 0)
Sheet7.Range("A2:B" & totalrows).Sort Key1:=Sheet7.Range("A2"), Order1:=xlDescending
End If
End Sub
Example 9: Using ToggleButton to hide or unhide worksheet columns/rows:
Private Sub ToggleButton1_Click()
'hide or unhide columns:
If ToggleButton1.Value = True Then
Sheet7.Columns("B").EntireColumn.Hidden = True
Sheet7.Columns("C").EntireColumn.Hidden = False
Else
Sheet7.Columns("B").EntireColumn.Hidden = False
Sheet7.Columns("C").EntireColumn.Hidden = True
End If
End Sub
Private Sub ToggleButton1_Click()
'hide or unhide rows:
Rows("1:3").Hidden = Not Rows("1:3").Hidden
End Sub
Note: A CheckBox can also be used to Toggle, just like the ToggleButton above.
Example 10: Using CheckBox to hide or unhide worksheet columns/rows:
Private Sub CheckBox1_Click()
'hide or unhide columns:
If CheckBox1.Value = True Then
Sheet7.Columns("B").EntireColumn.Hidden = True
Sheet7.Columns("C").EntireColumn.Hidden = False
Else
Sheet7.Columns("B").EntireColumn.Hidden = False
Sheet7.Columns("C").EntireColumn.Hidden = True
End If
End Sub
Private Sub CheckBox1_Click()
'hide or unhide rows:
Rows("1:3").Hidden = Not Rows("1:3").Hidden
End Sub
