How to access a ListBox on a UserForm by name?

400 Views Asked by At

I have a UserForm with multiple ListBoxes. I need to access a ListBox by name (and not directly by an object reference).

I'm trying to follow the recommendations from this post:

Dim ws As Worksheet, Field As String, lbName As String, lb As MSForms.ListBox
Field = "SomeString"
lbName = "SomePattern_" & Field
Set lb = ws.OLEObjects(lbName).Object ' Run-time error '1004': Method 'OLEObjects' of object '_Worksheet' failed

I'm sure I'm looking for the correct name because the ListBox was added by name as well:

' inside the UserForm code
With Me.Controls
    Dim lstbox As MSForms.ListBox
    Set lstbox = .Add("Forms.ListBox.1", Name:="SomePattern_" & Field, Visible:=True)
    Debug.Print lstbox.Name ' prints an exact match to lbName
End With

I tried to loop through Controls instead of OLEObjects but it failed as well.

1

There are 1 best solutions below

0
Dmitry D. Onishchenko On

With the help of comments above by @Rory I've fixed a number of issues in my code. This one work fine (in case the ListBox was added to the UserForm, not to a worksheet):

Dim lbName As String, lb As MSForms.Control

lbName = "SomePattern"
Set lb = MyUserForm.Controls(lbName)