Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need a formula in a specific cell when saved it gives the date and time it was saved last

Posted on 2012-08-22
7
Medium Priority
?
357 Views
Last Modified: 2012-09-06
Need a formula in a specific cell when saved it gives the date and time it was saved last

In a specific sheet a specific cell i want this changed each time it saves the workbook
0
Comment
Question by:bsharath
  • 3
  • 3
7 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38320029
Hello,

open the VB Editor, double click "ThisWorkbook" in the navigation pane (also called Project Explorer) and paste this code (adjust A1 to the cell you want to show the time stamp in)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = Now
End Sub

Open in new window


see attached

cheers, teylyn
Book3.xlsm
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 38320048
If this were a formula it would update each time the workbook is calculated and/or saved.

However, you can use the BeforeSave Event in VBA.

In your workbook give the cell required a RangeName of "SaveTime" and then use this VBA in the ThisWorkbook module in VBA.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Range("SaveTime").FormulaR1C1 = "=NOW()"
    Range("SaveTime") = Range("SaveTime").Value
    
End Sub

Open in new window


To access the ThisWorkbook module press Alt + F11 to open the VB Editor. In the left hand pane you will see a list of files that are open. Expand the file into which you want the routine and double click on ThisWorkbook. This will open a blank "sheet" in the right hand pane. Copy and paste the above into this sheet.

Thanks
Rob H
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 38320080
Ostensibly no difference between my suggestion and teylyn's.

Without sounding like I am blowing my own trumpet (who else would anyway?) the advantage of using the Range Name is that it wouldn't matter where you are in the workbook when the user saves.

Thanks
Rob
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50
ID: 38320100
Well, some slight differences exist. The range name simplifies things.

My suggestion uses the VBA "Now" which does not require copying and pasting as a value.

Combining the two, i.e. range name and VBA now, would be the perfect solution.

:)
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 38320118
So combining as Teylyn suggests:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("SaveTime") = Now
End Sub 

Open in new window


Thanks
Rob H
0
 
LVL 11

Author Comment

by:bsharath
ID: 38320203
Any way to achieve this with just a formula placed in a cell?
Just asking
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38320246
bsharath,

short answer: No.

A formula using the current date/time, like =NOW(),  will always update to the current date/time. There have been approaches using iterations, but that can turn ugly.

A true date/time stamp is best achieved with VBA. Range name or direct cell reference does not make a difference.

cheers, teylyn
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question