Option Button VBA

[russels]

Hi there,
I'm trying to create a macro so that when i highlight a selection i can quickly call the macro to fill the selection with option buttons.
Specifically, I need to be able to separate the group of option buttons as per row so that i'm limited only to the one option button clicked per row and have the button "cell linked" to the first column of the selection of that specific row.
Right now, the code below automatically populates my selection with option buttons, however, i can only have 1 option button clicked at a time. Additionally, i'd like to center and align the option buttons within the selection of buttons.
Thanks for any help

Sub AddOptionButton()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.OptionButtons.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Next
myRange.Select
End Sub

 

[AlphaFrog]

Place OptionButtons within a GroupBox to group them. The code below adds a GroupBox for each row in the selection and then hides it. Then places the OptionButtons in the group box (row).

Not sure what you meant by; "center and align the option buttons within the selection of buttons"

[color=darkblue]Sub[/color] AddOptionButton()
    
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] Range, r [color=darkblue]As[/color] Range
        
    [color=darkblue]For[/color] [color=darkblue]Each[/color] r [color=darkblue]In[/color] Selection.Rows
        [color=darkblue]With[/color] ActiveSheet.GroupBoxes.Add(r.Cells.Left, r.Cells.Top, r.Cells.Width, r.Cells.Height)
            .Visible = [color=darkblue]False[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] c [color=darkblue]In[/color] r.Cells
            [color=darkblue]With[/color] ActiveSheet.OptionButtons.Add(c.Left, c.Top, c.Width, c.Height)
                .Caption = ""
                .Name = "OB_" & c.Address(0, 0)
                .LinkedCell = r.Cells(1).Address(0, 0)
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]Next[/color] c
    [color=darkblue]Next[/color] r
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[russels]

Hi,
That works well. Thanks! What i meant to say was that i would like to center and align the button within the cell. Not a huge deal that it isn't.
Thanks

 

[AlphaFrog]

It could be centered in the cell, but the area of the optionbutton will be narrow and not cover the width of the cell. The button cannot be centered in its own area.

This would center (and narrow the area) each optionbutton in the cell.
With ActiveSheet.OptionButtons.Add(c.Left + (c.Width / 2) - 5, c.Top, 10, c.Height)