How would I automatically update other columns based on user input in Excel?-CodePudding

I am trying to automate column updating in Excel. What I'm trying to do is when my target column is updated to "Scheduled Audit" I want a timestamp/date to automatically be captured in another column. Also at the same time I want another column to be updated to "Issue Audit Agenda"

Below is what I have so far, and I'm trying to move away from target.offset because I might add and move around columns in the future. The application.enableEvents is there because I don't want this date auto-updating. any help would be appreciated.

If Target.Column = 11 And Target.Value "Scheduled Audit" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now, "mm/dd/yyyy HH:mm:ss")
Application.EnableEvents = True
End If

CodePudding user response：

If you want to hard-code the output columns:

If Not Intersect(Target, Me.Columns(11)) Is Nothing Then
    If Target.Cells(1).Value = "Scheduled Audit" Then
       Me.Cells(Target.Row, "AU").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
       Me.Cells(Target.Row, "AV").Value = "Issue Audit Agenda"
    End If
End If

If you want to do a search based on column header (untested):

Private Function ColumnNumber(
    ByRef Header As String, _
    ByVal ws As Worksheet, _
) As Variant

    ColumnNumber = Application.Match(Header, ws.Rows(1), 0)
End Function


Private Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns(11)) Is Nothing Then
        If Target.Cells(1).Value = "Scheduled Audit" Then

           one rror GoTo SafeExit

           Dim col As Variant
           col = ColumnNumber("Last Action Date", Me)

           If Not IsError(col) Then
               Application.EnableEvents = False
               Me.Cells(Target.Row, "AU").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
           End If

           col = ColumnNumber("Next Action", Me)
           If Not IsError(col) Then
               Application.EnableEvents = False
               Me.Cells(Target.Row, "AV").Value = "Issue Audit Agenda"
           End If
        End If
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

CodePudding user response：

This should work. Edit the header search parameters.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim TSrow As Integer
    Dim TScol As Integer
    'On Error Resume Next
    If Selection.Cells.Count = 1 Then
        If Target.Column = 11 And Target.Value = "Scheduled Audit" And Target.Row > 1 Then
            TSrow = Target.Row
            TScol = WorksheetFunction.Match("Time Stamp", Range("1:1"), 0)
            ActiveSheet.Cells(TSrow, TScol) = Format(Now, "mm/dd/yyyy HH:mm:ss")
            TScol = WorksheetFunction.Match("Issues", Range("1:1"), 0)
            ActiveSheet.Cells(TSrow, TScol) = "issue Audit Agenda"
        End If
    End If
End Sub