User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

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:

CheckBox

OptionButton

ToggleButton

--------------------------

 

CheckBox

 

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

 

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

 

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