Protect sheet problem

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

When I open the workbook, Sheet CALCULATEHERE is not protected properly.
"Select locked cells" and Select unlocked cells" are both checked. Can
someone explain what I'm doing wrong?

Thanks,
Bernie

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("CALCULATEHERE").Select
UNPROTECTSHEET
Range("A1") = 0
Range("A2") = 0
Range("A4").Select
DELETEWORKSHEETS
Sheets("CALCULATEHERE").Select
PROTECTSHEET
'When I check the sheet here, it is protected properly, but something
apparently goes wrong on the next line. ActiveWorkbook.Save
End Sub

Sub PROTECTSHEET()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
 
I don't think you're doing anything incorrectly--and if you are, so am I.

I couldn't make xl2002 keep that setting if I applied it via code. If I applied
it in the Userinterface (manually), then xl2002 seemed to remember it without a
problem.

A possible workaround for you is to protect the sheet the way you want when you
open it.

Private Sub Workbook_Open()
With Worksheets("calculatehere")
.Unprotect
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k, this setting wasn't remembered when you closed the workbook
and reopened. Setting it in code was the only way to do this.
 
Thanks for the response Dave.
I had already come up with a workaround using "Auto_Open", similar to your
suggestion.
I have been quite anxious to see if I was the only one experiencing the
problem, so I'm glad you couldn't get it to work either. It soothes the
nerves when there are two in the boat.

Again, Thanks.
Bernie
 
Back
Top