PDA

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"

gnod
12-07-2006, 07:43 AM
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