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
I guess you should use the Change-Event, not SelectionChange (
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.