| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Losing Cell Protection When Saving Workbook
Excel 2003. I programmatically protect each sheet in a workbook as I
programmatically create the sheets. Moreover, I enable only the unlocked cells in the sheets to be selected. I use: WkSh.Protect WkSh.EnableSelection = xlUnlockedCells When I put breakpoints in while debugging I find that the protection is working as expected (the sheet is protected and I can only select unlocked cells). When I am finished creating all the sheets, I then programmatically protect the whole work book as: WbNew.Protect When I check the sheets after this operation, they still behave as desired. HOWEVER, when I save the workbook using: WbNew.Close SaveChanges:=True and then open the workbook, I find the workbook protected (as expected), the sheets protected (as expected), but I can select the locked cells in the sheets What could be causing this? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Losing Cell Protection When Saving Workbook
This is one of those settings that excel doesn't remember when the file is
closed, then reopened. That was what this warning meant: Excel doesn't remember this setting--so the code uses the auto_open procedure to set it each time the workbook opens. Chaplain Doug wrote: Excel 2003. I programmatically protect each sheet in a workbook as I programmatically create the sheets. Moreover, I enable only the unlocked cells in the sheets to be selected. I use: WkSh.Protect WkSh.EnableSelection = xlUnlockedCells When I put breakpoints in while debugging I find that the protection is working as expected (the sheet is protected and I can only select unlocked cells). When I am finished creating all the sheets, I then programmatically protect the whole work book as: WbNew.Protect When I check the sheets after this operation, they still behave as desired. HOWEVER, when I save the workbook using: WbNew.Close SaveChanges:=True and then open the workbook, I find the workbook protected (as expected), the sheets protected (as expected), but I can select the locked cells in the sheets What could be causing this? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Losing Cell Protection When Saving Workbook
Dear Dave:
Not sure what you mean by "uses the auto_open procedure to set it each time the workbook opens." How can I do this programmatically as I produce the worksheet? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Dave Peterson" wrote: This is one of those settings that excel doesn't remember when the file is closed, then reopened. That was what this warning meant: Excel doesn't remember this setting--so the code uses the auto_open procedure to set it each time the workbook opens. Chaplain Doug wrote: Excel 2003. I programmatically protect each sheet in a workbook as I programmatically create the sheets. Moreover, I enable only the unlocked cells in the sheets to be selected. I use: WkSh.Protect WkSh.EnableSelection = xlUnlockedCells When I put breakpoints in while debugging I find that the protection is working as expected (the sheet is protected and I can only select unlocked cells). When I am finished creating all the sheets, I then programmatically protect the whole work book as: WbNew.Protect When I check the sheets after this operation, they still behave as desired. HOWEVER, when I save the workbook using: WbNew.Close SaveChanges:=True and then open the workbook, I find the workbook protected (as expected), the sheets protected (as expected), but I can select the locked cells in the sheets What could be causing this? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Losing Cell Protection When Saving Workbook
Dave:
When I manually set the sheet to protect (allowing only access to unlocked cells) and save the file and reopen, the protection settings are still there. Why am I losing them only when I set the protection progrmmatically? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Dave Peterson" wrote: This is one of those settings that excel doesn't remember when the file is closed, then reopened. That was what this warning meant: Excel doesn't remember this setting--so the code uses the auto_open procedure to set it each time the workbook opens. Chaplain Doug wrote: Excel 2003. I programmatically protect each sheet in a workbook as I programmatically create the sheets. Moreover, I enable only the unlocked cells in the sheets to be selected. I use: WkSh.Protect WkSh.EnableSelection = xlUnlockedCells When I put breakpoints in while debugging I find that the protection is working as expected (the sheet is protected and I can only select unlocked cells). When I am finished creating all the sheets, I then programmatically protect the whole work book as: WbNew.Protect When I check the sheets after this operation, they still behave as desired. HOWEVER, when I save the workbook using: WbNew.Close SaveChanges:=True and then open the workbook, I find the workbook protected (as expected), the sheets protected (as expected), but I can select the locked cells in the sheets What could be causing this? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Losing Cell Protection When Saving Workbook
There are certain routines that excel will run each time you open the workbook
(if macros are enabled!). One is named Auto_Open and is stored in a General module. The other is named Workbook_Open and is stored in the ThisWorkbook module. The code that you got the other day uses a routine named Auto_Open. Chaplain Doug wrote: Dear Dave: Not sure what you mean by "uses the auto_open procedure to set it each time the workbook opens." How can I do this programmatically as I produce the worksheet? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Dave Peterson" wrote: This is one of those settings that excel doesn't remember when the file is closed, then reopened. That was what this warning meant: Excel doesn't remember this setting--so the code uses the auto_open procedure to set it each time the workbook opens. Chaplain Doug wrote: Excel 2003. I programmatically protect each sheet in a workbook as I programmatically create the sheets. Moreover, I enable only the unlocked cells in the sheets to be selected. I use: WkSh.Protect WkSh.EnableSelection = xlUnlockedCells When I put breakpoints in while debugging I find that the protection is working as expected (the sheet is protected and I can only select unlocked cells). When I am finished creating all the sheets, I then programmatically protect the whole work book as: WbNew.Protect When I check the sheets after this operation, they still behave as desired. HOWEVER, when I save the workbook using: WbNew.Close SaveChanges:=True and then open the workbook, I find the workbook protected (as expected), the sheets protected (as expected), but I can select the locked cells in the sheets What could be causing this? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Losing Cell Protection When Saving Workbook
Not all of the protection settings are forgotten when the workbook is closed and
reopened. But there are a few--the .enableselection, .enableautofilter, ..enableoutlining are a few that come to mind. And these protection settings will be forgotten if you do it manually (if available) or via code (if I remember correctly). Chaplain Doug wrote: Dave: When I manually set the sheet to protect (allowing only access to unlocked cells) and save the file and reopen, the protection settings are still there. Why am I losing them only when I set the protection progrmmatically? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Dave Peterson" wrote: This is one of those settings that excel doesn't remember when the file is closed, then reopened. That was what this warning meant: Excel doesn't remember this setting--so the code uses the auto_open procedure to set it each time the workbook opens. Chaplain Doug wrote: Excel 2003. I programmatically protect each sheet in a workbook as I programmatically create the sheets. Moreover, I enable only the unlocked cells in the sheets to be selected. I use: WkSh.Protect WkSh.EnableSelection = xlUnlockedCells When I put breakpoints in while debugging I find that the protection is working as expected (the sheet is protected and I can only select unlocked cells). When I am finished creating all the sheets, I then programmatically protect the whole work book as: WbNew.Protect When I check the sheets after this operation, they still behave as desired. HOWEVER, when I save the workbook using: WbNew.Close SaveChanges:=True and then open the workbook, I find the workbook protected (as expected), the sheets protected (as expected), but I can select the locked cells in the sheets What could be causing this? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How copy cell workbook to workbook? | Excel Discussion (Misc queries) | |||
| How do I copy a cell + it's formula from one workbook to another? | Excel Discussion (Misc queries) | |||
| copying cell names | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
