How to loop through listboxes in a vba user form

Question

I have a VBA user form with 6 list boxes. Each list box contains related data in another list box on the same row e.g. list box 1 has an account code and list box 2 has an account name, etc. When a user selects an item within a listbox I want all other listboxes to select a corresponding row. How can I achieve this using a loop?

I know I can explicitly reference the list items as per excelcise example but I feel there must be a way to loop through available listboxes instead of listing them by name.

You can loop over the form's Controls collection and test each item's type to see if it's a listbox. — Tim Williams
– Tim Williams, Commented Feb 10, 2023 at 23:00

crusader86x · Accepted Answer · 2023-02-11 07:32:37Z

1

Private Sub CheckControls()

Dim contr As control

For Each contr In Controls
    If TypeName(contr) = "ListBox" Then
        Select Case contr.Name
            Case "ListBox1":
            Case "ListBox2":
            Case "ListBox3":
            'and so on....
        End Select
    End If
Next contr

End Sub

edited Feb 11, 2023 at 7:32

answered Feb 10, 2023 at 23:33

crusader86x

384 bronze badges

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

Comments

Karolis · Accepted Answer · 2023-02-11 00:02:48Z

After a bit of troubleshooting I think I found the answer in the Controls collection. Within each list box I call a procedure which takes existing listbox index, topIndex (useful when using scrolbars) and its own name.

Private Sub lbox_gl_account_Change()
  Call arrangeIndexOfLabels(Me.lbox_gl_account.listIndex, 
  Me.lbox_gl_account.topIndex, Me.ActiveControl.Name)
End Sub

The arrangeIndexOfLabels procedure loops through all controls in the collection and only affects those that are of type ListBox. It updates listIndex and topIndex for all ListBoxes except for the active one.

'Change index location of other listbox items according to the selected one
Private Sub arrangeIndexOfLabels(lngListIndex As Long, lngTopIndex As Long, strActiveControl As String)
Dim ctrlItem As Control
For Each ctrlItem In Me.Controls
    If TypeName(ctrlItem) = "ListBox" Then
        'Only changing index position of other list boxes than selected
        If ctrlItem.Name <> strActiveControl Then
            ctrlItem.listIndex = lngListIndex
            ctrlItem.topIndex = lngTopIndex
        End If
    End If
Next ctrlItem

End Sub

Collectives™ on Stack Overflow

How to loop through listboxes in a vba user form

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related