Jan 26 2021 01:08 PM
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!
Jan 26 2021 01:37 PM
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:
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
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)
Jan 26 2021 01:48 PM
Jan 26 2021 02:05 PM
Thank you so much for these insights. What is the 10 and 3 referencing in the formula?
Thanks!
Jan 27 2021 11:05 AM
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
Jan 28 2021 11:31 AM
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!
Jan 28 2021 01:46 PM
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.
Jan 28 2021 02:12 PM
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.
Jan 28 2021 11:40 PM
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).
Jan 29 2021 11:17 AM
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)
Jan 29 2021 12:25 PM
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)))
Feb 03 2021 02:08 PM
@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!
Feb 03 2021 02:55 PM
Feb 04 2021 09:36 AM
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!
Feb 04 2021 01:02 PM
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))
Feb 05 2021 06:41 AM
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!
Feb 05 2021 08:50 AM
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.
Feb 05 2021 11:04 AM
What would an equation be then to sum rows 3:8 if true and also add in rows 10:11? @Sergei Baklan
Feb 05 2021 11:35 AM
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.
Feb 05 2021 11:38 AM
=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"