CODEWITHSUNDEEP
excelvba
Pherix
Pherix

Reputation: 31

How to generate a timestamp to indicate when a change occurred?

When a cell uses a drop down list to select a value, I want an adjacent cell to have a timestamp that cannot be changed.

I have written the below function (the screenshot example is for when the drop down menu "Submitted" is selected, but I have another one for "Deleted" as well), and all seems to be working as intended until I save and close the spreadsheet. (See below screenshots)

Function to timestamp:

Function reqTimestamp(Status)
  If Status.Value = "Requested" And Not IsDate(Application.Caller) Then
    reqTimestamp = Format(Now, "dd-mm-yy hh:mm AM/PM")
  Else
    reqTimestamp = Application.Caller.Value
  End If
End Function

The setup in my Excel document:
Initial setup

The goal is when the appropriate selection is made in the drop down menu, a timestamp is created as seen here:
Final Product example

The intent in this setup is to have a document where once the status of the task changes, then a timestamp is made to indicate when the change occurred.

For example, when a request goes to "Submitted", then a timestamp is created in the appropriate cell, and then when it gets moved to a "Deleted" status, again, the timestamp is placed into the appropriate column.
Also, the cells do not need to change once the timestamp is created (to maintain integrity between the two events).

It works while the spreadsheet is open, but once you save and close it, all empty cells where the functions reside fill in with #NAME?, but the working function is still there.
I did find a workaround (sort of) by disabling the automatic calculations in the formulas section of options, but then the timestamp is only marked once the document is saved. This really doesn't work, as this is is (hopefully) going to be stored in a place where multiple people have access to it, so I am not sure what would be considered a "save" at that point.

There may not need to be a VB function written, but I do not know enough to word one.

Upvotes: 1

Views: 337

Answers (1)

Pherix
Pherix

Reputation: 31

Found the following to work, thanks to BigBen pointing me in the right direction. Here is the code that I added:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 Then
        If ActiveCell.Value = "Requested" Then
             If Not IsDate(ActiveCell.Offset(0, 1)) Then
                ActiveCell.Offset(0, 1) = Format(Now, "dd-mm-yyyy hh:mm")
             End If
        End If
        
        If ActiveCell.Value = "Deleted" Then
            If Not IsDate(ActiveCell.Offset(0, 2)) Then
                ActiveCell.Offset(0, 2) = Format(Now, "dd-mm-yyyy hh:mm")
            End If
        End If
    End If
End Sub

Upvotes: 2

Related Questions