Hi I have a following issue, I am using a VBA code as per below, which allows me to select multiple options from the drop down list. However the problem is if I make a mistake in selection, I cannot partially delete. When I try to partially delete the selection, after deleting and pressing enter, the cell auto populates additional numbers randomly. Only way to clear at the moment is to delete all selection or select empty cell option from drop down list. This is can become quite an issue if I need to select up to say 15 items from the list and then end up making mistake and have to start over. Is there a way to allow partial deletion up to i.e. if I have selected 1, 3, 25, 36 - I can just delete up to the coma ie. 1, 3, 25 and continue selecting again?
Thank you
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Thank you