vba 如何使 Combobox 项目列表动态化？

How to make the Combobox item list dynamic?

提问by Bhushan K

I am using a 'Generate' button on my worksheet. When I click on the button, a popup (form) come, which contains two comboboxes. Basis the selection in the first combobox, the second combobox option list is populated.

我在工作表上使用了“生成”按钮。当我单击按钮时，会出现一个弹出窗口（表单），其中包含两个组合框。根据第一个组合框的选择，填充第二个组合框选项列表。

For the first combobox, when I hardcode the item values it works fine. The form code is as follows:

对于第一个组合框，当我对项目值进行硬编码时，它工作正常。表单代码如下：

Private Sub UserForm_Initialize()

With ComboBox_DL
    .AddItem "DL1"
    .AddItem "DL2"
End With

End Sub

I tried to make this item list dynamic by fetching the combobox item values from a column the in the excel worksheet using the following form code:

我试图通过使用以下表单代码从 Excel 工作表中的列中获取组合框项目值来使此项目列表动态化：

Private Sub UserForm_Initialize()

With ComboBox_DL
For Each c In ActiveSheet.Range(Range("AE"), Range("AE").End(xlDown))
 .AddItem c.Value
Next
End With

End Sub

But the above code throws error: Run time error '1004': Method 'Range' of object '_Global' failed

但是上面的代码抛出错误：Run time error '1004': Method 'Range' of object '_Global' failed

I modified the code adding sheet details: 
With ComboBox_DL
    For Each c In ThisWorkbook.Worksheets("Business_Input_Data").Range(Range("AE"), Range("AE").End(xlDown))
        .AddItem c.Value
Next

It still throws the same error.

它仍然抛出相同的错误。

Can someone help please? Also, I want to know how to look up the values corresponding to the selection in combobox1 and populate the list in combobox2?

有人可以帮忙吗？另外，我想知道如何查找与组合框 1 中的选择相对应的值并填充组合框 2 中的列表？

回答by user1

Havent tested this as i have not created the userform to test under same conditions, but should work subject to minor alterations.

尚未对此进行测试，因为我尚未创建用户表单以在相同条件下进行测试，但应进行细微改动。

Dim n As Long
n = Sheets("Business_Input_Data").Cells(Rows.Count, "AE").End(xlUp).Row

With ComboBox_DL
    For Each c In ThisWorkbook.Worksheets("Business_Input_Data").Range("AE" & n)
        .AddItem c.Value
Next

回答by user3598756

you're missing the row index in "AE" furthermore use always explicit worksheet qualification in any Rangereference

您缺少“AE”中的行索引，此外在任何Range参考中始终使用明确的工作表限定

Private Sub UserForm_Initialize()
    Dim c As Range

    With ComboBox_DL
        For Each c In For Each c In ThisWorkbook.Worksheets("Business_Input_Data").Range(ThisWorkbook.Worksheets("Business_Input_Data").Range("AE1"), ThisWorkbook.Worksheets("Business_Input_Data").Range("AE1").End(xlDown))
         .AddItem c.Value
        Next
    End With
End Sub

but more elegant solutions are:

但更优雅的解决方案是：

Private Sub UserForm_Initialize()        
    With ThisWorkbook.Worksheets("Business_Input_Data")
        ComboBox_DL.RowSource = .Range("AE1", .Range("AE1").End(xlDown)).Address
    End With        
End Sub


Private Sub UserForm_Initialize()        
    With ThisWorkbook.Worksheets("Business_Input_Data")
        ComboBox_DL.List = .Range("AE1", .Range("AE1").End(xlDown)).Value
    End With        
End Sub

where:

在哪里：

• the former bindsyour ComboBoxlist to the range values whose address is given as ComboBoxRowSourceproperty

• the latter takes the values of the given range as Comboboxvalues

• 前者将您的ComboBox列表绑定到其地址作为ComboBoxRowSource属性给出的范围值

• 后者将给定范围的Combobox值作为值

回答by Werrf

If your combobox entries are a list on a worksheet, you don't need to use VBA to fill them at all. Instead, you can create a Dynamic Named Range, and use that as the Rowsource for the combobox.

如果组合框条目是工作表上的列表，则根本不需要使用 VBA 来填充它们。相反，您可以创建一个动态命名范围，并将其用作组合框的行源。

Say your list starts on Sheet3, cell A1. Go to Formulas | Name Manager to create a named range. Give it a useful name like "Combo", then put the following formula into RefersTo: =OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A),1)Save and close the Named Ranges dialogue.

假设您的列表从 Sheet3 的单元格 A1 开始。转到公式 | 名称管理器创建命名范围。给它一个有用的名称，如“组合”，然后将以下公式放入 RefersTo：=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A),1)保存并关闭命名范围对话框。

In the properties of your combobox, look for the line "RowSource". Set it to =Combo, or whatever name you used for your named range.

在组合框的属性中，查找“RowSource”行。将其设置为=Combo，或您用于命名范围的任何名称。

Any changes to the list, including lengthening or shortening it, will now be reflected immediately and automatically in the combo box.

对列表的任何更改，包括延长或缩短它，现在都将立即自动反映在组合框中。

EDITED TO ADD:

编辑添加：

To use the value selected in the first combobox to determine what list is used in a second combobox, we'll need to do two things.

要使用在第一个组合框中选择的值来确定第二个组合框中使用的列表，我们需要做两件事。

The first is to create named ranges for all the possible selections in the first list:

第一个是为第一个列表中的所有可能选择创建命名范围：

In the image, column A is the source for our first combo box; the other columns contain the possible sources for the second combo box.

在图像中，A 列是我们第一个组合框的来源；其他列包含第二个组合框的可能来源。

We then just need to put a little bit of code in the Change event for the first combobox:

然后我们只需要在第一个组合框的 Change 事件中添加一些代码：

Private Sub ComboBox1_Change()
    Me.ComboBox2.Value = ""
    Me.ComboBox2.RowSource = "=" & Me.ComboBox1.Value
End Sub

This code will trigger whenever ComboBox1 is changed. First it clears any existing value in ComboBox2, then it will set the row source property of ComboBox2 to a combination of the =symbol and whatever value was selected in the first box. Since those values are also named ranges, the second box will now use the selected named range as its list source.

每当 ComboBox1 更改时，就会触发此代码。首先它清除 ComboBox2 中的任何现有值，然后它将 ComboBox2 的行源属性设置为=符号和在第一个框中选择的任何值的组合。由于这些值也是命名范围，第二个框现在将使用选定的命名范围作为其列表源。

If you needed to, you could add more levels of cascading options, with different named ranges for each one. More than a couple of levels may become unmanageable, though - at which point we may want to look at another method.

如果需要，您可以添加更多级别的级联选项，每个选项都有不同的命名范围。但是，超过几个级别可能变得无法管理 - 在这一点上，我们可能想看看另一种方法。

回答by JDA

This is a solution to dynamically update the comboBox with emails in one column range.

这是一种使用一列范围内的电子邮件动态更新组合框的解决方案。

    Dim c As Range

ComboBox1.Value = ""
ComboBox1.Clear
For Each c In Sheets("emails").Range("F5:F5000")
    If c Like "*@*" Then
        ComboBox1.AddItem c
    End If
Next

The 'ComboBox.Value' Set the initial value. The 'ComboBox.Clear' Clears the previous rows in the comboBox.

'ComboBox.Value' 设置初始值。'ComboBox.Clear' 清除组合框中的前几行。