如何使组合框项目列表动态化？

Question

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

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

Private Sub UserForm_Initialize()

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

End Sub

我尝试使用以下表单代码从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

但是上面的代码抛出错误：运行时错误“1004”：对象“_Global”的方法“范围”失败

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

它仍然抛出相同的错误。

有人能帮忙吗？另外，我想知道如何在combobox1中查找与选择相对应的值，并在combobox2中填充列表？

Answer 1

“AE”中缺少行索引，而且在任何< code >范围引用中总是使用显式工作表限定

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

但更优雅的解决方案是：

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

其中:

> 前者将组合框

列表绑定到其地址作为组合框行源属性给出的范围值

后者将给定范围的值作为< code>Combobox值

Answer 2

我还没有测试过这个，因为我没有创建用户表单来在相同的条件下进行测试，但应该可以稍作修改。

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

Answer 3

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

假设您的列表从工作表 3 单元格 A1 开始。转到公式|名称管理器，用于创建命名区域。给它一个有用的名称，如“组合”，然后将以下公式放入 参考到： = OFFSET（Sheet3！$A$1，0，0，COUNTA（Sheet3！$A：$A），1）保存并关闭“命名范围”对话框。

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

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

编辑后添加：

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

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

然后，我们只需要在第一个组合框的Change事件中放一点代码：

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

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

如果需要，您可以添加更多级别的级联选项，每一级都有不同的命名范围。但是，超过两个级别可能会变得难以管理——此时，我们可能需要考虑另一种方法。