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.

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.

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.

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.

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.

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

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

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

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.

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.

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.

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.

Paul B:

Thank you. Your macro worked very, very well. Thank you 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.

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.

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.

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.

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.