VBA Code for generating time stamps in different cells based on value in one cell

Victor Lopez 41 Reputation points
2022-06-29T19:57:07.877+00:00

So I have a work sheet with a column that tracks when an item goes from one department to another, What I want to do is that when it reaches specific departments it does a time stamp in the respective referenced column (I.e. If the name "In Process" is selected from the drop down in column 16 then column 19 will stamp the time, if "Accepted" is selected in the drop down in column 16 then column 20 will stamp the time) the idea is to be able to track how long it took for it to go from one status/department to another. I have the following code that stamps the time for column 16's status being set at "In Process" and it stamps the time in column 19, but I'm not sure how to expand on it for other options like "accept" stamping column 20.

Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 16
xTimeColumn = 19
xRow = Target.Row
xCol = Target.Column
If Target.Text = "In Process" 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

JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
446 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
1,964 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,342 questions
No comments
{count} votes

Accepted answer
  1. OssieMac 411 Reputation points
    2022-07-02T01:12:58.29+00:00

    Following is untested code but the example is how I would handle multiple scenarios by using the Select Case method

    Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    Dim xDPRg, xRg As Range

    xCellColumn = 16  
    xTimeColumn = 19  
    xRow = Target.Row  
    xCol = Target.Column  
    Select Case Target.Text  
        Case "In Process"  
            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  
        Case "Accept"  
            'Code here for Accept  
              
        Case "other"    'Example only for adding additional cases if required  
          
        Case Else  
            'code here if none of the above cases. (Not essential. Only use if required  
          
    End Select  
          
    

    End Sub


0 additional answers

Sort by: Most helpful