Protecting and Unprotecting several worksheets at one time

  • Thread starter Thread starter Learning Excel
  • Start date Start date
L

Learning Excel

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.
 
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.
 
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
 
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
 
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?
 
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?
 
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-click>Insert>Module.

Move the code from the worksheet module into this new module(module1 most
likely)

Run the macros from Tools>Macro>Macros or assign to buttons or shortcut
keys.


Gord
 
Thanks for this.
I had the same problem & it really helped.
Also a VB novice.

Any idea of the code to ask for the Password to be inputted to unlock all.

Thanks again,
 
Back
Top