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.....

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.....

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.....

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

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

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