Excel VBA-遍历ListBox控件 [英] Excel VBA - Cycle through ListBox controls

问题描述

我试图循环浏览多个工作表，然后在每个工作表中使用列表框控件，并用我的代码填充它们.我正在使用以下代码:

  Dim sh As工作表昏暗的obj作为OLEObject昏暗的作为MSForms.ListBox昏暗的IDX只要长对于idx = 1到ThisWorkbook.Worksheets.Count设置sh = ThisWorkbook.Worksheets(idx)对于sh.OLEObjects中的每个obj如果obj.progID ="Forms.ListBox.1"，则设置lst = obj如果(lst.Name ="lst1")然后调用PopulateSimple(lst，"Table1")万一万一下一个下一个IDX 

不幸的是，当我将列表框设置为对象时，这似乎失败了.关于如何实现遍历不同工作表中的所有列表框然后填充它们的任何想法吗?

解决方案

尝试一下

  Sub Sample()昏暗工作表昏暗的obj作为OLEObject昏暗的IDX只要长对于idx = 1到ThisWorkbook.Worksheets.Count设置sh = ThisWorkbook.Worksheets(idx)对于sh.OLEObjects中的每个obj如果TypeOf obj.Object是MSForms.ListBox，则如果(obj.Name ="lst1")然后调用PopulateSimple(obj，"Table1")万一万一下一个下一个IDX结束子 

说明:之所以会出现此错误，是因为obj被声明为 OLEObject ，而lst被声明为 MSForms.ListBox ，因此类型不匹配./p>

I am trying to cycle through a number of worksheets, then listbox controls within each worksheet and populate them with my code. I am using the code below:

Dim sh As Worksheet
Dim obj As OLEObject
Dim lst As MSForms.ListBox
Dim idx As Long

For idx = 1 To ThisWorkbook.Worksheets.Count
    Set sh = ThisWorkbook.Worksheets(idx)
    For Each obj In sh.OLEObjects
        If obj.progID = "Forms.ListBox.1" Then
            Set lst = obj
            If (lst.Name = "lst1") Then
                Call PopulateSimple(lst, "Table1")
            End If
        End If
    Next
Next idx

This seems to fail unfortunately when I set the listbox to the object. Any idea as to how I can achieve looping through all the listboxes in different worksheets and then populating them?

解决方案

Try this

Sub Sample()
    Dim sh As Worksheet
    Dim obj As OLEObject
    Dim idx As Long

    For idx = 1 To ThisWorkbook.Worksheets.Count
        Set sh = ThisWorkbook.Worksheets(idx)
        For Each obj In sh.OLEObjects
            If TypeOf obj.Object Is MSForms.ListBox Then
                If (obj.Name = "lst1") Then
                    Call PopulateSimple(obj, "Table1")
                End If
            End If
        Next
    Next idx
End Sub

Explanation: You are getting that error because obj is declared as OLEObject and lst as MSForms.ListBox and hence a type mismatch.

这篇关于Excel VBA-遍历ListBox控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！