Excel VBA - Cycle through ListBox controls

Question

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?

What reference(s) do you require for this? MSForms.ListBox gives me a compile error, "User defined type not recognized" — David Zemens
– David Zemens, Commented Apr 10, 2013 at 14:54
Disregard, I found a reference to MS Forms library. When I run this code, sh.OLEObjects is empty. How have you inserted the ListBox control? — David Zemens
– David Zemens, Commented Apr 10, 2013 at 14:55
In my opinion you need to change Dim lst as MSForms.ListBox into Dim lst as OLEObject and that is all... — Kazimierz Jawor
– Kazimierz Jawor, Commented Apr 10, 2013 at 14:57
+ 1 @KazJaw: Yup that is the problem. However we don't need lst at all. — Siddharth Rout
– Siddharth Rout, Commented Apr 10, 2013 at 15:00

Siddharth Rout · Accepted Answer · 2013-04-10 14:57:33Z

2

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.

answered Apr 10, 2013 at 14:57

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Our Man in Bananas Over a year ago

you beat me to it by about 3 seconds !

Siddharth Rout Over a year ago

@Philip: Thanks Philip :) 3 Secs? 4 mins you mean? ;)

Our Man in Bananas · Accepted Answer · 2013-04-10 15:01:37Z

0

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

answered Apr 10, 2013 at 15:01

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Comments

Kazimierz Jawor · Accepted Answer · 2013-04-10 15:01:43Z

0

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...

answered Apr 10, 2013 at 15:01

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Cycle through ListBox controls

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related