Forum Discussion

Copper Contributor

Jul 08, 2020

How to clean content from a cell with dropdown list

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 t...

Copper Contributor

Jul 08, 2020

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

Subodh_Tiwari_sktneer

Silver Contributor

Jul 08, 2020

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.

mbehring
Copper Contributor
Jul 10, 2020
Subodh_Tiwari_sktneer

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

Cheers!
Mario
- Subodh_Tiwari_sktneer
  Silver Contributor
  Jul 10, 2020
  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