Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection
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. Karen wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection
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 "Karen" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
WS Protection: Different Levels of Protection on Different Ranges | Excel Discussion (Misc queries) | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
Worksheet protection is gone and only wokbook protection can be se | Excel Discussion (Misc queries) |