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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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? |
|
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 |