Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
Hi,
I've been using this Workbook code for a while and it works great to allow protected sheets and outline to work together. Private Sub Workbook_Open() Dim mySheetNames As Variant Dim iCtr As Long mySheetNames = Array("Summary", "Concept", "Approval", "Definition", "Planning", "Implementation", "Closeout") For iCtr = LBound(mySheetNames) To UBound(mySheetNames) With Worksheets(mySheetNames(iCtr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With Next iCtr End Sub Today, I realized that it locks the worksheets every time I open the spreadsheet. Is there a way to recognize if the worksheets are protected or not and leave them that way when the spreadsheet is opened? This spreadsheet will be used by thousands of people so I'd really appreciate help because it can't be used otherwise. Thank you, Michele |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
So if the worksheets are protected, then protect them in a special way. But if
they're unprotected, just leave them alone? Option Explicit Private Sub Workbook_Open() Dim mySheetNames As Variant Dim iCtr As Long mySheetNames = Array("Summary", "Concept", "Approval", _ "Definition", "Planning", _ "Implementation", "Closeout") For iCtr = LBound(mySheetNames) To UBound(mySheetNames) With Worksheets(mySheetNames(iCtr)) If .ProtectContents = True _ Or .ProtectDrawingObjects = True _ Or .ProtectScenarios = True Then 'protect it nicer(?) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If Else 'do nothing??? End If End With Next iCtr End Sub mjones wrote: Hi, I've been using this Workbook code for a while and it works great to allow protected sheets and outline to work together. Private Sub Workbook_Open() Dim mySheetNames As Variant Dim iCtr As Long mySheetNames = Array("Summary", "Concept", "Approval", "Definition", "Planning", "Implementation", "Closeout") For iCtr = LBound(mySheetNames) To UBound(mySheetNames) With Worksheets(mySheetNames(iCtr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With Next iCtr End Sub Today, I realized that it locks the worksheets every time I open the spreadsheet. Is there a way to recognize if the worksheets are protected or not and leave them that way when the spreadsheet is opened? This spreadsheet will be used by thousands of people so I'd really appreciate help because it can't be used otherwise. Thank you, Michele -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
Michelle,
Would it possibly be easier to go into tools/protection/ allow users to edit ranges and specify what they can edit and lock the rest. This seems simpler than using the code. I did this to protect my formulas from accidental erasure and still allow others to enter their data. It may work for your application. "mjones" wrote: Hi, I've been using this Workbook code for a while and it works great to allow protected sheets and outline to work together. Private Sub Workbook_Open() Dim mySheetNames As Variant Dim iCtr As Long mySheetNames = Array("Summary", "Concept", "Approval", "Definition", "Planning", "Implementation", "Closeout") For iCtr = LBound(mySheetNames) To UBound(mySheetNames) With Worksheets(mySheetNames(iCtr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With Next iCtr End Sub Today, I realized that it locks the worksheets every time I open the spreadsheet. Is there a way to recognize if the worksheets are protected or not and leave them that way when the spreadsheet is opened? This spreadsheet will be used by thousands of people so I'd really appreciate help because it can't be used otherwise. Thank you, Michele |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
On Apr 25, 10:35 am, DaddyO wrote:
Michelle, Would it possibly be easier to go into tools/protection/ allow users to edit ranges and specify what they can edit and lock the rest. This seems simpler than using the code. I did this to protect my formulas from accidental erasure and still allow others to enter their data. It may work for your application. "mjones" wrote: Hi, I've been using this Workbook code for a while and it works great to allow protected sheets and outline to work together. Private Sub Workbook_Open() Dim mySheetNames As Variant Dim iCtr As Long mySheetNames = Array("Summary", "Concept", "Approval", "Definition", "Planning", "Implementation", "Closeout") For iCtr = LBound(mySheetNames) To UBound(mySheetNames) With Worksheets(mySheetNames(iCtr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With Next iCtr End Sub Today, I realized that it locks the worksheets every time I open the spreadsheet. Is there a way to recognize if the worksheets are protected or not and leave them that way when the spreadsheet is opened? This spreadsheet will be used by thousands of people so I'd really appreciate help because it can't be used otherwise. Thank you, Michele Hi Dave, Yes, that is correct. The code is good, but should not change whether the worksheets are protected or not when the file is opened. I think you need to finish the code, right? DaddyO, This code is necessary when outline (groups) are used because what you say would normally work except with groups. Thanks, Michele |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
I'm confused.
If the sheet was protected, then it was protected to allow you to use the Outlining feature on that protected sheet. If the sheet was not protected, then it didn't do anything special--because you didn't need anything special done to the sheet. Isn't that what you wanted? mjones wrote: <<snipped Hi Dave, Yes, that is correct. The code is good, but should not change whether the worksheets are protected or not when the file is opened. I think you need to finish the code, right? DaddyO, This code is necessary when outline (groups) are used because what you say would normally work except with groups. Thanks, Michele -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
On Apr 25, 11:37 am, Dave Peterson wrote:
I'm confused. If the sheet was protected, then it was protected to allow you to use the Outlining feature on that protected sheet. If the sheet was not protected, then it didn't do anything special--because you didn't need anything special done to the sheet. Isn't that what you wanted? mjones wrote: <<snipped Hi Dave, Yes, that is correct. The code is good, but should not change whether the worksheets are protected or not when the file is opened. I think you need to finish the code, right? DaddyO, This code is necessary when outline (groups) are used because what you say would normally work except with groups. Thanks, Michele -- Dave Peterson Hi Dave, I'm a bit confused about what you're confused about. Here's what I'm doing and I hope it answers your question. The worksheets (with lots of groups) have two uses. At first, they're used to estimate project budgets so they're unprotected while estimates are being entered. Then secondly, after the project budget has been approved (by the government), budget fields cannot be updated anymore and the 'government' will password protect the worksheets (I wish I knew how to do them all at once ... but that's another story). After that, project managers keep updating the spreadsheet 'actual' fields for up to four years depending upon the length of the project. All the fields are unlocked except the budget fields. What is going on now, is that unprotected worksheets are getting protected whenever I open the file. The protection status should not change when opening the file; it should be what it was when closed (unprotected for unapproved budget or protected for approved budget). I hope that helps and thanks for your response. I notice how many people you help on this site and I'm sure we all are glad you're out there, Michele |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
Try the code in the first response.
It sounds like it'll do what you want. If it doesn't do what you want, post back with the problem. mjones wrote: On Apr 25, 11:37 am, Dave Peterson wrote: I'm confused. If the sheet was protected, then it was protected to allow you to use the Outlining feature on that protected sheet. If the sheet was not protected, then it didn't do anything special--because you didn't need anything special done to the sheet. Isn't that what you wanted? mjones wrote: <<snipped Hi Dave, Yes, that is correct. The code is good, but should not change whether the worksheets are protected or not when the file is opened. I think you need to finish the code, right? DaddyO, This code is necessary when outline (groups) are used because what you say would normally work except with groups. Thanks, Michele -- Dave Peterson Hi Dave, I'm a bit confused about what you're confused about. Here's what I'm doing and I hope it answers your question. The worksheets (with lots of groups) have two uses. At first, they're used to estimate project budgets so they're unprotected while estimates are being entered. Then secondly, after the project budget has been approved (by the government), budget fields cannot be updated anymore and the 'government' will password protect the worksheets (I wish I knew how to do them all at once ... but that's another story). After that, project managers keep updating the spreadsheet 'actual' fields for up to four years depending upon the length of the project. All the fields are unlocked except the budget fields. What is going on now, is that unprotected worksheets are getting protected whenever I open the file. The protection status should not change when opening the file; it should be what it was when closed (unprotected for unapproved budget or protected for approved budget). I hope that helps and thanks for your response. I notice how many people you help on this site and I'm sure we all are glad you're out there, Michele -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
On Apr 25, 2:00 pm, Dave Peterson wrote:
Try the code in the first response. It sounds like it'll do what you want. If it doesn't do what you want, post back with the problem. mjones wrote: On Apr 25, 11:37 am, Dave Peterson wrote: I'm confused. If the sheet was protected, then it was protected to allow you to use the Outlining feature on that protected sheet. If the sheet was not protected, then it didn't do anything special--because you didn't need anything special done to the sheet. Isn't that what you wanted? mjones wrote: <<snipped Hi Dave, Yes, that is correct. The code is good, but should not change whether the worksheets are protected or not when the file is opened. I think you need to finish the code, right? DaddyO, This code is necessary when outline (groups) are used because what you say would normally work except with groups. Thanks, Michele -- Dave Peterson Hi Dave, I'm a bit confused about what you're confused about. Here's what I'm doing and I hope it answers your question. The worksheets (with lots of groups) have two uses. At first, they're used to estimate project budgets so they're unprotected while estimates are being entered. Then secondly, after the project budget has been approved (by the government), budget fields cannot be updated anymore and the 'government' will password protect the worksheets (I wish I knew how to do them all at once ... but that's another story). After that, project managers keep updating the spreadsheet 'actual' fields for up to four years depending upon the length of the project. All the fields are unlocked except the budget fields. What is going on now, is that unprotected worksheets are getting protected whenever I open the file. The protection status should not change when opening the file; it should be what it was when closed (unprotected for unapproved budget or protected for approved budget). I hope that helps and thanks for your response. I notice how many people you help on this site and I'm sure we all are glad you're out there, Michele -- Dave Peterson Great! That did the trick. Thanks again! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Groups and Protected Worksheets - know if protected
Glad you got it working.
mjones wrote: <<snipped Great! That did the trick. Thanks again! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
Expanding and Collapsing Groups within a Protected Workbook | Excel Worksheet Functions | |||
Expand and Collaps Groups while Protected | Excel Worksheet Functions | |||
how can I allow users to open groups in a protected worksheet? | Excel Discussion (Misc queries) | |||
Protected worksheets | Excel Discussion (Misc queries) |