vba code to aggregate data selection from checkboxes into listbox

B

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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
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?
 
Upvote 0
AlphaFrog said:
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?
Click to expand...

The order doesn't really matter just as long as it aggregates the list correctly... Thanks, AlphaFrog
 
Upvote 0
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]
 
Upvote 0
AlphaFrog said:
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]
Click to expand...

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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Display data of Specific Sheets on Listbox Userform
2
Replies
11
Views
862
Akuini
Akuini
R
  • Question
Delete multi select rows from listbox
Replies
0
Views
525
Rfriend
R
A
  • Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
706
auglocqnuk
A
M
  • Question
I need help with this code to modify the data into a table using Userform for VBA Excel (I want to do the range unlimited instead to do C7:C80000
Replies
5
Views
469
Dan_W
D
C
  • Question
PREVENTING LISTBOX SELECTION FROM REVERTING BACK TO FIRST ITEM IN ROWSOURCE WHEN USERFORM LOSES AND REGAINS FOCUS
Replies
0
Views
738
chazrab
C

Forum statistics

Threads
1,214,893
Messages
6,122,118
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top