Multiple Validation selections not allowing single selection

[rob1987]

Hello Everyone,

I have the following code for my Excel spreadsheet which is intended to allow multiple selections in a cell from a data validation list. The code works if I select two or more of the options in the list but if I only need to select one then nothing appears in the cell.

The code I have used is:

Private Sub Worksheet_SelectionChange(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
    Else
        If newVal = "" Then
        Else
        Target.Value = oldVal & ", " & newVal
        End If
    End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

Can anybody please help suggest how I can change this to allow me to select one or more options from the validation list rather than always needing to have multiple selections.

Thank you.

 

[RoryA]

Your code runs when you select a different cell, not when you change the cell contents, so I don't really see how it would ever work reliably.

 

[rob1987]

Thank you, can you recommend another way of doing it at all please?

 

[RoryA]

Sorry, it should just be:

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
    Else
        If newVal = "" Then
        Else
        Target.Value = oldVal & ", " & newVal
        End If
    End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

 

[rob1987]

That's brilliant, thank you very much, I really appreciate your help