VBA doesn't work for multiple data validation entries

halesowenmum

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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,488
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
  1. 365
Platform
  1. 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
36,488
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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,127,843
Messages
5,627,208
Members
416,229
Latest member
mohammadmihdi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top