Forum Discussion

mbehring's avatar
mbehring
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 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.

excel
Excel on Mac
Macros and VBA

5 Replies

    • mbehring's avatar
      mbehring
      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's avatar
        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.

Resources