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

You can add column 6:

If Target.Column = 5 Or Target.Column = 6 Then


Jan wrote:
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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thank you that was it.

"Debra Dalgleish" wrote:

You can add column 6:

If Target.Column = 5 Or Target.Column = 6 Then


Jan wrote:
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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html