Jul 08 2020 01:26 AM
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.
Jul 08 2020 01:38 AM
If you are doing this with VBA, try ClearContents method instead of Clear like...
Range("A1:A10").ClearContents
Jul 08 2020 04:45 AM
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
Jul 08 2020 04:59 AM
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.
Jul 10 2020 08:30 AM
It worked perfectly! Thank you very much. I really appreciate it.
Cheers!
Mario
Jul 10 2020 09:52 AM - edited Jul 10 2020 09:54 AM
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