Error '424' object required; when referencing a listbox in a userform using Excel VBA - Stack Overflow

admin管理员组

文章数量:1287490

My UserForm1 has a listbox called lbxAccounts. At one point, whichever Account is selected (lbxAccounts.Value) is to be read into a string variable.

I simplified, such that CommandButton1's click event calls build_query_string, a public sub in Module1, that contains one line of code.

MsgBox lbxAccounts.Value

I run the UserForm1. It compiles. Account names are loaded into lbxAccounts. I select one of those account names.

When I click CommandButton1 I get

Run-time error '424': Object required

with the one line in build_query_string highlighted.

When I comment out the entire sub and put that line of code natively in the CommandButton1_Click() event, it works .

Is the lbxAccounts object not visible to a sub stored in Module1?
i.e. any module other than the UserForm module that contains the object?

I tried putting Dim lbxAccounts as Object in Module1's Declarations area.

My UserForm1 has a listbox called lbxAccounts. At one point, whichever Account is selected (lbxAccounts.Value) is to be read into a string variable.

I simplified, such that CommandButton1's click event calls build_query_string, a public sub in Module1, that contains one line of code.

MsgBox lbxAccounts.Value

I run the UserForm1. It compiles. Account names are loaded into lbxAccounts. I select one of those account names.

When I click CommandButton1 I get

Run-time error '424': Object required

with the one line in build_query_string highlighted.

When I comment out the entire sub and put that line of code natively in the CommandButton1_Click() event, it works .

Is the lbxAccounts object not visible to a sub stored in Module1?
i.e. any module other than the UserForm module that contains the object?

I tried putting Dim lbxAccounts as Object in Module1's Declarations area.

• excel
• vba

Share Improve this question Follow edited Feb 24 at 23:21 CommunityBot 111 silver badge asked Feb 23 at 18:14 stueystuey 3155 bronze badges 1

• 3 MsgBox UserForm1.lbxAccounts.Value – CDP1802 Commented Feb 23 at 18:25

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

If you want code in a regular module to access controls on your userform, the most robust way to do that would be to pass the userform instance to the called method in the regular module.

For example:

In UserForm1

Sub CommandButton1_Click()
    TheCalledsub Me 'here `Me` is the running instance of UserForm1
End Sub

In a regular module

Sub TheCalledsub(frm as UserForm1)
    Debug.Print frm.lbxAccounts.Value  'use `frm` to access `lbxAccounts`
End Sub

Your error comes from the fact that lbxAccounts is an object (of type listbox) on the userform. When you write code inside the form (eg in the click-event) and access lbxAccounts, the compiler looks inside the form, finds the definition and knows what you want to access. To be precise: A form is a class, and the compiler looks for members inside the class definition.

Outside of the form (eg in a regular module), lbxAccounts is unknown. Unfortunately, you are not using Option Explicit (you should always do!!!), else you would already get an error message from the compiler telling that there is a problem.

You can access the combobox outside the form code, but you need to tell the VBA compiler that you want to access the combobox of the form by specifying the object.

Debug.Print UserForm1.lbxAccounts.Value

Now the compiler knows that you want to access the member of an object named UserForm1 and everything is fine.

Or is it?

If you are familiar with OOP, you will recognize that you never defined an object with the name UserForm1. You only define a form with that name, and forms are classes, not objects. Using UserForm1 as object is a speciality of VBA: If there is no object variable with that name, it will create one on the fly, called the "Default Object". This often works okay, but it has a smell, and in complexer situations it can lead to surprising effects and nasty bugs.

Alternative is to create the object by your own:

Sub ShowForm
    Dim frm As UserForm1
    Set frm = new UserForm1
    frm.Show
End Sub

Sub build_query_string
    Debug.Print frm.lbxAccounts.Value  ' Will not work!
End Sub

But now you have another problem: In your routine build_query_string, frm is unknown as it is defined as a local variable. To solve that, you would need to declare the variable frm as global variable in the module. Which also has a smell...

If I were you, I would avoid that code in the module accesses the userform at all. Instead, pass the search string from the form into the routine. In fact, build_query_string shoudn't care where the string comes from.

Call in Form:

Sub CommandButton1_Click()
    build_query_string lbxAccounts.Value 
End Sub

Definition of Sub:

Sub build_query_string(queryString As String)
    Debug.Print queryString
End Sub

---

Declaring a variable Dim lbxAccounts as Object with the same name as the listBox will not work because it is a complete different object, only with the same name. As you don't assign anything to it, you again will get the 424 error.

本文标签： Error 3942439 object required when referencing a listbox in a userform using Excel VBAStack Overflow