VBA Timestamp

[Jacra]

Hi,

Good day.

Could anyone here provide a code that will automatically create a timestamp (date:time) on a specific cell on the cue of cell input then automatically timestamp on the next cell upon saving a document. timestamp for input will only be done once. then timestamp before exit will always consider the last save done before exit. which means every save, it will always update the timestamp before exit.

Hope I have explained it well enough.

Regards,
Jacra

 

[mikerickson]

You want one cell that will record the date and time when any cell is changed and whenever the file is saved.

If so, put =NOW() in a cell.

 

[Jacra]

You want one cell that will record the date and time when any cell is changed and whenever the file is saved.

If so, put =NOW() in a cell.

Hi,

that's why the threads name is Vba timestamp. Meaning im looking for a vba. I know the simple. I need the vba to automatically record or monitor the usage of the template im going to use.

 

[Stiuart_W]

You need to be a bit clearer about your requirements:-

1) What do you mean by "Next cell"? Is this the cell to the right of the cell in question, below, above or to the left?
2) It's not clear when you want this stamp to occur - Do you want the time stamp only when the cell in question is changed or do you want the timestamp upon saving the cell?

Regards

 

[Jacra]

You need to be a bit clearer about your requirements:-

1) What do you mean by "Next cell"? Is this the cell to the right of the cell in question, below, above or to the left?
2) It's not clear when you want this stamp to occur - Do you want the time stamp only when the cell in question is changed or do you want the timestamp upon saving the cell?

Regards

A. B. C. D.

1. Input data. Timestamp start. Timestamp (upon saving file)

2

3

4


In the above sample. Once a worksheet is being used or a data is entered it will auto timestamp on C1 then upon saving it will time stamp on D1. So once this document is being used again, next timestamp will be on C2 then when save it will be at D2. So only at column D will there be a override since every save will produce a time stamp. But when the document is closed and started again the time stamp will be on the succeeding cell below.

thanks in advance

 

[Stiuart_W]

If I take the above at face value you are saying that if any cell other than those in C or D is changed then a timestamp is made in column A in the next clear cell. This means that the column will become full very quickly.

Your "Example" shows Input data in Column B - Will data only be entered in B2 or can it be entered elsewhere?

 

[Jacra]

If I take the above at face value you are saying that if any cell other than those in C or D is changed then a timestamp is made in column A in the next clear cell. This means that the column will become full very quickly.

Your "Example" shows Input data in Column B - Will data only be entered in B2 or can it be entered elsewhere?

Input data can be elsewhere. Or better in sheet1 all inputs will be there. Then in sheet2 timestamps. As long as changes or inputs are made in sheet1 there will be time stamps. Timestamp for start then timestamp for saving. Upon opening the workbook again, new timestamps shall be on the next row after the previous once.

 

[mikerickson]

You could put this in the ThisWorkbook code module.

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

 

[Jacra]

You could put this in the ThisWorkbook code module.

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

Hi!

This works perfectly. Thanks a lot. I will just do some minor adjustments to incorporate it with my template.
Thanks again....

 

[Jacra]

Hi!

This works perfectly. Thanks a lot. I will just do some minor adjustments to incorporate it with my template.
Thanks again....

hi again.

Two last things. First, I noticed that every time you do changes, the timestamp for start is being override. the intent was one time timestamp upon start of use then timestamp before closing the file (which is correct already).

Second, can we have a timer incorporated in the vba. lets say, the template is open and in 5mins no changes is being done, it will automatic timestamp for end then a message box will appear prompting "do you wish to continue", Yes = a new time stamp(which will not override the previous one), No = exit document.

Sorry for the new considerations. I'm just being creative.

Thanks in advance.