How to clean content from a cell with dropdown list

[mbehring]

Hello all,

I've created a dropdown list for a group of cells in a column and I am using a VBA code so the user can select more than one item from the list. It's working fine.

The problem is that once an item or items are selected, it's not possible to clear the selection or change or deselect any item... and it's not possible to clear the cell content either.

The only way I've found to clear the cell content is actually using the "CLEAR ALL" button on the DATA Validation dialog window, which is not what I want. I just want to be able to clear the content so that the user can select items again if he/she wants.

Any help is appreciated.

Thanks.

[#2]

@mbehring 

If you are doing this with VBA, try ClearContents method instead of Clear like...

Range("A1:A10").ClearContents

[#3]

@Subodh_Tiwari_sktneer 

Thank you for your quick answer. However, where should I put this code line you've sent? I am not proficient in VBA. This is the code I am using to be able to select more than 1 item from the dropdown list:

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20180510
Dim xRgVal As Range
Dim xStrNew As String
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
xStrNew = Target.Value
Application.Undo
xStrNew = xStrNew & " " & Target.Value
Target.Value = xStrNew
Application.EnableEvents = True
End Sub

Thanks.

Mario

[#4]

@mbehring 

Please replace your code with the following code and you would be able to delete the cell content using the Delete key from the keyboard.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgVal As Range
Dim xStrNew As String
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
If Target <> "" Then
    Application.EnableEvents = False
    xStrNew = Target.Value
    Application.Undo
    xStrNew = xStrNew & " " & Target.Value
    Target.Value = xStrNew
    Application.EnableEvents = True
End If
End Sub

Let me know if you need a separate code to clear the cell content from the dropdown cells at once.

[#5]

@Subodh_Tiwari_sktneer 

It worked perfectly! Thank you very much. I really appreciate it.

Cheers!

Mario

[#6]

@mbehring 

You're welcome Mario! Glad it worked as desired.

Please take a minute to accept the post with the proposed answer as a Best Response in order to mark your question as Solved.

Regards,

Subodh