Timestamp depending on certain parameters in a cell

[MattHaynes]

I have a table that has a dropdown in cell E6 for "Stage 1" "Stage 2" and "Stage 3" on each selection in cell G6 there is a timestamp that changes every time another option is selected.

I then have a audit trail sheet with the following VBA code.

Option Explicit

Dim wsLOE As Worksheet, wsTrail As Worksheet
Dim PreviousValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Set wsTrail = ThisWorkbook.Worksheets("Trail")
    
    If Cells(5, Target.Column).Value = "Stage" Or Cells(5, Target.Column).Value = "Allocated" Then

            If Target.Value <> PreviousValue Then
                Call Log_Change(Target)
            End If

    End If
End Sub


Private Sub Log_Change(ByVal Cell_Target As Range)
    
    Dim RowInsertion As Long

    RowInsertion = wsTrail.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    wsTrail.Cells(RowInsertion, "A").Value = _
        Application.UserName & " changed cell " & Cell_Target.Address & " on " & ActiveSheet.Name & " from " & PreviousValue & " to " & Cell_Target.Value _
        & " on the timeline" & " timestamped at " & Format(Now(), "dd-mm-yy hh:mm:ss")
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

Private Sub Worksheet_Deactivate()
    
    Set wsLOE = Nothing
    Set wsTrail = Nothing
    
End Sub

I am trying to have a separate sheet with a time table to show timestamps of when stage have been changed. For example when stages "Mainsheet!E6" was changed from "Stage 1" to "Stage 2" a timestamp is placed in "Timestamps!B4" then when "Mainsheet!E6" is changed from "Stage 2" to "Stage 3" a new timestamp is placed in "Timestamps!C4".

My thought was to have a formula in Timestamps!B4 that is

=IF('QB1'!H6="Stage 1",'QB1'!J6,"")

This formula works as it shows the timestamp when Stage 1 is showing but when changed to Stage 2 it disappears.


If you have any help that would be helpful thank you.


If you have any help please let me know.

 

[OaklandJim]

I have what seems to be a solution that does what you asked. Others may chime in with better ones. Sheets must be named Mainsheet and Timestamps. In Mainsheet you must add two names 1. StagesSelection and 2. PreviousStage. (In case you do not know how select dropdown cell then use Formulas => Defined Names from ribbon.)

Name StagesSelection is for the cell with the dropdown. Name PreviousStage is used to keep track of what stage was selected previously. Otherwise if user accidentally clicks on the same stage twice in a row then you'll get a timestamp for that non-change.

When creating the name PreviousStage its RefersTo field must be just an equals sign. (Excel adds "=" which is fine.)

The first sub is the change event for the Mainsheet. The second is just a regular sub, which makes the timestamp where needed. I added a line of code that puts the name of the stage selected next to the timestamp. You can kill that if not wanted.

Private Sub Worksheet_Change(ByVal Target As Range)

'   If 1. user selected a value in the dropdown and
'      2. the selection is not the same as the previous selection
    If Target = Me.Range("StagesSelection") _
       And Me.Range("StagesSelection").Value <> [PreviousStage] _
     Then

'       Put current selection into name PreviousStage for next time.
        ThisWorkbook.Names("PreviousStage").RefersTo = "=" & """" & Me.Range("StagesSelection").Value & """"
    
'       Call sub that does timestamp.
        Call DoTimeStamp
    End If

End Sub


Sub DoTimeStamp()

    Dim iLastEntryRow As Long
    
    Dim iOffsetNext As Long
    
    Dim rAnchorCell As Range
    
'   Using sheet named Timestamps
    With Worksheets("Timestamps")
    
'       Anchor cell -- first timestamp is this cell.
        Set rAnchorCell = .Range("B4")
        
'       If not the first timestamp.
        If rAnchorCell.Value <> "" _
         Then
'           Get last row for last timestamp
            iLastEntryRow = rAnchorCell.Offset(1000000).End(xlUp).Row
        
'           Get row offset from the anchor cell
            iOffsetNext = iLastEntryRow - rAnchorCell.Row + 1
                
'       If first timestamp.
        Else
            iOffsetNext = 0
        End If
        
        With rAnchorCell
        
'          Put timestamp into sheet Timestamps
            .Offset(iOffsetNext).Value = Format(Now(), "h:m:s")
            
'           Put Stage selection into cell next to timestamp.
            .Offset(iOffsetNext, 1).Value = Worksheets("Mainsheet").Range("StagesSelection")
        
        End With
        
    End With

End Sub

Also, in your events code posted the purpose for Private Sub Worksheet_SelectionChange and for Private Sub Worksheet_Deactivate() is not clear to me.