Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

That is because the option button from the control toolbox does not have a
linked cell. The option button from the forms toolbar does. What exactly
would you like to do?
--
HTH...

Jim Thomlinson


"-JEFF-" wrote:

Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

The linked cell property is provided by the container of the OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try
to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

Using Excel 2000 9.0.6926, I selected View-Toolbars-Control Toolbox and
added option buttons. When I select Design Mode, then select one of the
buttons, right click, select properties, on of the properties I can change
manually is LinkedCell, however, I cannot change it using the line of code
below as I have changed other properties. My big picture is this: I have to
build an empty worksheet with 16 option buttons on each row. Each row has
three groups of its own. I have to be able to total the selections by column
(countif() works very well when the buttons are linked to a cell). I have
created one row and then I copy that row about 400 times, then go back and
set the properties for each optionbutton. Is there a more expeditious /
efficient method to create this sheet and be able to get the column totals?

"Jim Thomlinson" wrote:

That is because the option button from the control toolbox does not have a
linked cell. The option button from the forms toolbar does. What exactly
would you like to do?
--
HTH...

Jim Thomlinson


"-JEFF-" wrote:

Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

Hi Jim,

What makes you think that?

If you insert an optionbutton from the controls toolbox, then click the
properties icon, then you will see a linkedcell property. It is the shape,
the OLEObject, which is on the worksheet, so it is that is linked, rather
than the object within the shape, the optionbutton. You can get it from VBA
with

ActiveSheet.OLEObjects("OptionButton1").LinkedCell = "A1"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
That is because the option button from the control toolbox does not have a
linked cell. The option button from the forms toolbar does. What exactly
would you like to do?
--
HTH...

Jim Thomlinson


"-JEFF-" wrote:

Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but when I
try to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

Thank you Tom, works great!
-JEFf-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try
to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

I'll be darned. I never even noticed that it had a linked cell. Dopey me.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try
to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

Thank you very much for your efforts!
-JEFF-

"Jim Thomlinson" wrote:

I'll be darned. I never even noticed that it had a linked cell. Dopey me.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try
to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

I thought that based on a shear lack of understanding. Many of my thoughts
are based on that... The trick is figuring out which ones. My goal is as
always "to suck a little less each and every day". Based on what I just
learned tomorrow will be a better day.
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

Hi Jim,

What makes you think that?

If you insert an optionbutton from the controls toolbox, then click the
properties icon, then you will see a linkedcell property. It is the shape,
the OLEObject, which is on the worksheet, so it is that is linked, rather
than the object within the shape, the optionbutton. You can get it from VBA
with

ActiveSheet.OLEObjects("OptionButton1").LinkedCell = "A1"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
That is because the option button from the control toolbox does not have a
linked cell. The option button from the forms toolbar does. What exactly
would you like to do?
--
HTH...

Jim Thomlinson


"-JEFF-" wrote:

Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but when I
try to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

Now I feel like I'm being a PITB, but the code ran one time, now I am getting
the error "Unable to get the OLEobjects property of the worksheet class" Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable to
access the linkedcell property using the following code. I have accessed
some of the properties, (caption, value) using this syntax but when I try
to
change LinkedCell, I get "object doesn't support this property or method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

the code I posted is correct as an example.

However, if you are running it in a loop, I wouldn't think you would want to
link all optionbuttons to a single cell as that code would indiciate.

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"
end if
Next
would probably be more like

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P" & 10 + iob
end if
Next

I am not sure why you would get an error - possibly if the focus is not on
the sheet.

Are you using xl97?

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Now I feel like I'm being a PITB, but the code ran one time, now I am
getting
the error "Unable to get the OLEobjects property of the worksheet class"
Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the
OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable
to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but when I
try
to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

correct, I do not link all of the buttons to the same cell. I have an
algorithm. I kept it at that for simplicity for this forum.
Here's the more efficient version I am working on

For Each OptionButton In ActiveSheet.OLEObjects

'code to format my Option Buttons

Next

Here is my actual code:
Sub tmp()
Dim iob As Integer
Dim ir As Long
Dim MyRow As Integer
Dim i As Long
Dim MyObj
'start on row 11
MyRow = 11 'incriments every 16 option buttons
iob = 0
For ir = 11 To 50 'only trying to do fifty rows for now
For i = 1 To 16
iob = iob + 1

' linked cell
ActiveSheet.OLEObjects("OptionButton" & iob).LinkedCell = IIf(i = 1,
"P" & ir, _
IIf(i = 2, "R" & ir, IIf(i = 3, "T" & ir, IIf(i = 4, "V" & ir, IIf(i
= 5, "X" & ir, _
IIf(i = 6, "Z" & ir, IIf(i = 7, "AB" & ir, IIf(i = 8, "AD" & ir,
IIf(i = 9, "AF" & ir, _
IIf(i = 10, "AH" & ir, IIf(i = 11, "AJ" & ir, IIf(i = 12, "AL" & ir,
IIf(i = 13, "AN" & ir, _
IIf(i = 14, "AP" & ir, IIf(i = 15, "AR" & ir, "AT" & ir)))))))))))))))

'caption
ActiveSheet.OLEObjects("OptionButton" & iob).Object.Caption = ""
'group
ActiveSheet.OLEObjects("OptionButton" & iob).Object.Value = IIf(i =
2, "True", "false")
If i <= 7 Or (i 14 And i < 17) Then 'Group = "1"
ActiveSheet.OLEObjects("OptionButton" & iob).Object.GroupName =
Trim(Str(ir)) & "-" & "1"
ElseIf i = 8 Or i = 9 Then 'Group = "2"
ActiveSheet.OLEObjects("OptionButton" & iob).Object.GroupName =
Trim(Str(ir)) & "-" & "2"
Else 'Group = "3"
ActiveSheet.OLEObjects("OptionButton" & iob).Object.GroupName =
Trim(Str(ir)) & "-" & "3"
End If
'name
'ActiveSheet.OLEObjects("OptionButton" & iob).Name = "ob" &
Trim(Str(iob))
Next
i = 1
Next
End Sub


"Tom Ogilvy" wrote:

the code I posted is correct as an example.

However, if you are running it in a loop, I wouldn't think you would want to
link all optionbuttons to a single cell as that code would indiciate.

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"
end if
Next
would probably be more like

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P" & 10 + iob
end if
Next

I am not sure why you would get an error - possibly if the focus is not on
the sheet.

Are you using xl97?

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Now I feel like I'm being a PITB, but the code ran one time, now I am
getting
the error "Unable to get the OLEobjects property of the worksheet class"
Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the
OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable
to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but when I
try
to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

Using Excel 2000 9.0.6926. It seems funny that it ran one time and now I get
the error. I've tried your latest code and my latest efficient code and I
always get the error.

"Tom Ogilvy" wrote:

the code I posted is correct as an example.

However, if you are running it in a loop, I wouldn't think you would want to
link all optionbuttons to a single cell as that code would indiciate.

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"
end if
Next
would probably be more like

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P" & 10 + iob
end if
Next

I am not sure why you would get an error - possibly if the focus is not on
the sheet.

Are you using xl97?

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Now I feel like I'm being a PITB, but the code ran one time, now I am
getting
the error "Unable to get the OLEobjects property of the worksheet class"
Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the
OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am unable
to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but when I
try
to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell = "P11"

You code works through OptionButtons 1 to 640. If you get that error, I
would assume one of them doesn't exist (or has a different name).

You have this line commented out

'name
'ActiveSheet.OLEObjects("OptionButton" & iob).Name = "ob" &
Trim(Str(iob))

but if you were testing or something, perhaps one optionbutton is named
optionbuttonob & iob instead of optionbutton & iob

--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
correct, I do not link all of the buttons to the same cell. I have an
algorithm. I kept it at that for simplicity for this forum.
Here's the more efficient version I am working on

For Each OptionButton In ActiveSheet.OLEObjects

'code to format my Option Buttons

Next

Here is my actual code:
Sub tmp()
Dim iob As Integer
Dim ir As Long
Dim MyRow As Integer
Dim i As Long
Dim MyObj
'start on row 11
MyRow = 11 'incriments every 16 option buttons
iob = 0
For ir = 11 To 50 'only trying to do fifty rows for now
For i = 1 To 16
iob = iob + 1

' linked cell
ActiveSheet.OLEObjects("OptionButton" & iob).LinkedCell = IIf(i =
1,
"P" & ir, _
IIf(i = 2, "R" & ir, IIf(i = 3, "T" & ir, IIf(i = 4, "V" & ir,
IIf(i
= 5, "X" & ir, _
IIf(i = 6, "Z" & ir, IIf(i = 7, "AB" & ir, IIf(i = 8, "AD" & ir,
IIf(i = 9, "AF" & ir, _
IIf(i = 10, "AH" & ir, IIf(i = 11, "AJ" & ir, IIf(i = 12, "AL" &
ir,
IIf(i = 13, "AN" & ir, _
IIf(i = 14, "AP" & ir, IIf(i = 15, "AR" & ir, "AT" &
ir)))))))))))))))

'caption
ActiveSheet.OLEObjects("OptionButton" & iob).Object.Caption = ""
'group
ActiveSheet.OLEObjects("OptionButton" & iob).Object.Value = IIf(i
=
2, "True", "false")
If i <= 7 Or (i 14 And i < 17) Then 'Group = "1"
ActiveSheet.OLEObjects("OptionButton" & iob).Object.GroupName
=
Trim(Str(ir)) & "-" & "1"
ElseIf i = 8 Or i = 9 Then 'Group = "2"
ActiveSheet.OLEObjects("OptionButton" & iob).Object.GroupName
=
Trim(Str(ir)) & "-" & "2"
Else 'Group = "3"
ActiveSheet.OLEObjects("OptionButton" & iob).Object.GroupName
=
Trim(Str(ir)) & "-" & "3"
End If
'name
'ActiveSheet.OLEObjects("OptionButton" & iob).Name = "ob" &
Trim(Str(iob))
Next
i = 1
Next
End Sub


"Tom Ogilvy" wrote:

the code I posted is correct as an example.

However, if you are running it in a loop, I wouldn't think you would
want to
link all optionbuttons to a single cell as that code would indiciate.

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"
end if
Next
would probably be more like

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P" & 10 + iob
end if
Next

I am not sure why you would get an error - possibly if the focus is not
on
the sheet.

Are you using xl97?

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Now I feel like I'm being a PITB, but the code ran one time, now I am
getting
the error "Unable to get the OLEobjects property of the worksheet
class"
Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the
OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am
unable
to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but
when I
try
to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell =
"P11"

I ran this 15 times with nary a problem:

Sub BBCC()
For iob = 1 To 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & ActiveSheet.Name & "'!P" & 10 + iob

Next

End Sub

See my other comment on a changed name.

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Using Excel 2000 9.0.6926. It seems funny that it ran one time and now I
get
the error. I've tried your latest code and my latest efficient code and I
always get the error.

"Tom Ogilvy" wrote:

the code I posted is correct as an example.

However, if you are running it in a loop, I wouldn't think you would
want to
link all optionbuttons to a single cell as that code would indiciate.

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"
end if
Next
would probably be more like

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P" & 10 + iob
end if
Next

I am not sure why you would get an error - possibly if the focus is not
on
the sheet.

Are you using xl97?

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Now I feel like I'm being a PITB, but the code ran one time, now I am
getting
the error "Unable to get the OLEobjects property of the worksheet
class"
Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the
OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am
unable
to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but
when I
try
to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell =
"P11"

Yes it was the name of the very first button. When I tested I did not have
the name change portion rem'd out and the name got changed. I am going to
persue the "For each OptionButton in ActiveSheet.OLEobjects" loop for
efficiency which will also eliminate that prob and allow me to run over the
entire sheet even if the buttons have been previously changed.
Thank you very much for all of your time and efforts!
-JEFF-

"Tom Ogilvy" wrote:

I ran this 15 times with nary a problem:

Sub BBCC()
For iob = 1 To 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & ActiveSheet.Name & "'!P" & 10 + iob

Next

End Sub

See my other comment on a changed name.

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Using Excel 2000 9.0.6926. It seems funny that it ran one time and now I
get
the error. I've tried your latest code and my latest efficient code and I
always get the error.

"Tom Ogilvy" wrote:

the code I posted is correct as an example.

However, if you are running it in a loop, I wouldn't think you would
want to
link all optionbuttons to a single cell as that code would indiciate.

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"
end if
Next
would probably be more like

for iob = 1 to 10
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P" & 10 + iob
end if
Next

I am not sure why you would get an error - possibly if the focus is not
on
the sheet.

Are you using xl97?

--
Regards,
Tom Ogilvy

"-JEFF-" wrote in message
...
Now I feel like I'm being a PITB, but the code ran one time, now I am
getting
the error "Unable to get the OLEobjects property of the worksheet
class"
Any
ideas?
-JEFF-

"Tom Ogilvy" wrote:

The linked cell property is provided by the container of the
OptionButton.
You access it like this;
ActiveSheet.OLEObjects("OptionButton" & iob) _
.LinkedCell = "'" & Activesheet.Name & "'!P11"


--
Regards,
Tom Ogilvy


"-JEFF-" wrote in message
...
Using the OptionButton from the controls toolbox toolbar, I am
unable
to
access the linkedcell property using the following code. I have
accessed
some of the properties, (caption, value) using this syntax but
when I
try
to
change LinkedCell, I get "object doesn't support this property or
method"
How do I change the linkedcell property?

ActiveSheet.OLEObjects("OptionButton" & iob).Object.LinkedCell =
"P11"