VBA doesn't work for multiple data validation entries

[halesowenmum]

Hi all.

Got this VBA which is supposed to enable the adding and removing of more than one name from within the same cell. It's supposed to behave that if you click the first name it puts it in, if you then click a second different name, it puts that in too, if you come back next week and select the second name again, it would remove it. Except it's just adding a single name and I'm not sure why. It's Col H which is Col 8 as I understand it. The list of names is on another worksheet in this workbook and is referred to in Col H using a List dropdown which picks up those names. This is the code - can anyone see what might be the issue? I'm on Win 10 O365 with Excel 2016:

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        lUsed = InStr(1, oldVal, newVal)
        If lUsed > 0 Then
            If Right(oldVal, Len(newVal)) = newVal Then
                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
            Else
                Target.Value = Replace(oldVal, newVal & ", ", "")
            End If
        Else
            Target.Value = oldVal _
              & ", " & newVal
        End If
        
      End If
    End If
  End If
End If
exitHandler:
  Application.EnableEvents = True
End Sub

Any thoughts of what I can check? This is an inherited RAID Log and there are multiple, multiple instances of different VBA modules and I wonder if one of those is cancelling out this one (?). See, I changed the 'Column' bit to 8 but when I've pasted this in, it's 3!

 

[RoryA]

It sounds like you perhaps changed the wrong instance of the code then. Does it say 3 or 8 in your actual sheet code?

Also, it's been 9 years - please use code tags when posting code...

 

[halesowenmum]

What's been 9 years?

But why would there be multiple instances....? I added it once and changed it to 8 but when I looked in the worksheet via View Code, I saw multiple modules, some which appear to be 'the same' but without the change to 8, and others which are other VBA - it's a devil of a job if I've got to review every one to see if its different and then find how many are 'the same' and try and delete them however how do you delete a module - I'm not actually sure what the correct way to delete on is so it would be helpful to understand that. Thank you.

 

[RoryA]

You've been a member here for 9 years, so you ought to know to add code tags.

When you choose View Code by right-clicking a worksheet tab, it will take you to the code module for that tab. If that's the only sheet you need this to work on, then you only need to alter that one. If you need it to work the same on every sheet, you can use a workbook level event instead. If you need it for some, but not all, sheets, you can either put the code into each sheet, or use a workbook level code and add exceptions to it, perhaps by examining the name of the sheet.