Code for creating option buttons

[OssieMac]

Will really appreciate some help with this code for creating option buttons.

Option Explicit
Dim Sht As Worksheet
Dim buttonType
Dim leftPos As Integer
Dim topPos As Integer
Dim i As Integer

Sub Radio_Button_Create()
Set Sht = ThisWorkbook.Worksheets(1)
leftPos = 10
topPos = 10
For i = 1 To 10
buttonType = "Forms.OptionButton." & Format(i, "0")
topPos = topPos + 20
Sht.Shapes.AddOLEObject _
Left:=leftPos, _
Top:=topPos, _
Width:=8, _
Height:=8, _
ClassType:=buttonType
Next i
End Sub

It creates the first option button perfectly well but then fails on the
Sht.Shapes.AddOLEObject etc with Run time error 1004. Cannot insert object.

Regards,

OssieMac

 

[NickHK]

I assume you trying to create 10 option buttons and give each a known name:

Private Sub CommandButton2_Click()
Dim Sht As Worksheet
Dim i As Long
Dim OptButton As OLEObject

Const VERT_SPACE As Long = 20

Set Sht = ThisWorkbook.Worksheets(1)

For i = 1 To 10
Set OptButton = Sht.OLEObjects.Add( _
Left:=10, _
Top:=i * VERT_SPACE, _
Width:=8, _
Height:=8, _
ClassType:="Forms.OptionButton.1")

With OptButton
.Name = "MyOptionButton" & i
End With
Next i

End Sub

NickHK

 

[OssieMac]

A million thanks to you Nick. It works fine. I was certainly going along the
wrong path.

Regards,

OssieMac