Link Check Boxes to Cells With a Macro

You can use checkboxes on a worksheet, and link the results to a cell. If the box is checked, the cell shows TRUE, and if it’s not checked, the cell shows FALSE or the cell is empty. This makes it easy for someone to give a quick answer to a question, or select an option. You can even add programming to a check box, so something happens automatically when the box is clicked.

This will be a two part tutorial.

  1. First, we’ll set up the check boxes, and link them to cells. You’ll see how to do this manually, and with a macro.
  2. In the next part, we’ll set up the check boxes to run a macro.

Creating a To Do List

To see how the check boxes can be used, we’ll set up a To Do list, with a check box for each item. Here is the list, ready for the check boxes.

checkboxtodolist01

Insert a Check Box

To insert a check box, follow these steps:

  • Click the Developer tab on the Ribbon. If you don’t see the Developer tab, follow the instructions here, to show it.
  • In the Controls group, click the Insert command
  • There are two types of check boxes – Form Control and ActiveX Control.

checkboxinsert04

  • For this example, we’ll use the check box from the Form Controls, so click that.
  • On the worksheet, click near the top left corner of the cell where you want to add the check box -- cell B4 in this example.
  • The top left border of the check box frame should be inside cell B4. If necessary, move it down or right, so it’s inside the cell.

checkboxtodolist03

  • Click inside the check box frame, then select all the text, and delete it.
  • Make the check box frame narrower, so it just fits the box.

checkboxtodolist04

Copy the Check Box

Now that you’ve created and formatted one check box, you can copy it, and paste copies into the other cells.

  • Press Ctrl, and click on the check box, if it is not already selected.
  • On the Ribbon’s Home tab, click Copy (or use the Ctrl + C shortcut)
  • Select cell B5, and paste the check box, then paste into cells B6 and B7

checkboxtodolist05

Link the Check Boxes to Cells

If you add a check box to a worksheet, it isn’t automatically linked to a cell. If you want to capture the check box result, you’ll have to link it to a specific cell. If there are only a few check boxes, you can link them manually, by following these steps:

  • To select the check box in cell B4, press the Ctrl key, and click on the check box
  • Click in the Formula Bar, and type an equal sign =
  • Click on the cell that you want to link to, and press Enter

You can see the formula in the screen shot below:  =$D$4

checkboxtodolist06

NOTE: You can also link to a cell by going into the check box’s properties, but that takes longer.

Use Programming to Create Cell Links

In an ideal world, you would be able to use a relative reference to a cell as the check box link. Then, if you moved or copied the cell to a different location, it would automatically adjust to refer to the cell that in the same row, 20 columns to the right.

Unfortunately, that doesn’t work, and you have to link each check box individually. The good news is that you can use a macro, if you have lots of check boxes to link.

Instead of manually linking the remaining check boxes, we’ll use the following macro. To add this code to your own workbook, copy it to a regular code module.

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 2 'number of columns to the right for link

For Each chk In ActiveSheet.CheckBoxes
   With chk
      .LinkedCell = _
         .TopLeftCell.Offset(0, lCol).Address
   End With
Next chk

End Sub

Then, with the To Do list worksheet active, run the macro:

  • Click the Ribbon’s View tab, and at the far right, click Macros, then click View Macros, if a drop down list appears.
  • In the list, click LinkCheckBoxes, and click Run.

You won’t see anything happen, but each check box will be linked to the cell two columns to its right.

Test the Check Boxes

To test the check boxes, click on each one.

  • If you add a check mark, the cell that’s two columns to the right should show TRUE.
  • If you clear a check box, the linked cell should show FALSE.
  • If you clear the linked cell, the check box will also be cleared.

checkboxtodolist08

Next – Adding Macros to Check Boxes

In my next post, on Thursday, we’ll add a macro to each check box. I’ll see you back here on Thursday!

I’ll also have the completed sample file that you can download, to see how it all works.

____________

8 comments to Link Check Boxes to Cells With a Macro

  • Hi Debra,
    Thanks for sharing this. I enjoy your posts very much because they are really practical in real workplace.
    Now I can save plenty of time for creating links to check boxes. It's no longer a time-consuming manual process. Appreciate much! :)

  • Rudra Sharma

    Thanks Debra. I knew this how to do manually but programmatically never tried.

  • Jeff Pohlman

    Thanks for this tutorial on check boxes. I have a couple of questions. Once I run the macro to link the boxes 1 col to the left, how do I undo that. And, how do I link them, instead, to a location on another sheet? Thanks for your help, this will be a real time saver as I have a Book with about 300 check boxes I need to link to another page.
    Thanks again.

  • Madison

    This is an extremely large step to getting me where I want to go. I understand enough about code to know what this is saying, however in all reality no zero about coding. I am trying to do something very similar but with option buttons. What would be a code for group boxes to link row to row down a column as you have them shown here with checkboxes since as you put it the easy way of copy and pasting non absolutes is not an available option.

  • Kristi

    How can I modify this macro to activate based on multiple checkboxes?

    I have two columns - Email and Phone. I want to show TRUE for my Object cell "Contacted?" if there are 2 checkboxes under Phone and 3 under Email all checked and FALSE if they are short of all 5 checks.

    Thanks!!

  • Colin Fursman

    I want a cell to automatically insert a tick symbol if a different cell is true
    Is there a way of doing this

  • Chuck

    This is great, thank you. Im interested in the answer to Jeff's question as well - linking to another sheet in the workbook. Thanks.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>