Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop-down-list with Named ranges
I have searched the net but could not find a solution for the
following: I have a sheet with about 100 named ranges (multiple columns and rows). The user has to pick 2 or 3 Named ranges to copy to another file. Question is how to prompt a list of all Named ranges (preferably from all sheets) from which the user can pick one, to select the range after which it can be copied. An alternative might be that the user is prompted to select a named range from a drop-down list embedded in the sheet. Problem is I cannot create this list nor retrieve the proper range from it. I would appreciate any help or suggestion. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop-down-list with Named ranges
Hi there, try this code. You'll need to create a Embeded ComboBox and
call it cmdNamesCombo. Private Sub Worksheet_Activate() cmdNamesCombo.Clear 'populate list with named ranges For intnamescount = 1 To Application.Names.Count cmdNamesCombo.AddItem (Application.Names(intnamescount).Name) Next intnamescount End Sub Private Sub cmdNamesCombo_Click() 'Goes to selected range name and copies it Application.Goto Application.Names(cmdNamesCombo.Value).Name Selection.Copy End Sub FunkySquid Chootje wrote: I have searched the net but could not find a solution for the following: I have a sheet with about 100 named ranges (multiple columns and rows). The user has to pick 2 or 3 Named ranges to copy to another file. Question is how to prompt a list of all Named ranges (preferably from all sheets) from which the user can pick one, to select the range after which it can be copied. An alternative might be that the user is prompted to select a named range from a drop-down list embedded in the sheet. Problem is I cannot create this list nor retrieve the proper range from it. I would appreciate any help or suggestion. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop-down-list with Named ranges
Thanks FunkySquid - gonna work on that.
FunkySquid wrote: Hi there, try this code. You'll need to create a Embeded ComboBox and call it cmdNamesCombo. Private Sub Worksheet_Activate() cmdNamesCombo.Clear 'populate list with named ranges For intnamescount = 1 To Application.Names.Count cmdNamesCombo.AddItem (Application.Names(intnamescount).Name) Next intnamescount End Sub Private Sub cmdNamesCombo_Click() 'Goes to selected range name and copies it Application.Goto Application.Names(cmdNamesCombo.Value).Name Selection.Copy End Sub FunkySquid Chootje wrote: I have searched the net but could not find a solution for the following: I have a sheet with about 100 named ranges (multiple columns and rows). The user has to pick 2 or 3 Named ranges to copy to another file. Question is how to prompt a list of all Named ranges (preferably from all sheets) from which the user can pick one, to select the range after which it can be copied. An alternative might be that the user is prompted to select a named range from a drop-down list embedded in the sheet. Problem is I cannot create this list nor retrieve the proper range from it. I would appreciate any help or suggestion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list of named ranges | Excel Discussion (Misc queries) | |||
Named ranges dissapear from list | Excel Discussion (Misc queries) | |||
Create list of Named Ranges | Excel Worksheet Functions | |||
Named Ranges don't show up in drop-down list | Excel Discussion (Misc queries) | |||
Printing a list of all named ranges | Excel Programming |