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.

 

[amoverton2]

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.
.....


Yes


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

Thanks for the help!!! and here comes a new thread!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.