Show date/time that cell was last modified

[Void_]

Hello.
In a table, I would like a column to show the last date/time that the calculations in the neighboring column were updated. In particular StateBuilder is a calculated column in my table. The neighboring column SB_Time should populate with the date/time whenever a cell in StateBuilder has a new result calculated.

My below code accomplishes this exactly as desired, with one problem. After the workbook is opened, the first calculation update to StateBuilder does not update SB_Time. All subsequent updates to StateBuilder successfully update SB_Time as desired. I can’t determine why the first update never works and I am seeking a solution.

If I remove the “IsEmpty(previousStates)” condition from the below code, I get a Type Mismatch error at line “previousState = previousStates(i, 1)” and I can’t determine why.

Some notes
• StateBuilder is a remotely calculated column so Worksheet_Change and Worksheet_SelectionChange events will not work for this purpose
• The table name is MasterState
• The worksheet name is UseState

I appreciate any suggestions. While it may appear otherwise, I have the comprehension of a small child. So if you are inclined to offer any suggestions or instructions, please spell them out in simple and deliberative terms.

Private previousStates As Variant ' Declare a variant variable to store the previous states

Private Sub Worksheet_Calculate()
    Dim rng As Range
    Dim currentState As Variant
    Dim i As Long
    
    Set rng = Me.Range("MasterState[StateBuilder]") ' Assuming MasterState is a table
    
    Application.EnableEvents = False ' Disable events to prevent recursive calls
    
    If IsEmpty(previousStates) Then
        ' Store the previous states for the first time
        previousStates = rng.Value
    Else
        ' Loop through the StateBuilder column
        For i = 1 To rng.Rows.Count
            currentState = rng.Cells(i, 1).Value
            previousState = previousStates(i, 1)
            
            ' Check if the current state is different from the previous state
            If currentState <> previousState Then
                ' Update the SB_Time column with the current date and time
                rng.Cells(i, 2).Value = Now()
            End If
        Next i
    End If
    
    ' Update the previous states for the next iteration
    previousStates = rng.Value
    
    Application.EnableEvents = True ' Enable events again
End Sub

 

[Strithken]

Hi there.

The previousStates variable is Empty until the first time the Worksheet_Calculate event is triggered, so you'll have to write to the previousStates variable prior to that event. This can be accomplished by using the Workbook_Open event in the Workbook object.

The below should resolve your issue. If you're interested, I can advise on some code optimization/organization as well.

1. Append the following to the bottom of the Worksheet object's VBA code:

Public Sub Write_to_PreviousStates()
    previousStates = Me.Range("MasterState[StateBuilder]").Value
End Sub

2. Add VBA code to the Workbook object (replace Sheet1 with the code name for your Worksheet object where the MasterState table resides).

Private Sub Workbook_Open()
    Call Sheet1.Write_to_PreviousStates
End Sub

 

[Void_]

Hi there.

The previousStates variable is Empty until the first time the Worksheet_Calculate event is triggered, so you'll have to write to the previousStates variable prior to that event. This can be accomplished by using the Workbook_Open event in the Workbook object.

The below should resolve your issue. If you're interested, I can advise on some code optimization/organization as well.

1. Append the following to the bottom of the Worksheet object's VBA code:

Public Sub Write_to_PreviousStates()
    previousStates = Me.Range("MasterState[StateBuilder]").Value
End Sub

2. Add VBA code to the Workbook object (replace Sheet1 with the code name for your Worksheet object where the MasterState table resides).

Private Sub Workbook_Open()
    Call Sheet1.Write_to_PreviousStates
End Sub

SOLVED!
Brilliant! Thank you! Not only did this solve the issue, your explanation was clear and easy to follow. I thought this might be my problem so I had spent a lot of time experimenting with Workbook_Open within the same worksheet’s code. I did not think to write a separate sub to write previousStates and then call it with Workbook_Open in the Workbook object. It’s not clear to me why this worked and my method didn’t but I’m happy to be able to move on.
Thank you again!