Excel VBA named range from combobox selection #2


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.



Selected Answer

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 ShLI02Sub 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.



this is exactly what I was looking for.

thanks for the patience with this sometimes-confusing old man
Dean3551 (rep: 4) Dec 2, '19 at 9:04 am
I'm glad to have been of assistance. Not quite happy though about losing the last looked-at item when switching between sheets. If that's worth your making it the subject of a new question please go ahead. The title of the question should be something like "Retain combobox value while updating drop-down list".
Variatus (rep: 4574) Dec 2, '19 at 9:37 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login