Forum Discussion
mbehring
Jul 08, 2020Copper Contributor
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...
Subodh_Tiwari_sktneer
Jul 08, 2020Silver Contributor
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 SubLet me know if you need a separate code to clear the cell content from the dropdown cells at once.
mbehring
Jul 10, 2020Copper Contributor
It worked perfectly! Thank you very much. I really appreciate it.
Cheers!
Mario
- Subodh_Tiwari_sktneerJul 10, 2020Silver Contributor
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