locking and unlocking worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adam - 18 Jun 2007 15:00 GMT

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

Reply to this Message

Gord Dibben - 18 Jun 2007 22:15 GMT

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

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

Reply to this Message

RL - 29 Oct 2007 22:32 GMT

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,

> Here are 4 macros.
>
[quoted text clipped - 39 lines]
> >Does anyone know of an add-in that allows you to lock and unlock multiple
> >worksheets at one time?

Reply to this Message

Gord Dibben - 30 Oct 2007 01:41 GMT

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, this works just fine.
>
[quoted text clipped - 49 lines]
>> >Does anyone know of an add-in that allows you to lock and unlock multiple
>> >worksheets at one time?

Reply to this Message

RL - 31 Oct 2007 16:18 GMT

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

> To prevent users from seeing the macros and password, protect the project from
> viewing.
[quoted text clipped - 67 lines]
> >> >Does anyone know of an add-in that allows you to lock and unlock multiple
> >> >worksheets at one time?

Reply to this Message

Gord Dibben - 31 Oct 2007 17:26 GMT

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

>Thanks for the quick answer.
>
[quoted text clipped - 76 lines]
>> >> >Does anyone know of an add-in that allows you to lock and unlock multiple
>> >> >worksheets at one time?

Reply to this Message

RL - 31 Oct 2007 18:31 GMT

That worked. Thanks!!

> Apologies for the mis-direction.
>
[quoted text clipped - 88 lines]
> >> >> >Does anyone know of an add-in that allows you to lock and unlock multiple
> >> >> >worksheets at one time?

Reply to this Message