VBA doesn't work for multiple data validation entries

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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:
Rich (BB code):
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!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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... ;)
 
Last edited:

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,774
Messages
5,470,702
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top