How can i refresh the list in one active x combo box when another is triggered?

[tonywatsonhelp]

Hi Everyone,

Ok so i have two activex combo boxes on a sheet and i want to refesh the inputs for combobox1
when i select something in combo box 2

To give you an idea of what i'm trying to do heres my code:

Private Sub ComboBox2_Change()
Application.EnableEvents = False
ActiveSheet.Unprotect
With ThisWorkbook.Worksheets("Selection")
.ComboBox1.ListFillRange = "Colour_Dropdown1"
End With
ActiveSheet.Protect
Application.EnableEvents = True
End Sub

now the problem I'm getting is it doesn't seam to only be triggering when i make a change to combobox2?

So what am i doing wrong?
can someone please help me with the correct code?
Its current placed in the sheet module area.

So to sum up,

"Colour_Dropdown1" is a named range, the data in this range changes when you select a name in combobox2
to get combobox1 to update i have to manually repaste in the ListFillRange as "Colour_Dropdown1" so i want the macro to do this for me, or any other way i can do this is fine.
hope thats clear.
please help if you can

Thanks

Tony

 

[GWteB]

Try this

Private Sub ComboBox2_Change()
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    With ThisWorkbook.Worksheets("Selection")
        .ComboBox1.ListFillRange = ThisWorkbook.Names("Colour_Dropdown1").RefersTo
    End With
    ActiveSheet.Protect
    Application.EnableEvents = True
End Sub