Originally Posted by
LarryG
this whole discussion is about UserForm controls, rather than controls directly on a worksheet, right?
Worksheet I'm afraid.
snb's solution adapted to fit more closely to the OP's requirements (these are ActiveX option buttons):
Sub blah2()
margin = 1 'small adjustment to size and placement of optionbuttons to keep sheet grid visible.
With ActiveSheet
Set rngChkBoxes = .Range("I5,I7,I9,I18") 'only the first cell of each pair
'Delete ALL existing activex optionbuttons on the sheet:
For Each obj In .OLEObjects
If TypeName(obj.Object) = "OptionButton" Then obj.Delete
Next obj
'Add new ones:
For Each cll In rngChkBoxes.Cells
Set cll2 = cll.Offset(, 1) 'the second optionbutton location
With .OLEObjects.Add("Forms.OptionButton.1", , , , , , , cll.Left + margin, cll.Top + margin, cll.Width - 2 * margin, cll.Height - 2 * margin).Object
.Caption = "Yes"
.GroupName = "row" & Format(cll.Row, "000")
.Font.Size = 9
End With
With .OLEObjects.Add("Forms.OptionButton.1", , , , , , , cll2.Left + margin, cll2.Top + margin, cll2.Width - 2 * margin, cll2.Height - 2 * margin).Object
.Caption = "No"
.GroupName = "row" & Format(cll.Row, "000")
.Font.Size = 9
End With
Next cll
End With
End Sub