working with named ranges

[sumsaam]

Hi
i have 10 named ranges in sheet11 and in sheets to 1-10 i want a list box or combo box with names of 10 ranges on every sheet. which range i select, excel should show selected range on that sheet. code may be given for 2 or three ranges i will convert it for 10 ranges. thank you

 

[Akuini]

Let's say you have 3 name range: "aa", "bb", "cc"
The combobox are activeX control combobox.
Try this code (change to suit):

In a code module put this code:

[FONT=lucida console][COLOR=Royalblue]Public[/COLOR] [COLOR=Royalblue]Function[/COLOR] dName()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1084539-working-named-ranges.html[/COLOR][/I]
    dName = Application.Transpose(Array([COLOR=brown]"aa"[/COLOR], [COLOR=brown]"bb"[/COLOR], [COLOR=brown]"cc"[/COLOR]))
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Function[/COLOR]

[COLOR=Royalblue]Public[/COLOR] [COLOR=Royalblue]Sub[/COLOR] toSelect(adr [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR])
    ActiveSheet.Range(Application.Range(adr).Address).Activate
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

In each sheet module (with the combobox) put this code:
Assuming all combobox name are combobox1 (if not then change to suit)

[FONT=lucida console]
[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox1_Click()
    [COLOR=Royalblue]Call[/COLOR] toSelect(ComboBox1.Value)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox1_DropButt*******()
    ComboBox1.List = dName
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

Note: ComboBox1_DropButt*******() is ComboBox1_DropButtonC~lick (without ~)

 

[sumsaam]

thank you for code but it shows run time error 381 and debugger highlights the code line Combobox1.List........
combobox is not showing list

upper code i put in This workbook>General section of code and second code i have put in one other sheet having combo box.
tell me one thing is there worksheet name necessary with named range? i have put only name of ranges

 

[sumsaam]

Erorr is "Run time error 381" Could not set the list property. Invalid property array index

 

[Yongle]

I tested the code provided by @Akuini
It worked perfectly first time

This is what I did
- in NEW workbook
- (in sheet2) create 3 named ranges aa (A1:A4,) bb (B1:B4) and cc (C1:C4)
- (in sheet1) insert active-x combobox
- paste combobox code to sheet1's SHEET module
- paste other code to STANDARD module

Try doing that first to get you started

 

[Akuini]

thank you for code but it shows run time error 381 and debugger highlights the code line Combobox1.List........
combobox is not showing list

upper code i put in This workbook>General section of code and second code i have put in one other sheet having combo box.
tell me one thing is there worksheet name necessary with named range? i have put only name of ranges

Not sure why that happen. The combobox name is Combobox1, right?
Let's try a simpler way:
Delete the upper code Public Function dName() & Public Sub toSelect(adr As String)
In each sheet module (with the combobox) put this code:

Private Sub ComboBox1_Click()
    ActiveSheet.Range(Application.Range(ComboBox1.Value).Address).Activate
End Sub

Private Sub ComboBox1_DropButt*******()
    ComboBox1.List = Application.Transpose(Array("aa", "bb", "cc"))
End Sub

tell me one thing is there worksheet name necessary with named range? i have put only name of ranges

You did it right.

This is an example :
https://www.dropbox.com/s/zd6ekqw10irgcw7/1084539-working-named-ranges 1.xlsm?dl=0

 

[sumsaam]

2nd code is bug free, i think i couldnt explain my aim. i want to show the data of named ranges, not only highlighted ranges

 

[My Aswer Is This]

Show it where?

 

[sumsaam]

show it in the range specified in code

 

[Akuini]

2nd code is bug free, i think i couldnt explain my aim. i want to show the data of named ranges, not only highlighted ranges

Sorry, I don't understand what you mean. Can you explain in more detail, describe what you want to do step by step & using a real example would help.