Excel VBA: Form Control Checkbox Value (3 Examples)

This article illustrates how to check, set, and manipulate the form control checkbox value using VBA code in Excel. The form control checkboxes are very handy and productive to use in checklists where users need to select multiple options to complete a task. Let’s dive into the examples to learn how to manipulate the checkbox values with VBA code to make our daily work easy and time-saving.

How to Form Control Checkbox Value Using VBA in Excel: 3 Examples

Let’s say we have a student course registration form with 6 different courses for selection.  We’ll demonstrate our examples using this form that consists of 6 Form Control Checkboxes.

Write Code in Visual Basic Editor

Follow the steps to open the Visual Basic Editor and write some code there.

• Go to the Developer tab.
• Click the Visual Basic option.

• In the Visual Basic for Applications window, click the Insert dropdown to select the New Module.

Now that a new module is opened, write some code there and press F5 to run.

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

CheckBox Values:

There are exactly 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:

By using the CheckBox.Value property in VBA code, we can check the value of a Form Control Checkbox easily. 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 with right-click of the mouse. Now we can see the name in the Name Box, at the top-left corner of the worksheet.

Code:

Let’s copy and paste the following code into the visual basic code editor and press F5 to run it.

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

As we see the “Check Box 1” is checked in our dataset, and the MsgBox showed the Checkbox value as 1.

In addition, the following code will print the values of all six Checkboxes in the course list in cells 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:

We 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

In the following code, the shape.OLEFormat property is used to check the checkbox value.

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

Read More: VBA to Check If CheckBox Is Checked in Excel

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

Using VBA code, we can set the value of a Form Control Checkbox. For this, we need to select a Checkbox first and then set the value to make it checked or unchecked, or mixed. We can either set the values in the 1/-4146/2 format or in the xlOn/xlOff/xlMixed format. In a summary-

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

In this example, here, we have all the Checkboxes unchecked.

Now put the following code in the visual basic editor 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

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

In this example, we’ll show how to check or uncheck all the form control checkboxes in a worksheet or a whole workbook. This technique helps to reset all the Checkboxes with one click.

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

Let’s say in the current sheet named “example3”, we have all the Checkboxes unchecked.

Now run the above code in the visual basic editor to check all the Checkboxes at once.

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

3.2 Whole Workbook

To reset a whole workbook that consists of Checkboxes in different worksheets, try out the following code in the visual basic editor and see the magic.

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 associated with 3 different examples, click the right button on the Sheet Name and select the View Code.

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

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to manipulate the form control checkbox values using VBA code in Excel with 3 different examples. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• If Checkbox Is Checked Then Apply Formula in Excel
• What Can You Do with Cell Value If Checkbox Is Checked in Excel?
• Excel Checkbox: If Checked then Change Cell Color

<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel