Adding OptionButtons to the Userform programatically in VBA Excel

Question

I am very new to VBA programming. My scenario is I will get a list of String values I need these values to be displayed to the user using radio buttons on a small window so that whenever the user selects any value by clicking on the radio button I should be able to get that value in the VBA code. I searched for adding options button in the user form in the internet I got some solution which use GUI method of creating option buttons. But I need it done through program. I found a helpful thread in stackoverflow (How can I dynamically add a radio button on a form using VBA ) I used this but still I am unable to get any label or button on the user form, a plain userform will be displayed. So anybody please give information regarding this.

The code is :

Sub Button1_Click()
    lResult As Variant    ' this is a array which contains string vaues to be dispayed as radio button.

    ' Some operatin is done here to get the list of values in lResult

    Dim rad As Variant
    Set rad = UserForm1.Controls.Add("Forms.OptionButton.1", "radioFoo", True)
    rad.Caption = "bar"
    rad.Left = 10
    rad.Width = 10
    rad.Top = 10
End Sub

UserForm1 is the userform which I created using Insert option in VBA menu bar. I tried to add a single button on the userform. I did not use initialize function on userform. There is button on excel sheet Button1 I am calling this function on clicking that button.

Thank you

Answer 1

If you have a form named UserForm1 that contains a button named CommandButton1

You can set the Initialize method for your UserForm to programmatically create a group of radio buttons

Private Sub UserForm_Initialize()
    Dim OptionList(1 To 3) As String
    Dim btn As CommandButton
    Set btn = UserForm1.CommandButton1
    Dim opt As Control
    Dim s As Variant
    Dim i As Integer

    OptionList(1) = "Option 1"
    OptionList(2) = "Option 2"
    OptionList(3) = "Option 3"

    For Each s In OptionList
        Set opt = UserForm1.Controls.Add("Forms.OptionButton.1", "radioBtn" & i, True)
        opt.Caption = s
        opt.Top = opt.Height * i
        opt.GroupName = "Options"

        UserForm1.Width = opt.Width
        UserForm1.Height = opt.Height * (i + 2)

        i = i + 1
    Next

    btn.Caption = "Submit"
    btn.Top = UserForm1.Height - btn.Height + (0.5 * opt.Height)
    btn.Left = (UserForm1.Width * 0.5) - (btn.Width * 0.5)

    UserForm1.Height = UserForm1.Height + btn.Height + (0.5 * opt.Height)
End Sub

Private Sub CommandButton1_Click()
    Dim i As Integer

    For i = 0 To UserForm1.Controls.Count - 1
        If UserForm1.Controls(i) Then
            SelectedOption = UserForm1.Controls(i).Caption
        End If
    Next

    UserForm1.Hide
End Sub

If you want to pull your list from a sheet you can change

Dim OptionList(1 To 3) As String

OptionList(1) = "Option 1"
OptionList(2) = "Option 2"
OptionList(3) = "Option 3"

to pull from a range like this

Dim OptionList() as Variant
OptionList = Range("A1:A3")

In your "button_onclick()" procedure stored in a module add this code:

'This is set by the code in UserForm1
Public SelectedOption As String

Sub Button1_OnClick()
    UserForm1.Show
    MsgBox SelectedOption
End Sub

Which gets you this result:

And when you click submit a message box will pop up showing you which option was selected