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

Auto timestamp

0
  • Hi,
  • I saw this video " Automatically Timestamp Entries in Excel Edit Add to Favorites
  • "

  • And i learned about a macro.
  • But what If i have multiple values and I want to set a date change for each value respectively,i have "shipment date", "delivery date", "invoice date", and I want to know whenever the status of an order change at each step, time change will be updated in different columns for SURE.. 
Answer
Discuss

Answers

0

Hi Moun and welcome to the Forum

Here's one way to do it...

In the attached file, cells A4:A23 contain an imaginary 5-digit order number (custom formatted to show leading zeroes) and B4:B23 show the status of each order. Change a value in one of those yellow cells and the event macro below will add a timestamp to the appropriate row and column. I've added comments so you can see what's happening:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Based on tutorial from TeachExcel.com

Dim Status As Range
Dim Datestamp As Range

'Your data table range
Set Status = Range("B4:B23")

'Check if the changed cell is in the Status column or not.
If IsEmpty(Target) Or Intersect(Target, Status) Is Nothing Then Exit Sub

' Check that only one cell was changed (or quit)
If Target.Count > 1 Then
    MsgBox "Please change only one cell at a time (no datestamps were added)"
    Exit Sub
End If

'Stop events from running
Application.EnableEvents = False

'Determine cell / column for the date/time
Set Datestamp = Cells(Target.Row, Status.Column + Left(Target.Value, 1))

' Add the date/time
Datestamp.Value = Now

'Turn events back on
Application.EnableEvents = True

End Sub

It works as follows: B4:B23 have data validation so you can only pick a value from the dropdown or delete contents to make it a blank. The dropdown is based on the values in hidden cells C2:F2 which have a numbered 5 stage process like 1. Ordered, 2. Shipped, 3. Delivered, 4. Invoiced and 5. Paid (to match the corresponding date headings in C3:F3).

If a yellow cell is changed, the macro uses the VBA Left string function to strip out the number (e.g. 2 from 2. Shipped) then adds the timetamp that many columns to the right of the yellow. cell.

Hope this makes sense and works for you (in which case please don't forget to mark this Answer selected).

In proctice, you'd probably want to protect the sheet (e.g. so the timestamps can't be overwritten accidentally) but that's a separate question.

Discuss

Discussion

Moun, Did you see my Answer? Seems email alerting stopped working for a bit.
John_Ru (rep: 3222) May 19, '22 at 9:26 am
Okay, I give up- why do I bother to answer questions?
John_Ru (rep: 3222) May 23, '22 at 2:39 pm
Add to Discussion


Answer the Question

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