OptionButton .LinkedCell all end up in cell B14

[tryagain]

Hey Expert's

When i run following code then all optionsbut's link to cell B14 what the heck is going on
thanks for any 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

 

[tdh777]

Hi Tryagain,

By definition OptionButtons (also known as Radio buttons) can only have *1* out of the Group selected at any one time.

So what you are effectively doing is adding 36 radio buttons, only one of which can be selected at any time.

Therefore, when you update the Linked Cell for you button to be Cell "E" in the current Row you are effectively telling Excel that cell will be the new Linked cell to store the selected Radio Button value for ALL your radio buttons.

You will need to place each group of 3 buttons in a group box control to get the desired result.

ie

ActiveSheet.GroupBoxes.Add(Left,Top,Width,Height)

AROUND each group of 3 cells, before adding the 3 option buttons :

For Each c In Range("B3:B14")
    c.Select
    ActiveSheet.GroupBoxes.Add(Selection.Left, Selection.Top, Selection.Width * 3, Selection.Height).Caption = ""
    c.Select
...

The caption = "" is to avoid a whole bunch of "Group Control nn" cluttering your screen.

Cheers

Warren K.

 

[tryagain]

Warren K. ***** THANK YOU SO MUCH *****

 

[tdh777]

You're welcome. You should click the "Thank you for posting this" smiley face in the bottom-left corner of the reply !