Trying to apply 2 Worksheet Changes

[MLC1980]

Hi, this is my first port on the forum and I admit everything I know about VBA is self taught. I currently have a spreadsheet with a 'Worksheet Change' code to allow multiple dropdown selections in columns L and M. That code is working perfectly.

My issue is that I now want to add a column to the spreadsheet where a macro automatically adds the details of when the corresponding row was 'Last Updated' (date updated and who by). I found a code which looks like it should work but it is also a 'Worksheet Change' and I don't know how to combine the two, can anyone help?

The code that I currently have for the multiple dropdown selections is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("L2:M5000")) Is Nothing 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

The code that I found for the 'Last Updated' column is as follows (although I do also want it to include the application.username if that's possible):
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("$A:$V"), Target) Is Nothing Then
Range("W" & Target.Row).Value = Date
End If
End Sub

Thanks

 

[Joe4]

Welcome to the Board!

You cannot multiple Worksheet_Change procedures in the same module, so you need to combine all your code under one Worksheet_Change procedure (just have different blocks of code). The key is, you do NOT want anything in the first block of code that will exit the code before getting to the second block.

I tried updating and re-arranging your code. See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String
    Dim Newvalue As String

    On Error GoTo Exitsub

'***BLOCK 1***
If Not Application.Intersect(Range("$A:$V"), Target) Is Nothing Then
    Application.EnableEvents = False
    Range("W" & Target.Row).Value = Date
End If

'***BLOCK 2***
    If Not Intersect(Target, Range("L2:M5000")) Is Nothing Then
        If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            If Target.Value <> "" Then
                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
    End If


Exitsub:
    Application.EnableEvents = True
    
End Sub

Also note: When posting your code, please use the Code tags, like I did. It maintains spacing and adds colors, making your code much easier to read.
See here for details on how to do that (it is very easy): How to Post Your VBA Code