locking and unlocking worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know of an add-in that allows you to lock and unlock multiple
worksheets at one time?
 
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
 
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,
 
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
 
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
 
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
 
That worked. Thanks!!

Gord Dibben said:
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
Click to expand...
 
Back
Top