protecting all sheets in workbook

[JB]

I've created for my boss an absence spreadsheet for each month containing
all staff (120) It's got all sorts of formulas and links and stuff.
There's a sheet per month thus 12 sheets each containing info. And 2 other
sheets splitting the staff in 2 sections to analyse and 1 more sheet to
calculate and analyse all.
This is being used by the HR department. I want to protect each sheet so
that all they need to do is use the drop downs provided to enter the type of
absence on the day and to protect all 15 sheets to prevent accidental entry
in cells that contain formula.
But apparently I can't do this in one go. It seems crazy that this is not
possible.

I don't know what protect workbook does as it doesn't seem to do anything.
If I have to make a change like a member of staff leaving or starting, I
have to unprotect each sheet to do this 12 times. . Crazy!

What can I do please.
Thank you
Jen

 

[Don Guillett]

for each ws in worksheets
ws.protect
next ws

 

[Gord Dibben]

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

 

[JB]

Thank you for your reply.
I'm ok with Access VB but I've never used it on Excel.
Where to I add this coding please?
Jen

 

[Gord Dibben]

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord