I have an excel spreadsheet where I need to insert a data validation from a list, so far not a problem but I need to be able to select multiple entries without overwriting the previous as the normal data validation so the final result would be this:

I have found online a VBA code to insert in my spreadsheet to obatin the multiple selection without repetion:

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20180510
    Dim I As Integer
    Dim xRgVal As Range
    Dim xStrNew As String
    Dim xStrOld As String
    Dim xFlag As Boolean
    Dim xArr
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    xFlag = True
    xStrNew = " " & Target.Value & ","
    Application.Undo
    xStrOld = Target.Value
    If InStr(1, xStrOld, xStrNew) = 0 Then
        xStrNew = xStrNew & xStrOld & ""
    Else
        xStrNew = xStrOld
    End If
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

It kinda works but I have 2 problems:

1. I can select multiple choices from my data but the result is this

with the final comma

2. I cannot delete or empty the field if I make the wrong selection, I need to use the Erase all function on that cell and then use the dropdown function to re-extend the data validation field from the empty cells not completed so far

I'm not familiar with VBA so any help is appreciated.

I mainly use R and SQL this is a task that I need to do for another person in my office that is going to use this spreadsheet and need to use this function with the lowest difficulty.

Any suggestions?

CodePudding user response：

I have modified the code to add the space and comma only if it actually needs to join 2 strings together. So the first value does not have a comma attached until a second value is also selected.

I have also modified it to allow cells to be cleared. Pressing Delete will now properly allow the user to clear a cell.

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20180510
    Dim I As Integer
    Dim xRgVal As Range
    Dim xStrNew As String
    Dim xStrOld As String
    Dim xFlag As Boolean
    Dim xArr
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    xFlag = True
    xStrNew = Target.Value
    Application.Undo
    xStrOld = Target.Value
    If xStrNew <> "" Then
        If InStr(1, xStrOld, xStrNew) = 0 Then
            xStrNew = xStrNew & IIf(xStrOld <> "", ", " & xStrOld, "")
        Else
            xStrNew = xStrOld
        End If
    End If
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

I left it, in-case it is being used in code that was not copied to this post, but xArr & I are declared but not used. xFlag is declared and set True but not used in any expression.