locking and unlocking worksheets

[Guest]

Does anyone know of an add-in that allows you to lock and unlock multiple
worksheets at one time?

 

[Gord Dibben]

Here are 4 macros.

You can store them in a module in a newly created workbook which you save as an
Add-in or in your Personal.xls.

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

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Sub UnProtect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.UnProtect Password:="justme"
Next ws
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Thanks, this works just fine.

The only problem I have is that any of the users of the workbook can run the
macro and unprotect all sheets.

Is there a way to hide the macro so it doesn't show up in Tools>macros or it
is possible password protect the macro?

Thanks,

 

[Gord Dibben]

To prevent users from seeing the macros and password, protect the project from
viewing.

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock for Viewing.

Apply a unique password and save/close the workbook.

When re-opened, macros will be unviewable or uneditable.

You personally will still be able to run the macros just by typing the macroname
into the Tools>Macro>Macros dialog or using your super-secret shortcut key
combo.


Gord

 

[Guest]

Thanks for the quick answer.

I tried it and the macros are still visible in the Tools>Macro>Macros menu.
The Edit & Step Into bottons are greyed out, but the Run botton is not, so
users will still be able to select the macro and run it.

Any suggestions?
Thank you

 

[Gord Dibben]

Apologies for the mis-direction.

At the top of the module and above the Subs enter this line

Option Private Module

Or change the Subs to Private Subs

Private Sub ProtectAllSheets()


Gord

 

[Guest]

That worked. Thanks!!

Apologies for the mis-direction.

At the top of the module and above the Subs enter this line

Option Private Module

Or change the Subs to Private Subs

Private Sub ProtectAllSheets()


Gord