Inserting one checkbox and linking to multiple cells to trigger sums

%3CLINGO-SUB%20id%3D%22lingo-sub-2099283%22%20slang%3D%22en-US%22%3EInserting%20one%20checkbox%20and%20linking%20to%20multiple%20cells%20to%20trigger%20sums%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2099283%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20adding%20checkboxes%20that%20are%20linked%20to%20cells%20to%20calculate%20costs.%20So%20far%20I%20have%20one%20check%20box%20per%20cell.%20I%20was%20wanting%20to%20link%201%20checkbox%20to%206%20cells%20so%20that%20I%20can%20check%20one%20box%20and%20select%20them%20all.%20Is%20this%20possible%3F%20I%20have%20not%20found%20it%20to%20work%20when%20trying%20to%20link%20the%20cell%20and%20selecting%20the%20range.%20Any%20advice%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2099283%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2099427%22%20slang%3D%22de-DE%22%3ESubject%3A%20Inserting%20one%20checkbox%20and%20linking%20to%20multiple%20cells%20to%20trigger%20sums%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2099427%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F943711%22%20target%3D%22_blank%22%3E%40Jacqueline28%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELink%20check%20boxes%20to%20multiple%20cells%20at%20the%20same%20time%20using%20VBA%20code%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20are%20many%20check%20boxes%20to%20be%20linked%20to%20other%20cells%2C%20the%20first%20method%20will%20not%20work%20effectively.%20To%20link%20them%20to%20multiple%20cells%20at%20a%20time%2C%20you%20can%20apply%20the%20following%20VBA%20code.%20Please%20do%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EGo%20to%20your%20worksheet%20with%20the%20list%20of%20checkboxes.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EHold%20down%20the%20ALT%20%2B%20F11%20keys%20to%20open%20the%20Microsoft%20Visual%20Basic%20for%20Applications%20window.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EClick%20Insert%26gt%3B%20modules%20and%20paste%20the%20following%20code%20in%20the%20Module%20window%20box.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVBA%20code%3A%20Link%20check%20boxes%20to%20multiple%20cells%20at%20the%20same%20time%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E'1.Proposal...try...this%20solution..%3A)%0ASub%20LinkChecks%20()%0Ai%20%3D%202%0AFor%20Each%20cb%20In%20ActiveSheet.CheckBoxes%0Acb.LinkedCell%20%3D%20Cells%20(i%2C%20%22B%22).%20Address%0Ai%20%3D%20i%20%2B%201%0ANext%20cb%0AEnd%20Sub%0A%0A'2.%20Proposal%20Solution%0APrivate%20Sub%20CheckBox1_Click()%0A%20%20'Code%20in%20die%20entsprechende%20Tabelle%20und%20Bereich%20anpassen!%0A%20%20If%20CheckBox1%20Then%0A%20%20%20%20Range(%22D1%3AF1%22).Interior.ColorIndex%20%3D%203%0A%20%20Else%0A%20%20%20%20Range(%22D1%3AF1%22).Interior.ColorIndex%20%3D%2010%0A%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EAnd%20then%20press%20F5%20to%20run%20this%20code%2C%20all%20of%20the%20check%20boxes%20in%20the%20active%20worksheet%20have%20been%20linked%20to%20the%20cells.%20If%20you%20select%20the%20check%20box%2C%20the%20corresponding%20cell%20will%20be%20displayed%20TRUE%20If%20you%20clear%20a%20check%20box%2C%20the%20linked%20cell%20should%20be%20displayed%20FALSE.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20In%20the%20code%20above%2C%20i%20%3D%202%2C%20the%20number%202%20is%20the%20first%20row%20of%20your%20check%20box%20and%20the%20letter%20B%20is%20the%20column%20position%20that%20you%20need%20to%20link%20the%20check%20boxes%20to.%20You%20can%20change%20them%20as%20you%20wish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20hope%20I%20could%20help%20you%20a%20bit.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20know%20that%20I%20don't%20know%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2099463%22%20slang%3D%22de-DE%22%3ESubject%3A%20Inserting%20one%20checkbox%20and%20linking%20to%20multiple%20cells%20to%20trigger%20sums%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2099463%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efirst%20method%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EThe%20second%20method%20follows.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20know%20that%20I%20don't%20know%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 all. Is this possible? I have not found it to work when trying to link the cell and selecting the range. Any advice?

 

Thanks so much!

21 Replies

@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)

 

@Nikolino 

first method

 

The second method follows.

 

Nikolino

I know that I don't know (Socrates)

 

 

Thank you so much for these insights. What is the 10 and 3 referencing in the formula?

 

Thanks!

@Jacqueline28 

What is the 10 and 3 referencing in the formula?

This property specifies a color as an index into the color palette.

Please see the following link where there is also a table with the colors.

ColorIndex property

https://docs.microsoft.com/en-us/office/vba/api/excel.colorindex

 

Hope I was able to help you.

 

Nikolino

I know I don't know anything (Socrates)

 

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

Hi @Nikolino - 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!

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

 

Thank you. @Nikolino 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.

@Jacqueline28 

 

First, I would put the number of rows per group into a cell so that if you change the number of rows per group (3) to something else at a later date, it should hopefully be easy to update (I put it in cell A62).

 

I believe this will work - use offset and deduct one for the row offset if the remainder of the row count / 3 is greater than zero (so A6 references A6, A7 references A6, and A8 references A7).

 

=OFFSET($A3,-(MOD(ROWS($A$3:$A3)-1,$A$62)>0),0)

 

For your conditional sum, I personally would un-merge Column A and link the lower cell to the upper cell. Then, I think it would be a straightforward sumif.

 

Otherwise, you could try (and this would benefit from newer functions like LET, UNIQUE, FILTER, and SEQUENCE, which I don't have yet):

 

=SUM(((INT((ROW(A3:A61)-ROW(A$3))/A62)+1)=MMULT(TRANSPOSE(--(A3:A61)),--(INT((ROW(A3:A61)-ROW(A$3))/A62)+1=TRANSPOSE(ROW(INDIRECT("1:"&INT(ROWS(A3:A61)/A62)+1)))))*TRANSPOSE(ROW(INDIRECT("1:"&INT(ROWS(A3:A61)/A62)+1))))*Table133[Column9])

 

And, you may need to hit Ctrl+Shift+Enter after typing/copying into the formula bar (if you have  dynamic arrays, you may not need to).

 

 

 

@Jacqueline28 

I think I couldn't offer you a better description and better approach than Mr. JMB17 - Jeff Blakley has offered you.

 

Thank you for your patience and time.

 

Wish you a nice day / night with lots of health, joy and love.

 

Nikolino

I know I don't know anything (Socrates)

@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)))

@Sergei Baklan Thank you for all of your help. I have attached another sheet. I am trying to get a formula for the Conditional Formatting that when I check the box in the merged cells that it highlights all the 8 rows (instead of just row 3) as well as recognizes all 8 values in the total line. Thanks in advance for any help!

@Jacqueline28 

I'm not sure which 8 rows do you mean. If we speak about the columns that could be

image.png

I apologize @Sergei Baklan . Apparently, the right sheet did not attach. Here is the correct one that I was referencing to in the question above. Thanks!

@Jacqueline28 

It's hard to test on only one block, perhaps rue formula is

=INDEX($A:$A,INT((ROW()-ROW($A$3))/8)*8+ROW($A$3))

 

Thank you!@Sergei Baklan  Do you happen to know why my formula for the total is not adding up all the numbers when the boxes are checked? Looks like it is only calculating the top cell and bottom 2 vs. all. Thanks!

@Jacqueline28 

That's since your formula

=SUMIF(A3:A11,TRUE,J3:J11)

check if any of A3:A11 is TRUE and sum based on this. Actually if A3 is true you shall sum rows 3:8 since in A3 is check box for entire block. That could be formula like in conditional formatting rule to check entire block.

 

In your latest sample I see that the block could be of different size, that complicate the task. If so, we need to have some formal logic to recognize how many rows in the block, so far I don't see it.

What would an equation be then to sum rows 3:8 if true and also add in rows 10:11? @Sergei Baklan 

@Jacqueline28 

It depends on how Exel knows that from row 3 it shall sum till row 8 and from row 10 to row 11. Other words, which formal logic do you use to separate the blocks.

=INDEX($A:$A,INT((ROW()-ROW($A$3))/8)*8+ROW($A$3)) this was the formula in my conditional formatting to identify that rows 3:8 were together. Don't know how I write this to indicate they are in the sum once the A3:A8 is checked to be "true"

@Sergei Baklan