Solved

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

Posted on 2013-10-31
8
470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

707 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