Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: Object required | Excel Programming | |||
Object Required Error | Excel Programming | |||
424 Object required error | Excel Programming | |||
Object Required Error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |