Preventing Range Name changes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven - 24 Dec 2006 16:20 GMT

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

Reply to this Message

Chip Pearson - 24 Dec 2006 16:50 GMT

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.

Signature

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

>I have 4 key ranges in a file. I do not want the users to be able to
>change
[quoted text clipped - 19 lines]
>
> Steven

Reply to this Message

Steven - 24 Dec 2006 17:11 GMT

That works for me. Thank you very much.

> Steven,
>
[quoted text clipped - 39 lines]
> >
> > Steven

Reply to this Message

Dave Peterson - 24 Dec 2006 17:13 GMT

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.

> 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
[quoted text clipped - 14 lines]
>
> Steven

Signature

Dave Peterson

Reply to this Message