如何使 Combobox 项目列表动态化? [英] How to make the Combobox item list dynamic?

问题描述

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

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

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

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:

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

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

But the above code throws error: 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.

有人可以帮忙吗?另外，我想知道如何在combobox1中查找对应于选择的值并在combobox2中填充列表?

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?

推荐答案

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

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.

假设您的列表从 Sheet3 的单元格 A1 开始.转到公式 |名称管理器创建命名范围.给它一个有用的名字，比如Combo"，然后把下面的公式放到 RefersTo 中: =OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A),1) 保存并关闭命名范围对话框.

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.

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

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

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

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

编辑添加:

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

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:

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

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.

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

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

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

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

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.

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

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.

这篇关于如何使 Combobox 项目列表动态化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！