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.

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.

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.