View Full Version : Workbook unprotects after running macro...
apharrington
12-06-2006, 02:03 PM
I have written macros in protected worksheets; part of a workbook. The code in the macros is as follows:
ActiveSheet.Unprotect ("the password")
'the macro code here.....
ActiveSheet.protect Password:="the password", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
I have several worksheets, with macros on each. This works perfectly UNTIL I close the workbook and re-open it.
The entire worksheet is accessible; both locked and unlocked cells. When you click on Tools it still shows the worksheet as protected :think:
If I delete the Activesheet.protect/unprotect from the macro - then close the workbook and re-open it, that particular worksheet opens the way it should (protected) but of course, I can't run the macro.:help : pray2:
The workbook itself is not protected.
:dunno
lucas
12-06-2006, 05:07 PM
It would be great if we could see a sanitized(remove personal and proprietary info) sample...
apharrington
12-07-2006, 06:56 AM
4448
4448
Please pardon all the error messages but I had to delete some feeds. I have included 4 worksheets in this workbook; one has no macros, the other three do.
When I close the workbook and re-open it, only the sheets with the macros have both "locked," and "unlocked," cells available, yet still protected.
I have included the macros for the respective sheets. They all show the different ways I have attempted to correct this problem at the end of each in 're-protecting' the sheet.
:help :help : pray2:
apharrington
12-07-2006, 07:12 AM
Password is "agnes"
Input this in standard module.. Use constant variable so that if you want to change password, you can change it in this section..
Public Const strPassword As String = "agnes"
'Protect all worksheets
Sub ProtectAllSheet()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
sht.Protect strPassword
Next sht
Application.ScreenUpdating = True
End Sub
Input this in the "ThisWorkbook".. so that everytime you open your workbook it will be protected..
Private Sub Workbook_Open()
ProtectAllSheet
End Sub
apharrington
12-07-2006, 09:34 AM
THANK YOU FOR THAT!
I have a critical question however: Some of the sheets I need to omit from being protected in the main workbook (these were not included in my sample because of the content). I feel like an idiot for not mentioning that...sorry.
Would you know how to work around those? They do need to be left unprotected.
Len Piwowar
12-07-2006, 10:15 AM
Add the code in red below to the existing code. Replace SheetName1 & SheetName2 with the names of the sheets you want unprotected repeat for all sheets needed.
Public Const strPassword As String = "agnes"
'Protect all worksheets
Sub ProtectAllSheet()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
sht.Protect strPassword
Next sht
'After all sheets are protected go back and unprotect some of the sheets:
ThisWorkbook.Worksheets("SheetName1").unprotect strPassword
ThisWorkbook.Worksheets("SheetName2").unprotect strPassword
Application.ScreenUpdating = True
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.