Create Multi-Selection List in Excel

Is there a way to create a multi-selection list (drop down) In an Excell workbook?  I mean to be able to select more than 1 item at a time and have it displayed concatenated (ie.  abc, 123, def)

thanks,

K
Karen SchaeferAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
I found some issues and rewrote the code.

Kevin
Q-26978845.xls
0
 
Rory ArchibaldCommented:
Not a dropdown, no, but you can use a listbox, if that would be OK?
0
 
zorvek (Kevin Jones)ConsultantCommented:
The list box ActiveX control allows multiple selections but the control does not place the results directly into a cell. The selection has to be interpreted with VBA code. The VBA code below gets around this limitation and saves the selected values into the linked cell. It also restores the selected values from the linked cell. This is accomplished by intercepting the list box change event and storing into the linked cell the currently selected items as a vertical bar separated list. Another routine, InitializeSheet, is included to load the list box when the workbook is opened or the worksheet activated.

To create a multiple selection list box right-click on the menu bar and select Control Toolbox. Click on the List Box control (move the mouse over the controls and wait for a pop-up help box to tell you what it is.) Add a list box to the worksheet by clicking and dragging the mouse. Right-click on the control and select Properties. Set the LinkedCell property to the cell in which the selection is to be stored. Change the MultiSelect property to frmMultiSelectMulti. Change the ListFillRange to the range of cells containing the selectable values. Turn off design mode (click on the triangle/ruler/pencil icon on the Visual Basic or Control Toolbox toolbar.)

Add the following code to the worksheet module (right-click on the worksheet tab and select View Code to open the worksheet code module.) If the name of the list box control is not ListBox1 then change all occurrences of "ListBox1" in the following code to the name of the control.

Private Sub ListBox1_Change()

' Handle the list box change event.

   Dim Result As String
   Dim Index As Long

   For Index = 1 To ListBox1.ListCount
      If ListBox1.Selected(Index - 1) Then
         Result = Result & ListBox1.List(Index - 1) & "|"
      End If
   Next Index
   If Len(Result) > 0 Then Result = Left(Result, Len(Result) - 1)
   Range(ListBox1.LinkedCell) = Result

End Sub

Public Sub LoadListBox( _
      ByVal ListBox As msForms.ListBox _
   )

' Load the list box using the values in the linked cell.

   Dim Selections As Variant
   Dim Selection As Variant
   Dim Index As Long
   
   Application.EnableEvents = False
   Selections = Split(Range(ListBox.LinkedCell), "|")
   For Each Selection In Selections
      For Index = 1 To ListBox.ListCount
         If ListBox.List(Index - 1) = Selection Then
            ListBox.Selected(Index - 1) = True
            Exit For
         End If
      Next Index
   Next Selection
   Application.EnableEvents = True

End Sub

Public Sub InitializeSheet()

   ' Load the list box.
   LoadListBox ListBox1

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   ' Update the list box when the linked cell value changes.
   If Not Intersect(Target, Range(ListBox1.LinkedCell)) Is Nothing Then
      LoadListBox ListBox1
   End If

End Sub

Kevin
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Karen SchaeferAuthor Commented:
I am a bit confused by place the range of the listbox and setting the value of the listbox "Set the LinkedCell property to the cell in which the selection is to be stored. Change the MultiSelect property to frmMultiSelectMulti. Change the ListFillRange to the range of cells containing the selectable values. Turn off design mode (click on the triangle/ruler/pencil icon on the Visual Basic or Control Toolbox toolbar.)"

Is there a way to set the list value from another worksheeet within the same workbooK?

K
0
 
Karen SchaeferAuthor Commented:
I mean using a Named range or a range of  cells from another worksheet.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes, you can set the list fill range like so:

Sheet2!A1:A10

or the linked cell range:

Sheet2!B1

Kevin
0
 
Karen SchaeferAuthor Commented:
Ok I got the list fill Range to display in the List box, now How do I make if the the capability to have different selections for each cell .  currenlty it is placing the same value (llist selection) every cell, instead of allowing for different options. per cell.

K
0
 
Karen SchaeferAuthor Commented:
i would like to  place the value in the current cell  

List box values
ABC
CDE
EFG

Cell - Tested values ( Cell - A2) = ABC; CDE
Cell - Tested values ( Cell - A3) = ABC; EFG, CDE
Cell - Tested values ( Cell - A4) = ABC

k
0
 
zorvek (Kevin Jones)ConsultantCommented:
I'm not sure I follow but perhaps this might work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Dim TargetListBox As msForms.ListBox

    Set TargetListBox = Me.OLEObjects("ListBox1").Object
    If Target.Cells.Count = 1 And Not Intersect(Target, Me.[A2:A4]) Is Nothing Then
        TargetListBox.LinkedCell = Target.Address(False, False)
    Else
        TargetListBox.LinkedCell = vbNullString
    End If
       
End Sub

Add the above to your worksheet code module. It will make the list box point to the selected cell if it is in the range A2:A4.

Kevin
0
 
Karen SchaeferAuthor Commented:
would it be possible to provide me with a sample xls that contains the list box and how it works with inputting the data into the correct cells.

I want to use a list box to make my selection for each cell - where the value of the cell can vary.

Maybe I don't understand how the list box works.  Don't know how much you know about Access - but a list box in Access allows you to input different data into a field based on the list box

Like my example above:  

my list box values =

ABC
DEF
GHI
JKL

for the first cell (A2) I want the value to be ABC;DEF
in Cell (A3)  I want the value to be ABC;JKL

How do I set the list box so that what ever value(s) I select from the list box the results are place int he correct cell(s)
0
 
Karen SchaeferAuthor Commented:
thanks that did the trick.

K
0
All Courses

From novice to tech pro — start learning today.