I'm using a VBA code for a dropdown list with multiple options in Excel, but when I enter text manually in a cell that already contains one or more selected options, the code duplicates the previous selections and then adding the new text. For example, if I select "Option 1" and "Option 2" from the dropdown list, the cell displays "Option 1, Option 2". However, if I then type "text" in the same cell and click on another cell, the cell displays "Option 1, Option 2, Option 1, Option 2, text".
I'd like to modify the code to prevent this behavior and maintaining the selections Option 1, Option 2 with the new text when the cell is edited manually. Here's the current code I'm using:
Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", " If Destination.Count > 1 Then Exit Sub On Error Resume Next Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError If rngDropdown Is Nothing Then GoTo exitError 'Check if the cell has data validation or not If Destination.Validation.Type = xlValidateList Then If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) Or _ InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If End If End If End If End If exitError: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub
In respect to the forum rules, I wanted to mention that I also posted the same question in another forum a few days ago, but have not received an answer there. Here is the link to that post: Prevent VBA from duplicating dropdown list selections when entering manual text. (excelforum.com).
Can anyone help me modify this code to achieve the desired behavior? Thanks in advance for your help!