01-25-2018 12:58 AM
01-25-2018 12:58 AM
So I am a rookie when it comes to excel stuff, but have come a long way in last week or two. I have been killing myself trying to figure this out.
I have a form/spreadsheet that I am creating with multiple checkboxes. I have it set up so that the user can select multiple checkboxes, which then is linked to other cells with text. They pick what they want, text is spit out in another column. The text is concatenated to created a list separated by commas. It works pretty well. However, I want to add additional checkboxes that automatically select others. Like a quick selection option that autoselects a specific group of boxes (and performs the =if C1, "text", "") for each of those in the group). These are arranged in a specific order when concatenated. So in order to avoid redundancy I want the master group box to trigger the specified boxes so the concatenated "product makes sense....probably not explaining well.
It is kind of like a menu. You can choose any combination A, B, C, or D. You could also select X, which would be ABCD. But do it in one click. But I also want option to take away one. So user clicks X, but doesn't want C. They could click ABD. But I want them to be able to click X, then unclick C. There are many, many combinations (Like A through Z). I have found code to check and clear all checkboxes, which doesn't help. It is more like X would click group ABCD, Y would click EFGH, Z would click IJKL. If I want A, G, IJKL, then I would click A, G, Z. If I didn't want K, I would unclick that too (and I would home Z would unclick but IJL would stay).
Is this grouping? Dependent checkboxes? Using form controls, not activeX. I read something about mother/daugher stuff...Tried recording a macro buy clicking the "groups" a boxes and then assigning to master box, but not working.
Would appreciate any guidance. The project is pretty complex (at least for me) and hard to explain. Hoping above explanation makes some sense.
01-25-2018 07:27 AM
If you name your controls smartly this can be done with a bit of VBA.
Name the checkboxes within a group cbx_1_1, cbx_1_2, cbx_1_3, cbx_1_4
Name each group checkbox: cbxGrp_1, cbxGrp_2, ...
Attach this macro to each group checkbox:
Sub cbxGrp_Click() Dim oCbx As CheckBox Dim lValue As Long Dim sGrp As String sGrp = Application.Caller lValue = ActiveSheet.CheckBoxes(sGrp).Value sGrp = Split(sGrp, "_")(1) For Each oCbx In ActiveSheet.CheckBoxes If oCbx.Name Like "cbx_" & sGrp & "_*" Then oCbx.Value = lValue End If Next End Sub
01-28-2018 05:38 AM
Thank you Jan
This is working nicely. At first I was trying to group the checkboxes, but I get now that you leave them standing alone and only apply macro to the "quick pick" group box and that the "sub" checkboxes must have sequential numbers to work. Thank you!
Now here is another question. Can I make the "quick pick" group check box change from checked to mixed when one of the cbxGrp_1 "sub" checkboxes is unchecked? I want the cbxGrp checkbox to stay selected if things are added (and none of the "sub" checkboxes is unchecked - as it is acting now). I don't want it to change to unchecked if one of the "sub" cbx is unchecked, since this will uncheck all the "sub" checkboxes. I just want it to go to mixed to imply to user they have deviated from the "standard group".
01-29-2018 01:07 AM - edited 01-29-2018 01:08 AM
If you attach this macro to the sub-checkboxes that should do it:
Sub cbxGrp_Sub_Click() Dim oCbx As CheckBox Dim sGrp As String Dim lTrue As Long Dim lFalse As Long Dim lMixed As Long 'Checkbox values: '1 true '-4146 false '2 mixed sGrp = Application.Caller sGrp = Split(sGrp, "_")(1) For Each oCbx In ActiveSheet.CheckBoxes If oCbx.Name Like "cbx_" & sGrp & "_*" Then If oCbx.Value = 1 Then lTrue = lTrue + 1 ElseIf oCbx.Value = -4146 Then lFalse = lFalse + 1 Else lMixed = lMixed + 1 End If End If Next With ActiveSheet.CheckBoxes("cbxGrp_" & sGrp) If lTrue > 0 And lFalse = 0 And lMixed = 0 Then 'true .Value = 1 ElseIf lTrue > 0 And (lFalse > 0 Or lMixed > 0) Then .Value = 2 'mixed ElseIf lFalse > 0 And lTrue = 0 And lMixed = 0 Then .Value = -4146 'false End If End With End Sub