Can I display the file properties of a workbook in a custom headi.

S

Sings4Fun

I am sharing a workbook with another user where I work and we both make
changes to it on a regular basis. I am wondering if there is a function that
can display the name of the last person who saved it and the last date it was
saved so that we don't have to go into the header and manually update this
information each time we change something in the workbook.

Thanks!
 
G

Gord Dibben

Sings

User Defined Function, not built-in.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

In A1 enter =DOCPROPS("author")
In B1 enter =DOCPROPS("last save time")

Then run this macro

Sub CellInFooter()
With ActiveSheet
.PageSetup.RightFooter = .Range("A1").Text & _
" " & .Range("B1").Text
End With
End Sub


Gord Dibben Excel MVP
 
G

Gord Dibben

A much shortened version without the cells being populated.

Mis-read original question. So what else is new?<g>

Sub footer()
ActiveSheet.PageSetup.RightFooter = _
ActiveWorkbook.BuiltinDocumentProperties("last author") _
& " " & ActiveWorkbook.BuiltinDocumentProperties("last save time")
End Sub


Gord
 
Top