Home > other >  How to trigger more than one target value simultaneously in vba
How to trigger more than one target value simultaneously in vba

Time:12-29

I have a code that is triggered by user entry in column K and P: If any value is inserted in "K" then offset to corresponding cell in column R and fills the username. If any value is inserted in "P" then offset to corresponding cell in column Q and fills the data using TODAY().

My code looks like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserName As String
UserName = Environ("username")

    If Target.Column <> 11 Then Exit Sub
    Target.Offset(, 7).Value = IIf(Target.Value = "", "", UserName)
   
    If Target.Column <> 16 Then Exit Sub
    Target.Offset(, 1).Value = IIf(Target.Value = "", "", Now)


End Sub

The problem is that it is only working with the first IF, how can I get it to work with both?

Thank you in advance!!

CodePudding user response:

Test each cell in the target range.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
    Application.EnableEvents = False
    For Each cell In Target.Cells
        If cell.Column = 11 Then
            cell.Offset(, 7).Value = IIf(cell.Value = "", "", Environ("username"))
        ElseIf cell.Column = 16 Then
            cell.Offset(, 1).Value = IIf(cell.Value = "", "", Now)
        End If
    Next
    Application.EnableEvents = True

End Sub
  • Related