Excel Target.column for Data Validation would like to target some cells within column

Nov 7, 2017
Reaction score
Hello All, hopefully you can help, I have visited many forums and found the below code that works, but I have tried tweaking for my specific needs and I am unable to:

Basically it is allowing me to use any data validation cells in column 2 as multi select cells with comma separation (i.e. select1, select2, select3) , problem is that I have a few data validation cells in column 2 that I only want as a single select.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
If Target.Count > 1 Then GoTo exitHandler
lType = Target.Validation.Type
If lType = 3 Then
Application.EnableEvents = False
newVal = Target.Value
oldVal = Target.Value
Target.Value = newVal
'Select Case Target.Address
'Case Is = "$b$16", "$b$17","$b$24"
'If Target.Cells = "$b$17" Or "$b$18" Or "$f$4" Then
If Target.Column = 2 Then
If oldVal = "" Then
If newVal = "" Then
On Error Resume Next
Ar = Split(oldVal, ", ")
strVal = ""
For i = LBound(Ar) To UBound(Ar)
Debug.Print strVal
Debug.Print CStr(Ar(i))
If newVal = CStr(Ar(i)) Then
strVal = strVal
lCount = 1
strVal = strVal & CStr(Ar(i)) & ", "
End If
Next i
If lCount > 0 Then
Target.Value = Left(strVal, Len(strVal) - 2)
Target.Value = strVal & newVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question