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:
CODE I HAVE:
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