[SOLVED] Trigger Worksheet_SelectionChange after change in certain column

Trigger Worksheet_SelectionChange after change in certain column

I want Private Sub Worksheet_SelectionChange to trigger after entering a value in a cell within column E. However, after pressing Enter, I need to click back on the cell for the macro to execute.

I have attempted If Not Intersect along with Application.EnableEvents to trigger my macro.

This is the code in VBA, I am getting the value in column E, once it has the same value as column A, it will fill in timestamp for that row.

Sub recordTimeStamp()

' Declare variables
Dim Barcode As String
Dim CartNo As String
Dim Row As Long
Dim Timestamp As String

' Get the barcode from the scanner
Barcode = ActiveSheet.Range("E" & Application.ActiveCell.Row).Value

' Get the current timestamp
Timestamp = Format(Now, "yyyy-MM-dd hh:mm AM/PM")

' Loop through all the rows in column A
For Row = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

' If the barcode matches the cart number in a row
If Barcode = ActiveSheet.Cells(Row, 1).Value Then
    
    ' If the particular row of column E is not empty, fill in 
column E else column F
    If IsEmpty(ActiveSheet.Cells(Row, 5).Value) Then
        ' Fill in the timestamp in column E
        ActiveSheet.Cells(Row, 5).Value = Timestamp
    Else
        ' Fill in the timestamp in column F
        ActiveSheet.Cells(Row, 6) = Timestamp
    End If
    
    ' Remove the value in the dynamic (selected) row in column E
    ActiveCell.ClearContents
    
    ' Select the row with the timestamp
    ActiveSheet.Cells(Row, 1).Select
    
    ' Exit the loop
    Exit For

End If

Next Row
End Sub

This is where I call it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    recordTimeStamp
End Sub

Solution

I guess you should use the Change-Event, not SelectionChange (Worksheet_Change).

For example, when you enter something in Cell E1 and press Tab, Focus will be set to F1. The Change-Event will have target set to E1 (because cell E1 was changed). In this trigger you can react on the changed value of this cell.

After this event, the SelectionChange-Event will be triggered, but target will be F1 because now this cell has the focus. In this trigger, you neither know which cell had the focus before nor if or if not something was changed in that cell.