un/protecting multiple worksheets?

[Dave Potter]

I have workbooks with up to 40 sheets in them and some times need to
make changes to many identical sheets but since they are protected I
have to manually uprotect and protect them. Is there a was to do this
all at once? I'm not very good with VBA so if the only way is to use a
macro could you explain how to do this as if talking to a beginner? I
do appreciate this groups willingness to help others at no cost to
them. I have looked at the Google search site but couldnt find
anything I could understand. TIA for any help you can provide.

Dave

 

[jeff]

Hi, Dave,
I think it would help if we knew a bit more: do you
just want to protect/unprotect all the workbooks at the
same time? Do they have a password for protection, and
if so, are they all the same or is each one different?
Is that password something you don't mind entering or
could be stored (not very securely)? Also, then, do
you want to make a change on one sheet, and have that
change reflected in all the others? are all the
sheet-naming conventions the same (ie, when you change
cell "B256" on sheet "MyWorkSheet12" in Workbook
"ABC", is a change to go to "B256" on
sheet "MyWorksheet2" on "BCD", and ..3, etc?

Might not be bad to come up with something, but a lot
needs to be clarified, I think.
thx.
jeff

 

[Dave Potter]

Thanks Jeff,

Here's a bit of clarification.
Sheets tabs are named as follows:
1,2,3,4,5,6,7,Week 1,8,9,10,11,12,13,14,Week 2 ...etc thru Week 5,
#351 Month, Crew Turnover, Labor Guideline, 351.
All Tabs with a number for the name are identical and all tabs with
Week ? are identical. The other 4 sheets are each unique. Occasionally
I need to make some changes to all the numbered tabs or all the week ?
tabs. So, all changes will be the same on all identical sheets.
I would like to protect/unprotect all the identical sheets at once to
save time. Although the sheets are protected, they have no password.
The protection is just to keep people from erasing formulas and to
make it easier for them to scroll between unprotected cells without
getting a protected cell message. I hope that helps clarify my
situation and thanks for tackling this with me.
dave

 

[Dave Potter]

Thanks Jonathan,

I had thought about doing that but wasn't sure if that macro would
then work on other workbooks with different names and different sheet
tab names. Any ideas on that?
dave

 

[JE McGimpsey]

Here's a macro that will toggle protection on selected sheets.

Public Sub ToggleProtect1()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

For Each wkSht In ActiveWindow.SelectedSheets
With wkSht
If .ProtectContents Then
.Unprotect Password:=PWORD
Else
.Protect Password:=PWORD
End If
End With
Next wkSht
End Sub

If you're unfamiliar with macros, look at David McRitchie's Getting
Started with Macros:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Potter]

Thanks J E,

With my limited knowledge of VBA, I unhid my personal.xls workbook and
opened VBA and inserted a module and copied and pasted your code. I
then saved, closed , hid personal.xls and tried the code on my
workbook and recieved this error:

'run-time error '1004'

Method 'Protect' of Object'_Worksheet' failed

I'm assuming this is because of some portion of the code that I will
have to tweak, however, I don't know what portion to change.

dave

 

[Dave Potter]

J E,

One more thing:
I had multiple sheets selected when the method failed, however, when I
selected only one sheet the method worked fine. I would like to select
multiple sheets and have the macro work.
thanks, dave

 

[JE McGimpsey]

Oops, cut a bit too vigorously:

Public Sub ToggleProtect2()
Const PWORD As String = "drowssap"
Dim colSheets As New Collection
Dim wkSht As Worksheet
Dim i As Long
Application.ScreenUpdating = False
For Each wkSht In ActiveWindow.SelectedSheets
colSheets.Add wkSht
Next wkSht
colSheets(1).Select

For Each wkSht In colSheets
With wkSht
If .ProtectContents Then
.Unprotect Password:=PWORD
Else
.Protect Password:=PWORD
End If
End With
Next wkSht
For i = 2 To colSheets.Count
colSheets(i).Select False
Next i
End Sub

 

[Dave Potter]

J E,

Thanks!! Works like a charm. There is only one minor thing that has
changed since applying the macro.
Before: I had the 'Select locked cells' option turned off
After: The 'Select locked cells' option is turned on

Its not a major thing, but just eliminated the user gettting the
Protected cell error msg.

thanks again,
dave