Protection of many sheets
 

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
"Adam" skrev i melding
...
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?

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

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

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

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

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

Chamge the line

For Each WS In ActiveWorkbook.Worksheets

to

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


Gord Dibben MS Excel MVP


On Tue, 25 Jul 2006 11:39:06 -0700, PCakes
wrote:

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

"Harald Staff" wrote:

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

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

Thank you so much!

"Gord Dibben" wrote:

Chamge the line

For Each WS In ActiveWorkbook.Worksheets

to

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


Gord Dibben MS Excel MVP


On Tue, 25 Jul 2006 11:39:06 -0700, PCakes
wrote:

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

"Harald Staff" wrote:

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

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

Hallo, where does this macro code goes?Thanx

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?

Protection of many sheets
 

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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 wrote:

Hallo, where does this macro code goes?Thanx

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?




--

Dave Peterson

Protection of many sheets
 

Thanx! Worked like a charm!

"Dave Peterson" wrote:

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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 wrote:

Hallo, where does this macro code goes?Thanx

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?




--

Dave Peterson

Protection of many sheets
 

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.

"businka19" wrote:

Thanx! Worked like a charm!



"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?




--

Dave Peterson

Protection of many sheets
 

WS.Protect (PW), AllowFormattingCells:=True


Gord Dibben MS Excel MVP

On Tue, 17 Mar 2009 03:48:02 -0700, businka19
wrote:

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.

"businka19" wrote:

Thanx! Worked like a charm!



"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?




--

Dave Peterson

Protection of many sheets
 

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!

"Harald Staff" wrote:

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
"Adam" skrev i melding
...
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?