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 typicallyxlOnorxlOff. - ActiveX checkbox: loop through
Worksheet.OLEObjects, identify checkbox objects, and read their Boolean-likeValue. - 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
Longcounter 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
IsNullbefore converting the value to Boolean. - Wrong sheet or workbook: qualify references with a worksheet and workbook instead of relying on
ActiveSheetor 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
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA MATCH Error Handling: Avoid Run-Time ErrorsFor safer Excel VBA MATCH error handling, use Application.Match and test its result with IsError before using it.…
- VBA Error Handling Tutorial: Errors, Handlers, and CleanupVBA error handling lets a macro respond to a runtime problem instead of stopping unexpectedly. Start with On…
- Excel VBA Array Clear: How to Erase or Reset ValuesTo clear a VBA array, use the Erase statement: Erase arrayName. For a fixed-size array, this resets its…
- Can Google Sheets Run Macros? A Guide to How They WorkYes. Google Sheets can run macros: you can record a sequence of spreadsheet actions, save it, and run…
- VBA Copy Visible Cells: How to Paste Filtered Rows in ExcelYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the source range, then copy that result and paste it at…
- Excel VBA Editor: How to Open and Use ItThe Excel VBA Editor, also called the Visual Basic Editor or VBE, is the desktop window where you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.