Runtime error 424, Object required - while populating ComboBoxes

[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

<code>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
</code>

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.


I know I could do pretty much the same thing using Data Validation Lists, but my boss would like me to use ActiveX ComboBoxes and dont ask me why!


Quick help would be much appreciated.
Thanks,
Ajain



My Excel Version: Excel 2007

 

[VoG]

This doesn't help much but that code works for me without error.

 

[Richard Schollar]

Do you definitely have a combobox named "combobox2" on the sheet?

 

[mikerickson]

Are there trailing spaces in D1?