Groups and Protected Worksheets - know if protected

[mjones]

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]

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

 

[DaddyO]

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]

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.

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]

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]

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:

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]

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]

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.

Great! That did the trick. Thanks again!

 

[Dave Peterson]

Glad you got it working.

Great! That did the trick. Thanks again!