Passing Array from Module to UserForm Listbox in VBA

Question

I am attempting to pass a predefined array in a Module to a Userform Listbox.

I have assigned the array as a public variable within the UserForm and have attempted to assign it from within the module.

Here is the code for the Module

Sub TestList()

Dim ArrTest As Variant

'Code to populate the array would go here in real application

ArrTest = Array("A", "B", "C")
UserForm1.ListArray = ArrTest
UserForm1.Show

End Sub

And here is the code for the UserForm

Public ListArray As Variant

Private Sub UserForm_Initialize()

    ListBox1.List = ListArray

End Sub

When I step through the code, the UserForm opens up when it reaches the ListArray assignment in the module, however within the UserForm the Array is assigned as empty and encounters and encounters Run-time error '381' when it reaches the listbox generation.

Tim Williams · Accepted Answer · 2019-09-27 04:44:32Z

1

Initialize gets called as soon as you create an instance of UserForm1 (in this case you auto-create that instance just by referring to it). So your list assignment hasn't yet happened when the Initialize method runs.

Better to put things like this into a Public method on your form and call that explicitly.

'userform
Public Sub SetList(lst)
    Me.ListBox1.List = lst
End Sub


'regular module
Sub TestList()

    Dim ArrTest As Variant
    Dim frm As UserForm1

    ArrTest = Array("A", "B", "C")

    Set frm = New UserForm1
    frm.SetList ArrTest
    frm.Show

End Sub

See also: https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/

answered Sep 27, 2019 at 4:44

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

DisplayName · Accepted Answer · 2019-09-27 07:49:54Z

1

Tim gave you the explanation why your code didn't work along with a possible solution

A possible solution preserving your code would be setting the array in the Userform Activate event, which gets fired at Show method, hence after userform initialization:

Private Sub UserForm_Activate() ' <- use 'Activate' event instead of 'Initialize'
    ListBox1.List = ListArray
End Sub

Moreover, it's recommended to explicitly instantiate Userform objects

Tim already showed you a way, and here's another way:

With New UserForm1 ' instantiate and reference a new object of your Userfom1 class
    .ListArray = ArrTest
    .Show
End With ' here the new instance gets disposed

answered Sep 27, 2019 at 7:49

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

2 Comments

DisplayName Over a year ago

@EricJohnson, any feedback?

Mitalee Rao Over a year ago

got it to work with ListBox1.list = ListArray within the module itself, the UserForm1 didn't have an attribute called ListArray

Collectives™ on Stack Overflow

Passing Array from Module to UserForm Listbox in VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related