Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-10-31
8
Medium Priority
?
474 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 2000 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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