Protecting all worksheets of a workbook

I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar in
layout and format except data. I was able to select all sheets and 'lock' and
'hide' desired cells in each in one stroke. But locking and hiding of cells
dont come into effect till I protect each sheet. I want to password protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the entire
workbook, the menu inside doesn't seem to lead in the direction that I want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

To protect your worksheets:-

Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Protect Password:= password1
Next mySheet

"neeraj" wrote:

I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar in
layout and format except data. I was able to select all sheets and 'lock' and
'hide' desired cells in each in one stroke. But locking and hiding of cells
dont come into effect till I protect each sheet. I want to password protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the entire
workbook, the menu inside doesn't seem to lead in the direction that I want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

Try this:
http://vbaexpress.com/kb/getarticle.php?kb_id=142
************
Anne Troy
www.OfficeArticles.com

"neeraj" wrote in message
...
I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar
in
layout and format except data. I was able to select all sheets and 'lock'
and
'hide' desired cells in each in one stroke. But locking and hiding of
cells
dont come into effect till I protect each sheet. I want to password
protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few
employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the
entire
workbook, the menu inside doesn't seem to lead in the direction that I
want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

This seems to be a VB code. I am new to VB. Where do I write it in my workbook

"bigwheel" wrote:

To protect your worksheets:-

Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Protect Password:= password1
Next mySheet

"neeraj" wrote:

I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar in
layout and format except data. I was able to select all sheets and 'lock' and
'hide' desired cells in each in one stroke. But locking and hiding of cells
dont come into effect till I protect each sheet. I want to password protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the entire
workbook, the menu inside doesn't seem to lead in the direction that I want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

Press Alt+F11 then click InsertModule. Enter the line

Sub ProtectSheets() and copy the code to the next line. An End Sub
statement should be added automatically

"neeraj" wrote:

This seems to be a VB code. I am new to VB. Where do I write it in my workbook

I protect multiple sheets all at one time using a free utility that you can
download and install as an add-in. All sheets will have the same password.

http://www.asap-utilities.com/

"neeraj" wrote:

I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar in
layout and format except data. I was able to select all sheets and 'lock' and
'hide' desired cells in each in one stroke. But locking and hiding of cells
dont come into effect till I protect each sheet. I want to password protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the entire
workbook, the menu inside doesn't seem to lead in the direction that I want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

Thanks, I downloaded the utilitity, it worked but I still have to go about
clearing the 'select locked cells' in each individual sheet if I did not want
my users to be able to select locked cells

"Anne Troy" wrote:

Try this:
http://vbaexpress.com/kb/getarticle.php?kb_id=142
************
Anne Troy
www.OfficeArticles.com

"neeraj" wrote in message
...
I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar
in
layout and format except data. I was able to select all sheets and 'lock'
and
'hide' desired cells in each in one stroke. But locking and hiding of
cells
dont come into effect till I protect each sheet. I want to password
protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few
employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the
entire
workbook, the menu inside doesn't seem to lead in the direction that I
want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

neeraj

Try this macro.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP


On Tue, 20 Sep 2005 08:41:02 -0700, neeraj
wrote:

Thanks, I downloaded the utilitity, it worked but I still have to go about
clearing the 'select locked cells' in each individual sheet if I did not want
my users to be able to select locked cells

"Anne Troy" wrote:

Try this:
http://vbaexpress.com/kb/getarticle.php?kb_id=142
************
Anne Troy
www.OfficeArticles.com

"neeraj" wrote in message
...
I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar
in
layout and format except data. I was able to select all sheets and 'lock'
and
'hide' desired cells in each in one stroke. But locking and hiding of
cells
dont come into effect till I protect each sheet. I want to password
protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few
employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the
entire
workbook, the menu inside doesn't seem to lead in the direction that I
want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

How did you lock the cells in all of your worksheets at the same time? Thanks!

"neeraj" wrote:

I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar in
layout and format except data. I was able to select all sheets and 'lock' and
'hide' desired cells in each in one stroke. But locking and hiding of cells
dont come into effect till I protect each sheet. I want to password protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the entire
workbook, the menu inside doesn't seem to lead in the direction that I want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.

Toni

Right-click on a sheet tab and "select all sheets".

As you select which cells to lock and unlock from the FormatCellsProtection
tab all sheets will be done at once.

To ungroup the sheets click on any other sheet tab or right-click and "ungroup
sheets".

Protection won't take place until you protect the sheets through
ToolsProtectionProtect sheet.

You cannot do this step on grouped sheets.

You will have to do them one at a time or through VBA macro.

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 Excel MVP

On Thu, 29 Sep 2005 15:12:01 -0700, "Toni"
wrote:

How did you lock the cells in all of your worksheets at the same time? Thanks!

"neeraj" wrote:

I have a workbook say Book1.xls with 12 worksheets say Rep1, Rep2,
Rep3,...Rep12 for 12 different employees. These sheets are exactly similar in
layout and format except data. I was able to select all sheets and 'lock' and
'hide' desired cells in each in one stroke. But locking and hiding of cells
dont come into effect till I protect each sheet. I want to password protect
each sheet with the same password. Is there a way to password protect all
worksheets or the entire workbook in one go? The workbook Book1.xls along
with other workbooks would be shared on a network drive by a few employees. I
see there is an option:Tools-Protection-Protect and Share Workbook. Though
this option from its name sounds like it would be able to protect the entire
workbook, the menu inside doesn't seem to lead in the direction that I want
to go in. If this is not the option to do my job, what does
Tools-Protection-Protect and Share Workbook do? Thanks.