Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Specialized Timestamps


Hi all, I am new to VBA and am trying to make a macro which captures a timestamp when an edit is made. Specifically, I want to show when a specific string is entered/changed.

For example, there are 4 stages for a project: Shipped, Testing, Assemble, and Queue. When Queue is changed to Assemble, I would like to see how long it remained in the Queue state before it was edited. When the same text is changed from Assemble to Testing, I would like to see how long it remained in the Assemble state before it was edited, and so on and so forth. 

To give you an idea of the code I have now:

(Taken directly from https://www.teachexcel.com/talk/2826/troubleshooting-my-auto-timestamp-macro?nav=sim_side_col_forum with some edits)

Select All
Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
'Your data table range
Set myTableRange = Range("L2:L1500")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Column for the date/time
Set myDateTimeRange = Range("S" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("T" & Target.Row)
'Stop events from running
Application.EnableEvents = False
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
    myDateTimeRange.Value = Now
End If
'Update the updated date/time value
myUpdatedRange.Value = Now
'Update cell with username - 1 = Column A; 2 = Column B; etc.
Cells(Target.Row, 18).Value = "Last Edited By " & Application.UserName
'Turn events back on
Application.EnableEvents = True
End Sub

This works great, but only shows when an edit has been made irrespective of the specific set of words I am looking for.



As Variatus says, this is not about the macro you are using; you will need to design the actual system that tracks this, probably via a hidden worksheet you could call "changeTracking" and then manage that setup from there. And thanks for giving it a try with the code before you posted your question! :)
don (rep: 1989) Jun 22, '20 at 5:27 am
Add to Discussion


Selected Answer

As yet, your problem isn't one of coding but of presentation. Therefore I didn't look at your code but was looking for your worksheet design. That wasn't included with your question, unfortunately.

If you want to know how much time has lapsed you will need two times, start and end. If you record the times you need two columns. But you might opt to have only one column and  then record the lapse in another. You might try to record only the lapse and make do with a single column but that would give you insurmountable problems with changes the user might make to correct entry errors. The best way is to record start and end times permanently and think about a column for lapse. If you keep start and end times lapse can be calculated and re-calculated at any time. Therefore the question shifts to how you want to impart the information. Presentation.

Since you have four stages to your process you would have 4 start tiems and 4 end times, making for 8 columns in your worksheet. Depending upon how your system works, some of these times may abut. Testing may start the moment assembly is finished, or it might start a week later. This is where your worksheet design must match the system in your work flow.

Now, you might have a drop-down from which to select the stage and then record the time when that selection was made. In that case you would have a different column to assign the time stamp to, depending upon the stage shown in the drop-down. But the same thing also works the other way around. You might enter a time in particular column and adjust the drop-down to the value associated with that column. For both ways you would need to allow for change, meaning remove the time stamp for "Shipped" if the user changes the drop-down to "Testing", or the other way around, if that's the system you operate.

Once the designs of your work flow and worksheet are settled and matched to each other you can start coding. Then, if you have questions about the coding, it's unlikely that they can be answered without showing the tab on which it's all supposed to play out.


Answer the Question

You must create an account to use the forum. Create an Account or Login