Excel VBA: Form Control Checkbox Value - 3 Examples

The dataset showcases a course registration form with 6 different courses.

Using a Code in the Visual Basic Editor

Go to the Developer tab.
Click Visual Basic.

In the Visual Basic for Applications window, click Insert and select New Module.

Enter the code in the module and press F5 to run.

Example 1 – Check the Value of a Form Control Checkbox Using VBA in Excel

CheckBox Values:

There are 3 values that a Checkbox holds. When we check the value, it returns:
Value = 1, when checkbox = checked,
Value = -4146, when checkbox = not checked (unchecked),
And Value= 2, when checkbox = mixed.

Check the Value of a CheckBox:

Use the CheckBox.Value property in the VBA code to check the value of a Form Control Checkbox. The generic form of the code is:
CheckBoxes(“Checkbox Name”).Value

Get the Name of a CheckBox:

To check the name of a Form Control Checkbox, select the Checkbox and right-click. The name is displayed in the Name Box.

Code:

Enter the following code in the VBA editor and press F5 to run it.

Sub CheckCheckboxValue()
MsgBox ActiveSheet.CheckBoxes("Check Box 1").Value
End Sub

“Check Box 1” is checked and the MsgBox shows the Checkbox value as 1.

Use the following code to print the values of the six Checkboxes in F3:F8.

Sub CheckCheckboxValue()
    Dim CBVal As Long
    For i = 1 To 6
    CBVal = ActiveSheet.CheckBoxes("Check Box " & i).Value
    Range("F" & (2 + i)) = CBVal
    Next
End Sub

Alternative codes:

You can also use the Shape.ControlFormat property to get the value of a form control checkbox. The code is:

Sub CheckCheckboxValue()
ActiveSheet.Shapes("Check Box 1").ControlFormat.Value
End Sub

The shape.OLEFormat property is used to check the checkbox value.

Sub CheckCheckboxValue()
ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Value
End Sub

Example 2 – Set the Value of a Form Control Checkbox Using VBA in Excel

To set the value of a Form Control Checkbox, select it and set the value to make it checked, unchecked, or mixed. You can either set the values in 1/-4146/2 format or in xlOn/xlOff/xlMixed format.

Value = 1 or xlOn, checkbox = checked,
Value = -4146 or xlOff, checkbox = not checked (unchecked),
And Value= 2 or xlMixed, checkbox = mixed.

In this example, all Checkboxes are unchecked.

Enter the following code and run it to see the output.

Sub SetCheckboxValue()
ActiveSheet.CheckBoxes("Check Box 1").Value = 1
ActiveSheet.CheckBoxes("Check Box 2").Value = -4146
ActiveSheet.CheckBoxes("Check Box 3").Value = 2
ActiveSheet.CheckBoxes("Check Box 4").Value = xlOn
ActiveSheet.CheckBoxes("Check Box 5").Value = xlOff
ActiveSheet.CheckBoxes("Check Box 6").Value = xlMixed
End Sub

Example 3 – Run a VBA Code to Check or Uncheck All Multiple Form Control Checkboxes in a Worksheet or Workbook in Excel

3.1 Active Worksheet

Use the following code to check all the Form Control Checkboxes in the active worksheet.

Sub SetAllCheckboxWorksheet()
ActiveSheet.CheckBoxes.Value = True
End Sub

The current sheet is “example3” and all Checkboxes are unchecked.

Run the code to check all Checkboxes.

You can also set 1 or xlOn instead of True in the above code. You can uncheck all checkboxes by setting the value as False or -4146 or xlOff.

3.2 The Whole Workbook

To reset a whole workbook that has Checkboxes in different worksheets, use the following code:

Sub SetAllCheckboxWorkBook()
Dim WSheet As Worksheet
For Each WSheet In Sheets
On Error Resume Next
WSheet.CheckBoxes.Value = True
On Error GoTo 0
Next WSheet
End Sub

Notes

To view the code, right-click the Sheet Name and select View Code.

The VBA MsgBox function is used in the code to show the Checkbox value in the Message Box.

Excel VBA: Form Control Checkbox Value – 3 Examples