vba code to aggregate data selection from checkboxes into listbox

BrakeJake

New Member
Joined
Aug 8, 2012
Messages
4
I am at a loss for how to do this. My current code is simple and displays data only for the latest checked box:

Private Sub ComboBox1_Change()

End Sub

Private Sub CheckBox1_Change()
Me.ListBox1.RowSource = "Sheet2!A22:A32"
End Sub

Private Sub CheckBox2_Change()
Me.ListBox1.RowSource = "Sheet2!A2:A7, A22:A26"
End Sub

Private Sub CheckBox3_Change()
Me.ListBox1.RowSource = "Sheet2!A8:A13, A22:A26"
End Sub

Private Sub CheckBox4_Change()
Me.ListBox1.RowSource = "Sheet2!A14:A17"
End Sub

Private Sub CheckBox5_Change()
Me.ListBox1.RowSource = "Sheet2!A18:A21"
End Sub

Private Sub Label1_Click()

End Sub


However, I need these checkboxes to aggregate as they are checked without duplicating data that is already displayed (cells A22:A26 are shared between checkboxes1-3). Also, I just realized that my code errors on Me.ListBox1.RowSource = "Sheet2!A2:A7, A22:A26", so I guess I need to learn how to make split references too. Thanks ahead of time if you decide to take this on.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,358
Does the order of the items in the list box matter? If yes, what is your criteria; the order they were selected, same order as in column A, sorted?
 

BrakeJake

New Member
Joined
Aug 8, 2012
Messages
4
Does the order of the items in the list box matter? If yes, what is your criteria; the order they were selected, same order as in column A, sorted?

The order doesn't really matter just as long as it aggregates the list correctly... Thanks, AlphaFrog
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,358
This is one way...

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox1_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox2_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox3_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox4_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox5_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Aggregate()

    [COLOR=darkblue]Dim[/COLOR] strRng [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cell [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]If[/COLOR] CheckBox1 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A22:A32,"
    [COLOR=darkblue]If[/COLOR] CheckBox2 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A2:A7,A22:A26,"
    [COLOR=darkblue]If[/COLOR] CheckBox3 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A8:A13,A22:A26,"
    [COLOR=darkblue]If[/COLOR] CheckBox4 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A14:A17,"
    [COLOR=darkblue]If[/COLOR] CheckBox5 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A18:A21,"
    
    Me.ListBox1.Clear
    [COLOR=darkblue]If[/COLOR] Len(strRng) [COLOR=darkblue]Then[/COLOR]
        strRng = Left(strRng, Len(strRng) - 1)
        [COLOR=darkblue]With[/COLOR] Sheets("Sheet2")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Intersect(.Range("A:A"), .Range(strRng))
            Me.ListBox1.AddItem cell.Value
        [COLOR=darkblue]Next[/COLOR] cell
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

End [COLOR=darkblue]Sub[/COLOR]
 

BrakeJake

New Member
Joined
Aug 8, 2012
Messages
4
This is one way...

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox1_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox2_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox3_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox4_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox5_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Aggregate()

    [COLOR=darkblue]Dim[/COLOR] strRng [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cell [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]If[/COLOR] CheckBox1 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A22:A32,"
    [COLOR=darkblue]If[/COLOR] CheckBox2 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A2:A7,A22:A26,"
    [COLOR=darkblue]If[/COLOR] CheckBox3 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A8:A13,A22:A26,"
    [COLOR=darkblue]If[/COLOR] CheckBox4 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A14:A17,"
    [COLOR=darkblue]If[/COLOR] CheckBox5 [COLOR=darkblue]Then[/COLOR] strRng = strRng & "A18:A21,"
    
    Me.ListBox1.Clear
    [COLOR=darkblue]If[/COLOR] Len(strRng) [COLOR=darkblue]Then[/COLOR]
        strRng = Left(strRng, Len(strRng) - 1)
        [COLOR=darkblue]With[/COLOR] Sheets("Sheet2")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Intersect(.Range("A:A"), .Range(strRng))
            Me.ListBox1.AddItem cell.Value
        [COLOR=darkblue]Next[/COLOR] cell
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

End [COLOR=darkblue]Sub[/COLOR]

I hope you can help me again...

I have to get rid of the list box and add another column of data to pull into the mix. The userform will consist of 7 checkboxes and a submit button. Two of the checkboxes will be limiters of sorts... It still needs to aggregate without duplication and drop the values into another worksheet's "A" and "B" columns when submitted. The setup will look like this:

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox1_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox2_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox3_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox4_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox5_Change()
    Aggregate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Aggregate()

    [COLOR=darkblue]Dim[/COLOR] strRng [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cell [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]If[/COLOR] CheckBox1 [COLOR=darkblue]And Checkbox6 Then[/COLOR] strRng = strRng & "A1:A10,"
    [COLOR=darkblue]If[/COLOR] CheckBox1 [COLOR=darkblue]And Checkbox7 Then[/COLOR] strRng = strRng & "B1:B10,"
    [COLOR=darkblue]If[/COLOR] CheckBox1 [COLOR=darkblue]And Checkbox6 [/COLOR][COLOR=darkblue]And Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "A1:A10,B1:B10,"

    [COLOR=darkblue]If[/COLOR] CheckBox2 [COLOR=darkblue]and Checkbox6 Then[/COLOR] strRng = strRng & "A10:A20,"
    [COLOR=darkblue]If[/COLOR] CheckBox2 [COLOR=darkblue]And Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "B10:B20,"
    [COLOR=darkblue]If[/COLOR] CheckBox2 [COLOR=darkblue]And Checkbox6 [/COLOR][COLOR=darkblue]And[/COLOR][COLOR=darkblue]Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "A10:A20,B10:B20,"

    [COLOR=darkblue]If[/COLOR] CheckBox3 [COLOR=darkblue]and Checkbox6 Then[/COLOR] strRng = strRng & "A20:A30,"
    [COLOR=darkblue]If[/COLOR] CheckBox3 [COLOR=darkblue]And Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "B20:B300,"
    [COLOR=darkblue]If[/COLOR] CheckBox3 [COLOR=darkblue]And Checkbox6 [/COLOR][COLOR=darkblue]And[/COLOR][COLOR=darkblue]Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "A20:A30,B20:B30,"

    [COLOR=darkblue]If[/COLOR] CheckBox4 [COLOR=darkblue]And Checkbox6 Then[/COLOR] strRng = strRng & "A30:A40,"
    [COLOR=darkblue]If[/COLOR] CheckBox4 [COLOR=darkblue]And Checkbox7 Then[/COLOR] strRng = strRng & "B30:B40,"
    [COLOR=darkblue]If[/COLOR] CheckBox4 [COLOR=darkblue]And Checkbox6 [/COLOR][COLOR=darkblue]And Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "A30:A40,B30:B40,"

    [COLOR=darkblue]If[/COLOR] CheckBox5 [COLOR=darkblue]and Checkbox6 Then[/COLOR] strRng = strRng & "A40:A50,"
    [COLOR=darkblue]If[/COLOR] CheckBox5 [COLOR=darkblue]And Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "B40:B50,"
    [COLOR=darkblue]If[/COLOR] CheckBox5 [COLOR=darkblue]And Checkbox6 [/COLOR][COLOR=darkblue]And[/COLOR][COLOR=darkblue]Checkbox7 [/COLOR][COLOR=darkblue]Then[/COLOR] strRng = strRng & "A40:A50,B40:B50,"

'this needs to go away...
Me.ListBox1.Clear
    [COLOR=darkblue]If[/COLOR] Len(strRng) [COLOR=darkblue]Then[/COLOR]
        strRng = Left(strRng, Len(strRng) - 1)
        [COLOR=darkblue]With[/COLOR] Sheets("Sheet2")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Intersect(.Range("A:A"), .Range(strRng))
            Me.ListBox1.AddItem cell.Value
        [COLOR=darkblue]Next[/COLOR] cell
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

End [COLOR=darkblue]Sub[/COLOR]
This one is blowing my mind...
Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,767
Members
412,617
Latest member
mlharris
Top