-1

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.

Community
  • 1
  • 1
Dmitry D. Onishchenko
  • 145
  • 2
  • 12
  • `Controls` is correct. What did you try and what happened? – Rory Dec 13 '19 at 13:21
  • Well, your listbox is added to your userform and you are trying to access it on your ws object (which you haven't also specified, atleast not in the code you provided). What are you actually trying to achieve? – JvdV Dec 13 '19 at 13:24
  • `Dim lb As Control` `Set lb = MyUserForm.Controls(lbName).Object` `' Run-time error '-2147024809 (80070057): Could not find the specified object` – Dmitry D. Onishchenko Dec 13 '19 at 13:28
  • 3
    It's just: `Set lb = MyUserForm.Controls(lbName)` – Rory Dec 13 '19 at 13:30
  • Are you sure that you're trying to assign `Set lb = ...` not before the control is created? – Vitaliy Prushak Dec 13 '19 at 13:31
  • ^ ...that, or use a `With` statement to access it. > `With MyUserForm.Controls(lbName)` – JvdV Dec 13 '19 at 13:31
  • @Rory, thanks; I still get the same run-time error; @VitaliyPrushak , yes I'm sure because when I loop through `MyUserForm.Controls` and run `Debug.Print contr.Name & " --- " & TypeName(contr)` the required name gets printed out among others. – Dmitry D. Onishchenko Dec 13 '19 at 13:48
  • 2
    Your variable should also be declared as `MSForms.Control` – Rory Dec 13 '19 at 13:51
  • Please provide a [mcve] so that people can see exactly what you're doing and test. – Cindy Meister Dec 15 '19 at 07:00

1 Answers1

0

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)
Dmitry D. Onishchenko
  • 145
  • 2
  • 12