Preventing Range Name changes

[Guest]

I have 4 key ranges in a file. I do not want the users to be able to change
the range names. It seems if you do not Protect each sheet then they will
have access to the ranges in Insert / Name / Define. The sheets are
protected but If they insert a new worksheet then they will have access to
delete a range. I dont want to Workbook Protect because I have had bad
experiences with Pivot Table corruption on opening the file that is Workbook
Protected.

I have done a workaround in the BeforeSave event. Before the file saves it
tests that the ranges exist in their proper worksheet. If not then the save
is cancelled and a message appears telling the user not to change range names.

Is there a way to not allow range name additions or deletions in a file by
code? Like putting it in the OnOpen event.

Thank you for your help.

Steven

 

[Chip Pearson]

Steven,

You can set the Visible property of the Name to False. If Visible is False,
the name will not show up in the Names dialog (CTRL+F3) and will not
display in the Name Box (the dropdown to the left of the formula bar, above
the "A" column heading).

You have to use VBA to change the Visible property of a name:

ThisWorkbook.Names("TheName").Visible = False

In order to delete/change/view the Defined Name, once its Visible property
is False, you must use VBA. I suspect this is beyond the interest and
capabilities of your users.

There is one caveat: If the user attempts to create a named range with the
same name as your hidden name, the original hidden name is deleted and a
new, visible, name is created. About the only work-around for this is to
use names that are quiet unlikely to be used by the user.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

That works for me. Thank you very much.

Steven,

You can set the Visible property of the Name to False. If Visible is False,
the name will not show up in the Names dialog (CTRL+F3) and will not
display in the Name Box (the dropdown to the left of the formula bar, above
the "A" column heading).

You have to use VBA to change the Visible property of a name:

ThisWorkbook.Names("TheName").Visible = False

In order to delete/change/view the Defined Name, once its Visible property
is False, you must use VBA. I suspect this is beyond the interest and
capabilities of your users.

There is one caveat: If the user attempts to create a named range with the
same name as your hidden name, the original hidden name is deleted and a
new, visible, name is created. About the only work-around for this is to
use names that are quiet unlikely to be used by the user.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dave Peterson]

Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

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

It allows you to hide existing names (and unhide them, too).

It offers a lot of nice features that will make working with names easier.