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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,967
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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
 

markda2000

New Member
Joined
Mar 24, 2011
Messages
9
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
 

markda2000

New Member
Joined
Mar 24, 2011
Messages
9
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

markda2000

New Member
Joined
Mar 24, 2011
Messages
9
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 24, 2011
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

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
Top