VBA reference to listbox form control by variable name

Question

I am stuck on the syntax of referring to a listbox control on a form in VBA. I am trying to write a subroutine to build a SQL string based on the selections in a multiselect listbox. Currently, I have the following code:

Private Sub lstStudies_AfterUpdate()
    Call buildMultiSQL("lstStudies","field1")
End Sub

Private Sub buildMultiSQL(lstName As String, fldName As String)
     Dim lstBox As Object
     lstBox = Me.Controls(lstName)
     …
End Sub

I get the following error: "Run-time error '91': Object Block or With block variable not set" which highlights the line

lstBox = Me.Controls(lstName)

When I change the code to:

Private Sub buildMultiSQL(lstName As String, fldName As String)
     Dim lstBox As Object
     lstBox = Forms(frmMultiselect).Controls(lstName)
     …
End Sub

I get a different runtime error on the same line: "Run-time error '2465': [My Database Name] can't find the field 'lstStudies' referred to in your expression."

How do I refer to a list box that is a form control using a variable name? I have already double-checked that the listbox is really named lstStudies, and there are no other listboxes or controls with that name on the form.

I would just pass the control itself as the parameter buildMultiSQL(lstBox As MSForms.ListBox, fldName As String). You might want to consider storing the field name in the ListBox.Tag property. — TinMan

Tim Williams Tim Williams · Accepted Answer · 2018-11-29T20:39:09

1

votes

When assigning object variables you need to use Set

Set lstBox = Me.Controls(lstName)

VBA reference to listbox form control by variable name

1 Answers