Solved

Automatically show date of last data modification done anywhere on the 2010 Excel program

Posted on 2013-10-31
8
459 Views
Last Modified: 2013-12-02
We would like to add an automatically updated date field to show the last time any data has been updated and saved in the program.  Our program has 10 tabs and the users may open any tab to work on.  Information is automatically obtained from each tab and calculations and summaries are made on the SUMMARY tab.

If someone opens the document but make no saved changes we would like the "Last Updated" field to remain unchanged.

Any assistance that you can give will be appreciated.  Thank you.

sherman6789
0
Comment
Question by:sherman6789
  • 4
  • 4
8 Comments
 
LVL 18

Expert Comment

by:Steven Harris
Comment Utility
Can you use VBA, or need to keep away from VBA?

There is also some other things to consider:

What if they open the file and save without making changes?

What if they open the file, make changes, undo the changes, then save?
0
 

Author Comment

by:sherman6789
Comment Utility
I think that I can use VBA with instructions.
If someone opens the file and makes no changes we would perfer that the date not change.
However, we will accept it if necessary.
If changes are made and they undo the changes we can except the response either way.
We want to keep it simple.

Remember we want this cell to update no matter where the change is made in the file.  At this point, we don't need to know where the change was made.

I know that Excel will update the file date and time anytime that the file is opened and closed even if no change is made, even if it is not saved. (SAD)

Thanks for your assistance.
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
Comment Utility
You may be better off with Track Changes:

Review Tab > Track Changes > Highlight Changes.  This will let you know who and what was changed.

The reason I asked about the ability to use VBA is that some people are annoyed by the prompt that there are macros in the workbook.  Some companies will not use macros on shared sheets; however, you could always try something with VBA such as:

Alt+F11 to enter VBA Console > Ctrl+r to enter Project Explorer > Double-click 'ThisWorkbook' from under Microsoft Excel Objects and enter the following in the window to the right -

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success Then
        Range("A1").Value = Now
    End If
End Sub

Open in new window


Each time the workbook is saved, this will place the current date/time in cell A1.  Change the cell to suit your needs.  Save the Workbook as .xlsm format.

My thoughts:

If someone is just viewing the Workbook, there is no reason for them to save the file.  If they make changes and don't want to keep those changes, there is no reason to save the file.

As an afterthought, maybe you would want to capture the user as well?

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success Then
        Range("A1").Value = Now
        Range("B1").Value = Environ$("UserName")
    End If
End Sub

Open in new window


Each time the workbook is saved, this will place the current date/time in cell A1 and the username in cell B1.  Change the cells to suit your needs.  Save the Workbook as .xlsm format.
0
 

Author Comment

by:sherman6789
Comment Utility
Dear ThinkSpaceSolutions,

Thank you for your responses.  I am sure that I can use VBA but I am still learning.

A long time ago I was disapointed with Excel in one respect.  If we have a file that was last updated on June 3, 2011 and no changes have been made; if someone opens the file today and make no changes and does not save the file, Access will automatically change the date to today and when someone looks at the list of files in that directory, they will see today's date on that file and not June 3, 2011 even though no changes were made and it was not resaved.  I wrote to Microsoft years ago about that automatic change and I never heard from them and it is still doing that unnecessary change.

Back on the main point:  I will look over your suggestions and see what I can do.  I will let you know as soon as possible.   Thanks again.

sherman6789
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:sherman6789
Comment Utility
Thanks for your patience.  My job has me working on a special project and I have not had time to work on this project.  It is Friday evening now and I expect to be able to re-read your suggestions and try to work on this project this weekend.  I hope to make a reply to this link by Monday or Tuesday.

Thanks again.

sherman6789
0
 
LVL 18

Expert Comment

by:Steven Harris
Comment Utility
We will be here when you need us!
0
 

Author Closing Comment

by:sherman6789
Comment Utility
Thank you ThinkSpaceSolutions for your expert assistance.  

-sherman6789
0
 
LVL 18

Expert Comment

by:Steven Harris
Comment Utility
Glad to have helped!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Numbers are exporting as text 5 24
Dateadd 3 20
How to make an ADE file by code? 11 35
Access Migration to Sql Server 2 7
This collection of functions covers all the normal rounding methods of just about any numeric value.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now