Protect multiple selected sheets?
 

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

Protect multiple selected sheets?
 

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 wrote:

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

Protect multiple selected sheets?
 

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.


"Dave Peterson" wrote in message
...
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 wrote:

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

Protect multiple selected sheets?
 

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" wrote in message
...
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

Protect multiple selected sheets?
 

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" wrote in message
...

"Dave Peterson" wrote in message
...
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 wrote:

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

Protect multiple selected sheets?
 

That looks ok to me!



Bassman62 wrote:

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" wrote in message
...

"Dave Peterson" wrote in message
...
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 wrote:

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



--

Dave Peterson

Protect multiple selected sheets?
 

Thanks for the insight.

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

Thanks again.



"JLGWhiz" wrote in message
...
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" wrote in message
...
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