Protecting all worksheets of a workbook

[Guest]

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.

 

[Guest]

To protect your worksheets:-

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

 

[Anne Troy]

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

 

[Guest]

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

 

[Guest]

Press Alt+F11 then click Insert>Module. Enter the line

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

 

[Guest]

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/

 

[Guest]

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

 

[Gord Dibben]

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

 

[Guest]

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

 

[Gord Dibben]

Toni

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

As you select which cells to lock and unlock from the Format>Cells>Protection
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
Tools>Protection>Protect 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

 

[Guest]

Hi Gord! I locked my cells, protected my sheets, copied the file to a CD,
went to a client's site, copied the file to their computer and when I opened
the file, the cells were no longer locked. Do you know what I did wrong?
Toni

 

[Gord Dibben]

Toni

Did you test the protection and locked cells before you saved then copied to
CD?

As in, did you get what you wanted from my original suggestions?

If so, I have no idea why the copied and opened workbook had no cells locked.

Was the sheet protection still enabled on all sheets after opening on other
computer or was that gone also?


Gord

 

[Guest]

Hi Gord!

Your original suggestion did work and I did test the protection and locked
cells before I saved and copied to the CD. Figuring I had lost my mind, when
I got back to my office, I opened the original files and they were still
locked and protected on my computer. Just can't figure out how they could
have come unlocked at the client's site.

 

[Guest]

Larry...You ROCK! I have giant workbooks that I absolutely was dreading
locking each sheet manually. Thanks for the Link bud!

 

[Guest]

I am having the very same problem and I'm wondering if you were able to
resolve the issue of clearing the "select locked cells" after running the
utility to protect / unprotect worksheets?