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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Protection and Hiding Sheets | JudithJubilee | General Discussion | 4 | March 4th, 2005 02:16 PM |
Changing password protection on sheets | Tom Hewitt | General Discussion | 5 | February 25th, 2005 03: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 |