CODEWITHSUNDEEP
vbaexcel
Dimitris
Dimitris

Reputation: 2036

Excel VBA - Cycle through ListBox controls

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?

Upvotes: 0

Views: 2658

Answers (3)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

simplest way (moved from comment):

In my opinion you need to change 

Dim lst as MSForms.ListBox

into 

Dim lst as OLEObject

and that is all...

Upvotes: 0

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

can't you use the obj in your call to PopulateSimplesince you know it is a ListBox:

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 (obj.Name = "lst1") Then
                Call PopulateSimple(obj, "Table1")
            End If
        End If
    Next
Next idx

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

Upvotes: 2

Related Questions