Skip to main content

How to Assign Checkboxes With a Macro in Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

how-to-assign-checkboxes-with-a-macro-in-excel

Often I find myself creating more Excel projects with checkboxes. Sometimes dozens of checkboxes. For my last project, I created a quality checklist with 200 checkboxes. As you know if you have used these before, they all need to be assigned to cells.

So, instead of assigning the checkboxes myself, I found a macro to do it for me. I will go through all of the steps that were taken to assign checkboxes to cells. If that's not good enough, I have a video posted at the end of the article as well.

The first step in the process is making sure you have the developer tab activated. Since I'm using a Mac I will go through enabling the developer tab in that system. If you are running a Windows system you can figure out how to get the developer tab to display here.

Click on the Excel tab in the menu area and select preferences.

how-to-assign-checkboxes-with-a-macro-in-excel

Select the ribbon and toolbar options.

how-to-assign-checkboxes-with-a-macro-in-excel

Find the empty checkbox in the main tabs section and check that box before clicking the save button.

how-to-assign-checkboxes-with-a-macro-in-excel

Now that the developer tab is available, click on and select the checkbox option to insert it into the worksheet.

how-to-assign-checkboxes-with-a-macro-in-excel

Checkboxes come with text, but my preference is usually to have a blank checkbox. So. I usually delete this text.

how-to-assign-checkboxes-with-a-macro-in-excel

This is the point where I would normally assign the checkbox to a cell and be on my way. I would normally go to the formatting option and have the cell attached to the cell that it is in or the cell that needs to be affected for a process. This time I'm adding VBA code and running that code so the checkbox will auto-assign after the macro is run.

how-to-assign-checkboxes-with-a-macro-in-excel

To get the code in we need to select the Visual Basic editor button under the developer tab.

how-to-assign-checkboxes-with-a-macro-in-excel

Select the insert new module option.

how-to-assign-checkboxes-with-a-macro-in-excel

After the new module appears, copy and paste the code below inside that module.


Sub Assigncheckboxes() Dim cb As CheckBox Dim Col As Long Dim Row As Long Col = 0 Row = 0 For Each cb In ActiveSheet.CheckBoxes With cb .LinkedCell = _ .TopLeftCell.Offset(Row, Col).Address End With Next cb End Sub

how-to-assign-checkboxes-with-a-macro-in-excel

The most important part of the above code that you may need to change to your preferences is the row and column lines. Think of the checkbox as the origin and the row and column variables as the X and Y axes respectively. Change the position of the cell where you want the checkbox results (True/False) to appear. For example, if you needed the cell with the result to be one column to the left but on the same row, you replace Col = 0 with Col = -1.

Currently, these settings are marked at zero and can be changed if you would like to create an offset. Select the run button from the Visual Basic editor to run the program or run it from the workbook with the next few steps.

Navigate back to the workbook and click on the macros button.

how-to-assign-checkboxes-with-a-macro-in-excel

Select the macro that was just created followed by clicking the run button.

how-to-assign-checkboxes-with-a-macro-in-excel

After clicking on and off of the checkbox a few times I found the maco worked properly.

how-to-assign-checkboxes-with-a-macro-in-excel

Be sure to save the Workbook as a macro-enabled workbook so that the macro can be run again in the future. If celled are deleted by accident the code can be rerun to reset the cell result positions.

Assign Checkboxes to Cells Video

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder