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
– TinMan, Commented Nov 29, 2018 at 21:03

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

1

When assigning object variables you need to use Set

Set lstBox = Me.Controls(lstName)

answered Nov 29, 2018 at 20:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

VBA reference to listbox form control by variable name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related