Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to link checkboxes to multiple cells in Excel?

Link the checkboxes to multiple relative cells may help you to quickly and easily do some calculations when you need to sum, count or average only the checked or unchecked cells. But, have you ever tried to link the checkboxes to multiple cells at once in a worksheet?

Link checkboxes to multiple cells with formula one by one

Link checkboxes to multiple cells at once with VBA code


Quickly insert multiple check boxes in selections ta once:
Kutools for Excel’s Batch Insert Check Boxes utility can help you to quickly insert multiple checkboxes at once. Read more about this feature...
doc-create-combobox22

arrow blue right bubble Link checkboxes to multiple cells with formula one by one


To link the checkbox to a specific cell, in fact, you can apply a simple formula to link them manually.

1. After inserting the checkboxes in your worksheet, to select the checkbox, please press Ctrl key and then click the first checkbox that you want to link to other cell.

2. Then in the formula bar, type the equal sign =, and then click one cell that you want to link the checkbox to, B2 for example, see screenshot:

doc-link-multiple-checkboxes-1

3. And then press Enter key on the keyboard, now, when you check this checkbox, the linked cell will display TRUE, if uncheck it, it will display FALSE, see screenshot:

doc-link-multiple-checkboxes-2

4. Repeated the above steps to link other checkboxes one by one.


arrow blue right bubble Link checkboxes to multiple cells at once with VBA code

If there are hundreds and thousands checkboxes need to be linked to other cells, the first method will not work effectively, to link them to multiple cells at once, you can apply the following VBA code. Please do as this:

1. Go to your worksheet with the list of checkboxes.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: link checkboxes to multiple cells at once

Sub LinkChecks()
'Update 20150310
i = 2
For Each cb In ActiveSheet.CheckBoxes
cb.LinkedCell = Cells(i, "B").Address
i = i + 1
Next cb
End Sub

4. And then press F5 key to run this code, all checkboxes in the active worksheet have been linked to the cells, when you check the checkbox, its relative cell will display TRUE, if you clear a check box, the linked cell should show FALSE, see screenshot:

doc-link-multiple-checkboxes-3

Note: In the above code, i = 2, the number 2 is the starting row of your checkbox, and the letter B is the column location where you need link the checkboxes to. You can change them to your need.


Related articles:

How to select all checkboxes using a single checkbox in Excel?

How to quickly insert multiple checkboxes in Excel?

How to quickly delete multiple checkboxes in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Eyale · 3 months ago
    How about if you have some empty rows in ColumnA (as per your example) in between checkboxes? Using the above code it gets the linked cells wrong if there are empty rows because it does not skip them. Interested to see the solution
    • To post as a guest, your comment is unpublished.
      R · 7 days ago
      Did u find any solution for this? Same problem
  • To post as a guest, your comment is unpublished.
    Richard Anthony · 1 years ago
    Sub LinkCheckBoxes()

    Dim chk As CheckBox

    Dim lCol As Long

    lCol = 1 '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
  • To post as a guest, your comment is unpublished.
    Wedzmer Munjilul · 2 years ago
    Hello! Nice post about the VBA...
    but what if there are 3 columns that has checkboxes that needs to be linked in three other columns as well? Let's say columns B, C, and D has checkboxes and should be linked to columns H, I, and J respectively.
    • To post as a guest, your comment is unpublished.
      Paul Forde · 2 years ago
      Hi,

      Having same problem - Have you found out how to get around this?

      Thanks,
      Paul
      • To post as a guest, your comment is unpublished.
        Marcos Grazina · 1 years ago
        Hey,

        Try this:

        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



        If you have for example, checkboxes in D,E,F change "Icol" to 1, so it links to G,H and I, respectively.
        • To post as a guest, your comment is unpublished.
          ali · 11 months ago
          thank you very much
          • To post as a guest, your comment is unpublished.
            Nitin · 1 months ago
            Can you help me out with this same problem?
  • To post as a guest, your comment is unpublished.
    thea · 2 years ago
    Can I do this and program the boxes to say something other than true and false?
  • To post as a guest, your comment is unpublished.
    Louise · 2 years ago
    Hi,

    In your VBA code: link checkboxes to multiple cells at once, the code is set up to link the cell below it. How is the code if I want to link the cell to the left of the first one?

    Thanks!