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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,755
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
36,755
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

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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