URGENT: What's wrong with my program?

[Jill Gordon]

I am creating a visual basic program so that I can have connecting drop-down lists in my spreadsheet. My first two comboboxes work (these are for manufacturer and model). I have tried to add another combobox for registration number but it doesn't work. Can anyone tell me what is wrong with the program?

In my workbook it goes along the lines of:
Private Sub Workbook_Open()
Sheet1.ComboBox1.AddItem "Alfa Romeo", 0
Sheet1.ComboBox1.AddItem "Aston Martin", 1 ...etc

Sheet 1.ComboBox1.ListIndex = 0

End Sub

My program for sheet 1 looks like this:
Private Sub ComboBox1.List(ComboBox1.ListIndex) = "Alfa Romeo" Then
ComboBox2.AddItem "145", 0
ComboBox2.AddItem "146", 1 ...etc

ElseIf ComboBox2.List(ComboBox2.ListIndex) = "Aston Martin" Then
ComboBox2.AddItem "DB7", 0
ComboBox2.AddItem "Lagonda", 1 ...etc

Else
MsgBox "There was a problem in ComboBox1_Click"

End If
ComboBox2.ListIndex = 0

End Sub

For sheet 3 my program looks like this:
Private Sub ComboBox2_Change()

ComboBox3.Clear

If ComboBox2.List(ComboBox2.ListIndex) = "145" Then
ComboBox3.AddItem "A", 0
ComboBox3.AddItem "B", 1 ...etc

Else
MsgBox "There was a problem in ComboBox2_Click"

End If
ComboBox3.ListIndex = 0

End Sub

[Cimperiali]

I am not sure about this, as you provided no info about error you receive but:
before using the combo.listIndex property to read data in combo, you should be sure an item is selected (=listindex <> -1)
Before you set the listindex to a value, you should be sure you have enough elements (listCount > 0)
Ie:

private Sub Command1_Click()
If Combo2.ListIndex = -1 then
'No item selected
'you can do the following only if combo2.listcount is > 0
if Combo2.listCount > 0 then
Combo2.ListIndex = 0
else
msgbox "No items in combo"
exit sub
end if
end if

If Combo2.List(Combo2.ListIndex) = "a" then
Combo1.AddItem "a", 0
Combo1.AddItem "b", 1
End If

End Sub



Hope this may help.

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.