Issue With VBA Code Stopped Working

[RegularExcelUser]

I had used this piece of code I'd found on Youtube to allow me build a drop-down list where the user could select several options in a drop down list as opposed to just one. I had applied this to several different cells in my worksheet and it worked fine. However, it has now stopped working and I can't figure out why. I did originally have the Target.Address set as specific cell R1C1 references, but decided to name the cells as I have other script running on the same page that inserts rows based on clicking a command button, and another that hides rows based on selecting certain dropdown options. I thought it might be something to do with some of the other code, so I deleted those out entirely in a copy of my file to see if that would work. Unfortunately, that hasn't done the trick either. Appreciate it if someone could take a look and suggest why it might not be working, other changes I could make that might make it work or even a whole new code if you think there's a better way to do it.

Private Sub Worksheet_Change3(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "Badges" Or Target.Address = "Placeholder_Badges" Or Target.Address = "Delivery_Days" Or Target.Address = "Select_By_Country" Or Target.Address = "Select_By_Region" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Joe4]

Is this code supposed to run automatically? As it is is written, it won't, because it appears that you changed the name.

There is an event procedure named "Worksheet_Change" that runs automatically when a cell is manually updated if the code is placed in the Sheet module.
However, there are very strict rules around it, one being that it MUST be named "Worksheet_Change". That is non-negotiable.

It looks like you have named this one "Worksheet_Change3". That won't work, if you want it to run automatically. Once you change the name, it no longer fires automatically. You would have to run this code manually (or call it from somewhere else), and feed in the range you want it to run against.

 

[RegularExcelUser]

Thanks for the reply Joe4, much appreciated. I did indeed change the name, because I already have an entirely different piece of code that does something else called Worksheet_Change (it forces a data entry format on a particular cell). For both this and that other piece of code, I want both of them to run automatically as opposed to being triggered. Should I merge the 2 pieces of code within one routine, or how can I accomplish this? Apologies if these are VBA101 type questions, but unfortunately I'm VBA0.001 level!

 

[Joe4]

Should I merge the 2 pieces of code within one routine, or how can I accomplish this?

Yes, that is exactly what you should do, due to the following reasons:
1. If you want the code to fire automatically when a cell is automatically triggered, it MUST be in a procedure names EXACTLY "Worksheet_Change" in the proper sheet module in VBA.
2. Each procedure within a single module MUST have a unique name (you cannot have two different procedures named "Worksheet_Change" in the same VBA module).

Typically, you would just stack the code within the procerdure, i.e. structured something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    'First block of code

    'Second block of code

End Sub

You just have to make sure that you don't have an "Exit Sub" line in your first block of code that would kick you out of the procedure before you ever get to the second block.

 

[RegularExcelUser]

I gave that a go but can't seem to get it to work. The 2 distinct pieces of code are

Private Sub Worksheet_Change3(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "Badges" Or Target.Address = "Placeholder_Badges" Or Target.Address = "Delivery_Days" Or Target.Address = "Select_By_Country" Or Target.Address = "Select_By_Region" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

and

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "SKU_Format" Then
Application.EnableEvents = False
Target.Value = LCase(Target.Value)
Target.Value = Application.WorksheetFunction.Substitute(Target.Value, "-", "")
Target.Value = Mid(Target.Value, 1, 1) & "-" & Mid(Target.Value, 2, 3) & "-" & Mid(Target.Value, 5, 4) & "-" & Mid(Target.Value, 9, 2) & "-" & Mid(Target.Value, 11, 2) & "-" & Mid(Target.Value, 13, 4)
Application.EnableEvents = True
End If
End

I have merged and adjusted to create

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "SKU_Format" Then
Application.EnableEvents = False
Target.Value = LCase(Target.Value)
Target.Value = Application.WorksheetFunction.Substitute(Target.Value, "-", "")
Target.Value = Mid(Target.Value, 1, 1) & "-" & Mid(Target.Value, 2, 3) & "-" & Mid(Target.Value, 5, 4) & "-" & Mid(Target.Value, 9, 2) & "-" & Mid(Target.Value, 11, 2) & "-" & Mid(Target.Value, 13, 4)
Application.EnableEvents = True
End If

Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "Badges" Or Target.Address = "Placeholder_Badges" Or Target.Address = "Delivery_Days" Or Target.Address = "Select_By_Country" Or Target.Address = "Select_By_Region" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Have I missed something here, the first piece of the code seems to work fine, it's just the second bit that's failing (or rather not operating). Just hazarding a guess here, but is it something to do with the (ByVal Target As Range)?

 

[Joe4]

Try adding break point near the top of your combined code, as shown here: Debugging VBA Code: Adding Breakpoints

Then, initiate a change that should trigger the second part of your macro. Then use the F8 to advance through your VBA code one line at a time, and watch what happens.
You can then see exactly what it is doing. Often, the issue becomes quite apparent when you can see exactly how it goes through your code.

 

[Fluff]

This line is wrong

If Target.Address = "Badges" Or Target.Address = "Placeholder_Badges" Or Target.Address = "Delivery_Days" Or Target.Address = "Select_By_Country" Or Target.Address = "Select_By_Region" Then

Are those named ranges? If so are they a single cell each, or multiple cells?

 

[RegularExcelUser]

will not go through the VBA code, keep on getting the classic Windows "ding" anytime I try to F8 the code. Doesn't matter where I insert the breakpoint either.

 

[RegularExcelUser]

This line is wrong

If Target.Address = "Badges" Or Target.Address = "Placeholder_Badges" Or Target.Address = "Delivery_Days" Or Target.Address = "Select_By_Country" Or Target.Address = "Select_By_Region" Then

Are those named ranges? If so are they a single cell each, or multiple cells?

They are a a single cell each. I originally had R1C1 references there, but I've another piece of code that inserts rows above these so it would move them. Therefore I had to change them to named cells.

 

[Fluff]

In that case it needs to be like

If Target.Address = Range("Badges").address Or Target.Address = Range("Placeholder_Badges").Address ...