Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Assign list box selection as a variable

  1. #1
    Join Date
    13th July 2003
    Posts
    82

    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
    Last edited by AAE; July 6th, 2010 at 20:16. Reason: correct typo in thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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.
    e.g.
    VB:
    Dim strMyListbx As String ' or whatever
    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
    Last edited by AAE; July 6th, 2010 at 09:56. Reason: amend code
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    10th June 2010
    Location
    Seattle, WA
    Posts
    451

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th July 2003
    Posts
    82

    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?

    VB:
    With UserForm1.ListBox1 
        .AddItem "January" 
        .AddItem "February" 
        .AddItem "March" 
        .AddItem "April" 
    End With 
    UserForm1.show 
     '  here's where I want to access the selected month
    
    
    Does this clarify my question, or make it more confusing?
    Thanks again

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    13th July 2003
    Posts
    82

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    10th June 2010
    Location
    Seattle, WA
    Posts
    451

    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...

    VB:
    Sub GetMonthFromUser() 
         
        UserForm1.Show 
         
    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.

    VB:
    Private Sub UserForm_Initialize() 
         
        ListBox1.AddItem "January" 
        ListBox1.AddItem "February" 
        ListBox1.AddItem "March" 
        ListBox1.AddItem "April" 
         
    End Sub 
    Private Sub CommandButton1_Click() 
         
        MonthDoer ListBox1.Value 
        Unload Me 
         
    End Sub 
    
    
    Hope that's a little more helpful.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd April 2007
    Posts
    3,437

    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
    VB:
    Dim uiValue As String 
     
    uiValue = Choose.FromList(Array("one", "two", "three"), Prompt:="Pick a Number") 
     
    If StrPtr(uiValue) = 0 Then 
        MsgBox "Cancel pressed" 
    Else 
        MsgBox uiValue & " was selected." 
    End If 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Run Macro Procedure Based On Drop-Down Choice
    By spen in forum EXCEL HELP
    Replies: 4
    Last Post: May 16th, 2008, 00:21
  2. Mult Choice Listbox Form
    By phoenix101 in forum EXCEL HELP
    Replies: 6
    Last Post: July 21st, 2007, 23:49
  3. ListBox Existing Value or User Choice
    By John_Mc in forum EXCEL HELP
    Replies: 3
    Last Post: April 20th, 2006, 10:00
  4. Replies: 3
    Last Post: February 15th, 2005, 17:34

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno