Object Required Error
 

I have a series of 30 check boxes with the designation of...
ActiveSheet.Shapes("PC 1").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
I found that I could replace the above code with just....
[PC 1] = xlOff
[PC 2] = xlOff....etc
I then tried to use a For/Next Loop to turn off all 30 boxes with ....
For I = 1 to 30
["PC "&I] = xlOff
Next I
This failed with a message that says object required. Any Thoughts.....

Object Required Error
 

Marvin,
In the first routine, you are referring to the object as an item in a
collection:
ActiveSheet.Shapes("PC 1")

In the second, you are referring to the object directly:
[PC 1] = xlOff

This second method is not a string, so you cannot build it up.
Use the first method in a loop. And the .select is not required.

Dim I as long

For I = 1 to 30
With ActiveSheet.Shapes("PC " & i)
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
Next

NickHK

"Marvin" wrote in message
...
I have a series of 30 check boxes with the designation of...
ActiveSheet.Shapes("PC 1").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
I found that I could replace the above code with just....
[PC 1] = xlOff
[PC 2] = xlOff....etc
I then tried to use a For/Next Loop to turn off all 30 boxes with ....
For I = 1 to 30
["PC "&I] = xlOff
Next I
This failed with a message that says object required. Any Thoughts.....

Object Required Error
 

Still fails on the .Value with the error "Object does not support this
property or method. I tried adding a select to the end of the "With" command
and I received a object required error. I also unprotected the sheet with the
same results.

"NickHK" wrote:

Marvin,
In the first routine, you are referring to the object as an item in a
collection:
ActiveSheet.Shapes("PC 1")

In the second, you are referring to the object directly:
[PC 1] = xlOff

This second method is not a string, so you cannot build it up.
Use the first method in a loop. And the .select is not required.

Dim I as long

For I = 1 to 30
With ActiveSheet.Shapes("PC " & i)
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
Next

NickHK

"Marvin" wrote in message
...
I have a series of 30 check boxes with the designation of...
ActiveSheet.Shapes("PC 1").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
I found that I could replace the above code with just....
[PC 1] = xlOff
[PC 2] = xlOff....etc
I then tried to use a For/Next Loop to turn off all 30 boxes with ....
For I = 1 to 30
["PC "&I] = xlOff
Next I
This failed with a message that says object required. Any Thoughts.....

Object Required Error
 

How about going through the checkbox collection?

Either:

activesheet.checkboxes.value = xloff

or

dim myCBX as checkbox
for each myCBX in activesheet.checkboxes
mycbx.value = xloff
next mycbx

If you wanted to use a counter:

dim iCtr as long
for ictr = 1 to 30
activesheet.checkboxes("Pc " & ictr).value = xloff
next ictr



Marvin wrote:

Still fails on the .Value with the error "Object does not support this
property or method. I tried adding a select to the end of the "With" command
and I received a object required error. I also unprotected the sheet with the
same results.

"NickHK" wrote:

Marvin,
In the first routine, you are referring to the object as an item in a
collection:
ActiveSheet.Shapes("PC 1")

In the second, you are referring to the object directly:
[PC 1] = xlOff

This second method is not a string, so you cannot build it up.
Use the first method in a loop. And the .select is not required.

Dim I as long

For I = 1 to 30
With ActiveSheet.Shapes("PC " & i)
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
Next

NickHK

"Marvin" wrote in message
...
I have a series of 30 check boxes with the designation of...
ActiveSheet.Shapes("PC 1").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
I found that I could replace the above code with just....
[PC 1] = xlOff
[PC 2] = xlOff....etc
I then tried to use a For/Next Loop to turn off all 30 boxes with ....
For I = 1 to 30
["PC "&I] = xlOff
Next I
This failed with a message that says object required. Any Thoughts.....




--

Dave Peterson

Object Required Error
 

The following command worked perfectly and was the simplest approach.

activesheet.checkboxes.value = xloff

I did need to unprotect the sheet but outside of that this code works.

"Dave Peterson" wrote:

How about going through the checkbox collection?

Either:

activesheet.checkboxes.value = xloff

or

dim myCBX as checkbox
for each myCBX in activesheet.checkboxes
mycbx.value = xloff
next mycbx

If you wanted to use a counter:

dim iCtr as long
for ictr = 1 to 30
activesheet.checkboxes("Pc " & ictr).value = xloff
next ictr



Marvin wrote:

Still fails on the .Value with the error "Object does not support this
property or method. I tried adding a select to the end of the "With" command
and I received a object required error. I also unprotected the sheet with the
same results.

"NickHK" wrote:

Marvin,
In the first routine, you are referring to the object as an item in a
collection:
ActiveSheet.Shapes("PC 1")

In the second, you are referring to the object directly:
[PC 1] = xlOff

This second method is not a string, so you cannot build it up.
Use the first method in a loop. And the .select is not required.

Dim I as long

For I = 1 to 30
With ActiveSheet.Shapes("PC " & i)
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
Next

NickHK

"Marvin" wrote in message
...
I have a series of 30 check boxes with the designation of...
ActiveSheet.Shapes("PC 1").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
I found that I could replace the above code with just....
[PC 1] = xlOff
[PC 2] = xlOff....etc
I then tried to use a For/Next Loop to turn off all 30 boxes with ....
For I = 1 to 30
["PC "&I] = xlOff
Next I
This failed with a message that says object required. Any Thoughts.....




--

Dave Peterson

Object Required Error
 

You may want to remember the alternatives.

I've see that single line of code break when there are lots and lots of
checkboxes on the worksheet.

Marvin wrote:

The following command worked perfectly and was the simplest approach.

activesheet.checkboxes.value = xloff

I did need to unprotect the sheet but outside of that this code works.

"Dave Peterson" wrote:

How about going through the checkbox collection?

Either:

activesheet.checkboxes.value = xloff

or

dim myCBX as checkbox
for each myCBX in activesheet.checkboxes
mycbx.value = xloff
next mycbx

If you wanted to use a counter:

dim iCtr as long
for ictr = 1 to 30
activesheet.checkboxes("Pc " & ictr).value = xloff
next ictr



Marvin wrote:

Still fails on the .Value with the error "Object does not support this
property or method. I tried adding a select to the end of the "With" command
and I received a object required error. I also unprotected the sheet with the
same results.

"NickHK" wrote:

Marvin,
In the first routine, you are referring to the object as an item in a
collection:
ActiveSheet.Shapes("PC 1")

In the second, you are referring to the object directly:
[PC 1] = xlOff

This second method is not a string, so you cannot build it up.
Use the first method in a loop. And the .select is not required.

Dim I as long

For I = 1 to 30
With ActiveSheet.Shapes("PC " & i)
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
Next

NickHK

"Marvin" wrote in message
...
I have a series of 30 check boxes with the designation of...
ActiveSheet.Shapes("PC 1").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
I found that I could replace the above code with just....
[PC 1] = xlOff
[PC 2] = xlOff....etc
I then tried to use a For/Next Loop to turn off all 30 boxes with ....
For I = 1 to 30
["PC "&I] = xlOff
Next I
This failed with a message that says object required. Any Thoughts.....




--

Dave Peterson


--

Dave Peterson