Hi Everyone,
Im using the following code to insert multiple data in a single cell.
For example:
I choose a from a drop-down list (data validation cell) and it inserts the clicked item in the same data validation cell.
The problem im having is:
Say I have multiple items already inserted in the cell(P1), if for some reason I click the drop down arrow or activate the cell(P1) and then I click a different portion of the sheet, it takes the old value inside the cell(P1) and duplicates it, in the cell(P1) again.
So say I have:
John, Tom, Rick
it ends up with
John, Tom, Rich, John, Tom, Rick
I guess it needs a way to exit the handler (or stop the code) if it finds itself clicking away from the source cell (P1).
Hope it makes sense.
Thank you and
xluser26
Im using the following code to insert multiple data in a single cell.
For example:
I choose a from a drop-down list (data validation cell) and it inserts the clicked item in the same data validation cell.
Code:
'------------------ Data Validation add in same cell ---------------------
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 = 16 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
The problem im having is:
Say I have multiple items already inserted in the cell(P1), if for some reason I click the drop down arrow or activate the cell(P1) and then I click a different portion of the sheet, it takes the old value inside the cell(P1) and duplicates it, in the cell(P1) again.
So say I have:
John, Tom, Rick
it ends up with
John, Tom, Rich, John, Tom, Rick
I guess it needs a way to exit the handler (or stop the code) if it finds itself clicking away from the source cell (P1).
Hope it makes sense.
Thank you and

xluser26