如何加载组合框，然后如何在 vba 中使用从中选择的值？

how to load the combobox and then how to use the selected value from it in vba?

提问by nazanin

a question is : how to load a or give value to a combo box list and then call it in my worksheet and get the selected value from it ?i have a module that i want to call my userform1 which include the combobox in it . but when i debug the program it is just a show of the combo box . i think it doesn't do anything ... thanks for your time ..this is the code for user form:

一个问题是：如何加载组合框列表或为组合框列表赋值，然后在我的工作表中调用它并从中获取选定的值？我有一个模块，我想调用我的 userform1，其中包含组合框。但是当我调试程序时，它只是组合框的显示。我认为它没有任何作用......感谢您的时间......这是用户表单的代码：

Private Sub UserForm_Initialize()

With ComboBox1

    .AddItem "weibull"
    .AddItem "log-normal"
    .AddItem "gambel"
    .Style = fmStyleDropDownList

End With
End Sub

and this is how i ask in my sub to show the combobox:

这就是我在 sub 中要求显示组合框的方式：

UserForm1.Show
If ComboBox1.ListIndex = -1 Then
MsgBox "There is no item currently selected.", _
vbInformation, _
"Combo Box Demo"
Exit Sub
End If
 MsgBox "You have selected " & ComboBox1.List(ComboBox1.ListIndex) & "." & vbNewLine _
& "It has " & ComboBox1.ItemData(ComboBox1.ListIndex) ", _
vbInformation, _
"Combo Box Demo"

the second part is what i found in net , but it made the program at least to show the combo box !

第二部分是我在网上找到的，但它使程序至少显示了组合框！

回答by Tony Dallimore

I thought Siddharth's answer was pretty clear particularly as it was posted from a mobile! However, I had an email from the OP saying he did not understand the answer. I provided the following background which was apparently sufficient to allow him to understand Siddharth's answer and solve his problem. I post it here for the benefit of any other visitor who needs more background on forms than Siddharth provides.

我认为 Siddharth 的答案非常清楚，特别是因为它是从手机发布的！但是，我收到了一封来自 OP 的电子邮件，说他不明白答案。我提供了以下背景，显然足以让他理解悉达多的回答并解决他的问题。我把它贴在这里是为了任何其他需要比 Siddharth 提供的表格更多背景的访问者的利益。

If you select VBA Help and type “userform show” you will get a description of the Show command.

如果您选择 VBA 帮助并键入“userform show”，您将获得 Show 命令的描述。

If your user form is named “UserForm1”, you can have statements:

如果您的用户表单名为“UserForm1”，您可以有语句：

1. UserForm1.Show
2. UserForm1.Show vbModal
3. UserForm1.Show vbModeless

1. UserForm1.Show
2. UserForm1.Show vbModal
3. UserForm1.Show vbModeless

Statements 1 and 2 are equivalent.

语句 1 和 2 是等效的。

The choice of VbModal or vbModeless completely changes the way the user form is controlled.

VbModal 或 vbModeless 的选择完全改变了用户表单的控制方式。

If a form is shown modeless, the user can see it but cannot access it. If I have a macro that takes a long time, I will use a modeless form to show progress. If I am working down the rows of a large worksheet I might have a form containing:

如果表单显示为无模式，则用户可以看到它但无法访问它。如果我有一个需要很长时间的宏，我会使用无模式的表单来显示进度。如果我正在处理大型工作表的行，我可能有一个包含以下内容的表单：

I am working on rownnnnofmmmm

I am working on rownnnnofmmmm

Each of the boxesis a label. I set the value of the label containing “mmmm” to the number of rows when I start the macro. I set the value of the label containing “nnnn” to the row number at the start of each repeat of my loop. The user sees:

每一个boxes都是一个标签。当我启动宏时，我将包含“mmmm”的标签的值设置为行数。我将包含“nnnn”的标签值设置为循环每次重复开始时的行号。用户看到：

     I am working on row    1 of 5123
then I am working on row    2 of 5123
then I am working on row    3 of 5123
and so on.

If it takes the macro five minutes to process every row, this tells the user that something is happening. Without the form, the user might think the macro had failed. With the form, the user knows the macro is busy and they have time to get a fresh cup of coffee.

如果宏需要五分钟来处理每一行，这会告诉用户发生了一些事情。如果没有表单，用户可能会认为宏失败了。通过表单，用户知道宏很忙，他们有时间喝一杯新鲜的咖啡。

On the other hand, if the form is shown modal, the macro stops until the user does something that closes or unloads the form with a statement such as:

另一方面，如果表单显示为modal，宏将停止，直到用户使用以下语句关闭或卸载表单：

Unload Me

The positioning of this statement depends on your form. I normally have a Submit button that saves information before ending with this statement.

此语句的位置取决于您的表单。我通常有一个提交按钮，用于在以该语句结束之前保存信息。

Once the Unload Me statement is executed, the macro restarts at the statement after the Show statement. When the macro restarts, the form has gone. This is why the form must save anything the macro needs in global variables.

执行 Unload Me 语句后，宏将在 Show 语句之后的语句处重新启动。当宏重新启动时，表单就消失了。这就是为什么表单必须在全局变量中保存宏需要的任何东西。

回答by Siddharth Rout

You are trying to access a control when the userform is already closed. And I say closed becuase you are not using vbmodelessto show the form. So the only way the next line after that can run is when the form is closed. Here is what I recommend.

当用户窗体已关闭时，您正尝试访问控件。我说关闭是因为你没有vbmodeless用来显示表单。因此，在此之后的下一行可以运行的唯一方法是在表单关闭时。这是我推荐的。

Declare public variables in a module which will hold the relevant values when the useform closes and then use that later. For example

在模块中声明公共变量，该变量将在 useform 关闭时保存相关值，然后稍后使用。例如

Paste this code in the userform

将此代码粘贴到用户表单中

Option Explicit

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "weibull"
        .AddItem "log-normal"
        .AddItem "gambel"
        .Style = fmStyleDropDownList
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If ComboBox1.ListIndex <> -1 Then
        SelectItem = ComboBox1.List(ComboBox1.ListIndex)
        pos = ComboBox1.ListIndex + 1
    End If
End Sub

And paste this in a module

并将其粘贴到模块中

Option Explicit

Public SelectItem As String, pos As Long

Sub Sample()
    '
    '~~> Rest of your code
    '

    SelectItem = "": pos = 0
    UserForm1.Show

    If pos = 0 Then
        MsgBox "There is no item currently selected.", _
        vbInformation, "Combo Box Demo"
        Exit Sub
    End If

    MsgBox "You have selected " & SelectItem & "." & vbNewLine & _
    "It is at position " & pos, vbInformation, "Combo Box Demo"

    '
    '~~> Rest of your code
    '
End Sub

Also

还

There is no .Itemdataproperty of the Combobox. It is available in VB6 but not in VBA. With .Itemdataproperty of the Combobox, I guess you were trying to get the position?

.ItemdataCombobox没有属性。它在 VB6 中可用，但在 VBA 中不可用。有了.ItemdataCombobox 的属性，我猜你是想获得这个位置？