Using named range as list for an ActiveX combo box.

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
131
Office Version
  1. 365
Platform
  1. Windows
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top