reset dependent combobox if 1st combobox is changed

[markda2000]

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.

 

[diddi]

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.

 

[Norie]

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:

Combobox2.Clear

 

[markda2000]

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

 

[Norie]

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:

ComboBoxHandSet.ListIndex = -1

Or, if you wanted to have the first item selected then:

ComboBoxHandset.ListIndex = 0

By the way you might be able to cut down the code a bit.

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.

 

[markda2000]

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.

 

[Norie]

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.

Private Sub ComboBoxManufacturer_Change()
 
    If ComboBoxManufacturer.ListIndex <> -1 Then
        ComboBoxHandset.List = Range(ComboBoxManufacturer.Value)
        ComboboxHandset.ListIndex = -1
    End If
End Sub

 

[markda2000]

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.

 

[Norie]

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?

 

[markda2000]

I have worked it out there was a close bracket in the wrong place.

correct code for anyone else who is reading this is

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

Thank you for your help on this.