Forum Discussion

Michellerennolds's avatar
Michellerennolds
Copper Contributor
Aug 07, 2025

VBA multiple select no repeat dropdown not working

Hello,

I have an excel with one column as a multiple select no repeat dropdown.

It was working fine until I added a row now none of the column will allow multiple select.

I have used the data validation, selected the multiple items and added the VBA again but no luck.

This is the VBA I am using:

Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim DelimiterType As String Dim rngDropdown As Range Dim oldValue As String Dim newValue 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   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 'do nothing Else If newValue = "" Then 'do nothing Else Destination.Value = oldValue & DelimiterType & newValue ' add new value with delimiter End If End If End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

 

Any help is appreciated!

excel
Macros and VBA

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Aug 08, 2025

    First of all that code paste is a mess so I cleaned it up (so I and others can read it)

    But I don't see any problem with the code and it works in a sheet I tried. 

    I tried 'breaking' it using multiple non-continuous ranges of data validation and different types of data validation and they all worked fine.  The only thing is that the code must be on the worksheet you are working on.

    Maybe if you could supply the broken workbook (no confidential information please) or explain better what you did when you 'added a row'

     

    Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue 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
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
    'do nothing
  Else
    If newValue = "" Then
      'do nothing
    Else
      Destination.Value = oldValue & DelimiterType & newValue
      ' add new value with delimiter
    End If
  End If
End If
exitError:
  Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

     

Resources