OptionButton LinkedCell trouble

[redhunter]

Trying to link OptionBut's to range B3:B14 3 but's on each
But when i run this code, all Link's goes to B14 !!!
What the heck is going on :-) pls. help

Sub AddOptionButton()
ActiveSheet.DrawingObjects.Delete
For Each c In Range("B3:B14")
    c.Select
        With ActiveSheet.OptionButtons.Add(Selection.Left, Selection.Top, Selection.Width, Selection.Height)
            .LinkedCell = "Ark1!" & Cells(c.Row, "E").Address
            .Name = "knap" & c.Row * 3 - 8
            .Caption = "100%"
        End With

    c.Offset(0, 1).Select
         With ActiveSheet.OptionButtons.Add(Selection.Left, Selection.Top, Selection.Width, Selection.Height)
           .Name = "knap" & c.Row * 3 - 7
           .Caption = "10%"
      End With

   c.Offset(0, 2).Select
         With ActiveSheet.OptionButtons.Add(Selection.Left, Selection.Top, Selection.Width, Selection.Height)
             .Name = "knap" & c.Row * 3 - 6
             .Caption = "0%"
      End With
Next
End Sub

[SamT]

:
Try

 .LinkedCell = "Ark1!E" & C.Row

[redhunter]

nope that didn't help ;-)

[SamT]

May be

Str ="Ark1!E" & C.Row
.LinkedCell = Str

Excel 2002 Help File:
Example

This example adds a check box to worksheet one and links the check box value to cell A1.

With Worksheets(1)
    Set cb = .Shapes.AddFormControl(xlCheckBox, 10, 10, 100, 10)
    cb.ControlFormat.LinkedCell = "A1"
End With

Note that nowhere in my help files does it indicate that any sort of optionbutton has a "Linked Cell," But viewing the Properties of one on an Excel Sheet does show the LinkedCell Property

[redhunter]

nope that didn't help

[SamT]

Try using OLE Objects

    ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=270, Top:=84, Width:=108, Height:=19.5).

[redhunter]

can you pls. paste complete code, i got errors when run

[SamT]

I just recorded a Macro to see how to insert OLE objects with VBA. That is the complete code I recorded

Using Controls on worksheets is hard! It's all really old Excel 4 legacy code.

If It were me, I would just make a table with the Percentages in it, then Use the Sheet Before Doubleclick Event Sub select what I needed based on the Target Address. Simple, straight forward logic.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("B3:D14") is nothing then Exit sub
Cancel = True
Intersect(Target.Row, Range("B3:D14")).Font.Bold = False
Target.Font.Bold = True

Sheets("Arc1").Cells(Target.Row, "E") = Target

End Sub

[redhunter]

ok SamT ill try that. thanks anyway ;.)