CODEWITHSUNDEEP
excelvbatimestamp
TheFullPackage
TheFullPackage

Reputation: 3

How to insert current timestamp when cell value changes based on cell content

I have a drop-down list in a column B and I want to capture timestamps of changes made to that column based on different options available in drop-down and extract that timestamp to different columns

User can choose from the list and i have a macro that inputs timestamp of any change made.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
        End If
    End If
End Sub

I am looking for a way to modify it, so that once user chooses "In-progress" timestamp is saved in column D, later that cell can be changed into "Closed" and timestamp of that should be presented in column E (respective row) but the value of Column D will stay the same.

I wanna trace timestamp of status changes.

Upvotes: 0

Views: 325

Answers (1)

Cyril
Cyril

Reputation: 6829

You can use a switch for each of your outputs to determine the column (c), based on your change event, such that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
    dim r as long, c as long
    r = target.row
    select case lcase(target.value)
        case "in-progess"
            c = 4
        case "closed"
            c = 5
        case else
            c = 0
    end select
    if c > 0 then cells(r,c).value = now()
end sub

untested code

Upvotes: 1

Related Questions