Runtime error 424, Object required

[Ajain]

Hello Everyone,


I have 2 Activex ComboBoxes on a sheet and I am trying to populate the range in the 2nd combobox based on the value in the first combobox.


Here is my code. But whenever I run it, I get the following error:

"Runtime error 424, Object required" .. Please Help!


Note: Linked Cell for ComboBox1 is D1

[pre]

Private Sub ComboBox1_Change()
Dim SubType As String
SubType = Range("$D$1").Value
Select Case SubType
Case "Fruits"
ComboBox22.ListFillRange = "Fruits_Range"
Case "Vegetables"
ComboBox22.ListFillRange = "Veg_Range"
End Select
End Sub

[/pre]
I defined Fruits_Range and Veg_Range using Formula > Name Manager


Here is the logic: If someone selects "Fruits" from the 1st List box, then I would like to display a list of fruits in the 2nd box.


Quick help would be much appreciated.


Thanks,

Ajain

 

[Hui]

Check that your 2nd combo box is really called ComboBox22 ?

Do you know you can do that without VBA ?

This was referred to in:

http://chandoo.org/forums/topic/using-lists-embedded-from-other-lists

 

[Ajain]

Hi Hui,


Oops, that was a typo on my part while posting the code, the actual code is:

[pre]

Private Sub ComboBox1_Change()
Dim SubType As String
SubType = Range("$D$1").Value
Select Case SubType
Case "Fruits"
ComboBox2.ListFillRange = "Fruits_Range"
Case "Vegetables"
ComboBox2.ListFillRange = "Veg_Range"
End Select
End Sub

[/pre]

I know I could do pretty much the same thing using Data Validation lists, but then I don't have the option to edit the font in the drop down and also until one does not click on the cell, the drop down arrow does not appear.


Thanks