Dependent Combobox Problem

[amoverton2]

I have a userform with 4 comboboxes, I would like them to be dependent comboboxes with an advanced filter used in the process to whittle down the selection by the 4th box.

I have been watching videos and reading tutorials but I can't find exactly what I want, however I did find one but it returns an error that is not seen in the tutorial video.

Here is the code that returns the error:

Private Sub UserForm_Initialize()

Dim sh As Worksheet
Set sh = Sheets("Pool2")

Dim i As Long
For i = 2 To sh.Range("A10000").End(x1Up).Row (this is usually what the debugger picks on)

If Application.WorksheetFunction.CountIf(sh.Range("A2", "A" & i), sh.Cells(i, 1)) = 1 Then
Me.ComboBox1.AddItem sh.Cells(i, 1)
End If
Next i

End Sub

Here is a snippet of the data being used (the max is 600 rows). The idea is for someone to pick the code, then the rate, then if they qualified, and the last combobox is the name with no duplicates in any of the comboboxes. If there is a way to do it without using an advanced filter that'll be cool. In the original worksheet the columns used below are not A,B,C,D but D (Code), B (Rate), K (Qualified), C (Name). Sorry to make it hard...

Book1
	A	B	C	D
1	CODE	RATE	QUALIFIED TO BE A SPONSOR?	LAST,FIRST NAME
2	930	GSM2	YES	ABINA,STEPHEN
3	950	ETN1	NO	ACKERMAN,BRANDON
4	SURGEMAIN	EN2	NO	ACORDA,ROBERT
5	105.3	EMN2	NO	ACOSTA,BRANDON
6	900T	MMA2	NO	ADAMS,DRAKE
7	760	ND3	NO	ADAMS,HANNAH
8	210	ETVCS	NO	ADAMS,JACOB
9	930	MMN1	YES	AHEARN,JAMES
10	SURGEMAIN	FC2	NO	AKAMINE,JADELYN
11	305	EMN1	NO	ALEXIS,JOSIAH
12	TBD	MMN1	NO	ALLEN,CHACE
13	760	BM2	NO	ALVAREZ,FERNANDO
14	930	GSM2	NO	ALVAREZ,ORLYN
15	900T	MMN1	NO	ANDERSON,AARON
16	100	CMDCM	YES	ANDERSON,LEONARD
17	760	ND2	YES	ANDERSON,RUSSELL
18	310	EMCS	NO	ANDRADE,SHERRE
19	960	MMN1	NO	ANGLIM,EVAN
20	930	MMA2	NO	ANGUIANOMENDEZ,VICTOR
21	310	GSMC	YES	APONTE,LARRY
22	246	ETV2	YES	ARAGON,AUSTIN
23	930	MM2	NO	ARMIJO,KYLAS
24	930	EN1	NO	ARNESTO,MARCELISEO
25	1170	PS2	NO	ARNOLD,VICTORIA
26	930	MM1	NO	ARRINGTON,LAMONT
27	105.3	MMN1	NO	BAL,BRIAN
28	MSD	DC2	NO	BALL,KATHRINE
29	930	TM2	NO	BALLARD,GARRETT
30	930	MM2	NO	BALLESTEROS,GABRIEL
31	210	FCC	YES	BARNARD,AARON
32	920	HT1	NO	BARNEY,CODY
33	760	FC2	NO	BARSI,RACHEL
34	305	EMN2	NO	BASNETT,NICHOLAS
35	TBD	ETN1	NO	BAUDER,REINHART
36	760	ND2	NO	BAUGHMAN,KENT
37	TBD	EM1	NO	BEALS,SKYLER
38	310	FCCS	NO	BELL,SHAWN
39	950	FCC	NO	BELOY,EUTIQUIO
40	SURGEMAIN	TM1	NO	BERKSON,SETH
41	920	HT1	NO	BERRIDGE,JONATHON
42	950	ET2	YES	BIENKO,CASSANDRA
43	TBD	MMN1	NO	BIGGS,NOEL
44	305	MMN2	NO	BINNER,DYLAN
45	MSD	HN	NO	BLACK,CHANCE
46	246	MMA2	YES	BLACKMON,SETH
47	930	GSM2	YES	BLACKWELL,JUSTIN
48	SURGEMAIN	MM1	NO	BLAKELEY,JAKQUELYNN
49	930	MMA2	NO	BLOCK,JONATHON
50	TBD	MMN2	YES	BOAZ,JACOB
Pool2

Cell Formulas
Range	Formula
A2:A50	A2	='[Future Sponsorship List.xlsm]Enlisted Sponsor Pool'!D5
B2:B50	B2	='[Future Sponsorship List.xlsm]Enlisted Sponsor Pool'!B5
C2:C50	C2	='[Future Sponsorship List.xlsm]Enlisted Sponsor Pool'!K5
D2:D50	D2	='[Future Sponsorship List.xlsm]Enlisted Sponsor Pool'!C5

Thank you, I really do appreciate the help, and if anyone can tell me why the code I post is returning the error in simple terms that'll be great.

 

[Akuini]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

 

[amoverton2]

Book1.xlsm

drive.google.com

Data and the Userform are shared. Edit at will. However, other sheets referenced in the userform code are not included (i.e. Search and Update) because they are not the focus.

 

[Gokhan Aycan]

For i = 2 To sh.Range("A10000").End(x1Up).Row

If this was copy pasted, typo in xlUp. You wrote x1Up.

 

[Akuini]

Try this:

I made some changes in Sub UserForm_Initialize() and on Change & Enter event of:
Me.cmbBBDCODE
Me.cmbSPONRATE
Me.cmbQUAL
Me.cmbSPONNAME
and add Sub toChange()

Part of the code does this:
if a combobox changes, then clear the next combobox. It's to prevent incorrect data on all combobox.
For example:

Private Sub cmbBBDCODE_Change()
    Me.cmbSPONRATE.Value = ""
    Me.cmbQUAL.Value = ""
    Me.cmbSPONNAME.Value = ""
    Call toChange
End Sub

The file:

amoverton2 - 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[amoverton2]

Try this:

I made some changes in Sub UserForm_Initialize() and on Change & Enter event of:
Me.cmbBBDCODE
Me.cmbSPONRATE
Me.cmbQUAL
Me.cmbSPONNAME
and add Sub toChange()

Part of the code does this:
if a combobox changes, then clear the next combobox. It's to prevent incorrect data on all combobox.
For example:

Private Sub cmbBBDCODE_Change()
    Me.cmbSPONRATE.Value = ""
    Me.cmbQUAL.Value = ""
    Me.cmbSPONNAME.Value = ""
    Call toChange
End Sub

The file:

amoverton2 - 1.xlsm

Shared with Dropbox

www.dropbox.com

This is awesome, thank you so much!!!

Can I ask one more favor?

How can I set the comboboxes to sort alphabetically (cmbSPONRATE and cmbSPONNAME) order or numerical order (then alphabetical, for the cmbQUAL) when entered?

 

[Akuini]

How can I set the comboboxes to sort alphabetically (cmbSPONRATE and cmbSPONNAME) order or numerical order (then alphabetical, for the cmbQUAL) when entered?

You need a helper column, I'm using col Z in sheet "Enlisted Sponsor Pool". You can adjust it here:

'sheet's name where the helper column is located.
Private Const sList As String = "Enlisted Sponsor Pool"

'the helper column, first cell
Private Const xH As String = "Z1"

If you want cmbBBDCODE.List to be sorted then use this line:
cmbBBDCODE.List = toSort(d.Keys)
but if col Code is always sorted then no need to sort it again, so use this line:
cmbBBDCODE.List = d.Keys
in this part:

Private Sub cmbBBDCODE_Enter()
.....   
    cmbBBDCODE.List = d.Keys
'    cmbBBDCODE.List = toSort(d.Keys)

The file:

amoverton2 - 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[amoverton2]

How does the info in Z1 & Z2 (the helper column) get there? Or do I just put two values in alphabetical order or is it from the private const? --> just trying to understand, thanks for your patience!

 

[amoverton2]

You need a helper column, I'm using col Z in sheet "Enlisted Sponsor Pool". You can adjust it here:

'sheet's name where the helper column is located.
Private Const sList As String = "Enlisted Sponsor Pool"

'the helper column, first cell
Private Const xH As String = "Z1"

If you want cmbBBDCODE.List to be sorted then use this line:
cmbBBDCODE.List = toSort(d.Keys)
but if col Code is always sorted then no need to sort it again, so use this line:
cmbBBDCODE.List = d.Keys
in this part:

Private Sub cmbBBDCODE_Enter()
.....  
    cmbBBDCODE.List = d.Keys
'    cmbBBDCODE.List = toSort(d.Keys)

The file:

amoverton2 - 2.xlsm

Shared with Dropbox

www.dropbox.com

I have another challenge that I'm having a hard time figuring out and you probably know how to do it.

How can I display everything that is on the Enlisted Sponsor Pool sheet in a listbox (in userform ESP) with dependent combo boxes to filter data where the listbox shows only shows the filtered data.

However this is not like before where I wanted specific data, I would like to be able to filter all data on the sheet.
For example; first filter is UIC, next filter is Paygrade, next filter is Code, next filter Qualified. I only need a max of 4 filters.

With new form with ListBox

amoverton2 - 2.xlsm

drive.google.com

 

[Akuini]

How does the info in Z1 & Z2 (the helper column) get there? Or do I just put two values in alphabetical order or is it from the private const? --> just trying to understand, thanks for your patience!

In "Sub UserForm_Initialize" the code populates the defined range (col D,B,K,C) into variable va,vb,vc,vd.
Then, for example, when you enter cmbSPONRATE, vb will be filtered by va value that match cmbBBDCODE.Value. The filtered value is assigned to var d (dictionary object) to make it unique.
The process is in this part:

Private Sub cmbSPONRATE_Enter()
.....
For i = LBound(va) To UBound(va)
If UCase(va(i, 1)) = UCase(Me.cmbBBDCODE.Value) Then d(vb(i, 1)) = Empty
Next

Me.cmbSPONRATE.List = toSort(d.Keys)

Then to sort the value in d, the code sends d values to a helper column (pointed by constant xH, in this example is Z1, well Z1 actually is a cell but it's easier to code by using the first cell in the helper column) then sort it. And then the values in the helper column is populated to cmbSPONRATE.List. The process is in Function toSort.

The helper column is a temporary helper column, its values is changed depend on which combobox is changed.
.....

is it from the private cons?

Yes

I have another challenge that I'm having a hard time figuring out and you probably know how to do it.

It's a different problem, you need to start a new thread.