You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
LB in GA, Thank you!
Hey LB,
I would be happy if you or another person in this “thread” would like to help me instead of Debra.
Thanks to all of you,
Mark in SC
@Mark, at the end of the code, you could clear the contents of the data validation cell, by adding this line of code:
Cells(lRow, lCol + 1).Value = Target.Value
Target.ClearContents ‘< =====THIS IS THE NEW LINE
End Select
@Mark — you can try the line of code that I just posted in your original question
Debra,
Thank you for your quick and helpful response. (I was not well-informed on Blog etiquette, and I won’t ask others in this string for help as they can start thier own Blog.
I have created a worksheet based on your “seperate rows” worksheet. Then each selected object has a dependent list using the indirect command and naming the dependent range name the same name as the object selected in the first dropdown. Sorta like the “DataValRegionCust” ex. and using range names of “Ontario”, “Quebec”.
I wanted to make this work in a List Box (ActiveX Control) so that I could use the 1-fmListStyleOption and
1-fmMultiSelectMulti so that I can pick several objects at once in the first list and have them output the same as I have working. **I get #NA as the output**
I am attaching my code that works with the simpler dropdown list.
Option Explicit
Private Sub ListBox1_Click()
End Sub
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column ‘column with data validation cell
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
‘do nothing
Else
If Target.Value = “” Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 1, 12, 14, 18
If Target.Offset(0, 1).Value = “” Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value
Target.ClearContents
End Select
End If
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& ” ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
“Select Multiple Items from Excel Data Validation List HELP”
Even though I am aware that I am able to view the code, but it is useless to me considering I know absolutely nothing about VB and/or Macros. Could someone please explain to me in “detail” and “step by step” on how I would go about getting this done? Please explain it to me like I am a stupid five year old. I will not be offended at all. Thanks ahead for the help.
If there are more than one selection in the drop down list that is able to be selected, will the “countif” function still work? For example, I choose from the list A,B and C in one row. I still want the function to still be able to recognize how many instances of A,B and/or C has occurred. Hope that makes sense. Thanks.