Adding Range to Worksheet Names Collection

[Keith Willshaw]

Hi I use the worksheet names collection on a hidden
sheet to store drop down values for comboboxes using
the combobox listfillrange property

Note I am referring to the Worksheet names collection
NOT the workbooks Name collection

This works fine but in Excel 2000 I cant find any way
to add a new range to the collection without writing a
VBA function !

I can add it to the Workbooks name collection using
the GUI brought up by Insert>Name>Define but
I want it specific to that hidden sheet

Programmatically I can add it simply enough

Dim mywks As Worksheet
Dim myname As Name

Set mywks = ActiveSheet
With mywks

mywks.Names.Add Name:="RHOV", RefersTo:="Unit_Def.Map!$BK2:$BK4"

End With

Trouble is I want end users to be able to do it

Does anybody know how this is meant to be done
using Excel's own interface ?

Keith

 

[Jim Rech]

What you want to do is called creating a local name. In the Define Name
dialog precede the name with the active sheet's name:

Sheet1!MyName

 

[Keith Willshaw]

What you want to do is called creating a local name. In the Define Name
dialog precede the name with the active sheet's name:

Sheet1!MyName

Thanks Jim I appreciate it.

I knew there must be a way but after 3 hours scanning
the help files and tearing my hair out I coulldnt find it.

Keith