Select Multiple Items from List In Excel

In this post, I will demonstrate How to select multiple items from list in EXCEL.

You might also like to read Change table name excel online

---

Select Multiple Items from List In Excel

If you are trying to use a list in Excel, you will note that it only allow one item selection. So, How you can select multiple items from a list as shown below?

Steps

• Go to the sheet that have the column list.
• Right-click the sheet tab > Click on View Code.

• Specify your column number that have your list in my example is 2 and 4 .
• Paste the following code by changing Target column number with your column number in this line “If Target.Column = 2 Then (2 is the column number)“

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
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 = 2 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
' NOTE: you can use a line break,
' instead of a comma
' Target.Value = oldVal _
' & Chr(10) & newVal
End If
End If
End If

'For second Column
If Target.Column = 4 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
' NOTE: you can use a line break,
' instead of a comma
' Target.Value = oldVal _
' & Chr(10) & newVal
End If
End If
End If
End If
exitHandler: Application.EnableEvents = True End Sub

Apply Multi Selection for all list in your Excel sheet

If you need to apply Multi Selection for all list in your Excel sheet, so you should Try to use the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
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
End If
exitHandler: Application.EnableEvents = True End Sub

Note : This code has been developed by Contextures Inc.

---

See Also

• Excel with formula in PowerApps.
• Export Enterprise Custom Fields to Excel file in Project Server 2016