VBA Code for Automatic Timestamp

**Mhace** · 05-28-2013, 09:38 PM

Hi All,

I just want to ask for a VBA Code for Automatic Timestamp with this format hh:mm am/pm.

For example I input something in Column A then a timestamp in Column B appears and also if I put something in Column C a timestamp will also appear in Column D.

Any Idea out there?

Thanks a lot.

**stanleydgromjr** · 05-28-2013, 10:16 PM

Mhace,

You can adjust the row ranges to accommodate your needs.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then make a change to the cells in Range("A1:A1000"), and, in Range("C1:C1000")

**Mhace** · 05-29-2013, 02:18 AM

Hi Stan,

Thanks a lot for this.

This really works. But I tried sharing the File so that other user can use it even if im using the file but it doesn't work and it prompt an error.
Or is it possible to protect the sheet and the only working Column is column A and C so that I can enter a timestamp even if it is protected?
Is sharing or protecting the sheet possible?
Thanks again.

**stanleydgromjr** · 05-29-2013, 06:53 AM

Mhace,

Thanks for the feedback.

You are very welcome. Glad I could help.

But I tried sharing the File so that other user can use it even if im using the file but it doesn't work and it prompt an error.

Sorry, I have not done any macro programming for file sharing.

In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

**Pfin8** · 06-04-2013, 07:11 PM

You are the bomb.com Stan! Thanks!!!!! Just used it and it worked!

**stanleydgromjr** · 06-04-2013, 07:21 PM

Pfin8,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

**jacra** · 03-18-2015, 02:02 AM

Hi,

With regards to automatic timestamp, please refer to the code below;
---------------------------------------------------------------------------------------
Dim TimeStampCell As Range

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Range("C65536").End(xlUp).Offset(0, 1).Value = Now()
End Sub

Private Sub Workbook_Open()
Sheet1.Range("C65536").End(xlUp).Offset(1, 0).Value = Now()
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sheet1.Range("C65536").End(xlUp).Value = Now()
End Sub

-------------------------------------------------------------------------------------------
What the above code does is place a timestamp in cell C1 upon editing the worksheet then place another timestamp at cell D1 before the document is closed or when it is saved. Then when opened again for editing it does another timestamp in cell C2 and upon exit does another timestamp in cell D2. Basically, the objective of the code is to record the time when the sheet was used and when it was closed. The list of course will pile up on column C and column D. The problem with the code is that every changes made in the workbook, during the time it was opened, it overrides the first timestamp.

What I would like it to be is when the file is opened and upon first entry of data in any of the worksheet in the workbook, there will be a timestamp (let's call this timestamp "entry stamp") at cell C1. Then, after usage of the workbook, upon exit, it will do another timestamp (let's call this "exit stamp") at cell D2). Now, when the file is opened again for use, the next entry stamp will be at the following row C2 and same with exit stamp at D2. The intent is there will be no override of entry stamp.

Another addition to this is that can we use a timer for idle time. let's say, from entry stamp, the user have been idle for about 5 mins and with this I would like the workbook to auto exit stamp then have a message box which asks "do you wish to continue?". If the answer is yes, then it will enter a new timestamp on the next row of column C then same is done upon exit for exit stamp. If the answer is no, then the workbook or worksheet will automatically close.