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.

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.

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.

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.

Protecting and Unprotecting several worksheets at one time
 

Is it possible to get your macro for this?
Thank you!

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!

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!

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!

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!