excel - 循环更新多个列表框

Question

我有一个小的 VBA 代码，其中包含 3 个列表框，列表基于定义的范围。是否可以对多个列表框进行循环迭代Sheet4.ListBox & i & .AddItem "Select All"

这是我的以下代码。

    Sub Test()

        Dim i As Integer
        Dim MyRange As Range
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("RESULT")
        last_row = Cells(Rows.Count, "B").End(xlUp).Row
        For i = 1 to 3 
        Set MyRange = ws.Range(Cells(2,i),Cells(last_row,i))
        Call update_listbox(i, MyRange)

End Sub

Sub(update_listbox)
'here i am updating my listing in ListBox

        Sheet4.ListBox & i &.Clear      ''Getting Error here    
        Sheet4.ListBox & i & .AddItem "Select All"  ''Getting Error here        
        Dim myList As Collection
        Dim myVal As Variant
        Set myList = New Collection
        On Error Resume Next
        For Each myCell In myRange.Cells
        myList.Add myCell.Value, CStr(myCell.Value)
        Next myCell
        On Error GoTo 0
        For Each myVal In myList
        Sheet4.ListBox & i &.AddItem myVal      'Getting Error here 
        Next myVal
End Sub

请帮忙。如果不可能，需要其他解决方法

标签： excelvbalistbox

Answer 1

ListBox & i创建字符串“ListBox1”或“ListBox2”等。您无法使用它直接识别列表框对象，但您可以使用字符串从合格集合中识别列表框。

以下是从工作表的私有代码表（右键单击工作表名称选项卡，查看代码）遍历工作表的 ListBox 对象的两种方法。

Option Explicit

Sub refLBs()
    Dim c As Object, i As Long

    For Each c In Me.OLEObjects
        Debug.Print c.Name
    Next c

    For i = 1 To Me.OLEObjects.Count
        Debug.Print Me.OLEObjects("listbox" & i).Name
    Next i
End Sub