Saving of Date and Time in a Cell

[JamesMitel]

Hello,

I have created a database in excel with multiple sheets with various functions and stored on OneDrive which allows others to see it.

What I require is for it to show the Time and Date it was last saved in a particular cell in a worksheet.

I have done various searchers across the net and tried multiple things using VB (Visual Basic) but have had no luck. A quick search on this forum showed me how to do the last time saved, but not both Time AND Date.

Another thing, will this macro continue to function if hosted on onedrive - I usually click 'open with Excel application' - but need others to see the date.


Help on this matter is greatly appreciated - I've been scratching my head for the past few days now!

 

[jain2712]

Hey James,

Paste below code in vb sheet called "ThisWorkbook".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("tracker").Range("B2") = Format(Now, "dd/mm/yy hh:mm:ss")
End Sub
Private Sub Workbook_Open()
Sheets("tracker").Range("B3") = Format(Now, "dd/mm/yy hh:mm:ss")
End Sub

Kindly note that you should change the name and range either in your workbook or in above coding to run it perfectly.

 

[JamesMitel]

Hey James,

Paste below code in vb sheet called "ThisWorkbook".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("tracker").Range("B2") = Format(Now, "dd/mm/yy hh:mm:ss")
End Sub
Private Sub Workbook_Open()
Sheets("tracker").Range("B3") = Format(Now, "dd/mm/yy hh:mm:ss")
End Sub

Kindly note that you should change the name and range either in your workbook or in above coding to run it perfectly.

Thanks for that! -Which 'name' is it that I need to change? - Would that be the name I have called that particular sheet and where in the code above do I make the change?

Range I'm guessing is the cell it will go on - so B3 being the cell location according to the code?

 

[jain2712]

Hey James,

Name change here is indicating sheet name change. Like if you have sheet name is Sheet1, then change the code accordingly.
e.g.Sheets("Sheet1").Range("B2") = Format(Now, "dd/mm/yy hh:mm:ss")

Range change means, yes you are right. B2 and B3 would be the cell in which it will show the last time of file opening and closing. In B2, it will show you the file open time and in B3 it will show the file closing time.