Dependent List in Control Combo-Box

A

Abdul

Can any body tell me that is it possible to create dependent lists i
Combo-boxes ?

Thank
 
P

papou

Hi Abdul
Yes this is possible, here's a sample code with combined combo boxes from
the Control tools box.
Make sure you have your first list of values in column 1 and other dependant
values in the adjacent columns
Place code on your first combo(please note no headers used in the data
lists):
Private Sub ComboBox1_Change()
MajListes Me.ComboBox2, ComboBox1.ListIndex
End Sub
Private Sub MajListes(Cbbx As MSForms.ComboBox, ValeurIndx As Integer)
Const PremiereLigne As Integer = 1
Dim DerniereLigne As Integer, NoCol As Integer, CelEntree As Range
NoCol = ValeurIndx + 2
DerniereLigne = Cells(PremiereLigne, NoCol).End(xlDown).Row
Set CelEntree = ActiveSheet.Range(Cells(PremiereLigne, NoCol),
Cells(DerniereLigne, NoCol))
With Cbbx
.ListFillRange = CelEntree.Address
.ListIndex = 0
End With
Set CelEntree = Nothing
End Sub

HTH
Cordially
Pascal
 
A

Abdul

will you please elaborate it. Where do i have to put these codes i
Excel?

Thank you for your advice in advanc
 
P

papou

Abdul
Uinfortunately I cannot find any example at the moment so I will try and
explain as clearly as possible:
Place your list entries onto your worksheet starting column A row 1 for your
first choice list
Place other entries depending on your first list choice into adjacent
columns (therefore starting column B)
Create two combo box controls from the Control tools box and place them onto
your worksheet
Right-click on your first combo and choose Properties
In the ListFillRange property set the current address of your first list
entries (for example Sheet1!A1:A10)
Close the properties window
right-click again on your first combo box and choose View code
paste the sample code I posted previously

Cancel the create(creation?) mode (click on the relevant button from the
control tools box - the one with a pen)
Choose an item in your first combo and see what happens in the other combo.

HTH
Cordially
Pascal
 
A

Abdul

i did what u explained, but code stops at:

Set CelEntree = ActiveSheet.Range(Cells(PremiereLigne, NoCol),
Cells(DerniereLigne, NoCol))

What i have to de here
 
P

Papou

Abdul
Sorry for the delay in my answer (just got home now)
The code should not break, thus it should be in one whole line:
Set CelEntree = ActiveSheet.Range(Cells(PremiereLigne,
NoCol),Cells(DerniereLigne, NoCol))
If useful, I could send you a sample workbook (if so provide your email)

HTH
Cordially
Pascal
 
M

MIKO

Another solution with less code:

- Example:
List 1 Name = Products
Items
Fruit
Vegetable

List 2 Name = Fruit
Items
Banana
Orange
Apple

List3 Name = Vegetable
Items
Cabbage
Lettuce

Next Steps :
- Create 2 ComboBox (ComboBox1 and ComboBox2)
- Open Properties for ComboBox1 and do ListfillRange =
Products... then close this window.
- Right-clik again on ComboBox1 and select "View Code"
and paste this code

Private Sub ComboBox1_Change()
With ComboBox2
.Text = ""
.ListFillRange = ComboBox1.Text
.ListIndex = 0
End Sub

-MIKO
 
Top