Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Paul B - Thanks for your help! The macro worked per your input below.
impressive to see 30 worksheets protected at once. I am using Excel 2000 Thanks again! "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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Your welcome
-- 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" wrote in message ... Paul B - Thanks for your help! The macro worked per your input below. impressive to see 30 worksheets protected at once. I am using Excel 2000 Thanks again! "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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Did you put the code in the same workbook as the worksheets to protect?
Did you only run the first macro? Were there any errors when you ran it? ktykerr wrote: 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 -- Dave Peterson |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Hi Dave,
Thanks for the quick reply. I've put the code under the "thisworkbook" workbook alone. I just ran the first macro and there were no errors. After submitting my first post, I learned that the first sheet is protected because I cannot touch/edit the data on the 1st worksheet itself. While on the other worksheets I can still edit the data inside the cell that's why I'm wondering. I tried experimenting by deleting data on the next worksheets and after pressing the €œEnter€ key it does prompted a message that the worksheet is protected as well. So the code is working properly. However, my next problem is that...after sharing the protected file the users can €œaccess/view€ and €œsave as€ the file as read only. After they have saved the file as read only they were able to open/view the code through Macro-Visual Basic Editor in effect they now can see the password. Is there any way I can hide the macro code so that they wont have access on the password? Also, is that a normal result that only the first sheet is fully protected while you can still click and edit data the cells of the other worksheets? "Dave Peterson" wrote: Did you put the code in the same workbook as the worksheets to protect? Did you only run the first macro? Were there any errors when you ran it? ktykerr wrote: 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 -- Dave Peterson |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Move the code to a general module
Insert|Module and try it again. You can protect your code by (inside the VBE): Tools|VBAProject Properties|Protection tab give it a memorable password. Save and close your file and reopen it to test. ktykerr wrote: Hi Dave, Thanks for the quick reply. I've put the code under the "thisworkbook" workbook alone. I just ran the first macro and there were no errors. After submitting my first post, I learned that the first sheet is protected because I cannot touch/edit the data on the 1st worksheet itself. While on the other worksheets I can still edit the data inside the cell that's why I'm wondering. I tried experimenting by deleting data on the next worksheets and after pressing the €œEnter€ key it does prompted a message that the worksheet is protected as well. So the code is working properly. However, my next problem is that...after sharing the protected file the users can €œaccess/view€ and €œsave as€ the file as read only. After they have saved the file as read only they were able to open/view the code through Macro-Visual Basic Editor in effect they now can see the password. Is there any way I can hide the macro code so that they wont have access on the password? Also, is that a normal result that only the first sheet is fully protected while you can still click and edit data the cells of the other worksheets? "Dave Peterson" wrote: Did you put the code in the same workbook as the worksheets to protect? Did you only run the first macro? Were there any errors when you ran it? ktykerr wrote: 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 -- Dave Peterson -- Dave Peterson |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Hi Dave,
I tried it and it's working. Thank you for all the help. Sorry for the late reply i can't send my post last time since the page is prompting 'service is temprary not available'. Thanks again. "Dave Peterson" wrote: Move the code to a general module Insert|Module and try it again. You can protect your code by (inside the VBE): Tools|VBAProject Properties|Protection tab give it a memorable password. Save and close your file and reopen it to test. ktykerr wrote: Hi Dave, Thanks for the quick reply. I've put the code under the "thisworkbook" workbook alone. I just ran the first macro and there were no errors. After submitting my first post, I learned that the first sheet is protected because I cannot touch/edit the data on the 1st worksheet itself. While on the other worksheets I can still edit the data inside the cell that's why I'm wondering. I tried experimenting by deleting data on the next worksheets and after pressing the €œEnter€ key it does prompted a message that the worksheet is protected as well. So the code is working properly. However, my next problem is that...after sharing the protected file the users can €œaccess/view€ and €œsave as€ the file as read only. After they have saved the file as read only they were able to open/view the code through Macro-Visual Basic Editor in effect they now can see the password. Is there any way I can hide the macro code so that they won€„¢t have access on the password? Also, is that a normal result that only the first sheet is fully protected while you can still click and edit data the cells of the other worksheets? "Dave Peterson" wrote: Did you put the code in the same workbook as the worksheets to protect? Did you only run the first macro? Were there any errors when you ran it? ktykerr wrote: 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 -- Dave Peterson -- Dave Peterson |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
I am looking to be able to do this as well. I was able to use the coding
above to lock all worksheets at one time but I haven't been able to add the allow-users-to-edit-ranges. Can someone please help me? "EugeniaP" wrote: 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 |
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Does the address of the range change for each sheet?
ladytiger7481 wrote: I am looking to be able to do this as well. I was able to use the coding above to lock all worksheets at one time but I haven't been able to add the allow-users-to-edit-ranges. Can someone please help me? "EugeniaP" wrote: 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 -- Dave Peterson |
#27
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry?
Why not just use the build in "protect sheet" function? what benefit are you
looking for? cheers "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. |
#28
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry?
Using the built-in "protect sheet" function requires that user goes to each
sheet individually and protects. I think OP is looking for a simple way of doing all sheets at once. Something like this................. Sub ProtectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Protect Password:="justme" Next N Application.ScreenUpdating = True End Sub 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 Tue, 25 Aug 2009 07:45:03 -0700, David Corner <David wrote: Why not just use the build in "protect sheet" function? what benefit are you looking for? cheers "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. |
#29
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
I copied the unprotect macroe
Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? |
#30
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
I'd bet that the password isn't really 123 for all the worksheets in the
workbook that contains the code. Maybe you can add a line just to double check: For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws That may give you a hint. SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson |
#31
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
These two macros are what you are using?
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 I see no reason why the unprotect won't work. Gord Dibben MS Excel MVP On Tue, 1 Sep 2009 10:16:02 -0700, SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? |
#32
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
if i used the protect all ws macroe, would that not put the same password on
all ws? "Dave Peterson" wrote: I'd bet that the password isn't really 123 for all the worksheets in the workbook that contains the code. Maybe you can add a line just to double check: For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws That may give you a hint. SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson |
#33
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Depends on if that worked correctly and if anybody/any code changed the
password. I'd still bet that the password isn't what you think. SLD wrote: if i used the protect all ws macroe, would that not put the same password on all ws? "Dave Peterson" wrote: I'd bet that the password isn't really 123 for all the worksheets in the workbook that contains the code. Maybe you can add a line just to double check: For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws That may give you a hint. SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson -- Dave Peterson |
#34
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Where do I insert the line you sugest?
For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws "Dave Peterson" wrote: Depends on if that worked correctly and if anybody/any code changed the password. I'd still bet that the password isn't what you think. SLD wrote: if i used the protect all ws macroe, would that not put the same password on all ws? "Dave Peterson" wrote: I'd bet that the password isn't really 123 for all the worksheets in the workbook that contains the code. Maybe you can add a line just to double check: For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws That may give you a hint. SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson -- Dave Peterson |
#35
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
right where I wrote it. Just above the line causing the trouble.
Then when/if the line fails, you'll remember the last worksheet name that was shown to you. That's the one to check. SLD wrote: Where do I insert the line you sugest? For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws "Dave Peterson" wrote: Depends on if that worked correctly and if anybody/any code changed the password. I'd still bet that the password isn't what you think. SLD wrote: if i used the protect all ws macroe, would that not put the same password on all ws? "Dave Peterson" wrote: I'd bet that the password isn't really 123 for all the worksheets in the workbook that contains the code. Maybe you can add a line just to double check: For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws That may give you a hint. SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#36
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
This is working, but when you have over 100 tabs it is hard to keep track of
when the last mesege box is going to error out on me. Is there a line that I can enter that will give me a report that i can clearly see what was the last tab, or can it take me to the tab that does not have the correct password? "Dave Peterson" wrote: right where I wrote it. Just above the line causing the trouble. Then when/if the line fails, you'll remember the last worksheet name that was shown to you. That's the one to check. SLD wrote: Where do I insert the line you sugest? For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws "Dave Peterson" wrote: Depends on if that worked correctly and if anybody/any code changed the password. I'd still bet that the password isn't what you think. SLD wrote: if i used the protect all ws macroe, would that not put the same password on all ws? "Dave Peterson" wrote: I'd bet that the password isn't really 123 for all the worksheets in the workbook that contains the code. Maybe you can add a line just to double check: For Each ws In ThisWorkbook.Worksheets msgbox "Processing: " & ws.name ws.Unprotect Password:="123" Next ws That may give you a hint. SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#37
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Option Explicit
Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets On Error Resume Next ws.Unprotect Password:="123" On Error GoTo 0 If ws.ProtectContents _ Or ws.ProtectDrawingObjects _ Or ws.ProtectScenarios Then MsgBox ws.Name & " is still protected" End If Next ws End Sub SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson |
#38
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Thanks so much. That worked. it helped me identify which tabs did have a
different password. This is a big help to me. "Dave Peterson" wrote: Option Explicit Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets On Error Resume Next ws.Unprotect Password:="123" On Error GoTo 0 If ws.ProtectContents _ Or ws.ProtectDrawingObjects _ Or ws.ProtectScenarios Then MsgBox ws.Name & " is still protected" End If Next ws End Sub SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson |
#39
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
Is there a way to request a pasword to run the macroes?
"SLD" wrote: Thanks so much. That worked. it helped me identify which tabs did have a different password. This is a big help to me. "Dave Peterson" wrote: Option Explicit Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets On Error Resume Next ws.Unprotect Password:="123" On Error GoTo 0 If ws.ProtectContents _ Or ws.ProtectDrawingObjects _ Or ws.ProtectScenarios Then MsgBox ws.Name & " is still protected" End If Next ws End Sub SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson |
#40
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect all excel tabs in a file with one password entry
What are you trying to achieve?
Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim N As Single Dim pword as String pword = InputBox("Enter a password to run this macro") If pword < "drowssap" Then Exit Sub For N = 1 To Sheets.Count Sheets(N).Unprotect Password:="123" Next N Application.ScreenUpdating = True End Sub Now you should lock the project from view. Select workbook/project in VBE and Right-ClickVBAProject PropertiesProtection. Lock and enter a unique password. Do not forget this password..................you now have three different passwords. sheet protect...............123 inputbox...........drowssap project properties...............unique word Gord On Wed, 16 Sep 2009 11:00:01 -0700, SLD wrote: Is there a way to request a pasword to run the macroes? "SLD" wrote: Thanks so much. That worked. it helped me identify which tabs did have a different password. This is a big help to me. "Dave Peterson" wrote: Option Explicit Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets On Error Resume Next ws.Unprotect Password:="123" On Error GoTo 0 If ws.ProtectContents _ Or ws.ProtectDrawingObjects _ Or ws.ProtectScenarios Then MsgBox ws.Name & " is still protected" End If Next ws End Sub SLD wrote: I copied the unprotect macroe Sub Unprotect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub and it is still giving me an error on the 4th line. the passwords are the same on both macroes. What am I doing wrong? -- Dave Peterson |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a excel file and forgot the password | Excel Discussion (Misc queries) | |||
Excel file automatically opens | Excel Discussion (Misc queries) | |||
Remove or change password protected excel file | Excel Discussion (Misc queries) | |||
password protection of an MHTML Excel file | Excel Discussion (Misc queries) | |||
How Do I open an excel file without Excel Viewer support | Excel Discussion (Misc queries) |