Forum Discussion

Jacqueline28's avatar
Jacqueline28
Copper Contributor
Jan 26, 2021

Inserting one checkbox and linking to multiple cells to trigger sums

Hi - I am adding checkboxes that are linked to cells to calculate costs. So far I have one check box per cell. I was wanting to link 1 checkbox to 6 cells so that I can check one box and select them ...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jan 26, 2021

Jacqueline28 

Link check boxes to multiple cells at the same time using VBA code

 

If there are many check boxes to be linked to other cells, the first method will not work effectively. To link them to multiple cells at a time, you can apply the following VBA code. Please do the following:

 

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

 

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

 

  1. Click Insert> Module and paste the following code in the Module window box.

 

VBA code: Link check boxes to multiple cells at the same time

 

 

'1.Proposal...try...this solution..:)
Sub LinkChecks ()
i = 2
For Each cb In ActiveSheet.CheckBoxes
cb.LinkedCell = Cells (i, "B"). Address
i = i + 1
Next cb
End Sub

'2. Proposal Solution
Private Sub CheckBox1_Click()
  'Code in die entsprechende Tabelle und Bereich anpassen!
  If CheckBox1 Then
    Range("D1:F1").Interior.ColorIndex = 3
  Else
    Range("D1:F1").Interior.ColorIndex = 10
  End If
End Sub

 

 

  1. And then press F5 to run this code, all of the check boxes in the active worksheet have been linked to the cells. If you select the check box, the corresponding cell will be displayed TRUE If you clear a check box, the linked cell should be displayed FALSE.

 

Note: In the code above, i = 2, the number 2 is the first row of your check box and the letter B is the column position that you need to link the check boxes to. You can change them as you wish.

 

I hope I could help you a bit.

 

Nikolino

I know that I don't know (Socrates)

 

Jacqueline28's avatar
Jacqueline28
Copper Contributor
Jan 28, 2021

Hi NikolinoDE - Could I send you and example of what I am working on to see if you could give me specific insights to the document?

 

Thanks in advance!

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 28, 2021

    Jacqueline28 

     

    Here are two files in this direction as an example.

     

    I hope that you will take a step back into your project with these suggested solutions.

     

    I wish you continued success with Excel

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

     

    • Jacqueline28's avatar
      Jacqueline28
      Copper Contributor
      Jan 28, 2021

      Thank you. NikolinoDE Please see attached link. For example, I am trying to get the box in the merged cell of A3 and A4 when checked to highlight both rows as well as trigger both J3 and J4 to be "true" and add both cost amounts to the final "total" at the bottom of the sheet.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 29, 2021

        Jacqueline28 

        With dynamic arrays support that could be transformed to

        =SUM(FILTER(Table133[Column9],'Group 3'!$A$3:$A$61))+
 SUM(FILTER(Table133[Column9],'Group 3'!$A$2:$A$60))

        or, more reliable

        =SUM(FILTER(Table133[Column9],
            INDEX('Group 3'!A:A,ROW(Table133[[#Headers],[Column1]])+1):
            INDEX('Group 3'!A:A,ROWS(Table133[Column1])+
                                ROW(Table133[[#Headers],[Column1]]))))+
 SUM(FILTER(Table133[Column9],
            INDEX('Group 3'!A:A,ROW(Table133[[#Headers],[Column1]])):
            INDEX('Group 3'!A:A,ROWS(Table133[Column1])+
                                ROW(Table133[[#Headers],[Column1]])-1)))

Resources