Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Are there ways to protect multiple sheets at once? | New Users to Excel | |||
Protect Selected Sheets | Excel Programming | |||
Macro to protect multiple sheets? | Excel Worksheet Functions | |||
How do I protect multiple sheets at one time? | Excel Worksheet Functions | |||
Protect multiple sheets in one time | Excel Programming |