Protection

[Karen]

I have a workbook that consists of approx. 60
worksheets. Each worksheet is individually password
protected with the same password. At times, someone
forgets to protect one worksheet before closing the
file. Is there a way to prompt the user with a message
(I'll assume it requires VBA code) on closing if he or
she forgets to password protect one or more of the
worksheets?

Thank you

 

[Dave Peterson]

I think I'd let the user close without protecting the sheets.

But apply the password the next time the workbook was opened:

Option Explicit
Sub auto_open()
Dim myPwd As String
Dim wks As Worksheet

myPwd = "whateveryouwant"

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'already protected
Else
wks.Protect Password:=myPwd
End If
Next wks

End Sub

The bad news is that if the user disables macros (or just stops auto_open), you
could be in trouble.

 

[Harald Staff]

Hi

Place this in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each sh In Me.Worksheets
If sh.ProtectContents = False Then
Cancel = True
MsgBox sh.Name & " is not protected.", _
vbInformation, "Cancelling close"
Exit Sub
End If
Next
End Sub

The above is the most annoying solution. If the code knows the password then
it might use it to protect instead of forcing the poor girl to do it
manually.

For Each sh In Me.Worksheets
If sh.ProtectContents = False Then
sh.Protect "Very Secret Words"
End If
Next

HTH. Best wishes Harald