Hi All - I have a spreadsheet that is updated several times a day and is referred to by many people. I would like to get a "last modified" time in the title bar so that people know the data has been changed. I do this manually by copying the "Last Modified" time from the file properties, but how can this be done automatically?? Any ideas anybody.
Hi martin,
I'm not quite sure what you mean by Title bar and how you go about copying something into it. Can you explain?
You could have the last modified date in a cell, which automatically updates whenever the workbook is saved.
This code goes into the ThisWorkbook module. Change the cell reference from B1 to any other cell you'd prefer.Code:Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim d d = Date & " " & Now ActiveSheet.Range("B1").Value = d End Sub
hth
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Teylyn
When I say "copying" all I mean is copy and paste. The "Title Bar" is just a cell where the title of the documents is written, It could simply be cell B1 for example. I've not used code before what do you mean when you say "goes into the ThisWorkbook module"?
Looking forward to reply
right-click on a sheet tab and select View Code. This will open the VBE (Visual Basic Editor). In the left hand navigation pane, look for ThisWorkbook and double click it (if you have several Excel files open, make sure to select the correct file). Copy the code above and paste it into the code window, which is the big white area of the VBE. Close the VBE.
The code will automatically run when the workbook is saved, so hit Ctrl-S and you should see cell B1 change.
hth
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks