Populate one combo box based on the selection of another combo box

[Alex]

How can I have have the results of a second combobox based on the selection
in the first combobox
Ex. if Ford is selelected in the car combobox the model combobox will only
list ford models

Thanks

Alex

 

[Incidental]

Hi Alex

One way you can do it is to use select case to load an array into the
model combobox like the code below

Private Sub ComboBoxCar_Change()
Select Case ComboBoxCar.Value 'Select case using the combobox1 _
current value
Case "ford" 'if it's ford do the code below
ComboBoxModel.List = Array("mustang", "fusion") 'load an array of
models _
to the second combobox
Case "other" 'if it's other do the code below
ComboBoxModel.List = Array("some car", "some other car") 'as above
load _ the array
End Select
ComboBoxModel.ListIndex = 0 'set the second combobox to the first
value _
which would be 0 this will remove the previous array from showing in
the list
End Sub

hope this is of some help

S

 

[merjet]

What kind of ComboBoxes? On UserForm? On worksheet from Controls
Toolbox toolbar? On worksheet from Forms toolbar?

Merjet

 

[Alex]

I am using the Forms toolbar and working on a worksheet - I don't have much
experience using controls with Excel any help would be appreciated

Thanks

Alex

 

[Alex]

I get an Obeject Required error message when I your code. I am working with
controls on a worksheet


Sub DropDown2_Change()

Select Case ComboBox.xls!DropDown2.Value 'Select case using the combobox1 _
current value
Case "ford" 'if it's ford do the code below
ComboBox.xls!DropDown2.List = Array("mustang", "fusion") 'load an array of
models _ to the second combobox
Case "other" 'if it's other do the code below
ComboBox.xls!DropDown2.List = Array("some car", "some other car") 'as above
load _ the array
End Select
ComboBox.xls!DropDown2.ListIndex = 0 'set the second combobox to the first
value _ which would be 0 this will remove the previous array from showing in
the List
End Sub

 

[merjet]

Suppose brands and models in columns B & C starting in Row 2 like
this:
Ford Focus
Ford Mustang
Chevy Impala
Chevy Malibu

and cell E1 is DropDown1's cell link. Assign this macro to DropDown1.

Sub DropDown1_Change()
Dim iRow As Integer
Dim strBrand As String
Dim strLoc1 As String

'get selection using DropDown1's cell link
strBrand = ActiveSheet.Cells(ActiveSheet.Range("E1") + 1, 1)
iRow = 2
Do
If ActiveSheet.Cells(iRow, 2) = strBrand And iCt = 0 Then
strLoc = ActiveSheet.Cells(iRow, 3).Loc
iCt = 1
End If
If ActiveSheet.Cells(iRow, 2) <> strBrand And iCt = 1 Then
strLoc = strLoc & ":" & ActiveSheet.Cells(iRow - 1, 3).Loc
iCt = 2
End If
iRow = iRow + 1
Loop Until iCt = 2
ActiveSheet.Shapes("Drop Down 2").Select
With Selection
.ListFillRange = strLoc
.LinkedCell = ""
.DropDownLines = 8
.Display3DShading = False
End With
ActiveSheet.Range("E1").Select 'unselects DropDown2
End Sub


Hth,
Merjet

 

[merjet]

Oops.

"3).Loc" s/b "3).Address" in 2 places

Merjet

 

[Alex]

I am getting an overflow error message at
iRow = iRow + 1

can I send you the file?

 

[Dave Peterson]

Try:

dim iRow as long

(Instead of integer)

I am getting an overflow error message at
iRow = iRow + 1

can I send you the file?