A Microsoft Office (Excel, Word) forum. OfficeFrustration

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

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

Can I protect all excel tabs in a file with one password entry?



 
 
Thread Tools Display Modes
  #21  
Old July 31st, 2009, 01:09 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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  
Old August 3rd, 2009, 04:44 AM posted to microsoft.public.excel.misc
ktykerr
external usenet poster
 
Posts: 4
Default 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 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

  #23  
Old August 3rd, 2009, 12:46 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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 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
  #24  
Old August 6th, 2009, 02:28 AM posted to microsoft.public.excel.misc
ktykerr
external usenet poster
 
Posts: 4
Default 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  
Old August 19th, 2009, 03:35 AM posted to microsoft.public.excel.misc
ladytiger7481
external usenet poster
 
Posts: 4
Default 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  
Old August 19th, 2009, 04:22 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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  
Old August 25th, 2009, 03:45 PM posted to microsoft.public.excel.misc
David Corner
external usenet poster
 
Posts: 1
Default 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  
Old August 25th, 2009, 04:31 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old September 1st, 2009, 06:16 PM posted to microsoft.public.excel.misc
sld
external usenet poster
 
Posts: 10
Default 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  
Old September 1st, 2009, 08:02 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
 




Thread Tools
Display Modes

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

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
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 03: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


All times are GMT +1. The time now is 12:03 PM.


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