Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
The subject says it all.
I do this very often and is a time consuming going one by one, is there a way, without using VBA, to do this protection and unprotection of worksheets with something like CTRL all at once ? -- THANKS. Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
Not possible without VBA
Gord Dibben MS Excel MVP On Sat, 5 Apr 2008 19:33:00 -0700, Learning Excel wrote: The subject says it all. I do this very often and is a time consuming going one by one, is there a way, without using VBA, to do this protection and unprotection of worksheets with something like CTRL all at once ? -- THANKS. Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
Well is very frustrating going over and over, protecting and unprotecting,
again and again, but that's the way it is. Thanks a lot Gord Dibben, again and again. -- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. "Gord Dibben" wrote: Not possible without VBA Gord Dibben MS Excel MVP On Sat, 5 Apr 2008 19:33:00 -0700, Learning Excel wrote: The subject says it all. I do this very often and is a time consuming going one by one, is there a way, without using VBA, to do this protection and unprotection of worksheets with something like CTRL all at once ? -- THANKS. Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
Are you restricted to no VBA or just need help with it?
I can provide macros to protect and unprotect all sheets or multiple selected sheets. Gord On Sun, 6 Apr 2008 15:45:00 -0700, Learning Excel wrote: Well is very frustrating going over and over, protecting and unprotecting, again and again, but that's the way it is. Thanks a lot Gord Dibben, again and again. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
Is it possible to get your macro for this?
Thank you! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
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 Gord Dibben MS Excel MVP On Thu, 25 Sep 2008 13:44:01 -0700, StayinAlive wrote: Is it possible to get your macro for this? Thank you! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
This is very helpful, but I have almost no VBA experience. Can you provide
some direction on how to get this code in my spreadsheet? "Gord Dibben" wrote: 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 Gord Dibben MS Excel MVP On Thu, 25 Sep 2008 13:44:01 -0700, StayinAlive wrote: Is it possible to get your macro for this? Thank you! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
I think I figured it out. Right clicked on one of the tabs, selected View Code, then pasted the code you provided. Earlier, Excel did not appear to be saving the code because there were no Macros listed when I closed and reopened the file. This time it seemed to work. Did I do it correctly? "Jaime" wrote: This is very helpful, but I have almost no VBA experience. Can you provide some direction on how to get this code in my spreadsheet? "Gord Dibben" wrote: 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 Gord Dibben MS Excel MVP On Thu, 25 Sep 2008 13:44:01 -0700, StayinAlive wrote: Is it possible to get your macro for this? Thank you! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting and Unprotecting several worksheets at one time
Best to place macros like this into a general module rather than in a
worksheet module. Since you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime........................ Alt + F11 to open VBE. CTRL + r to open Project Explorer. Select your workbook/project and right-clickInsertModule. Move the code from the worksheet module into this new module(module1 most likely) Run the macros from ToolsMacroMacros or assign to buttons or shortcut keys. Gord On Wed, 4 Feb 2009 12:14:02 -0800, Jaime wrote: I think I figured it out. Right clicked on one of the tabs, selected View Code, then pasted the code you provided. Earlier, Excel did not appear to be saving the code because there were no Macros listed when I closed and reopened the file. This time it seemed to work. Did I do it correctly? "Jaime" wrote: This is very helpful, but I have almost no VBA experience. Can you provide some direction on how to get this code in my spreadsheet? "Gord Dibben" wrote: 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 Gord Dibben MS Excel MVP On Thu, 25 Sep 2008 13:44:01 -0700, StayinAlive wrote: Is it possible to get your macro for this? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for protecting and unprotecting multiple worksheets | Excel Discussion (Misc queries) | |||
Protecting Multiple Worksheets at the same time | Excel Worksheet Functions | |||
Unprotecting Worksheets | Excel Discussion (Misc queries) | |||
Unprotecting worksheets | Excel Worksheet Functions | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) |