excel - 循环通过工作表上的 activeX 组合框并将命名范围分配给列表填充

Question

我有 A 列和 B 列。 B 列中的每个单元格都包含一个 activeX ComboBox。我想要的是ComboBox在 B 列中根据 A 列中的输入（直接在组合框旁边）参考来自不同工作表的命名范围进行填充

为此，我需要ComboBox使用嵌套的 IF 语句在工作表上的每个循环中运行，但无法弄清楚如何循环组合框对象，然后在其他条件为真时为组合框分配一个命名范围。

为简单起见，单元格 A1 将位于 旁边ComboBox1。单元格 A2 将在ComboBox2等旁边。

基本示例：

myRange1 = ActiveWorkbook.Worksheets("Ranges").Range("NamedRange1")
myRange2 = ActiveWorkbook.Worksheets("Ranges").Range("NamedRange2")
i = 1

For Each ComboBox on ActiveWorksheet

If Cell("A" & i) 'condition1 here' Then
ComboBoxi.ListFillRange = myRange1
i = i+1
Elseif Cell("A" & i) 'condition2 here' Then
ComboBox'i'.ListFillRange = myRange2
i = i+1
Else 'do nothing

Next

标签： excelvbaloopsobjectcombobox

Answer 1

我想我找到了一种更清洁的方法来做到这一点。我没有意识到您不必为命名范围指定工作表，即使这些范围位于单独的工作表上。在此示例中，"Range1"并且"Range2"位于它们自己的工作表上。此外，它无需声明ComboBoxes为对象即可工作。ComboBoxes保留为 、 和 的ComboBox1默认ComboBox2名称ComboBox3。通过从命名范围填充组合框来到这个解决方案

我相信会有一个很好的方法来循环这个而不是if为每个唯一的单元格Range A1:A3和关联的ComboBox.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim result As String
Dim search1P As String
Dim search2P As String

search1P = "1P"
search2P = "2P"

If Not Intersect(Target, Range("A1:A3")) Is Nothing Then 'if target is within range A1:A3

result = Target.Value ' set result variable equal to whatever the target cell value is

If Not Intersect(Target, Range("A1")) Is Nothing Then 'if cell A1, evaluate cb1

    If InStr(UCase(result), search1P) <> 0 Then 'if result variable contains "1P" then
    ComboBox1.ListFillRange = "Range1" 'Populate cb1 with "Range1"
    ElseIf InStr(UCase(result), search2P) <> 0 Then 'if result variable contains "2P" then
    ComboBox1.ListFillRange = "Range2" 'populate combobox with "Range2"
    Else: ComboBox1.ListFillRange = "" 'leave cb blank if source cell is invalid
    End If

ElseIf Not Intersect(Target, Range("A2")) Is Nothing Then 'if cell A2, update cb2

    If InStr(UCase(result), search1P) <> 0 Then 'if result variable contains "1P" then
    ComboBox2.ListFillRange = "Range1" 'Populate cb1 with "Range1"
    ElseIf InStr(UCase(result), search2P) <> 0 Then 'if result variable contains "2P" then
    ComboBox2.ListFillRange = "Range2" 'populate combobox with "Range2"
    Else: ComboBox2.ListFillRange = "" 'leave cb blank if source cell is invalid
    End If

ElseIf Not Intersect(Target, Range("A3")) Is Nothing Then 'if cell A2, update cb3

    If InStr(UCase(result), search1P) <> 0 Then 'if result variable contains "1P" then
    ComboBox3.ListFillRange = "Range1" 'Populate cb1 with "Range1"
    ElseIf InStr(UCase(result), search2P) <> 0 Then 'if result variable contains "2P" then
    ComboBox3.ListFillRange = "Range2" 'populate combobox with "Range2"
    Else: ComboBox3.ListFillRange = "" 'leave cb blank if source cell is invalid
    End If

End If

End If

End Sub