VBA - Restrict Code to One Column for Multi-choice Dropdown

[welshraz]

Hello!

I have this code which works, but I would like to restrict it to just one column - is this possible? It would be massively helpful to prevent users from doing them same in additional columns. Thanks!

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
 
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
 
exitError:
  Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
End Sub

 

[MikeVol]

Hi @welshraz.
Edit this line:

If Destination.Count > 1 Then Exit Sub

with:

If Destination.Count > 1 Or Destination.Column <> 1 Then Exit Sub

Now the macro will only run when there is a change in column A (or another column if you specify a different number in Destination.Column). Replace 1 with the number of the desired column to configure for the desired column.

 

[welshraz]

Hi @welshraz.
Edit this line:

If Destination.Count > 1 Then Exit Sub

with:

If Destination.Count > 1 Or Destination.Column <> 1 Then Exit Sub

Now the macro will only run when there is a change in column A (or another column if you specify a different number in Destination.Column). Replace 1 with the number of the desired column to configure for the desired column.

Thanks. This works perfectly, except now the powers that be want to restrict it to two columns....can this be done, or do I need to go back to my original solution?