Excel VBA: Form Control Checkbox Value (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel VBA Form Control Checkbox Value

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.

Excel VBA Form Control Checkbox Value


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

Excel VBA Form Control Checkbox Value

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

Excel VBA Form Control Checkbox Value

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
End Sub

Excel VBA Form Control Checkbox Value

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.

Excel VBA Form Control Checkbox Value

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

Excel VBA Form Control Checkbox Value

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.

Excel VBA Form Control Checkbox Value

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


  • To view the code associated with 3 different examples, click the right button on the Sheet Name and select the View Code.

Download Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF