Solved

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

Posted on 2013-10-31
8
466 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
ID: 39615057
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
ID: 39615234
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
ID: 39615395
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:sherman6789
ID: 39615438
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
 

Author Comment

by:sherman6789
ID: 39652372
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
ID: 39653417
We will be here when you need us!
0
 

Author Closing Comment

by:sherman6789
ID: 39690571
Thank you ThinkSpaceSolutions for your expert assistance.  

-sherman6789
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39690590
Glad to have helped!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

786 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