Assign list box selection as a variable

  • I have what I would think is a standard requirment, but I can't figure out a straighforward way to do it.

    I need to have a procedure populate and display a userform with a listbox of choices, wait for the selection, and then continue processing with the selected item, similar to the way InputBox works. The only way I can see to do this is:
    1. Populate and display the listbox in the primary procedure
    2. Write code attached to the userform that extracts the choice (Listbox.value)
    3. Call a macro in the primary module with this selected value as the argument
    4. The called macro in the primary module then stores the selection in a Public variable
    5. Close the userform
    6. The primary procedure then continues on, accessing the Public variable

    Is there any way to to steps 1 and then 5 and 6 directly? Seems there would be, but I can't find it. -- Thanks

  • Re: Assign list box selection as a vairable

    I see no need to create another macro just to "store" a variable when you can directly assign the list box selection to a variable.

    1. Dim strMyListbx as String ' or whatever
    2. strMyListbx = me.listbox1.value

    Perhaps if you explained more fully and provided the context of the project a more precise answer can be given.

    See this important link on The Scope and Lifetime of Excel VBA Variables

  • Re: Assign list box selection as a vairable

    You know the form has its own code module... yes?

    1. Show myVerySpecialUserForm
    2. In the form's code module, use the Initialize event to populate the listbox list values.
    3. Still in the form code module, put the code you want to run in the listbox control change event. Conclude this event code section with the line Unload Me.

    That's it - you're done.

  • Re: Assign list box selection as a vairable

    Thanks, but I guess I don't understand how to assigne a list box selection directly to a variable. If I have a listbox defined, and the user then selects an entry, from what I understand the selection can be accessed via a module connected with a button on the form, or to the listbox procedure (e.g. Private Sub ListBox1_Click()), not the procedure that displays the form. How do you pass the selection directly back to the procedure that builds and loads the userform? For example, if I had defined a simple listbox to show a few month names, using the following code, how do I get the selected month back to the procedure that shows this userform?

    1. with UserForm1.ListBox1
    2. .AddItem "January"
    3. .AddItem "February"
    4. .AddItem "March"
    5. .AddItem "April"
    6. End With
    8. ' here's where I want to access the selected month

    Does this clarify my question, or make it more confusing?
    Thanks again

  • Re: Assign list box selection as a vairable

    Thanks, but that's my question. The code attached to the listbox change event needs to get the selection back to the original procedure that showed the form. How is this done? -- it appears public variables can't be used between the userform code module and the procedure that initiated the form (or am I incorrect about this assumption?).

    I posted some typical code in response to this question if this makes what I need to do a bit clearer.

  • Re: Assign list box selection as a vairable

    OK, sorry - that may not have been good advice about the change event. Honestly I haven't used listboxes, preferring the way a combobox displays the selection list to the user. They can be made to behave much like a list box. But that doesn't answer your question.

    So to proceed with just what you first asked, how to do it in fewer steps:
    You don't need a variable to hold the listbox selection at all, just pass it directly to the macro you're calling during the execution of the userform code.

    The thing I said about populating the listbox values during the form initialize event still applies.

    To load the form this is all you need...

    1. Sub GetMonthFromUser()
    2. UserForm1.Show
    3. End Sub

    Here's sample code for UserForm1, to populate the list box. Then when user clicks CommandButton1, call a macro named MonthDoer (having a string argument) passing the selected ListBox1 value directly to it.

    Hope that's a little more helpful.

  • Re: Assign list box selection as a vairable

    Look at the attached.
    There is a userform called Choose which has a function called .FromList

    .FromList has arguments
    ChooseFrom - an array of the items from which the user will choose. It will also accept a Range as an argument, putting the contents of the cells in the range as the list.
    Prompt (optional) - the prompt to be shown
    Title (optional) - the title shown in the dialog box
    Default (optiona) - the default choise
    MultiSelect (optional)- MultiSelect is False if not specified
    Delimiter (optional) - the delimiter used to return a string if Multi-Select is True, default is comma.

    Choose.FromList will return a string, either the single item selected or (if Multi-Select is True) a delimited string.

    It would be used in code like