after discussion with another member it was decided my first post was vague, so now I included a depersonalized file with some test name ranges so it is clearer at what I'm attempting. On ShIL02 in cell (Q8) is the result from what I choose in combo box named (Namecb1) on the same worksheet. That combo box is populated from the results of a macro my daughter-in-law wrote for me (results are listed in A8:A155 on the same worksheet). She and my son are on Navy tour of duty at sea so any communication I have with them I keep to family matters so asking for her help is not possible (I have limited VBA experience) That list is then named (list.namedranges) which is then feeding info. to combo box (Namecb1) also on the same worksheet. The results in cell Q8 I wish to be the values from the name range I choose in the combo box. There might be several values per name range and those I wish to be continued listed down column Q. I cannot ask my daughter-in-law for help and I'm not going to wait another 4 months to ask her.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel VBA named range from combobox selection #2
Answers
The attached workbook has the functionaliy you desire, to wit, it reads the named ranges into a combobox and lists the values in the range you select there below the Cbx.
The Cbx now has a name which is descriptive of its function. It no longer has a linked cell, and its list is no longer read from a worksheet but supplied by the code directly.
Note that I added a namd range called Test.MyChildren. Its sole purpose is to show you an alternative way of creating a dynamic range. Its formula is a little shorter than the one you use.
I didn't touch any of your code. Instead I added event procedures to the code module of ShLI02. Sub Worksheet_Activate runs whenever you open the worksheet. It loads the Cbx and selects its first item. Therefore the previous selection will be lost if you switch between sheets. Sub CbxRangeNames_Change runs when you make a selection in the Cbx.
Observe that I added code to list only range names that start with "test". The purpose is to give you a tool to prevent certain ranges from being listed. It's easy to disable or remove this filter if you don't need it, or to modify its criteria.
I have added some comments to the code which I hope will enable you to make such changes as will be required to adapt the code to your final workbook.
Discussion
thanks for the patience with this sometimes-confusing old man