在 VBA Excel 中以编程方式向用户窗体添加选项按钮

Adding OptionButtons to the Userform programatically in VBA Excel

提问by Anup

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.

我对 VBA 编程很陌生。我的场景是我将获得一个字符串值列表我需要使用小窗口上的单选按钮向用户显示这些值，以便每当用户通过单击单选按钮选择任何值时，我应该能够获得该值在 VBA 代码中。我在互联网上的用户表单中搜索了添加选项按钮，我得到了一些使用 GUI 方法创建选项按钮的解决方案。但我需要通过程序来完成。我在 stackoverflow 中找到了一个有用的线程（如何使用 VBA 在表单上动态添加单选按钮）我使用了它，但仍然无法在用户表单上获取任何标签或按钮，将显示一个普通的用户表单。所以请任何人提供有关这方面的信息。

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.

UserForm1 是我使用 VBA 菜单栏中的 Insert 选项创建的用户窗体。我试图在用户表单上添加一个按钮。我没有在用户表单上使用初始化函数。Excel 表 Button1 上有一个按钮，我在单击该按钮时调用此函数。

Thank you

谢谢

回答by Ripster

If you have a form named UserForm1that contains a button named CommandButton1

如果您有一个名为的表单UserForm1，其中包含一个名为CommandButton1

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

您可以为您的 UserForm 设置 Initialize 方法以编程方式创建一组单选按钮

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:

在存储在模块中的“button_onclick()”过程中，添加以下代码：

'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

当您单击提交时，会弹出一个消息框，显示您选择了哪个选项

回答by kpark

Remember in using option buttons, your option buttons need to share the same GroupName.
Your control Nameis only there for you to refer it back for changing/reading.
Your Captionis a string that appear on your userform to the users.
Your GroupNameis a string that allows Excel to recognize the option buttons are linked together.

请记住，在使用选项按钮时，您的选项按钮需要共享相同的 GroupName。
您的控件名称仅供您参考以进行更改/阅读。
您的标题是一个字符串，显示在您的用户表单上给用户。
您的GroupName是一个字符串，它允许 Excel 识别链接在一起的选项按钮。

So, if opt1's GroupName is "1" while opt2's GroupName is "2", then you will be able to select both since they are in different Groups.

因此，如果 opt1 的 GroupName 为“1”而 opt2 的 GroupName 为“2”，那么您将能够选择两者，因为它们在不同的组中。

Private Sub UserForm_Initialize()
    Dim opt1 As Control, opt2 As Control

    Set opt1 = UserForm1.Controls.Add("Forms.OptionButton.1", , True)
    With opt1
        .Name = "radioFoo"
        .GroupName = "1"
        .Caption = "Option 1"
    End With

    Set opt2 = UserForm1.Controls.Add("Forms.OptionButton.1", , True)
    With opt2
        .Name = "radioFoo2"
        .GroupName = "1"
        .Caption = "Option 2"
        .Left = 100
    End With

End Sub

EDIT:
From seeing your edited post and your comment...
No, you don't need to have UserForm_Initialize() method.

It is an Excel-VBA functionality called Event.
What it's used for is specifying the userform to do something when userform is initialized (first started).
Similarly from your code, Button1_Click() is an event as well.
Since you are telling Excel to do the following at the event where Button1 is clicked by the user...

Anyways, let me briefly explain to you what option buttons do.
A groupof option buttons forces the user to select only one option out of options given by the program.
And an option button in VBA only allows you to create one option. So, if you want to create 2 options, you must create 2 option buttons.
But there is just one problem: what if you want to create 2 groups of option buttons so that the user can select 2 separate options? For example, food and drinks?
VBA presents us a property of an option button called GroupName. GroupName allows VBA to distinguish between separate groups of option buttons.
Therefore, in every option button you create, it is essential that you initialize its GroupName value. If you see any implementation of option button without GroupName, you are playing with fire.

编辑：
从看到你编辑过的帖子和你的评论......
不，你不需要 UserForm_Initialize() 方法。

这是一个名为Event的 Excel-VBA 功能。
它的用途是指定用户表单在初始化（首次启动）时执行某些操作。
与您的代码类似，Button1_Click() 也是一个事件。
由于您告诉 Excel 在用户单击 Button1 的事件中执行以下操作...

无论如何，让我向您简要解释一下选项按钮的作用。
一组选项按钮强制用户从程序给出的选项中只选择一个选项。
VBA 中的选项按钮只允许您创建一个选项。因此，如果要创建 2 个选项，则必须创建 2 个选项按钮。
但只有一个问题：如果您想创建 2 组选项按钮以便用户可以选择 2 个单独的选项，该怎么办？例如，食物和饮料？
VBA 为我们提供了一个名为GroupName的选项按钮的属性。GroupName 允许 VBA 区分不同的选项按钮组。
因此，在您创建的每个选项按钮中，必须初始化其 GroupName 值。如果您看到没有 GroupName 的选项按钮的任何实现，那么您就是在玩火。

So, let's finally take a look at your code:

所以，让我们最后看看你的代码：

Sub Button1_Click()
    ' Some operatin is done here to get the list of values in lResult

    Dim rad1 As Control, rad2 As Control
    Set rad1 = UserForm1.Controls.Add("Forms.OptionButton.1", "radioFoo1", True)
    rad1.Caption = "bar"
    rad1.Left = 10
    rad1.Width = 10
    rad1.Top = 10
    rad1.GroupName = "Group1"

    Set rad2 = UserForm1.Controls.Add("Forms.OptionButton.1", "radioFoo2", True)
    rad2.Caption = "foo"
    rad2.Left = 10
    rad2.Width = 10
    rad2.Top = 50
    rad2.GroupName = "Group1"
End Sub

Just one thing:
- As I've implicitly mentioned before, an option button with only one option does not mean anything. If you are looking for on/off kind of functionality, you might as well go with checkbox.
So, I've created another option button defining it to be in the same group as the first option button you've created (rad1).

只有一件事：
- 正如我之前隐含地提到的，只有一个选项的选项按钮没有任何意义。如果您正在寻找开/关类型的功能，您不妨使用复选框。
因此，我创建了另一个选项按钮，将其定义为与您创建的第一个选项按钮 (rad1) 位于同一组中。

Hope it helps.

希望能帮助到你。

Cheers,
kpark

干杯，
kpark

Be sure to select the best answer when the question/problem has been answered/solved. Thanks.

确保在问题/问题得到回答/解决后选择最佳答案。谢谢。