Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Range Name changes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Range Name changes
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) "Steven" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Range Name changes
That works for me. Thank you very much.
"Chip Pearson" wrote: 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) "Steven" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Range Name changes
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. Steven wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preventing | New Users to Excel | |||
Preventing Formula Changes | New Users to Excel | |||
Preventing errors | Excel Discussion (Misc queries) | |||
Preventing user entering duplicate values in a cell range | Excel Worksheet Functions | |||
Preventing deletion. | Excel Discussion (Misc queries) |