Cycle through all listboxes in a form (XL2007)

[AOB]

Hi everybody,

Does anybody know how to loop through all listboxes on a user form?

I'm trying to cycle through a set of list boxes (10 for now but that number may grow) and populate them with distinct values from corresponding fields in a database.

I've set up the listboxes on the form such that their names correspond to the fields in the database. However, while this method would work for a set of list boxes on a sheet, it doesn't work when they're on a form?

Private Sub UserForm_Activate()
[INDENT]Dim lbListBox As ListBox
Dim cnConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
 
Set cnConnection = New ADODB.Connection
 
With cnConnection
[INDENT].Provider = "Microsoft.Ace.OLEDB.12.0"
.Open stDatabase
[/INDENT]End With
 
[COLOR=red]For Each lbListBox In Me[/COLOR]
[INDENT]
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open "SELECT DISTINCT [" & lbListBox.Name & "] FROM MyTable", cnConnection, adOpenStatic
 
With lbListBox
[INDENT].Clear
Do
[INDENT]If Not IsNull(rsRecordset.Fields(lbListBox.Name)) Then .AddItem rsRecordset.Fields(lbListBox.Name)
rsRecordset.MoveNext
[/INDENT]Loop Until rsSubRecordset.EOF
[/INDENT]End With
 
rsRecordset.Close
Set rsRecordset = Nothing
 
[/INDENT]Next lbListBox
 
cnConnection.Close
Set cnConnection = Nothing
 
[/INDENT]End Sub

It doesn't like the line in red ("Run-time error '438' : Object doesn't support this property or method") because it seems listboxes aren't recognised on forms in the same way as they are on sheets.

Any suggestions for how this can be done? I'd rather not have to have separate code to populate each individual list box...

Thanks in advance!

AOB

 

[Jon von der Heyden]

I tend to loop thru controls and test the type of control; usually because I tend to handle different sorts of controls at the same time...

Dim ctl As MSForms.Control

For Each ctl In Me.Controls
    If TypeName(ctl) = "ListBox" Then
        'do your thing here
    End If
Next ctl

 

[AOB]

Cheers Jon - exactly what I was looking for!

Thanks very much!

AOB

 

[UHsoccer]

Jon,

Could you please take a look at my post

http://www.mrexcel.com/forum/showthread.php?t=631979&highlight=listboxes

I need to do the same but have a hard time converting to your suggestion