Assign Radio Buttons in Excel User Form to a Group at Runtime

[gedkins]

In the following vba code from Excel 365 I am creating a number of radio buttons. I cannot seem to assign them to a group for later use in code.
I have tried using .Group and .OptionButtonGroup but neither come up in intellisense. Its like the Microsoft Forms 2.0 Object Library is not referenced, but it is. What am I missing?

TIA.

Private Sub AddProductLine()

Dim optBtn As control
Dim c As control
Dim theTop, theWidth As Double
Dim i As Integer
Dim theProductList As Variant


theProductList = GetProductNames '-----GETTING PRODUCT LINES FROM EXCEL SHEET
theTop = 10 '-----SETTING TOP OF FIRST BUTTON IN FRAME
theWidth = 50 '-----SETTING WIDTH OF ALL THE RADIO BUTTONS

'-------------------ADDING THE RADIO BUTTONS TO THE FRAME PRODUCT LIST
For i = 1 To UBound(theProductList)
Set optBtn = ProductFrame.Controls.Add("Forms.OptionButton.1", theProductList(i, 1), True)
With optBtn
.Caption = theProductList(i, 1)
.Width = theWidth
.Top = theTop
.Left = 12

End With
theTop = theTop + 17.5
Next i
ProductFrame.Height = theTop + 15
Me.Controls.Item("GAP").Value = True

End Sub

 

[Micron]

You want to add the option buttons to a frame? Looks to me like that code would loop through and alter the object variable (that which you SET) on each pass, thereby replacing it with something new each time - if it runs without raising an error, which isn't clear. You'd want to use a With block that applies to the frame object and then a With block for the button properties, as in

Dim optButn As control
 With UserForm3.Frame1
     Set optButn = .Controls.Add("Forms.OptionButton.1")
End With

With optButn
     .Top = 10
     .Left = 10
     .Caption = "Test"
End With

Please use of code tags ("VBA" button on posting toolbar) to maintain indentation and readability of code.

 

[gedkins]

My code runs without error. I still don't understand why I cannot set the buttons to belong to a group.

 

[Micron]

Me neither because I have done little grouping of controls in Excel. What I showed was for adding option buttons to a frame. Control groups would be for controls on a sheet, control collections would be for use in a frame on a form (unless you can put a frame on a sheet too, but I don't know). You explicitly noted this is for a frame:

ADDING THE RADIO BUTTONS TO THE FRAME PRODUCT LIST

so that's what I wrote as a sample. Unfortunately I still can't figure out if a frame is even involved

You want to add the option buttons to a frame?

What I wrote adds a button to a frame on a userform.

 

[Domenic]

First, delcare optBtn as an OptionButton from the MSForms library...

Dim optBtn As msforms.OptionButton

Then you'll be able to use the IntelliSense to choose the GroupName property of the OptionButton object...

So you can assign a group name as follows...

optBtn.GroupName = "MyGroupName"

Hope this helps!

 

[gedkins]

That was it. Thanks very much!

 

[Domenic]

You're very welcome, cheers!