Protection of many sheets

[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?

 

[Harald Staff]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Gord Dibben]

Chamge the line

For Each WS In ActiveWorkbook.Worksheets

to

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


Gord Dibben MS Excel MVP

 

[Guest]

Thank you so much!

Chamge the line

For Each WS In ActiveWorkbook.Worksheets

to

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


Gord Dibben MS Excel MVP

 

[businka19]

Hallo, where does this macro code goes?Thanx

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

 

[Dave Peterson]

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[businka19]

Thanx! Worked like a charm!

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[businka19]

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.

 

[Gord Dibben]

WS.Protect (PW), AllowFormattingCells:=True


Gord Dibben MS Excel MVP

 

[Michelle Thompson]

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!