Can anyone find the error in this code? I've inputted it to the developer, but it doesn't seem to be applying to the cells.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Me.Protect UserInterfaceOnly:=True Application.EnableEvents = True On Error GoTo Exitsub If Target.Column = 15 Or Target.Column = 25 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
Did you install the code in the code pane of a worksheet? It won't work if you put it in ThisWorkbook or in a regular module sheet. I doubleclicked Sheet1 in the screenshot below and put the code there--where it is working for me.
@Ingeborg Hawighorst I have a column which indicates what services a client accepts. The column links to a drop down list on sheet 2. I am wanting to be able to select multiple items from the drop down list.
@adavis726 That does not really help troubleshoot the problem. If you expect the code to do something, but it doesn't do what you expect, you can step through the code with F8 to see which IF branch is executed. Then you know where things go "wrong" and you can inspect the value that is the condition of the IF branch.
Maybe someone inserted a column. That would cause the macro to fail, because it is now watching the wrong columns.
That sort of thing.
If you can't do these checks yourself, you will need to upload a sample file that shows the problem.