locking and unlocking worksheets
 

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

locking and unlocking worksheets
 

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


On Mon, 18 Jun 2007 07:00:01 -0700, adam wrote:

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

locking and unlocking worksheets
 

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 Toolsmacros or it
is possible password protect the macro?

Thanks,

"Gord Dibben" wrote:

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


On Mon, 18 Jun 2007 07:00:01 -0700, adam wrote:

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

locking and unlocking worksheets
 

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-clickVBAProject
PropertiesProtectionLock 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 ToolsMacroMacros dialog or using your super-secret shortcut key
combo.


Gord


On Mon, 29 Oct 2007 14:32:01 -0700, RL wrote:

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 Toolsmacros or it
is possible password protect the macro?

Thanks,

"Gord Dibben" wrote:

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


On Mon, 18 Jun 2007 07:00:01 -0700, adam wrote:

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

locking and unlocking worksheets
 

Thanks for the quick answer.

I tried it and the macros are still visible in the ToolsMacroMacros 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" wrote:

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-clickVBAProject
PropertiesProtectionLock 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 ToolsMacroMacros dialog or using your super-secret shortcut key
combo.


Gord


On Mon, 29 Oct 2007 14:32:01 -0700, RL wrote:

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 Toolsmacros or it
is possible password protect the macro?

Thanks,

"Gord Dibben" wrote:

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


On Mon, 18 Jun 2007 07:00:01 -0700, adam wrote:

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

locking and unlocking worksheets
 

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


On Wed, 31 Oct 2007 08:18:05 -0700, RL wrote:

Thanks for the quick answer.

I tried it and the macros are still visible in the ToolsMacroMacros 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" wrote:

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-clickVBAProject
PropertiesProtectionLock 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 ToolsMacroMacros dialog or using your super-secret shortcut key
combo.


Gord


On Mon, 29 Oct 2007 14:32:01 -0700, RL wrote:

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 Toolsmacros or it
is possible password protect the macro?

Thanks,

"Gord Dibben" wrote:

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


On Mon, 18 Jun 2007 07:00:01 -0700, adam wrote:

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

locking and unlocking worksheets
 

That worked. Thanks!!

"Gord Dibben" wrote:

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


On Wed, 31 Oct 2007 08:18:05 -0700, RL wrote:

Thanks for the quick answer.

I tried it and the macros are still visible in the ToolsMacroMacros 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" wrote:

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-clickVBAProject
PropertiesProtectionLock 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 ToolsMacroMacros dialog or using your super-secret shortcut key
combo.


Gord


On Mon, 29 Oct 2007 14:32:01 -0700, RL wrote:

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 Toolsmacros or it
is possible password protect the macro?

Thanks,

"Gord Dibben" wrote:

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


On Mon, 18 Jun 2007 07:00:01 -0700, adam wrote:

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