Password protecting multiple sheets

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

Is there a way to set a password for multiple sheets
without having to set each one separately. I have over 60
sheets in one of my workbooks. I don't want to protect
the whole workbook with a password because the file is
used by other users but rather lock cells in each of the
sheets with a password thereby allowing users to only edit
certain cells. Because I have many packages to do, I
don't want to be setting these passwords for over 600
sheets.

Thanks
 
Allison, you can do it with 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

And to unprotect them
Sub unprotect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run.
--
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 2000 & 97
** remove news from my email address to reply by email **
 
Hi Allison!

You need a subroutine in a Module in the workbook or in your Personal
workbook.such as:

Sub ProtectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Protect Password:="not4u2see"
Next n
End Sub

And if you'd like fries with that use:

Sub UnprotectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="not4u2see"
Next n
End Sub


Or go for the Big Mac!

Public Sub ToggleProtect1()
' From J E McGimpsey modified by NH
Application.ScreenUpdating = False
Const PWORD As String = "not4u2see"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
Application.ScreenUpdating = True
MsgBox Mid(statStr, 2)
End Sub
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Allison,

If you are not having any programming knowledge, I suggest that
download and install an add-in for Excel, ASAP-Utilities, it has th
ability to protect all worksheet in one go and unprotect in one go. I
has other useful features as well...just log in to it's website t
download....the most important of all...it is free!

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

Hope that will help

Rgds,
Gilber
 
Back
Top