Userform VBA cascading unique combo boxes

[cookie81]

Dear all

I have a file with a userform.
there are 6 combo boxes which i need to fill up based on the table sheet "Listuni"

Sector Desc	Segment Desc	Function Desc	Supervisor Name	Name	Rank
SECTOR1	Network	NetworkSEG	manger1	emp1	junior
SECTOR2	business	businessSEG	manger2	emp2	NA
SECTOR3	BAO	BAOSEG	manager3	emp3	NA
SECTOR4	tresor	tresorSEG	manger60	emp4	NA
SECTOR5	aa	aaSEG	Manager200	emp5	NA
SECTOR6	bb	bbSEG	manger1	emp6	NA
SECTOR7	cc	ccSEG	manger2	emp7	NA
SECTOR8	dd	ddSEG	manager3	emp8	NA
SECTOR9	ff	ffSEG	manger60	emp9	NA
SECTOR10	yy	yySEG	Manager200	emp10	NA
SECTOR1	ww	wwSEG	manger1	emp11	NA
SECTOR2	ee	eeSEG	manger2	emp12	NA
SECTOR3	rr	rrSEG	manager3	emp13	NA
SECTOR4	tt	ttSEG	manger60	emp14	NA
SECTOR5	uu	uuSEG	Manager200	emp15	directeur
SECTOR6	ii	iiSEG	manger1	emp16	directeur
SECTOR7	oo	ooSEG	manger2	emp17	directeur
SECTOR8	pp	ppSEG	manager3	emp18	directeur
SECTOR9	ss	ssSEG	manger60	emp19	directeur
SECTOR10	dd	ddSEG	Manager200	emp20	directeur
SECTOR1	ff	ffSEG	manger3	emp21	directeur
SECTOR2	gg	ggSEG	manger4	emp22	directeur
SECTOR3	hh	hhSEG	manager4	emp23	directeur
SECTOR4	jj	jjSEG	manger61	emp24	directeur
SECTOR5	kk	kkSEG	Manager201	emp25	directeur
SECTOR6	ll	llSEG	manger5	emp26	directeur
SECTOR7	zz	zzSEG	manger6	emp1	directeur
SECTOR8	xx	xxSEG	manager5	emp2	directeur
SECTOR9	vv	vvSEG	manger62	emp3	directeur
SECTOR10	bb	bbSEG	Manager202	emp1	ancien

<tbody>
</tbody><colgroup><col><col span="2"><col><col><col></colgroup>

I need for each combobox to be filled with values depending on the preceding box
for instance if I select sector 1 in combobox CboSector, i should only be able to select ff, Network, ww in combo box CboSeg

i need unique values no repeat values as the list is a couple of thousand rows,
Attached is a copy of my user form and the code that I have inserted. but his code does not remove duplicate and is not able to provide cascade data from one combo to the other. please help!

sector	Segment Desc	Function Desc	Supervisor Name	Name	Rank
SECTOR1	ff	ffSEG	manger3	emp21	directeur
	Network	NetworkSEG	manger1	emp1	junior
	ww	wwSEG	manger1	emp11	NA

<tbody>
</tbody>

" Dim i As Long, j As Long
Dim ws As Worksheet
Set ws = Worksheets("Listuni")

With Data
For j = 1 To 5
For i = 1 To ws.Cells(65536, j).End(xlUp).Row
Select Case j
Case 1
.CboSector.AddItem ws.Cells(i, j)
Case 2
.CboSeg.AddItem ws.Cells(i, j)
Case 3
.CboDep.AddItem ws.Cells(i, j)
Case 4
.CboMan.AddItem ws.Cells(i, j)
Case 5
.CboNam.AddItem ws.Cells(i, j)
Case 6
.CboRank.AddItem ws.Cells(i, j)
End Select
Next
Next
End With



End Sub

 

[MickG]

Try this in Your Userform Module
Data assumed to be on Sheet("Listuni").

Option Explicit
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
Call cValues(ComboBox1.Value, ComboBox2, 2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] ComboBox2_Change()
Call cValues(ComboBox2.Value, ComboBox3, 3)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Change()
Call cValues(ComboBox3.Value, ComboBox4, 4)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] ComboBox4_Change()
Call cValues(ComboBox4.Value, ComboBox5, 5)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] ComboBox5_Change()
Call cValues(ComboBox5.Value, ComboBox6, 6)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]With[/COLOR] Sheets("Listuni")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
 [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: Dic(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
    Me.ComboBox1.List = Application.Transpose(Dic.keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] cValues(txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Obj [COLOR="Navy"]As[/COLOR] Object, col [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] Dn              [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng             [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic             [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]With[/COLOR] Sheets("Listuni")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
  [COLOR="Navy"]End[/COLOR] With
  [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1).Value = txt [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                 Dic(Dn.Value) = Empty
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] Dn
Obj.List = Application.Transpose(Dic.keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick