PDA

View Full Version : OptionButton LinkedCell trouble



redhunter
03-10-2018, 11:36 AM
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
03-10-2018, 01:11 PM
:dunno:
Try
.LinkedCell = "Ark1!E" & C.Row

redhunter
03-10-2018, 02:03 PM
nope that didn't help ;-)

SamT
03-10-2018, 02:34 PM
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
03-10-2018, 03:10 PM
nope that didn't help

SamT
03-10-2018, 03:42 PM
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
03-10-2018, 04:08 PM
can you pls. paste complete code, i got errors when run

SamT
03-10-2018, 04:22 PM
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
03-11-2018, 06:03 AM
ok SamT ill try that. thanks anyway ;.)