Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
locking and unlocking worksheets
Does anyone know of an add-in that allows you to lock and unlock multiple
worksheets at one time? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking/Unlocking cells upon condition | Excel Discussion (Misc queries) | |||
Locking Cells or worksheets | Excel Worksheet Functions | |||
Locking rows or Regions in Excel worksheets | Excel Worksheet Functions | |||
Locking down worksheets | Excel Discussion (Misc queries) | |||
How to view tiled Excel worksheets by locking rows for scrolling . | Excel Worksheet Functions |