Protect multiple selected sheets?

[Bassman62]

I have two questions but I think the first answer will solve the second.
First: Why does the following line error out with "Run-time error '450':
Wrong number of arguments or invalid property assignment?
Debug.Print ActiveWindow.SelectedSheets (I thought this line earlier
today?)

Now the 2nd question;
I'm trying to protect / unprotect a group of selected sheets using the
following code but it errors out if more than one worksheet is selected with
"Run-time error '1004': Method 'Protect' (or 'Unprotect') of object
'_Worksheet' failed.
Sub Protect_Switch()
' Alternate WorkSheet Protection ON or OFF
Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
If sh.ProtectContents = True Then
sh.Unprotect
Else
sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
False, AllowSorting:=True, AllowFiltering:=True
End If
Next sh
End Sub

 

[Dave Peterson]

debug.print is use to print something (text) in the immediate window.

Activewindow.selected sheets doesn't have a value/text that could be printed.


I'd try something like:

Option Explicit
Sub Protect_Switch()

Dim sh As Object
Dim mySelectedSheets As Object

Set mySelectedSheets = ActiveWindow.SelectedSheets

mySelectedSheets.Item(1).Select 'unselect all the sheets but one

For Each sh In mySelectedSheets
If sh.ProtectContents = True Then
sh.Unprotect
Else
sh.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=False, _
AllowSorting:=True, AllowFiltering:=True
End If
Next sh

mySelectedSheets.Select 'reselect the sheets.
End Sub

 

[Bassman62]

Thanks Dave,

This is good. I'm encouraged in that I actually understand the code you
sent.

My next goal will be to set all of the selected sheets to the same state of
protection.

But don't help me out (yet) - I want to figure this out on my own.

Thanks again for the excellent assistance.

 

[JLGWhiz]

I think this is most likely the problem with the Wrong number of Arguments
message.

a.. You tried to assign a value to a read-only property, or you tried to
assign a value to a property for which no Property Let procedure exists.
Assigning a value to a property is the same as passing the value as an
argument to the object's Property Let procedure. Properly define the
Property Let procedure; it must have one more argument than the
corresponding Property Get procedure. If the property is meant to be
read-only, you can't assign a value to it.

This appears to be one of those constants that has a very limited use.

 

[Bassman62]

My next goal will be to set all of the selected sheets to the same state

of protection.

Got it! - Based on the state of the first of the SelectedSheets.
Thanks again to Dave Peterson.

Sub Protect_Switch()
Dim sh As Object
Dim mySelectedSheets As Object
Set mySelectedSheets = ActiveWindow.SelectedSheets
Set sh = mySelectedSheets.Item(1)
mySelectedSheets.Item(1).Select 'unselect all the sheets but one
If sh.ProtectContents = True Then
For Each sh In mySelectedSheets
sh.Unprotect
Next sh
Else
For Each sh In mySelectedSheets
sh.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=False, _
AllowSorting:=True, AllowFiltering:=True
Next sh
End If
mySelectedSheets.Select 'reselect the sheets.
End Sub

 

[Dave Peterson]

That looks ok to me!

My next goal will be to set all of the selected sheets to the same state
of protection.

Got it! - Based on the state of the first of the SelectedSheets.
Thanks again to Dave Peterson.

Sub Protect_Switch()
Dim sh As Object
Dim mySelectedSheets As Object
Set mySelectedSheets = ActiveWindow.SelectedSheets
Set sh = mySelectedSheets.Item(1)
mySelectedSheets.Item(1).Select 'unselect all the sheets but one
If sh.ProtectContents = True Then
For Each sh In mySelectedSheets
sh.Unprotect
Next sh
Else
For Each sh In mySelectedSheets
sh.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=False, _
AllowSorting:=True, AllowFiltering:=True
Next sh
End If
mySelectedSheets.Select 'reselect the sheets.
End Sub

 

[Bassman62]

Thanks for the insight.

If I can get my head around these concepts I know it'll help in the future.

Thanks again.