Dynamic listing of ComboBox?

[zabiullakhan]

I have this code to populate ComboBox2 with refrence to ComboBox1, however this ComboBox2 is not geting updated. please help

Private Sub ComboBox1_Change()
    If ActiveSheet.ComboBox1.Value = "animals" Then
        ActiveSheet.ComboBox2.ListFillRange = Range("Reference!B2:B7")      
    ElseIf ActiveSheet.ComboBox1.Value = "flowers" Then
        ActiveSheet.ComboBox2.ListFillRange = Range("Reference!B8:B14") 
    ElseIf ActiveSheet.ComboBox1.Value = "colors" Then
       ActiveSheet.ComboBox2.ListFillRange = Range("Reference!A14:A15")  
    End If
End Sub

 

[wut]

I think I don't understand what you're asking.

 

[zabiullakhan]

Yes refrence is a sheet?

however the above code also dosent work

 

[wut]

That's because I was trying to set the listfilledrange as a range... same as you were.

Get rid of the "Range()"; just, "Reference!AX:BX"

eg:

Private Sub ComboBox1_Change()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
    If ActiveSheet.ComboBox1.Value = "animals" Then<o:p></o:p>
<o:p> </o:p>
        ActiveSheet.ComboBox2.ListFillRange = [COLOR=red]"Reference!A14:A15"<o:p></o:p>[/COLOR]
<o:p> </o:p>
    ElseIf ActiveSheet.ComboBox1.Value = "flowers" Then<o:p></o:p>
<o:p> </o:p>
        ActiveSheet.ComboBox2.ListFillRange = [COLOR=red]"Reference!A14:A15"<o:p></o:p>[/COLOR]
<o:p> </o:p>
    ElseIf ActiveSheet.ComboBox1.Value = "colors" Then<o:p></o:p>
<o:p> </o:p>
       ActiveSheet.ComboBox2.ListFillRange = [COLOR=red]"Reference!A14:A15"<o:p></o:p>[/COLOR]
<o:p> </o:p>
    End If<o:p></o:p>
<o:p> </o:p>
End Sub

 

[zabiullakhan]

I get this error at line 3
"Object dosent support this property or method"

please help

 

[wut]

I tried the code out and it works just fine. Take it step by step. Start with this. Does this block work for you?

Private Sub ComboBox1_Change()
 
    If ActiveSheet.ComboBox1.Value = "animals" Then
 
        ActiveSheet.ComboBox2.ListFillRange = "Sheet1!A14:A15"

    End If
 
End Sub

Start with a brand new workbook;

Put two comboboxes on it;

Fill "A14" and "A15" with text;

Enter "design" mode and double-click on the first combo-box;

Paste the code above into the object module;

Go back to your new worksheet and type "animals" into the first combo-box;

Come back here if it doesn't work.

 

[zabiullakhan]

do i use "Combobox control" or "activeX Combobox"?

 

[wut]

I would use the control toolbox to create any objects. From what I understand, they're way more versatile.

So, activex.