Object Required Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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 said:
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.....
Click to expand...
Click to expand...
 
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


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 said:
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 said:
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.....
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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


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

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.....
Click to expand...
Click to expand...
Click to expand...
 
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.
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 said:
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


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.

:

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

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.....
Click to expand...
Click to expand...
Click to expand...
 
Back
Top