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
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Does anyone know how to incorporate the allow-users-to-edit-certain-ranges
part into this protection macro? Thank you! "Dave Peterson" wrote: Your code worked fine for me. Did you create another sub or function and name it Unprotect? If you did, then change the name of that sub/function to something else. Jen@ccbcc wrote: Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="123" Next ws End Sub Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub Each time I attempt to run the unprotect macro, I receive an error which points to the line "ws.Unprotect Password:="123" "Dave Peterson" wrote: If you protect the sheets without supplying a password, then the code won't need to use a password, either. But if you use a password manually, you'll need it in code, too. I think you made a typo when you made that suggested change. Post your current code and indicate the line that caused the error. Jen@ccbcc wrote: If I simply replace the protect with unprotect, I receive the following error message: Compile error: Expected function or variable Also...is there a way to perform these macros without requiring a password? "Dave Peterson" wrote: Just use ws.unprotect password:="123" to unprotect the sheets. They all share the same password, right? Jen@ccbcc wrote: Does anyone know how to do the reverse (i.e. unprotect all sheets at once)? "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry?
I have no understanding of writing VB, but is it possilbe to write a macro
that will password protect all sheets in a workbook simultaneously with all of the following options checked (and all others cleared): "Select unlocked cells", "Format Rows"? Thanks in advance for any help on this issue. I can copy and paste if someone else can write the code. "Indiana born" wrote: I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#13
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry?
Sub ProtectAllSheets()
Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count With Sheets(N) .Protect Password:="justme", AllowFormattingRows:=True .EnableSelection = xlUnlockedCells End With Next N Application.ScreenUpdating = True End Sub You might want to unprotect them all at some point......... Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Unprotect Password:="justme" Next N Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 29 Oct 2008 14:01:01 -0700, hollyc83 wrote: I have no understanding of writing VB, but is it possilbe to write a macro that will password protect all sheets in a workbook simultaneously with all of the following options checked (and all others cleared): "Select unlocked cells", "Format Rows"? Thanks in advance for any help on this issue. I can copy and paste if someone else can write the code. "Indiana born" wrote: I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#14
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Paul B - this is great stuff. How would I alter that statement to indicate a
specified range of columns and rows? e.g. I want to protect Column B, and Rows 2 and 3 on all sheets. "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#15
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
First we unlock all cells then lock column B and rows 2 & 3
Note: the protection of column B extends to all cells in that column, not just rows 2 and 3 Is that that you want? Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Cells.Locked = False .Range("B:B,2:3").Locked = True .Protect Password:="123" End With Next ws End Sub Gord Dibben MS Excel MVP On Mon, 2 Mar 2009 10:56:01 -0800, Sheetsie wrote: Paul B - this is great stuff. How would I alter that statement to indicate a specified range of columns and rows? e.g. I want to protect Column B, and Rows 2 and 3 on all sheets. "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#16
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Understood. Thank you! This is exactly what I needed. Much appreciated Gord.
"Gord Dibben" wrote: First we unlock all cells then lock column B and rows 2 & 3 Note: the protection of column B extends to all cells in that column, not just rows 2 and 3 Is that that you want? Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Cells.Locked = False .Range("B:B,2:3").Locked = True .Protect Password:="123" End With Next ws End Sub Gord Dibben MS Excel MVP On Mon, 2 Mar 2009 10:56:01 -0800, Sheetsie wrote: Paul B - this is great stuff. How would I alter that statement to indicate a specified range of columns and rows? e.g. I want to protect Column B, and Rows 2 and 3 on all sheets. "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#17
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Glad to help.
Thanks for the feedback. Gord On Mon, 2 Mar 2009 13:39:01 -0800, Sheetsie wrote: Understood. Thank you! This is exactly what I needed. Much appreciated Gord. "Gord Dibben" wrote: First we unlock all cells then lock column B and rows 2 & 3 Note: the protection of column B extends to all cells in that column, not just rows 2 and 3 Is that that you want? Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Cells.Locked = False .Range("B:B,2:3").Locked = True .Protect Password:="123" End With Next ws End Sub Gord Dibben MS Excel MVP On Mon, 2 Mar 2009 10:56:01 -0800, Sheetsie wrote: Paul B - this is great stuff. How would I alter that statement to indicate a specified range of columns and rows? e.g. I want to protect Column B, and Rows 2 and 3 on all sheets. "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#18
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Is there a way to prompt for the password?
"Gord Dibben" wrote: Glad to help. Thanks for the feedback. Gord On Mon, 2 Mar 2009 13:39:01 -0800, Sheetsie wrote: Understood. Thank you! This is exactly what I needed. Much appreciated Gord. "Gord Dibben" wrote: First we unlock all cells then lock column B and rows 2 & 3 Note: the protection of column B extends to all cells in that column, not just rows 2 and 3 Is that that you want? Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Cells.Locked = False .Range("B:B,2:3").Locked = True .Protect Password:="123" End With Next ws End Sub Gord Dibben MS Excel MVP On Mon, 2 Mar 2009 10:56:01 -0800, Sheetsie wrote: Paul B - this is great stuff. How would I alter that statement to indicate a specified range of columns and rows? e.g. I want to protect Column B, and Rows 2 and 3 on all sheets. "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. |
#19
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Hi, I'm trying to run this macro code in my excel file since I also need and
wanted to protect several sheets at a time. However, after running it, only the first sheet was protected and the rest is still unprotected. Can anyone help me on this please? Thanks in advance. "Jen@ccbcc" wrote: Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="123" Next ws End Sub Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub Each time I attempt to run the unprotect macro, I receive an error which points to the line "ws.Unprotect Password:="123" "Dave Peterson" wrote: If you protect the sheets without supplying a password, then the code won't need to use a password, either. But if you use a password manually, you'll need it in code, too. I think you made a typo when you made that suggested change. Post your current code and indicate the line that caused the error. Jen@ccbcc wrote: If I simply replace the protect with unprotect, I receive the following error message: Compile error: Expected function or variable Also...is there a way to perform these macros without requiring a password? "Dave Peterson" wrote: Just use ws.unprotect password:="123" to unprotect the sheets. They all share the same password, right? Jen@ccbcc wrote: Does anyone know how to do the reverse (i.e. unprotect all sheets at once)? "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. -- Dave Peterson -- Dave Peterson |
#20
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Hi, I'm trying to run this macro code in my excel file since I also need and
wanted to protect several sheets at a time. However, after running it, only the first sheet was protected and the rest is still unprotected. Can anyone help me on this please? Thanks in advance. "Jen@ccbcc" wrote: Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="123" Next ws End Sub Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub Each time I attempt to run the unprotect macro, I receive an error which points to the line "ws.Unprotect Password:="123" "Dave Peterson" wrote: If you protect the sheets without supplying a password, then the code won't need to use a password, either. But if you use a password manually, you'll need it in code, too. I think you made a typo when you made that suggested change. Post your current code and indicate the line that caused the error. Jen@ccbcc wrote: If I simply replace the protect with unprotect, I receive the following error message: Compile error: Expected function or variable Also...is there a way to perform these macros without requiring a password? "Dave Peterson" wrote: Just use ws.unprotect password:="123" to unprotect the sheets. They all share the same password, right? Jen@ccbcc wrote: Does anyone know how to do the reverse (i.e. unprotect all sheets at once)? "Paul B" wrote: Indiana, you can use a macro like this, Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Indiana born" Indiana wrote in message ... I have spreadsheets with multiple tabs - I presently have to password protect each tab separately. I'm looking for a way to protect all tabs with one password entry. i am not share the files, but others do view them and I don't want changes made. -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multi-User vs Client/Server | GeorgieGirl | General Discussion | 3 | June 8th, 2005 11:42 PM |
how do i open password protect word file if forgot password? | chandraprakash | General Discussion | 1 | November 9th, 2004 02:20 PM |
Automatically Updating Excel Data In Word Doc Inside Excel File | Business Design Architect | General Discussion | 0 | August 29th, 2004 10:55 PM |
decipher log of scanpst.exe | km | General Discussion | 0 | July 18th, 2004 09:00 AM |
Unsafe Attachments | Ron | Installation & Setup | 2 | June 9th, 2004 01:55 AM |