Populating a ComboBox using a ListBox from a second UserForm

Question

I have a UserForm1 that contains a ComboBox with three items. When item #3 is selected, a new UserForm2 pops up. That UserForm2 contains a ListBox that is populated with many entries from a sheet. I want to be able to select one of those entries in the ListBox and then pass the selected entry from the ListBox to the ComboBox in UserForm1 (using a button in UserForm2).

My simplified code looks like this:

(UserForm1)

Sub UserForm1_Initialize()
   ComboBox1.AddItem "Item1"
   ComboBox1.AddItem "Item2"
   ComboBox1.AddItem "Item3"
End Sub

Sub ComboBox1_Click()
   If ComboBox1 = "Item3" Then
      Load UserForm2
      UserForm2.show
   End If
End Sub

(UserForm2)

Sub UserForm2_Initialize()
   ListBox.AddItem "Apple"
   ListBox.AddItem "Pear"
   ListBox.AddItem "Banana"
End Sub

Sub CommandButton1_Click()
   MyVal = ListBox                 'using .Value or .Text in this or the next line doesn't help
   UserForm1.ComboBox1 = MyVal
   Unload Me
End Sub

(Module1)

Public MyVal As String

As soon as I click the button to pass the value from the ListBox to the ComboBox1, I get a

"Runtime '380' - invalid property value"

error. Any ideas?

Answer 1

You select data from a listbox using the Column property. Starting at 0.

MyVal = ListBox.Column(0) 'This will store the data in the first column of the selected row

Answer 2

Refresh Combobox using listbox from second Userform

"It seems that before passing the MyVal to the ComboBox1, the program is running the "Sub UserForm1_Initialize()" again."

Apart from some typos (regarding both UserForm_Initiate syntaxes) your main issue is that by invoking the default instance of a(n existing) userform you get it reinstanciated. Especially trying to assign a value by referencing another userform's default instance (see UserForm2's code module) via

    UserForm1.ComboBox1 = MyVal 

... re-initializes the default instance of UserForm1 and destroys any changes before.

Modified example to understand the way (see code below)

So delete the code line above in the 2nd Userform code module and call an instance of UserForm2 via function YourChoice() in the 1st one returning the found value MyVal as function result in order to stick as close as possible to your post using a "global" variable MyVal. This would allow you to use instances of a UserForm and to get what you wanted.

Further possible steps - (Not)tl;tr

It's good use, however

• not only to instantiate a ("New") UserForm object instead of calling the form's default instance,

but

• to handle its QueryClose event to prevent rash termination,
• to decide upon a UserForm's life time by the calling code (regular or class module),
• to separate model logic (i.e.the data), view (i.e. the form) and presentation, aka MVP design pattern using the advanced possibilities of classes.

Generally you should prefer not to unload within the form's code behind - I didn't change that to leave the example comprehensible, but would recommend to study ►Mathieu Guindon's outstanding article "UserForm1.Show?" for a first insight, better understanding and some ideas on the road.

Citation

A Userform is not only a class, but disposes of an internal pecularity - or to cite Mathieu:

"UserForm is a class module: it defines the blueprint of an object. Objects usually want to be instantiated, but then someone had the genius idea of granting all instances of MSForms.UserForm a predeclared ID, which in COM terms means you basically get a global object for free."

c.f. Disadvantages in putting code into UserForms instead of modules

Modified Example code

• UserForm1

Option Explicit

Sub UserForm_Initialize()             ' << typo: not UserForm1_Initialize !
   ComboBox1.AddItem "Item1"
   ComboBox1.AddItem "Item2"
   ComboBox1.AddItem "Item3"
End Sub

Sub ComboBox1_Click()
   If ComboBox1 = "Item3" Then
      '''      Load UserForm2          ' << don't reference the default instance 
      '''      UserForm2.Show          ' << but
      ComboBox1.List(2) = yourChoice   '    make it a function call returning your choice
   End If
End Sub

• UserForm2

Option Explicit

Sub UserForm_Initialize()               ' typo: not UserForm2_Initialize !
   ListBox1.AddItem "Apple"
   ListBox1.AddItem "Pear"
   ListBox1.AddItem "Banana"
End Sub

Sub CommandButton1_Click()
    ' Purpose: assign listbox value to variable (here: global; better: via class property)
    MyVal = ListBox1                    ' equals ListBox1.List(ListBox1.ListIndex)
    '''   UserForm1.ComboBox1 = MyVal   ' << reinitializes the default instance of UF1 !!!
    Unload Me                           ' (better to make the calling function terminate the form instead)
End Sub

• Example call

Side note: - as close as possible to the OP and sticking to the global variable method as mentioned above to allow better comprehension (consider mentioned suggestions, too :-)

Option Explicit

Public MyVal As String                  ' here: global variable

Sub showMain()
With New UserForm1
    .Show vbModeless ' any mode allowed: 0-vbModeless  or 1-vbModal (default)
End With
End Sub

Function yourChoice$()
With New UserForm2
    .Show vbModal   ' or simply: .Show to allow a correct return of the function result
End With
yourChoice = MyVal  ' (a modeless form would execute any code before termination)
End Function

Further link This post doesn't treat modeless forms; calling the 2nd Userform this way would need other logic - see Destroy a modeless UserForm instance properly