reset dependent combobox if 1st combobox is changed

markda2000

New Member
Joined
Mar 24, 2011
Messages
9
I am a complete novice when it comes to VBA but have been asked by employers to complete a project. I am stuck on several parts of it but the one that is giving me the most pain at this moment is dependent comboboxs.

I have two comboboxs set up where the second has a choice that changes dependant on the first. eg

1st combobox is a list of Car manufactures
2nd combobox is a list of model.

This works fine for me however if the user picks say "Ford" from combobox1 then "mustang" from combobox2, if they then go back to combox1 and pick "Chevrolet" at present the result is Chevrolet Mustang. How do I clear combobox2 when the user goes back to Combobox1 and changes the value?

Many thanks in advance.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
hi and welcome to the board.
could you post the code you are using the controls the comboboxes.
its a lot easier to work from what you already have.
 
Upvote 0
Where are these comboboxes located, eg worksheet, userform, and how are they currently working?

There are a few ways you can 'clear' a combobox, for example if it's on a userform:
Code:
Combobox2.Clear
 
Upvote 0
thanks guys for the quick replies

The ranges of the combobox's are held on the worksheet
Combobox1 is ComboBoxmanufacturer
Combobox2 is ComboBoxHandset

Private Sub ComboBoxmanufacturer_Change()

Select Case ComboBoxmanufacturer

Case "Acer"
ComboBoxHandset.List = Range("Acer").Value

Case "Apple"
ComboBoxHandset.List = Range("Apple").Value

Case "Blackberry"
ComboBoxHandset.List = Range("blackberry").Value

Case "HP"
ComboBoxHandset.List = Range("HP").Value

Case "HTC"
ComboBoxHandset.List = Range("HTC").Value

Case "Huawei"
ComboBoxHandset.List = Range("Huawei").Value

Case "LG"
ComboBoxHandset.List = Range("LG").Value

Case "Motorola"
ComboBoxHandset.List = Range("Motorola").Value

Case "Nokia"
ComboBoxHandset.List = Range("Nokia").Value

Case "Philips"
ComboBoxHandset.List = Range("Philips").Value

Case "Samsung"
ComboBoxHandset.List = Range("Samsung").Value

Case "SonyEricsson"
ComboBoxHandset.List = Range("SonyEricsson").Value

Case "Toshiba"
ComboBoxHandset.List = Range("Toshiba").Value

Case "Other"
ComboBoxHandset.List = Range("OtherManufacturer").Value


End Select

End Sub
 
Upvote 0
As far as I can see that code will reset the list in the 2nd combobox.

Though if there is already something selected in the 2nd one it won't change.

You could just add this to clear what's selected:
Code:
ComboBoxHandSet.ListIndex = -1
Or, if you wanted to have the first item selected then:
Code:
ComboBoxHandset.ListIndex = 0

By the way you might be able to cut down the code a bit.
Code:
ComboBoxHandSet.List = Range(ComboboxManufacturer.Value).Value

ComboBoxHandset.ListIndex = -1

PS Sorry if the lower/upper case of the name(s) are wrong - my caps lock button is playing up.:eek:
 
Upvote 0
Hi Norie,

You are correct the current behavior is that if there is something in the 2nd box then changing the first value leaves the 2nd unchanged. I need the 2nd to be cleared.

I am as i said a complete novice with VBA I have only used it on this project alone, and I do not profess to have e a great knowledge of excel either so I am not familiar with the code or the terminology.

I tried your code
ComboBoxHandSet.ListIndex = -1

and
ComboBoxHandset.ListIndex = 0
in both the
Private Sub ComboBoxmanufacturer_Change()
and in
Private Sub ComboBoxHandset_Change()

neither worked for me, but maybe I am placing the code in the wrong place.
 
Upvote 0
The code should go in the 'parent' combobox's Change event and come after the code to populate the 'child' combobox.

Something like this perhaps.
Code:
Private Sub ComboBoxManufacturer_Change()
 
    If ComboBoxManufacturer.ListIndex <> -1 Then
        ComboBoxHandset.List = Range(ComboBoxManufacturer.Value)
        ComboboxHandset.ListIndex = -1
    End If
End Sub
 
Upvote 0
Thanks for you continued support and patience with me.

I have tried using the code but for some reason it has decapitalised the M on ComBoxManufacturer. When I have changed it back and the userform is run it again changes the M to m and reports a runtime error.

I know I must be doing something stupid for this to happen but cannot fathom what that is.
 
Upvote 0
Like I said I may have the case of the names wrong.

That shouldn't affect anything though and if it's being changed then it's probably being changed to the correct case.

What is the error message you get and on what line of code?

Do you definitely have named ranges called 'Acer','Apple', etc?
 
Upvote 0
I have worked it out there was a close bracket in the wrong place.

correct code for anyone else who is reading this is

Code:
If ComboBoxmanufacturer.ListIndex <> -1 Then
        ComboBoxHandset.List = Range(ComboBoxmanufacturer).Value
        ComboBoxHandset.ListIndex = -1
        
 End If

Thank you for your help on this.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top