Thanks Thanks:  0
Likes Likes:  0
Page 1 of 6 123 ... LastLast
Results 1 to 10 of 56

Thread: Drop-down list with multiple entries in a single cell

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Drop-down list with multiple entries in a single cell

    Is there anyway, I can create a drop down list that allows users to choose items from a list and place them into a single cell with comma separation (e.g. Rome, New York, Rio Di Janero)

    If I could limit it to one item per cell, a simple drop down data validation list would work, but I need to limit entry to 1 cell/column.

    Any suggestions would be much appreciated.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,891
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    You could create a UserForm that has a multi-selects listbox. The UserForm could be triggered when the user select a specific cell or clicks a button.

    This code will concatenate multiple selections from a Data Validation dropdown list (code source) in cell M10.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim oldVal As String
        Dim newVal As String
        
        If Target.Address(0, 0) <> "M10" Then Exit Sub
    
        On Error GoTo ReEnable
        Application.EnableEvents = False
        newVal = Target.Value
        Application.Undo
        oldVal = Target.Value
        Target.Value = newVal
    
        If oldVal <> "" And newVal <> "" Then
            Target.Value = oldVal & ", " & newVal
        End If
    ReEnable:
        Application.EnableEvents = True
        
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    Thanks AF -

    My VBA experience is nil but I found an explanation of how to create a userform so I've got a few questions.

    Is M11 the cell where the data is entered or where the list starts?

    I need to be able to enter the data in a number of specific cells D15:D314- any suggestions for how I could trigger the same userform when the user clicks on anyone of those 300 cells? The data for the checklist is in CL4:CL103

    Also - any suggestions on other resources to see how to create the checklist?

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,891
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    Quote Originally Posted by Innoguide View Post
    Thanks AF -

    My VBA experience is nil but I found an explanation of how to create a userform so I've got a few questions.

    Is M11 the cell where the data is entered or where the list starts?

    I need to be able to enter the data in a number of specific cells D15:D314- any suggestions for how I could trigger the same userform when the user clicks on anyone of those 300 cells? The data for the checklist is in CL4:CL103

    Also - any suggestions on other resources to see how to create the checklist?
    I had previously made two separate suggestions. Sorry I didn't make that clear.

    Suggestion 1 was to make a UserForm.
    Suggestion 2 was to use a Data Validation drop down list and use the code I provided to concatenate multiple selections from that list. The code I previously provided assumed the Data Validation drop down list was in cell M10.

    ------------

    This is how to make the user form solution. I think it's probably the better way to do it for your situation.

    Create a userform with two controls on it; a ListBox and a command button.
    ListBox1
    CommandButton1

    Put this code in the UserForm's code module...
    Code:
    Private Sub UserForm_Initialize()
    
        With ListBox1
            .List = Range("CL4:CL103").Value    'Range of cells with the list of cities
            .MultiSelect = fmMultiSelectMulti
        End With
        
        UserForm1.Caption = "Select Cities"
        CommandButton1.Caption = "Okay"
        
    End Sub
    
    
    Private Sub CommandButton1_Click()
    
        Dim i As Long, strTemp As String
        With ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) Then strTemp = strTemp & .List(i) & ", "
            Next i
        End With
        If Len(strTemp) Then
            strTemp = Left(strTemp, Len(strTemp) - 2)
            ActiveCell.Value = strTemp
        End If
        Unload Me
        
    End Sub
    Put this in the worksheet module. Right-click on the sheet tab and select View Code. Paste the code below VBA Edit window.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Count = 1 Then
            'Range of cells to have the pop-up userform
            If Not Intersect(Range("D15:D314"), Target) Is Nothing Then
                UserForm1.Show
            End If
        End If
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    Alpha Frog -

    Brilliant - Thanks so much for walking me through that - works perfectly!

    A couple of questions for refinement:

    My cities list is imported into the worksheet from another source and may or may not contain blanks, do you have any suggestions for how to modify the code to ignore blanks?

    Also - Is it possible to skip the import of the data into the worksheet and for the Userform to just reference a named range in the Cities.xls worksheet?

    Thanks Again!

  6. #6
    Board Regular
    Join Date
    Sep 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    AF -

    Strange but second time through and the pop-up isn't activating when I click on the cells. The exact code you gave is in the places you suggested and Macros are enabled - is there anything else I could be missing?

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,891
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    Quote Originally Posted by Innoguide View Post
    My cities list is imported into the worksheet from another source and may or may not contain blanks, do you have any suggestions for how to modify the code to ignore blanks?

    Also - Is it possible to skip the import of the data into the worksheet and for the Userform to just reference a named range in the Cities.xls worksheet?
    Ignore Blanks - easy

    Where in Cities.xls is the cities data located; File path, worksheet name, and cell range?

    ...second time through and the pop-up isn't activating when I click on the cells.
    Run this macro below and then click on a cell in D15:D314. If it works again, it means you have some code somewhere that is disabling the event triggers. Look for a line of code like this; Application.EnableEvents = False

    Code:
    Sub ReEnable()
        Application.EnableEvents = True
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    Board Regular
    Join Date
    Sep 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    The file is located in the same path and directory as the worksheet with the VBA which is C:\Users\Mike\Documents\SCC\Next Draft\[Cities.xlsm]

    The Worksheet is named Sheet1 and the range (A4:A253) is named Cities.

    However, the path needs to be relative to the VBA workbook if possible since I'm going to need to deploy this to several computers with different paths and can't change the path each time.

    Thanks

  9. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,891
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    Userform's module:
    Code:
    Const MySeperator As String = ", "
    
    Private Sub UserForm_Initialize()
        
        Dim i As Long, strPath As String, strFile As String, wb As Workbook
        
        strPath = ThisWorkbook.Path & Application.PathSeparator ' File path
        strFile = "Cities.xls"                                  ' File name
        
        If Len(Dir(strPath & strFile)) Then
        
            Application.ScreenUpdating = False
            Set wb = Workbooks.Open(Filename:=strPath & strFile, ReadOnly:=True)
            On Error Resume Next
                vCities = wb.Sheets("Sheet1").Range("A4:A253")  'Cities List: worksheet and range
                wb.Close SaveChanges:=False
            On Error GoTo 0
            Application.ScreenUpdating = True
        
            With ListBox1
                'Filter Blanks
                For i = LBound(vCities) To UBound(vCities)
                    If vCities(i, 1) <> "" Then .AddItem Trim(vCities(i, 1))
                Next i
                .MultiSelect = fmMultiSelectMulti
            End With
            
            UserForm1.Caption = "Select Cities"
            CommandButton1.Caption = "Okay"
                
        Else
            MsgBox "Cannot locate file: " & vbCr & vbCr & strPath & strFile, , "File Not Found"
            Unload Me
        End If
        
    End Sub
    
    Private Sub CommandButton1_Click()
    
        Dim i As Long, strTemp As String
        With ListBox1
            'Read selections
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    strTemp = strTemp & .List(i) & MySeperator
                    .Selected(i) = False    ' Clear selections
                End If
            Next i
        End With
        If Len(strTemp) Then
            strTemp = Left(strTemp, Len(strTemp) - Len(MySeperator))
            ActiveCell.Value = strTemp
        End If
        
        UserForm1.Hide
        
    End Sub
    
    Private Sub UserForm_Activate()
        'Match Listbox selections with ActiveCell selections
        Dim strCell As String, i As Long
        If Len(ActiveCell.Value) Then
            strCell = ActiveCell.Value & MySeperator
            With ListBox1
                For i = 0 To .ListCount - 1
                    .Selected(i) = InStr(1, strCell, .List(i), 1)
                Next i
            End With
        End If
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        'Hide userform if Close "X" clicked
        If CloseMode = 0 Then
            Cancel = True
            Me.Hide
        End If
         
    End Sub
    Worksheet module:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Count = 1 Then
            'Range of cells to have the pop-up userform
            If Not Intersect(Range("D15:D314"), Target) Is Nothing Then
                On Error Resume Next
                    UserForm1.Show
                On Error GoTo 0
            End If
        End If
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  10. #10
    Board Regular
    Join Date
    Sep 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Drop-down list with multiple entries in a single cell

    That is awesome Alpha Frog! Thanks so much.

    One minor thing - If you try to make a change, you can select an additional city or deselect a previously selected city, but if you try to deselect all of the cities that were previously selected (even of only 1 was previously selected), it makes no changes to the selections.

    What would it take to allow all to be cleared?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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