KennyExcel
New Member
- Joined
- Jan 22, 2016
- Messages
- 5
Hi all,
Newbie here in need of some help.....
I have a cell which is a data validation dropdown that will eventually contain a lot of values. What I am trying to do is make this multi-select. I have already entered some code to make it multi-select but in the following format:
Value1, Value2, Value3
I would like the format to be:
Value 1
Value 2
Value 3
A bonus add on if possible would be if you select the same value for a second time, it will remove it from the selected list
Here is my current code (Bare in mind I am a newbie to this) I have inserted in the view code section of the tab:
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
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Appreciate any help you can give
Newbie here in need of some help.....
I have a cell which is a data validation dropdown that will eventually contain a lot of values. What I am trying to do is make this multi-select. I have already entered some code to make it multi-select but in the following format:
Value1, Value2, Value3
I would like the format to be:
Value 1
Value 2
Value 3
A bonus add on if possible would be if you select the same value for a second time, it will remove it from the selected list

Here is my current code (Bare in mind I am a newbie to this) I have inserted in the view code section of the tab:
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
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Appreciate any help you can give
