Using named range as list for an ActiveX combo box.

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
93
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,950
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
May 17, 2013
Messages
93
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,950
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The basic code will be something like:

Code:
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

Board Regular
Joined
May 17, 2013
Messages
93
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.
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,703
Messages
5,855,242
Members
431,716
Latest member
Ibyb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top