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.

NOTE: For a quicker way to add check boxes, you can use the macro from the Excel VBA – Check Boxes page on my Contextures site.

  • 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.

____________

You may also like...

53 Responses

  1. mfexcel says:

    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! :)

  2. Rudra Sharma says:

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

  3. Jeff Pohlman says:

    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.

  4. Madison says:

    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.

  5. Kristi says:

    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!!

  6. Colin Fursman says:

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

  7. Chuck says:

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

  8. Emma says:

    Hi, This worked for me. Though I am using it on a large number of cells and need to see which are both TRUE and FALSE. This solution linked the checkboxes with the corresponding T/F column, but only changed the ticked boxes to TRUE and did nothing for the ones that are unticked. I need any box that is left unticked to read as FALSE without selecting it then unselecting. Could you tell me what to add to the macro to do this?

    Thanks :)

    • @Emma, you could add a couple of lines to the LinkCheckBoxes macro, so it sets the linked cell to FALSE:

      Sub LinkCheckBoxes()
      Dim chk As CheckBox
      Dim lCol As Long
      Dim c As Range
      
      lCol = 2 'number of columns to the right for link
      
      For Each chk In ActiveSheet.CheckBoxes
        With chk
          Set c = .TopLeftCell.Offset(0, lCol)
          .LinkedCell = c.Address
          c.Value = False
        End With
      Next chk
      
      End Sub
      
  9. Amanda L says:

    Hey, I want to make an excel for performance review for my company. I want to add checks that go to specific paragraphs with the information. To basically reduce typing and help with grammar for other employees. Example: [check box] Agent did not meet metrics today. When checked it would go into a section labeled “Area of improvement” and have “Agent did not meet metrics listed under that field.

    Is this possible?

  10. Antonio says:

    Thank you!!! This was a great help, but I have a worksheet with multiple columns of checkboxes and some of these require specific text….what I am trying to say is how do I perform this Macro WITHOUT the true/false text appearing in the spreadsheet?

    Thank you again!!

  11. oslec says:

    Hi Debra,

    Your tutorial is what I am looking for the long time. In fact, I am searching it for week. I appreciated your help, indeed. But, there’s some problem with the result. I have 3 columns of checkboxes and I just edit your code to copy the code result to 3 columns to the right instead of 2. But all the result started at 1 row above the checkboxes. Can you help me that? Thanks in advance

  12. Tyler says:

    Thanks so much for this. I am writing a workbook to track student attendance, and this code helps a ton. I have a column of 25 check boxes, and I would like to have one separate check box that can control all 25 at once. Is there any way to do this?

  13. Vince says:

    Anyone can help me on this, need to assign controlled series number for each form and it will automatically assign during printing.

    Hope you can help for me for this matter.

  14. Shady Mohsen says:

    Very useful tutorial. I was really frustrated about linking all check boxes manually. I will share this on Facebook.

  15. Shady Mohsen says:

    You can use the following code to automatically link any check box you create to its parent cell.

    Dim NoOfCheckBoxes As Integer

    Private Sub Worksheet_Activate()
    NoOfCheckBoxes = ActiveSheet.CheckBoxes.Count
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim chk As CheckBox
    If NoOfCheckBoxes ActiveSheet.CheckBoxes.Count Then
    For Each chk In ActiveSheet.CheckBoxes
    With chk
    .LinkedCell = _
    .TopLeftCell.Address

    End With
    Next chk
    End If
    End Sub

  16. przemo_li says:

    GJ.

    One addition, when one have range .Address will work for .LinkedCell:

    .LinkedCell = Range(“A1”).Address

  17. Albond says:

    Hello Debra, thanks for your excel file.
    I have around 500 multiple choice Questions coming from my old internet site
    I did a copy Paste from the HTML page to excel.
    But here is my problem I’m unable to link my checkbox to a cell
    the checkbox is blocked =EMBED(“Forms.HTML:Checkbox.1″;””)
    You can find the file on the public link https://dl.dropboxusercontent.com/u/19774988/test.xlsx

    • TimL says:

      Did you get an answer for how to get the values from an internet site =EMBED(“Forms.HTML:Checkbox.1″;””) ?

      I have the same challenge

  18. K. Killough says:

    This worked fantastically! Thanks!

  19. Peregrine says:

    I used modified it for scroll bars and it worked perfectly. I was really worried that I might have to link 130 scroll bars one by one.

    Thanks a ton!

  20. Tim Grinstead says:

    Nothing happened when I did this? Is there a step to link before running the macro

  21. Alastair says:

    This worked well for me, but I have a problem in that if I run a drop down option search such as by alphabet or date the file changes but due to the absolute reference link in the checkboxes the checkboxes remain fixed to different data.

  22. Michael Dam says:

    Hi Debra,

    Needed this badly!! Bu it doesn’t work…?? No response or change of “LinkedCell” in properties.

    This is what I use:

    Sub LinkCheckBoxes()
    Dim chk As CheckBox
    Dim lCol As Long
    Dim c As Range

    lCol = 53

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

    End Sub

    Bu when running Macro nothing happens…??

    Best regards
    Michael

  23. Michael Dam says:

    PS:

    When stepping in to the macro it jumps directly from

    “For Each chk In ActiveSheet.CheckBoxes”

    to

    “End Sub”

    ??

    Best regards
    Michael

    • dave says:

      That means that there are no checkboxes on the activesheet. So either the sheet that is active is not the sheet you really want — or there are no checkboxes from the Forms toolbar on that sheet.

  24. Michael Dam says:

    Hi Dave,

    Youre right!

    Thanks!

    I had used ActiveX not Forms… It all works now!!

    Best regards
    Michael

  25. Tyler M. says:

    Thank you for this piece of code. Very helpful. Exactly what I was looking for.

  26. Landon B. says:

    Hi Debra, thanks for putting this together.

    How would you adjust the macro instruction if I want the “TRUE”/”FALSE” value to appear directly on the cell that the checkbox is in?

  27. dave says:

    The code on Debra’s site:
    http://www.contextures.com/excelcheckboxvba.html
    (in the “Add Check Boxes” section)
    made the link cell the same cell that contained the checkbox.

    The line that tells the program what to use for the linked cell is:
    .LinkedCell = c.Offset(0, 0) _
    .Address(external:=True)

    That cell is empty to begin with, but changes to true/false when you check/uncheck it.

    But (in my opinion), that really clutters up the look of the worksheet.

    • Thanks Dave, and I agree with you about the clutter.
      If the true/false really needs to be in the same cell, I’d make the cell wide, with the checkbox at the right, and text in a small font at the left.

      • dave says:

        Or make the font color the same color as the cell’s fill color (white on white, for example).

        You could still see the true/false value in the formulabar if you wanted.

  28. liz carlisle says:

    Hiya,

    You’ve just helped me run my first macro, but your macro makes my results happen 2 columns away from where i need it!

    I need to change the code so the cell links to itself (creating a data set for a number of clients where I measure many things, have to total each column which is a different piece of data, eg: homeless, at risk, substance abuse etc etc _. So … I have the data measure across the top and a new row for each client. IE: cell A1 =homeless, b1=at risk etc, cell A2 downwards is the check box) . I need to add up each column. Please can you tell me which bit of the macro code I need to change (I figure it’s the bit where it says “lCol = 2 ‘number of columns to the right for link” but I have no idea what to write.

    Please can you help??? You have already revolutionised my life … would love it if you can do it again!

    Thanks (PS .. tried to give you a screen shot but not working for me!

  29. liz carlisle says:

    Debra,

    You are a goddess, and I am your humble worshipper. I can now go to the office tomorrow and tell the man who said you can’t add up tick boxes that I can and indeed have added up text boxes (even if I do admit to having help)!

    Thanks SO much xx

  30. arellanos says:

    Debra,

    I’m trying to get one check box to prompt a certain text clause and another checkbox to prompt a slight variation of that clause. I want the check box to prompt the cause on a different worksheet too. Any guidance would be greatly appreciated! Thank you in advance!

    • Link the check box to a cell, then put formulas in the other cells, and show a result based on TRUE or FALSE in the linked cell.
      For example: =IF(A1=TRUE,”The box was checked”,”No check mark”)

      • arellanos says:

        I tired doing what you mention but the text itself isn’t appearing

        • What formula did you use, and which cell is linked to the check box?

          • arellanos says:

            I linked my “One borrower” checkbox (which on sheet one) to I2 (on sheet 3). G3 has the correct text I need for “One borrower” and G9 has the correct text for multiple borrowers (both still on sheet 3). I then need that text to prompt on A66 on sheet 5.

            I used the formula you mentioned above to give me a true or false answer. Then I tried us another if formula at IF true would prompt for the on borrower text and if false would prompt for the multiple borrower text.

            I’ve also tried to use VBA to prompt for the right text but can’t get the actual to appear. It’s also worth noting that the one/multiple is actually a formula that is pulling information from my data entry sheet on sheet one.

            Am I even going about this the right way or is my logic off?

          • Use this formula in cell A66 on Sheet5:
            =IF(Sheet3!I2=TRUE,Sheet3!G3,Sheet3!G9)

  31. Shaun says:

    Hi Debra,

    Thank you so much this solution. I’m trying to make an attendance list for a group of 200 people in check boxes.
    Attendance is recorded weekly and I’m trying to make a spreadsheet to capture a whole year.
    That’s 52 x 200 cells = approx 10,000 cells.

    At the moment, Excel is hanging, and based on the short time it took to only do 200, I’m doubtful it should take this long (it has been churning away for around 20 minutes). CPUs are running at around 12%, and I’m only using 25% of my memory.
    Excel has marked itself as “Not Responding”. Do you think excel “gives up” when it reaches this stage, or do you think it’s actually still doing something productive?

    Cheers.

    • Shaun, it’s probably still doing something, although I hope it’s finished by now!
      That’s an awful lot of check boxes though, and Excel is giving you a clear message that it’s not happy about it.
      I’d just mark the cells with “X” characters, or a number 1, instead of loading the sheet with check boxes.

  32. Dan says:

    Thanks for this, very helpful! For me (newest version of excel on windows 10), the checkbox is TINY. Any way to increase the size of the checkbox within the cell? Thanks!

  33. Ryan says:

    This has been very helpful thank you, however I am experiencing a small issue with the worksheet I am trying to do this with.

    For my use I have multiple columns in the workbook with check boxes all the check boxes start in row 3 and go to row 200 so $H$3:$H$200 for example. I have them in Col H,K, N,Q,U, and X and I have a blank Col next to each for the True/False input.

    This is the code from you that I am running:

    Sub LinkCheckBoxes()
    Dim chk As CheckBox
    Dim lCol As Long
    Dim c As Range

    lCol = 1 ‘number of columns to the right for link

    For Each chk In ActiveSheet.CheckBoxes
    With chk
    Set c = .TopLeftCell.Offset(1, lCol)
    .LinkedCell = c.Address
    c.Value = False
    End With
    Next chk

    End Sub

    Now my issue is the first col H works just fine it returns a value of false in the cell exactly one col to the right of the check box, however in Col K the value is returned one cell to the right and one down, but Col N is correct, then Col Q,U,X the first check box is correct but Row 4-200 are returned one to the right and one down.

    Any ideas what might be causing this error?

    Thank you for any help.

  34. Ajit Phape says:

    Here I have a peculiar problem when arriving a serial number with each mark (Check).
    I want a running serial number for each check mark else nil entry.

    Suppose M10 is positive (Check mark) then entry in N10 should be 1 else ” ” and so on..
    M11 is Negative a/a N11 Should be ” ” else…
    M12 is positive a/a N12 Should be 2 here (=IFERROR(IF(AND(O11>=”a”,F11=2),P10+1),””) is working fine but does not display anything when formatted to ;;; and w/o format it display error which I do not want to.

    Please enlighten me how to go about It

    Thanks
    Ajit Phape

  35. Chetan says:

    Hello,

    Thanks, I can’t stress enough how much this has helped me. I was wondering if there is way to check or uncheck a checkbox from macro. If I have a checkbox, I want it to be able to be checked or unchecked either by human or by a macro? Is it possible? Thanks in advance.

  36. Kate says:

    Good! The first recomendation that works! Thanks!

Leave a Reply to Vince Cancel reply

Your email address will not be published. Required fields are marked *