Data Validation Multi Select

G

Guest

I have set up a spreadsheet that has data validation in 2 columns. The data
validations are 2 ranges set up on a hidden worksheet. I found the below
code from contextures, which allows multi-select of data validation options
and shows the selections separated by a comma in the cell. I changed the
Target.column =5 for my specific worksheet.

I would like to somehow adapt the code to allow this same feature to work
for the 2nd column of data validation (column 6), but have yet to figure out
how to do it.
Is it possible to do? And if yes, can someone tell me how?
TIA

Option Explicit
*****************
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 = 5 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
 

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

Top