Excel multi Combo Box Problem..

[techxupport]

Wow...today at work I was given a project to do and I sit my by desk and google for 3 hours and can't found a clue on how to set this up. Any help will be deeply appreciated.

The easiest way for me to explain this is using automaker as an example.

Let say there are 3 auto makers.
Honda
Nissan
Toyota

Each of them have different model cars
Honda: Accord, Civic, Pilot
Nissan: Maxima, Altima, Pathfinder
Toyota: Camry, Corolla, Landcruiser

What I want to do is when I select Honda from the first combo box, on the second combo box will only shows Accord, Civic and Pilot and so on for the Nissan and Toyota.

I've Honda on A1, Nissan on A2 and Toyota on A3
Accord on B1
Civic on B2
Pilot on B3
Maxima on B4
Altima on B5
Pathfinder on B5
Camry on B6
Corolla on B7
Landcruiser on B9.

Which Combo box should I use, Form or Control Toolbox Combo box?

Please help.

[Jacob Hilderbrand]

We can do this with Data | Validation and Named Ranges. Set the data up in a convenient layout like in the picture then set the Validation as =Indirect(B9) where B9 has the Makes (Which are also Named Ranges for the models).

[Jacob Hilderbrand]

We will also want to clear the value in C9 whenever there is any change to B9. To do that we can use VBA. Right click on the sheet tab and select View Code. Paste the following code into the Code Window that opens up.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B9")) Is Nothing Then
        Range("C9").ClearContents
    End If
End Sub

Close the VBE and try it out. This code will run automatically when needed.

See the attached file for more assistance.

[techxupport]

Thanks DrJ.

The Data | Validation worked. I was going crazy thinking that only VB scripts can do this, never thought about a function within excel. As for the extra tip, it will definitely come in handy. For this spreadsheet, all the value is fixed.

Thanks again.

[Jacob Hilderbrand]

You're Welcome

Take Care

[techxupport]

We will also want to clear the value in C9 whenever there is any change to B9. To do that we can use VBA. Right click on the sheet tab and select View Code. Paste the following code into the Code Window that opens up.

[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B9")) Is Nothing Then
Range("C9").ClearContents
End If

End Sub
[/vba]

Close the VBE and try it out. This code will run automatically when needed.

See the attached file for more assistance.

DRJ,

Looking at the VBA script. What if I need to do that to more then one field, for example like C9:c50....

Do I change the code like this?

Thanks for your help.


[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B9:B50")) Is Nothing Then
Range("C9:C50").ClearContents
End If

End Sub
[/vba]

[techxupport]

just try it and it clear everything. Guess I have to replicate the formula for all the rows unless there is a way to do dynamic ranges.