A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Protection of many sheets



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2005, 10:25 AM
Adam
external usenet poster
 
Posts: n/a
Default 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?
  #2  
Old March 16th, 2005, 10:49 AM
Harald Staff
external usenet poster
 
Posts: n/a
Default

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?



  #3  
Old March 16th, 2005, 12:37 PM
Adam
external usenet poster
 
Posts: n/a
Default

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?




  #4  
Old April 28th, 2006, 09:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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?




  #5  
Old April 28th, 2006, 09:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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?




  #6  
Old July 25th, 2006, 07:39 PM posted to microsoft.public.excel.worksheet.functions
PCakes
external usenet poster
 
Posts: 13
Default 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?




  #7  
Old July 25th, 2006, 08:36 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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?





  #8  
Old July 26th, 2006, 04:11 PM posted to microsoft.public.excel.worksheet.functions
PCakes
external usenet poster
 
Posts: 13
Default 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?





  #9  
Old March 11th, 2009, 02:29 PM posted to microsoft.public.excel.worksheet.functions
businka19
external usenet poster
 
Posts: 3
Default 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?




  #10  
Old March 11th, 2009, 02:33 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protection and Hiding Sheets JudithJubilee General Discussion 4 March 4th, 2005 03:16 PM
Changing password protection on sheets Tom Hewitt General Discussion 5 February 25th, 2005 04:33 PM
Protection of Sheets NACHO General Discussion 5 September 28th, 2004 10:41 PM
Protection sheets Mark General Discussion 2 September 23rd, 2004 10:06 AM


All times are GMT +1. The time now is 04:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.