• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

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

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
sherman6789
Asked:
sherman6789
  • 4
  • 4
1 Solution
 
Steven HarrisPresidentCommented:
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
 
sherman6789Author Commented:
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
 
Steven HarrisPresidentCommented:
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
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!

 
sherman6789Author Commented:
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
 
sherman6789Author Commented:
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
 
Steven HarrisPresidentCommented:
We will be here when you need us!
0
 
sherman6789Author Commented:
Thank you ThinkSpaceSolutions for your expert assistance.  

-sherman6789
0
 
Steven HarrisPresidentCommented:
Glad to have helped!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now