Can I protect all excel tabs in a file with one password entry? in Excel General Questions  
 |  Edit my Profile  |  Help
 
     
  
 
 
 
Indiana born 12/19/2005 12:39 PM PST
  Question
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Paul B 12/19/2005 1:09 PM PST
  Answer
  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 born@discussions.microsoft.com> wrote in message
news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
>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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Indiana born 12/20/2005 5:46 AM PST
   
  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 born@discussions.microsoft.com> wrote in message
> news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> >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.
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Paul B 12/20/2005 8:49 AM PST
   
  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" <Indianaborn@discussions.microsoft.com> wrote in message
news:27E060E4-D03B-465D-A3AF-8BCFD9D8AA3F@microsoft.com...
> 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 born@discussions.microsoft.com> wrote in message
>> news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
>> >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.
>>
>>
>>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jen@ccbcc 6/11/2008 12:52 PM PST
   
  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 born@discussions.microsoft.com> wrote in message
> news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> >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.
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 6/11/2008 3:15 PM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > >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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jen@ccbcc 6/12/2008 6:00 AM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > >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
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 6/12/2008 6:12 AM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > >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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jen@ccbcc 6/12/2008 6:27 AM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > >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
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 6/12/2008 7:19 PM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > > >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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
EugeniaP 9/5/2008 12:59 PM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > > > >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
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
ladytiger7481 8/18/2009 7:34 PM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > > > > >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
> >
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 8/18/2009 8:24 PM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > > > > > >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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
ktykerr 7/31/2009 1:53 AM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > > >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
> >
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 7/31/2009 5:11 AM PST
   
  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 born@discussions.microsoft.com> wrote in message
> > > > > > > news:38EDFDC8-56DC-44E7-B2A9-4E49148F2FC7@microsoft.com...
> > > > > > > >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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies  
  More...