Sheet Protection

[wally]

is there a way I can protect and unprotect more than one sheet at a
time. I have 40 sheets in a workbook and would like to
protect/unprotect 16 at one time. I know I can do it one at a time but
I find this time consuming. Thank you, Wally

 

[Ken Johnson]

Hi Wally,

The following macros worked for me...

To protect some sheets first use Shift-Click Tab to group those sheets
then run the following macro...

Public Sub ProtectSelectedSheets()
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect
Next Sht
End Sub

To Unprotect some protected sheets first use Shift-Click Tab to group
those sheets then run the following macro...

Public Sub UnprotectSelectedSheets()
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
Sht.Unprotect
Next Sht
End Sub

I have assumed that you have NOT used passwords to protect your sheets.

Ken Johnson

 

[wally]

Hi Wally,

The following macros worked for me...

To protect some sheets first use Shift-Click Tab to group those sheets
then run the following macro...

Public Sub ProtectSelectedSheets()
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect
Next Sht
End Sub

To Unprotect some protected sheets first use Shift-Click Tab to group
those sheets then run the following macro...

Public Sub UnprotectSelectedSheets()
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
Sht.Unprotect
Next Sht
End Sub

I have assumed that you have NOT used passwords to protect your sheets.

Ken Johnson

 

[wally]

Ken, sorry I took so long in getting back. I can't get the macro to
work. Iv'e tried more than several times. Any suggestions?
Wally

 

[Ken Johnson]

Hi wally,

Are you ofay with macros or a newbie?

What exactly happens?

Is your security level set at medium?

When you open your workbook do you get the Security Warning dialog
which gives you the option of Enabling Macros or Disabling Macros?



To change security level to medium go Tools>Macro>Security then select
Medium>OK>Close the workbook>reopen the workbook.

If you do get that dialog are you clicking on Enable macros?

Was I correct in assuming you are NOT using a password with your
protection?

It's unlikely to be a version issue, it works on my PCs with XL 2003
and my old iMac with OS 9.2

Let me know how you go. The code works perfectly my end. I'll be using
it myself. Thanks for the idea.

Ken Johnson

 

[wally]

I have xp2003. I am familiar with macros, I use them in more than one
workbook and sheets. I do not get any security level warning dialog. I
can access the macro function anytime I request it. I am not using a
password. You give me the VBA(?) that will perform the function I want.
Now I don't understand exactly what I have to do with it to make it
work. Do I click the record macro button and then type in the info, I'm
just confused.
Thanks

 

[Ken Johnson]

Hi Wally,

1. Copy this...

Public Sub ProtectSelectedSheets()
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect
Next Sht
End Sub

2.On your workbook, open the VBA Editor by pressing Alt + F11
3. In the VBA Editor go Insert > Module then paste the code into the
blank module that appears.

4.Come back here and copy this code...

Public Sub UnprotectSelectedSheets()
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
Sht.Unprotect
Next Sht
End Sub

5. Go back to the VBA editor in your workbook, which should still be
open at the new module, then paste in the code into the same module.

6. Go back to your worksheet by going Alt + F11

7. Save

8. To run either macro Shift-click or Ctrl-click sheet tabs to select
the sheets to protect or unprotect, then go
Tools>Macro>Macros...>select the appropriate macro then click the Run
button.

Let me know how you go.


Ken Johnson

 

[wally]

Ken,
Works like a charm, thank you very much.
Wally

 

[Ken Johnson]

That's great Wally!
Thanks for the feedback.

If you haven't already, you might want to look into storing them in
your Personal Macro Workbook so that they are available for use in all
your workbooks.

Ken Johnson