Using named range as list for an ActiveX combo box.

[Phil Payne]

My Set Up
I use Microsoft Office Standard 2013.
I have a Table that self populates and formats itself dependant on value selected using an ActiveX Combo Box.
I use a Named Range as the list for that ActiveX ComboBox.

My issue - making any change to the named range causes its dependent table to update (i.e. when not asked to!).

1 With the Named Range placed on separate worksheet.
Whenever I change the Named Range (list) Excel moves from the Named Ranges worksheet to the worksheet holding the dependent Combo Box and Table and rebuilds the Table.

2 With the named Range placed on same worksheet as ComboBox. Whenever I change the Named Range (list) Excel rebuilds the Table. (on screen update)

How can i make a change to a Named Range without it behaving as if I have used the drop down arrow in its dependent combo box & table?

 

[RoryA]

Rather than linking the control directly to the named range, I'd use code to assign the value of the named range to the list property of the control. That will stop the behaviour you are describing.

 

[Phil Payne]

Rather than linking the control directly to the named range, I'd use code to assign the value of the named range to the list property of the control. That will stop the behaviour you are describing.

Thanks for the prompt response RoryA. All i have to do now is figure out how to do that - do you have any examples?

Thanks again for taking the time to reply.

 

[RoryA]

The basic code will be something like:

Sheets("sheet name").listboxname.List = application.Range(rangename).Value

The question will be when and how to run the code. Possibly in the activate event of the worksheet with the listbox on it, assuming the list doesn't alter very often.

 

[Phil Payne]

Hi RoryA
So, I've spent some time at this and managed to do as you suggested and that, along with other methods have shown me that the answer to my original question "How can i make a change to a Named Range without it behaving as if I have used the drop down arrow in its dependent combo box & table?" is to move the Named Range to the same sheet as the combobox as the effect is then much less noticeable.
I am sure someone would be able to do this but I cannot spend the time needed to research this further.

Thank you very much for your time and effort on my behalf.