How to enter multiple timestamps in a single sheet?

Hi All,

In the attached excel file, below VBA code is entered for timestamp.

Please Login or Register  to view this content.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRInt As Integer
Dim xDStr As String
Dim xFStr As String
On Error Resume Next
xDStr = "A" 'Data Column
xFStr = "B" 'Timstamp Column
If (Not Application.Intersect(Me.Range(xDStr & ":" & xDStr), Target) Is Nothing) Then
xRInt = Target.Row
Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss AM/PM")
End If
End Sub

Please Login or Register  to view this content.

Column B enters timestamp based on entries in Column A. Similarly, i want timestamp to be entered in column D only if the state in Column C is Closed Complete, Closed Incomplete or Closed Skipped.


Also, the timestamp should be applicable only to the column range starting from B2 and D2 respectively because the first row contains headings.

Can someone please help me with the code?

Thanks in advance.

Not sure how you really want to happen.

Please Login or Register  to view this content.

Thank you for your response Jindon.

What you have understood is correct.

However, the code isn't working well. Timestamp is being noted only for initial entry (in any cell) after opening the file. Later when i try to change the value in Column A and C, the timestamps remain same in Column B and D and are not being updated.

I have to close excel and restart for it to work. Still, only the first entry (in any cell) timestamp is noted and not the others.

Can you look into it once?

OOps.. of course.

Please Login or Register  to view this content.

Needs to be

Please Login or Register  to view this content.

Hi Jindon,

I applied the revised code in the sheet and it appeared to work fine. However, after 5 minutes, no timestamp is being recorded.

Can you check the attached excel file once if I made any mistake in the code?

Also, the timestamp remained in columns B and D even if i deleted the contents in cells of Column A and C. Is there something you can do such that if there are no values in Column A and C, then the Columns B and D should also be blank and no timestamp should be entered.

Thanks again.

If you delete the contents in col.C, it should clear the time stamp if there is.

If you also want to clear Col.B when you delete the contents in Col.A then add one line in bold

Please Login or Register  to view this content.

However, after 5 minutes, no timestamp is being recorded.

Can not replicate this issue.

Yeah its working now .

2 questions Jindon.

1. Along with row 1, how can i exclude rows 2 &3?

2. Can i use the same code in another sheet of the same workbook?

If you want to use the same code to multiple worksheets then use ThisWorkbook_SheetChange event.

Delete current code in Sheet1.
To ThisWorkbook code module

Please Login or Register  to view this content.

Ok Jindon, but what if the columns are different in both the sheets.

For example, in the second sheet of the attached file, timestamp is to captured in columns E, O, S and X.

However, timestamp in column S is to be captured only if Status in column R is "Completed"

Similarly, timestamp in column X is to be captured only if Status in column W is "Closed".

Note: Timestamp for columns in sheet 1 (Tickets) to remain same i.e. column B, D and F.

Many thanks in advance.

Now you have working code for Sheet1, not ThisWorkbook code module.
Why don't you try that for yourself?
Just adjusting target column & output collumn refernce.

In the attached file I tried the code provided by you for multiple worksheets To ThisWorkbook code module and modified the sheet names.

However, for both the sheets its selecting the same columns for timestamp.

May i know where exactly shall i enter the timestamp target column (E,O,S,X) for the second sheet.

( timestamp in column S is to be captured only if Status in column R is "Completed" and timestamp in column X is to be captured only if Status in column W is "Closed" )

Also as mentioned earlier, i already have two codes in ThisWorkbook. Can i paste this one below those two codes?

Sorry i only know the basics of excel and am not at all aware of VBA codes and therefore unable to try most of the things myself.

Your assistance is highly appreciated. Thank you.

No benefit of using ThisWorkbook event code.

Working with absolute perfection. Thank you so much Jindon.


A few queries:


If the sheet is locked, then i get the below error while capturing timestamp:

Run-time error '1004'

Any solution for the codes to run on a protected cell and protected sheet?

(Timestamp cells are locked and data entry cells are unlocked when the sheet is protected)



Also since the time i entered these two codes on the two sheets, there's a lag of 1-2 seconds in displaying the timestamp. Is excel getting slow because i have entered codes in both the sheets?



Also, if any error occurs, i have to close and restart excel for the codes to work again. Is this normal?


Thanks again for your great help.

Why are you asking one by one?

Upload your workbook that you are working with, otherwise I need to GUESS too much.

There is no worksheet protected...

1) Tickets sheet
Replace the code with

Please Login or Register  to view this content.

2) DM Records sheet

Please Login or Register  to view this content.

The reason why the sheet is slow is that formula takes time to calculate as well as conditional formattings...

If you use vba, everything should be done by code.

Insert bold line in each procedure

Please Login or Register  to view this content.

I wonder why sheets are locking automatically after few actions such as copy paste etc.

Rest all going perfect. You are a true troubleshooter Jindon.

Thanks a ton.