Protection of many sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with 50 sheets. I would like to protect all of these sheets
with the same password at the same time. i.e. i do not want to protect the
workbook but every single sheet and I don't want to go through every sheet
protecting it.
Is this possible?
 
Hi

Not without macro code. Here it is:

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

HTH. Best wishes Harald
 
thanks! Problem solved..

"Harald Staff" skrev:
Hi

Not without macro code. Here it is:

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

HTH. Best wishes Harald
Click to expand...
 
Harald Staff said:
Hi

Not without macro code. Here it is:

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

HTH. Best wishes Harald
Click to expand...
 
Harald Staff<

I have a similar situation as Adam. I have a workbook in which my Project
Managers in the field must fill out at the end of each week till the end of
the year. All the sheets are identical and I want to lock down the formulas
but allow them to fill in the required cells on a weekly basis. Is there a
way to lock down all the sheets and identical cells without having to do it
for each one?

shawnlacey
 
What changes would I make if I wanted to project say 3 of the sheets, A, B, C
and not the rest?
 
Chamge the line

For Each WS In ActiveWorkbook.Worksheets

to

For Each WS In Worksheets(Array("A", "B", "C"))


Gord Dibben MS Excel MVP
 
Thank you so much!

Gord Dibben said:
Chamge the line

For Each WS In ActiveWorkbook.Worksheets

to

For Each WS In Worksheets(Array("A", "B", "C"))


Gord Dibben MS Excel MVP
Click to expand...
 
Hallo, where does this macro code goes?Thanx

Harald Staff said:
Hi

Not without macro code. Here it is:

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

HTH. Best wishes Harald
Click to expand...
 
Hallo,

if I would like to lock the sheets using this macro but leave the cell
format possible what would I need to do? Thank u.
 
Macro worked great, but the person opening the file can just go to
tools->protection->unprotect sheet and they don't even have to enter a
password to unprotect it. How does the password I input in the macro become
'activated' so to speak? Do I have to password protect the workbook somehow,
because the password that I coded into the macro, I never typed in anywhere
else in the file...?

Thanks!
 
Back
Top