Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    How do I get a user to select several items?

    I have a list of 10 countries in adjacent cells (vertically).

    I want to display these in some form of list box, get the user to select one or more and then put the selected countries in some variables I can use later.

    But - I actually have no idea where to start on this. I've never done list boxes or resolving what is selected.

    Can someone provide some sample code I can work with?

    Cheers

    Alan

  2. #2
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Have a look at this MS tutorial, it explains listboxes and combo boxes quite well then if you are still not sure we can take it step by step.
    Which version of Excel are you using?

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Excel 2010

    Alan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here are some directions and an example file: http://www.contextures.com/excelvbalistboxcreate.html from MS MVP Debra Dalgleish.


    Steve

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Alan,

    Here is some code that shows how to take a list of cell values and populate a list box. To enable the selection of more than one value, using the properties window for the listbox, change the MultiSelect property to 1-fmMultiSelectMulti. One important note: Prior to populating a listbox, you should always clear its contents to avoid appending the existing list with a new one. If you are merely adding a new item to the existing list then clearing the list is not necessary.

    Once you make the selections, the additional code shows how to set them to an array variable s().

    HTH,
    Maud

    Listbox.png

    Code:
    Public Sub PopulateListBox()
    'ADD ITEMS CELLS C4:C13 TO LISTBOX
    With Worksheets("Sheet1")
    .ListBox1.Clear  'ALWAYS CLEAR LISTBOX PRIOR TO POPULATING
    For I = 4 To 13
        .ListBox1.AddItem Cells(I, 3)
    Next I
    End With
    End Sub
    
    Public Sub ClearListbox()
    'CLEAR CONTENTS OF LISTBOX
    With Worksheets("Sheet1")
    For I = 0 To .ListBox1.ListCount - 1
        .ListBox1.Clear
    Next I
    End With
    End Sub
    
    Public Sub ListBoxValues()
    'SET THE SELECTED ITEMS TO THE ARRAY VARIABLE S
    Dim s(10) As Variant
    num = 0
    With Worksheets("Sheet1")
    For I = 0 To .ListBox1.ListCount - 1
        If .ListBox1.Selected(I) = True Then
            s(num) = .ListBox1.List(I)
            Debug.Print s(num)
            num = num + 1
        End If
    Next I
    End With
    MsgBox "View s variables in immediate window"
    End Sub
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    alan sh (2014-03-13)

  7. #6
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    maudibe,

    Thanks for that - I have some code that works now. One additional thing - how do I get it to populate the list box as soon as I open the spreadsheet (assuming macros are enabled)?

    never mind - found it.

    regards

    Alan
    Last edited by alan sh; 2014-03-13 at 06:56.

Posting Permissions

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