Locking Named Cells

[wjoc1]

Lads,

I have a lot of named cells in a workbook. Can I lock the name of th
cells but allow users to change the data in them?
I do not want them to be able to change the cell names becuase o
references to them elsewhere in the workbooks.

Lia

 

[Dave Peterson]

Are you sure that the users are changing the names? Maybe they're just adding
another name for that range?

If they're just adding other names, then it shouldn't hurt your other
formulas/code.

I don't think that there is a way to stop the users from deleting and adding a
different name, but one thing you could try is to hide those names.

You could do it through code or you could use an addin by Jan Karel Pieterse's
(with Charles Williams and Matthew Henson) named Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

But anyone who knows a little VBA or can find Jan Karel's addin will be able to
unhide and screw up your workbook. (But I can't think of any co-worker who
would actually want to???)

 

[wjoc1]

Hi,

How exactly would I go about hiding the name of named cells with vb?

Liam

 

[Bob Phillips]

Thisworkbook.Names("this_name").Visible = False

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[wjoc1]

Bob,

Simple as that! Thanks a lot

Liam

 

[wjoc1]

Can I lock all the names with some generic reference without haveing to
specify each one of them?

ThisWorkbook.Names("name1").Visible = False
ThisWorkbook.Names("name2").Visible = False
ThisWorkbook.Names("name3").Visible = False

etc... is very long winded

Is there one line of code that can do this for me?

Thanks,
Liam

 

[Dave Peterson]

You could hide them all or you could name the ones that should be hidden in a
nice manner:

dim myName as Name
for each myName in thisworkbook.names
myname.visible = false
next myname

or

dim myName as Name
for each myName in thisworkbook.names
if lcase(left(myname.name,4)) = "hid_" then
myname.visible = false
end if
next myname

But if I were doing this as a developer, I'd just use Jan Karel Pieterse's
addin.