How to update last save time & last author for each worksheet

[the majestic ferny]

Hello. I found code that allows me to display document properties for
a workbook and I am using it to show the last save time and last author
as well as other document properties. But I think the code only
updates the first worksheet. Would I need to add the code to each
worksheet or is there a way to have it update all worksheets
simultaneously?
Thanks.
Here's the code:
<code>
Sub DocumentPropertiesLastModified()
Dim a As String
Dim b As String
Dim c As String
Dim d As String
'Worksheets(1).Activate
d = ActiveWorkbook.BuiltinDocumentProperties("title")
Cells(1, 1).Select
ActiveCell.FormulaR1C1 = d
a = ActiveWorkbook.BuiltinDocumentProperties("last save time")
Cells(2, 5).Select
ActiveCell.FormulaR1C1 = "last modified:" & " " & a
b = ActiveWorkbook.BuiltinDocumentProperties("Author")
Cells(1, 5).Select
ActiveCell.FormulaR1C1 = "created by:" & " " & b
c = ActiveWorkbook.BuiltinDocumentProperties("Last author")
Cells(3, 5).Select
ActiveCell.FormulaR1C1 = "last edited by:" & " " & c
End Sub
</code>

 

[Rowan Drummond]

Try:

Sub DocumentPropertiesLastModified()
Dim Ttle As String
Dim lstSave As String
Dim Athr As String
Dim lstAthr As String
Dim wks As Worksheet

With ActiveWorkbook
Ttle = .BuiltinDocumentProperties("title")
lstSave = .BuiltinDocumentProperties("last save time")
Athr = .BuiltinDocumentProperties("Author")
lstAthr = .BuiltinDocumentProperties("Last author")

For Each wks In .Worksheets
wks.Cells(1, 1).FormulaR1C1 = Ttle
wks.Cells(2, 5).FormulaR1C1 = _
"last modified:" & " " & lstSave
wks.Cells(1, 5).FormulaR1C1 = "created by:" & " " & Athr
wks.Cells(3, 5).FormulaR1C1 = _
"last edited by:" & " " & lstAthr
Next wks
End With
End Sub

Hope this helps
Rowan

 

[the majestic ferny]

That worked! Thanks a lot Rowan

 

[Rowan Drummond]

You're welcome. You may want to include this in the workbook_open event
so that it is kept up to date.

 

[the majestic ferny]

Aha! That was my next question. I realized it wouldn't update till I
ran the code.
Got that working, but is there a way to do it on Save? Or does it
really matter?

 

[Rowan Drummond]

Only you would know if it really matters. I would probably go for the
workbook_open event. Paste the following in the ThisWorkbook module in
the VBA project.

Private Sub Workbook_Open()
Dim Ttle As String
Dim lstSave As String
Dim Athr As String
Dim lstAthr As String
Dim wks As Worksheet

With Me
Ttle = .BuiltinDocumentProperties("title")
lstSave = .BuiltinDocumentProperties("last save time")
Athr = .BuiltinDocumentProperties("Author")
lstAthr = .BuiltinDocumentProperties("Last author")

For Each wks In .Worksheets
wks.Cells(1, 1).FormulaR1C1 = Ttle
wks.Cells(2, 5).FormulaR1C1 = _
"last modified:" & " " & lstSave
wks.Cells(1, 5).FormulaR1C1 = "created by:" & " " & Athr
wks.Cells(3, 5).FormulaR1C1 = _
"last edited by:" & " " & lstAthr
Next wks
End With
End Sub

Regards
Rowan

 

[the majestic ferny]

Hi Rowan. Is there a way to have it work on both Open() and
BeforeSave() events? If I try to just add another sub with
BeforeSave() I get a compile error. I guess I would like the cell
values to update if a user saves the document but then keeps working on
it.
Thanks for your great assistance!

 

[Rowan Drummond]

Hi

When using events you must match the event signature set out in VBA so
the BeforeSave event must be:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'code
End Sub

The problem with this is that it runs BEFORE the save at which point the
properties will not yet have been updated. You may be able to play
around with it to have it save, update the cells on your sheets and then
save again. You can find more info on events from Chip Pearson at:

http://www.cpearson.com/excel/events.htm

Regards
Rowan

More on events at: