Excel VBA Checkbox Loop: How to Read Every Checkbox

VBA & macros · Updated November 21, 2026

To loop through worksheet checkboxes in Excel VBA, use the worksheet’s CheckBoxes collection for Forms controls or its OLEObjects collection for ActiveX controls. Read each control’s Value to check whether it is selected; the right code depends on which kind of checkbox your sheet contains.

Identify the checkbox type first

Excel has two commonly encountered worksheet checkbox types, and VBA exposes them through different collections. A Forms checkbox is a control placed on a worksheet, often through the Developer tab’s Insert menu under Form Controls. An ActiveX checkbox is inserted from the ActiveX Controls section of the same menu. They may look similar, but their VBA object models are not interchangeable.

To identify one, right-click it. Forms controls usually show commands such as Format Control; ActiveX controls show Properties and View Code when Design Mode is active. You can also inspect the Developer tab’s Insert menu or select the object and look at the control type. Do not confuse either worksheet control with a checkbox inserted into a cell in newer Microsoft 365 versions: an in-cell checkbox stores a Boolean value in the cell, so you loop through the cells rather than through the controls collection.

  • Forms checkbox: loop through Worksheet.CheckBoxes; its value is typically xlOn or xlOff.
  • ActiveX checkbox: loop through Worksheet.OLEObjects, identify checkbox objects, and read their Boolean-like Value.
  • In-cell checkbox: inspect the range’s cell values, which are generally TRUE or FALSE, rather than using a controls collection.

Loop through Forms checkboxes

This example checks every Forms checkbox on a sheet named Sheet1 and prints the caption and state of selected controls in the Immediate window. In the VBA editor, press Ctrl+G on Windows to show that window; on Mac, use the View menu in the VBA editor to open Immediate Window.

Sub ListSelectedFormCheckboxes()
    Dim ws As Worksheet
    Dim cb As CheckBox

    ' Choose the worksheet containing the Forms controls.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Check every Forms checkbox on that worksheet.
    For Each cb In ws.CheckBoxes
        If cb.Value = xlOn Then
            Debug.Print cb.Name, cb.Caption, cb.Value
        End If
    Next cb
End Sub

For Each visits every item in the collection without requiring you to know how many controls there are or what their names are. The cb.Name value is the control’s object name, while cb.Caption is its visible label. They are not necessarily the same: a checkbox named Check Box 4 might display the caption “Include shipping.” Use the caption for readable reporting and the name when you need to refer to a particular control.

To inspect every checkbox, not only selected ones, move the Debug.Print line outside the condition or print a status in each branch. Forms control values can include xlMixed when the control allows a mixed state. If that setting is enabled, test for xlOn, xlOff, and xlMixed explicitly instead of assuming there are only two possible states.

Loop through ActiveX checkboxes

ActiveX controls are stored among a worksheet’s OLE objects. The collection can contain objects other than checkboxes, so test the underlying object type before reading checkbox properties. This code handles a selected, cleared, or null (mixed) checkbox without treating null as a Boolean.

Sub ListActiveXCheckboxes()
    Dim ws As Worksheet
    Dim ole As OLEObject
    Dim chk As Object

    ' Choose the worksheet containing the ActiveX controls.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' OLEObjects can include controls other than checkboxes.
    For Each ole In ws.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            Set chk = ole.Object

            If IsNull(chk.Value) Then
                Debug.Print ole.Name, chk.Caption, "Mixed"
            ElseIf CBool(chk.Value) Then
                Debug.Print ole.Name, chk.Caption, "Checked"
            Else
                Debug.Print ole.Name, chk.Caption, "Unchecked"
            End If
        End If
    Next ole
End Sub

The outer object’s name is available as ole.Name, while the ActiveX checkbox’s caption and value come from chk. If you want to process every ActiveX control, remove the type test only if the code that follows is valid for every object in the collection. Otherwise, a button or another OLE object could cause a property error.

Process checkboxes across multiple worksheets

When the same kind of controls appears on several worksheets, put the per-sheet loop inside a worksheet loop. For example, this version reports selected Forms checkboxes on every worksheet in the workbook:

Sub ListSelectedFormsInWorkbook()
    Dim ws As Worksheet
    Dim cb As CheckBox

    ' Visit each worksheet in this workbook.
    For Each ws In ThisWorkbook.Worksheets
        For Each cb In ws.CheckBoxes
            If cb.Value = xlOn Then
                Debug.Print ws.Name, cb.Name, cb.Caption
            End If
        Next cb
    Next ws
End Sub

ThisWorkbook means the workbook containing the VBA code. If the macro is stored in Personal.xlsb or another workbook, that may not be the workbook with the checkboxes. In that case, set a workbook variable to the intended workbook or use a specific reference such as Workbooks("Orders.xlsx"). A worksheet name in the code must match the tab name exactly.

For an ActiveX workbook-wide scan, use the same outer worksheet loop but apply the ActiveX example’s OLEObjects loop inside it. Do not combine the two collections and assume they contain the same controls: Forms checkboxes are not found by searching only OLEObjects, and ActiveX checkboxes are not found by searching only CheckBoxes.

Use the checkbox state in useful ways

Printing states is a safe way to confirm that the loop finds the controls you expect. Once verified, replace or supplement Debug.Print with the action your workbook needs. For example, you can count selections, gather captions, or apply a rule to a related row. Keep the action separate from the control-discovery logic so that it is easier to troubleshoot.

  • Count selected items: declare a Long counter before the loop, add one when the value is on, and display or return the count after the loop.
  • Update a linked cell: Forms controls can have a linked cell configured through their Format Control settings. Read that cell if the workbook’s design already uses linked values.
  • Find a related row: use a consistent naming scheme or a known linked-cell location; a checkbox caption alone is not a dependable row address.
  • Skip mixed states: if a three-state control is enabled, decide whether mixed should count as selected, unselected, or neither before writing the business rule.

A frequent misconception is that a Forms checkbox’s linked cell is the checkbox itself. It is not: the link is a separate worksheet cell that reflects the control state. If you loop the checkbox controls, read cb.Value; if you loop linked cells or in-cell checkboxes, read the appropriate range value. Choose one approach deliberately to avoid counting the same selection twice.

Common errors and practical fixes

If the loop finds no controls, first confirm the control type and worksheet. A macro that searches ws.CheckBoxes will not find ActiveX checkboxes, and a macro that searches ws.OLEObjects will not find Forms checkboxes. Also confirm that the code points to the sheet containing the controls, not merely the currently active sheet.

  • “Object doesn’t support this property or method”: the code may be using a Forms property on an ActiveX object, or reading checkbox properties from a non-checkbox OLE object.
  • Type mismatch on ActiveX value: a three-state checkbox can return Null. Check IsNull before converting the value to Boolean.
  • Wrong sheet or workbook: qualify references with a worksheet and workbook instead of relying on ActiveSheet or the active workbook.
  • Wrong caption or name: inspect both properties; changing the visible text does not necessarily rename the control.
  • Macro does not run: save in a macro-enabled format such as .xlsm, enable macros only for a workbook you trust, and check that the code is in a standard module or appropriate workbook module.

Use the Developer tab’s Design Mode when editing ActiveX controls, but turn it off before testing their behavior. Forms controls do not use ActiveX Design Mode. If the loop runs but the displayed results seem stale, verify that the user has actually changed the checkbox and that the code is reading the same sheet and control.

Windows, Mac, and Excel for the web

The examples target Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web, so use desktop Excel to run these procedures. ActiveX controls are a Windows technology and are not supported in Excel for Mac. If a workbook must work on both Windows and Mac, Forms controls or in-cell checkboxes are generally more suitable, but confirm that the specific controls and VBA behavior are supported by the Excel versions your users have.

Excel for Mac includes a VBA editor, though some object-model features and interface details can differ from Windows. Avoid relying on Windows-only ActiveX code in a cross-platform workbook; test the actual file in the target desktop versions. A Forms-controls loop may be appropriate where those controls are supported, while looping in-cell checkbox values is often simpler when the worksheet design uses cell-based checkboxes.

Run macros safely

The sample procedures only read controls and write text to the Immediate window. If you adapt a loop to clear checkboxes, change linked cells, delete objects, or modify worksheet data, save a copy of the workbook before running it. VBA actions cannot be undone with Ctrl+Z, and running a macro may clear Excel’s undo history. Keep a backup and test changes on a copy before applying them to a workbook that matters.

To run a procedure on Windows, open the VBA editor with Alt+F11, insert or select a standard module, and run the macro from the editor. On Mac, the VBA editor shortcut differs by keyboard and Excel setup; use Excel’s Tools > Macro > Visual Basic Editor menu if needed. The Developer tab may need to be enabled in Excel settings. Do not enable macros in files from unknown sources simply to inspect checkbox behavior.

Frequently Asked Questions

How do I loop through all checkboxes on the active sheet?
For Forms controls, use For Each cb In ActiveSheet.CheckBoxes and inspect cb.Value. For ActiveX controls, loop through ActiveSheet.OLEObjects and filter for objects whose type is CheckBox. For reusable macros, explicitly set the worksheet rather than depending on whichever sheet happens to be active.

How do I tell whether a Forms checkbox is checked?
Compare its value with xlOn. A cleared Forms checkbox normally returns xlOff; a three-state checkbox may also return xlMixed. Do not test it as though its value were always the same Boolean type used by an ActiveX checkbox.

Can one VBA loop handle Forms and ActiveX checkboxes?
Not by using one collection: the two control types are exposed differently. You can write one procedure with separate loops, one over CheckBoxes and one over OLEObjects, but each branch must use the properties and state checks appropriate to its control type.

Can I loop through checkboxes in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel to run VBA, or redesign the task around worksheet cell values and features available in the web app.

What if my checkbox is inside a cell?
Newer Microsoft 365 Excel versions support in-cell checkboxes that store a TRUE or FALSE value in the cell. Loop through the relevant range and inspect each cell’s Value; do not look for those cells in the Forms or ActiveX control collections.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.