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

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 wrote:
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

That worked! Thanks a lot Rowan :)

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 wrote:
That worked! Thanks a lot Rowan :)

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 wrote:
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 wrote:
That worked! Thanks a lot Rowan :)

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 wrote:
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 wrote:

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 wrote:

That worked! Thanks a lot Rowan :)