OptionButton .LinkedCell all end up in cell B14

tryagain

Board Regular
Joined
Mar 15, 2010
Messages
102
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

tdh777

Board Regular
Joined
Feb 28, 2012
Messages
142
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
Code:
ActiveSheet.GroupBoxes.Add(Left,Top,Width,Height)
AROUND each group of 3 cells, before adding the 3 option buttons :

Code:
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.
 

tdh777

Board Regular
Joined
Feb 28, 2012
Messages
142
You're welcome. :) You should click the "Thank you for posting this" smiley face in the bottom-left corner of the reply ! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top