CODEWITHSUNDEEP
excelvbavalidation
Andrea
Andrea

Reputation: 115

excel data validation multiple selection within the same celle separated by comma

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:

List Data Validation Result
Mango Apple, Mango, Pixel
Iphone Pixel, Apple
Pixel
Apple Apple, Mango
Mango Apple, Mango, Pixel
Iphone Pixel, Apple
Pixel

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
List Data Validation Result
Mango Apple, Mango, Pixel,

with the final comma

  1. 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?

Upvotes: 1

Views: 894

Answers (1)

Toddleson
Toddleson

Reputation: 4457

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.

Upvotes: 1

Related Questions