Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts

    Excel - data validation from a list multiple selections

    Hi there

    Is there any way that a cell can be validated to select multiple items from a list? I have set up a spreadsheet that selects one from a list, but how do you select more than one option?

    Thanks in Advance!

  2. #2
    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
    Data validation does not have this option. You would have to use a listbox and it would require some VBA to get the multiple selections. There is some example code and an example workbook on MS MVP Debra Dalgleish's Contexture page at http://www.contextures.com/excelvbalistboxcreate.html

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Photorer (2014-03-27)

  4. #3
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts
    I thought as much.... it looks like we will have to stick to the one, and perhaps add a second column to allow for two selections each....

    Thanks!

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Photorer,

    On the contrary, this code will let you append the values in a cell from a data validation list. In this example, the data validation list is from H1 to H5 containg the values 1 through 5. Each time you select a value is A1 from the list, the selected value is appended to the existing A1 value. The initial value selected is 4 followed by a 2 followed by a 5. I have coded spaces between the values but they can easily be removed.

    HTH,
    Maud

    Dval1.png Dval2.png Dval3.png Dval4.png

    Place in the sheet module:
    Code:
    Dim value
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.value = "" Then Exit Sub
        LastRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row
        Cells(LastRow + 1, 8) = Target.value
        With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$H$1:$H$" & LastRow + 1
        Application.EnableEvents = False
        [a1] = value & " " & Target.value
        Cells(LastRow + 1, 8) = ""
        [a2].Select
        End With
    End If
    Application.EnableEvents = True
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        value = [a1]
    End If
    End Sub
    Attached Files Attached Files

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

    Photorer (2014-03-28)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    If you wish to stack the appended values in the cell then change the line

    [a1] = value & " " & Target.value

    to

    [a1] = value & Chr(10) & Target.value

    Dval5.png

    HTH,
    Maud

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

    Photorer (2014-03-28)

  9. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Photorer,

    I don't know if this may be useful but using this code, you can rapidly add multiple values to the same cell without any type of data validation. If you press ctrl-z, the cursor will change from the default cursor to the NorthWest arrow. While in this mode, any cell you click on will append its value to cell A1 in stack form. Pressing ctrl-z again will return the cursor to its default shape and clicking the cells will behave normally.

    Because there is code in the Workbook_Open event routine, you may have to save the book to a trusted location before it will run properly depending on your security settings.

    dval6.png

    ThisWorkbook Module
    Code:
    Private Sub Workbook_Open()
    Application.OnKey "^z", "PointSelection"
    End Sub
    Worksheet Module
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Ctrlz And Intersect(Target, Range("A1")) Is Nothing Then
        [a1] = [a1] & Chr(10) & Target.value
    End If
    End Sub
    Standard Module
    Code:
    Public Ctrlz As Boolean
    
    Public Sub PointSelection()
        If Ctrlz = True Then
            Ctrlz = False
            Application.Cursor = xlDefault
        Else:
            Ctrlz = True
            Application.Cursor = xlNorthwestArrow
        End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-28 at 09:47. Reason: Updated the macro to prevent blank values from being appended to cell

  10. #7
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts
    Hi Maud

    Wow that is clever! I will have to look at whether I can add Macros to the spreadsheet we will be using. I don't know if the permissions on users machines will permit this, but it is a great way of doing it - thanks! I have learned a lot!

    Cheers
    Alain

Posting Permissions

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