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

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

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

Merjet

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

"merjet" wrote:

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

Merjet

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

"Incidental" wrote:

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

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

Oops.

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

Merjet

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

can I send you the file?

"merjet" wrote:

Oops.

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

Merjet

Try:

dim iRow as long

(Instead of integer)

Alex wrote:

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

can I send you the file?

"merjet" wrote:

Oops.

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

Merjet




--

Dave Peterson