VBA Code Not Working On Saved Book

RebuildToday3

New Member
Joined
Feb 19, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
My VBA code used to work and I cannot get it to work again. It is only on the saved file. If I open a blank new excel sheet and insert the code it works there. I'm not sure what to do to fix it as I've tried all I know. I'm wanting to have a column of drop-down menus. These menus have multiple selections without repetition is my plan. It used to work on my saved book and doesn't now. It will only select one from the drop-down menu, not multiple, yet works on a new book.

CODE I HAVE:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' 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.Column = 5 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 & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,442
Office Version
  1. 365
Platform
  1. Windows
Is the sheet protected?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try running this code from module1.
VBA Code:
Sub reset()
Application.EnableEvents = True
End Sub

Then try the event code.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,463
Members
416,029
Latest member
CSM1

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