Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi Selection Lists (Excel 2003)

    Hi

    I have looked at the various posting on List selection but did not see anything which indicated that it is possible to have a list which will allow multi selection, is this possible.

    Regards

    Mike

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Selection Lists (Excel 2003)

    Both the list box from the Forms toolbar and from the Control Toolbox have a multi-select option (in the Control tab of the Format Control dialog and in the Properties pane/window, respectively).
    If you activate one of the two multi-select options, you'll have to handle everything in code, though, the link to a result cell won't work any more.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Selection Lists (Excel 2003)

    Hi Hans,

    Thanks, I was looking to see if I can use this in a cell with a name range, ie control what data is entered in the column.

    Regards

    Mike

  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

    Re: Multi Selection Lists (Excel 2003)

    Yes you can.

    But, as Hans mentioned, it must be done in code looking at each item to see if it is selected. It can not be done with the "Cell Link" which only works when multiselect is false.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Selection Lists (Excel 2003)

    Thanks Steve,

    Can you give me an example on how this can be done please.

    Regards

    Mike

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Selection Lists (Excel 2003)

    If you provide more in detail what you want to accomplish, someone may come up with a suggestion.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Selection Lists (Excel 2003)

    Hi Hans,

    What I want is very simple.

    I have a spreadsheet with several columns, on some of them I would want to control what was entered, so the following

    Fixed data - John, Fred, Jane, Joan
    This is for Column A

    In A1 - John
    In A2 - John and Jane
    etc..

    Anything else entered would not be allowed.

    Regards

    Mike

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Selection Lists (Excel 2003)

    It may be simple to ask, but not to implement. The Validation dropdown list doesn't provide for multiple selection, neither do combo boxes. List boxes do, but they're too bulky for this purpose.

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

    Re: Multi Selection Lists (Excel 2003)

    Try this.

    I created a listbox. When you select a cell in column A it becomes visible and is filled with the 4 names. Make your selection and when you select a different cell the listbox fills the cell with the selected items.

    The code is in the sheet worksheet object. in vb (right click on sheet name and choose "view code" to look at it)

    Steve

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi Selection Lists (Excel 2003)

    If I have properly understood your needs, the attached file should what you want. There is a proviso though; to work properly Cells A1 through A4 must be entered in that order.
    HTH
    Regards
    Don

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi Selection Lists (Excel 2003)

    Hi Steve
    I am impressed! That is cute. On my old clunker of a laptop running Office 97, I found it necessary to add
    Application.ScreenUpdating = True
    immediately after
    .Visible = False
    in order to hide the listbox.
    Regards
    Don

  12. #12
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Selection Lists (Excel 2003)

    Thanks Steve,

    This is exactly what I want.

    Regards

    Mike

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Selection Lists (Excel 2003)

    One small addition to your very nice code: after the line

    .AddItem vArray(i)

    you can insert

    If InStr(ActiveCell, vArray(i)) Then
    .Selected(i) = True
    End If

    This will select items in the list box according to the cell value. Otherwise, clicking in a populated cell, then clicking outside the list box will clear the cell.

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

    Re: Multi Selection Lists (Excel 2003)

    Good Idea!

    My mind was set on just filling an empty cell, not editing an already filled cells.

    Steve

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

    Re: Multi Selection Lists (Excel 2003)

    The "problem" with this could be if you have names like: Mary, Jane, Mary Jane. When you select "Mary Jane" in a cell and reselect the cell all three will be selected.

    A possible workaround would be (though I would use a variable to hold the " and " since it is being used thrice and it also makes modifying easier and can be more generic)
    If InStr(" and " & ActiveCell, " and " & vArray(i)) Then
    .Selected(i) = True
    End If

    This will catch "most" of the issues, though with example above, Mary and Mary Jane will both be selected when you reselect the cell.

    One way around it would be to parse the text and check each item individually, though I think it would slow it down and probably would not be worth the effort...

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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