Custom information in Excel footer

[kmewing]

I need to create an Excel template that prints some standard metadata in the
footer of each worksheet. The metadata consists of Document Name, Author,
Last Save Date, and Status. (Status is a one-word entry.) To populate the
footer, I would like to have a separate worksheet (named “Metadataâ€) where
the user would fill in the values for these four fields, then these values
would automatically propagate to the footers of the other worksheets in the
workbook. I’d like the footers to appear as follows:

Document Name: <name>
Author: <name>
Last Saved: <date>
Status: <value>

I expect I’ll need to use VB scripting to make this work. Can anyone point
me to a source where I can get some guidance on doing the above task? I’m
not deeply versed in VB myself but I’m willing to try it. Thanks for any
help.

 

[JLatham]

The code that follows goes into the Workbook code section. To put it there
(Excel 2003 and earlier):
Open the workbook and right-click on the little Excel icon immediately to
the left of the word "File" in the menu toolbar. Choose [View Code] from the
list that appears.
Copy the code below and paste it into the code module presented to you, make
changes to the Const values declared (as the name of the sheet where you want
to keep this special Metadata). Save and close the workbook. When you open
it up again, the footers will automatically be revised.

I've gathered the 1st 3 metadata items from the workbook values themselves
rather than having you type them in. If you really want to type stuff into
those cells (Status still is a manual entry), then we can modify the code
easily enough. The document name and last date saved will not be valid until
the file is has actually been saved once - if you're creating a brand new
file, that is.

Here's the code:

Option Explicit
'code by J.Latham
'email: (e-mail address removed)
'
'change these definitions as required for
'your workbook setup for the metadata sheet
Private Const infoSheetName = "SpecialSheet"
Private Const docNameCell = "B1"
Private Const docAuthorCell = "B2"
Private Const docSavedDateCell = "B3"
Private Const docStatusCell = "B4"

Private Sub Workbook_Open()
'this automatically updates the contents
'of the 1st 3 items on the information sheet
Dim infoSheet As Worksheet

Set infoSheet = _
ThisWorkbook.Worksheets(infoSheetName)
infoSheet.Range(docNameCell) = ThisWorkbook.Name
infoSheet.Range(docAuthorCell) = ThisWorkbook.Author
infoSheet.Range(docSavedDateCell) = _
FileDateTime(ThisWorkbook.FullName)
Set infoSheet = Nothing ' housekeeping
'the Status entry is a manual entry
'update all footers
UpdateWorksheetFooters
End Sub

Private Sub UpdateWorksheetFooters()
Dim infoSheet As Worksheet
Dim anySheet As Worksheet
Dim footerText As String

Set infoSheet = _
ThisWorkbook.Worksheets(infoSheetName)
footerText = "Document Name: " & _
infoSheet.Range(docNameCell) & vbLf
footerText = footerText & "Author: " & _
infoSheet.Range(docAuthorCell) & vbLf
footerText = footerText & "Last Saved: " & _
infoSheet.Range(docSavedDateCell) & vbLf
footerText = footerText & "Status: " & _
infoSheet.Range(docStatusCell)

For Each anySheet In ThisWorkbook.Worksheets
With anySheet.PageSetup
.LeftFooter = footerText
'you can also use these sections if you like
'.CenterFooter = "here is center of footer"
'.RightFooter = "right aligned in footer"
End With
Next
Set infoSheet = Nothing

End Sub

 

[kmewing]

This looks great! Thanks for going to this effort. I did, though, neglect
to mention that I'm using Excel 2007. I've searched the online help and
other resources and have not found how to access the workbook code section in
Excel 2007. Can you give me any guidance there? Thanks.

The code that follows goes into the Workbook code section. To put it there
(Excel 2003 and earlier):
Open the workbook and right-click on the little Excel icon immediately to
the left of the word "File" in the menu toolbar. Choose [View Code] from the
list that appears.
Copy the code below and paste it into the code module presented to you, make
changes to the Const values declared (as the name of the sheet where you want
to keep this special Metadata). Save and close the workbook. When you open
it up again, the footers will automatically be revised.

I've gathered the 1st 3 metadata items from the workbook values themselves
rather than having you type them in. If you really want to type stuff into
those cells (Status still is a manual entry), then we can modify the code
easily enough. The document name and last date saved will not be valid until
the file is has actually been saved once - if you're creating a brand new
file, that is.

Here's the code:

Option Explicit
'code by J.Latham
'email: (e-mail address removed)
'
'change these definitions as required for
'your workbook setup for the metadata sheet
Private Const infoSheetName = "SpecialSheet"
Private Const docNameCell = "B1"
Private Const docAuthorCell = "B2"
Private Const docSavedDateCell = "B3"
Private Const docStatusCell = "B4"

Private Sub Workbook_Open()
'this automatically updates the contents
'of the 1st 3 items on the information sheet
Dim infoSheet As Worksheet

Set infoSheet = _
ThisWorkbook.Worksheets(infoSheetName)
infoSheet.Range(docNameCell) = ThisWorkbook.Name
infoSheet.Range(docAuthorCell) = ThisWorkbook.Author
infoSheet.Range(docSavedDateCell) = _
FileDateTime(ThisWorkbook.FullName)
Set infoSheet = Nothing ' housekeeping
'the Status entry is a manual entry
'update all footers
UpdateWorksheetFooters
End Sub

Private Sub UpdateWorksheetFooters()
Dim infoSheet As Worksheet
Dim anySheet As Worksheet
Dim footerText As String

Set infoSheet = _
ThisWorkbook.Worksheets(infoSheetName)
footerText = "Document Name: " & _
infoSheet.Range(docNameCell) & vbLf
footerText = footerText & "Author: " & _
infoSheet.Range(docAuthorCell) & vbLf
footerText = footerText & "Last Saved: " & _
infoSheet.Range(docSavedDateCell) & vbLf
footerText = footerText & "Status: " & _
infoSheet.Range(docStatusCell)

For Each anySheet In ThisWorkbook.Worksheets
With anySheet.PageSetup
.LeftFooter = footerText
'you can also use these sections if you like
'.CenterFooter = "here is center of footer"
'.RightFooter = "right aligned in footer"
End With
Next
Set infoSheet = Nothing

End Sub

I need to create an Excel template that prints some standard metadata in the
footer of each worksheet. The metadata consists of Document Name, Author,
Last Save Date, and Status. (Status is a one-word entry.) To populate the
footer, I would like to have a separate worksheet (named “Metadataâ€) where
the user would fill in the values for these four fields, then these values
would automatically propagate to the footers of the other worksheets in the
workbook. I’d like the footers to appear as follows:

Document Name: <name>
Author: <name>
Last Saved: <date>
Status: <value>

I expect I’ll need to use VB scripting to make this work. Can anyone point
me to a source where I can get some guidance on doing the above task? I’m
not deeply versed in VB myself but I’m willing to try it. Thanks for any
help.

 

[kmewing]

I found how to access View Code in Excel 2007. I inserted the code as you
stated (making changes in the code where specified). I get no errors, but
when I do Print Preview, no information appears in the footer. Are there any
other parameters to check?

 

[JLatham]

A couple of things that I can think of is, first, that your Macro Security
setting may not even be providing you with any alert/warning that the
workbook contains macros.

You did save the book with the macro in it as a .xlsm file, didn't you? It
needs to be a .xlsm vs .xlsx file.

Now, back to security settings. Click the Office button, then the [Excel
Options] button at the lower right corner of that window. Go to the Trust
Center. Look at Macro Settings first, and make the "Disable all macros with
notification" button the option of choice. This will cause workbooks not in
"trusted locations" to provide a yellow alert bar near the top of the Excel
worksheet window telling you to "click here for more options" which will
allow you to choose whether to allow the macros in the workbook to run or
not. Naturally you would for books from known/trusted sources, and perhaps
not from unknown/doubtful sources until you can investigate the code.

You might also look at the [Trusted Locations] section also, that may give
you some ideas also.

Finally, after dealing with [Macro Settings] and/or [Trusted Locations],
look at the [Message Bar] section, and make sure that the "Show the Message
Bar in all..." option is selected.

Close Excel and reopen it, then open the workbook again and see if things
work - you should first see the pale yellow Message Bar saying that the book
has macros and asking you to click it to see the "Enable/Disable" window.
Enable them in that window.

Only other thing that might cause failure to perform at this point is if the
worksheets are protected.

Let me know how this goes. If things still don't seem right, add this line
of code somewhere within the Workbook_Open() code segment, after all of the
Const and Dim statements:

MsgBox "I am a macro, and I am running!"

And if you see that message when you open the workbook, but footers still
don't get set up, we have some further looking to do.

Oh - you did change the name of the worksheet up in the declarations section
to hold the name of the sheet you've chosen to hold those 4 information
items, didn't you?

 

[kmewing]

Summary: I tried all your suggestions and still no response. The test
message box does not appear either. See my embedded comments and my question
at the end.

You did save the book with the macro in it as a .xlsm file, didn't you? It
needs to be a .xlsm vs .xlsx file.

Ah, no I didn't. But now I have.

Now, back to security settings. Click the Office button, then the [Excel
Options] button at the lower right corner of that window. Go to the Trust
Center. Look at Macro Settings first, and make the "Disable all macros with
notification" button the option of choice. This will cause workbooks not in
"trusted locations" to provide a yellow alert bar near the top of the Excel
worksheet window telling you to "click here for more options" which will
allow you to choose whether to allow the macros in the workbook to run or
not. Naturally you would for books from known/trusted sources, and perhaps
not from unknown/doubtful sources until you can investigate the code.

Settings all seemed to be correct. I do get the yellow bar at the top when
I invoke the Excel file. I click Options and select "Enable this content."
No footer info shows up.

You might also look at the [Trusted Locations] section also, that may give
you some ideas also.

This just contains default stuff. I've never added or deleted anything
manually.

Finally, after dealing with [Macro Settings] and/or [Trusted Locations],
look at the [Message Bar] section, and make sure that the "Show the Message
Bar in all..." option is selected.
Done.

Close Excel and reopen it, then open the workbook again and see if things
work - you should first see the pale yellow Message Bar saying that the book
has macros and asking you to click it to see the "Enable/Disable" window.
Enable them in that window.

Done as directed.

Only other thing that might cause failure to perform at this point is if the
worksheets are protected.

I don't see any protections. I'm querying the originator of the file to
verify.

Let me know how this goes. If things still don't seem right, add this line
of code somewhere within the Workbook_Open() code segment, after all of the
Const and Dim statements:

MsgBox "I am a macro, and I am running!"

And if you see that message when you open the workbook, but footers still
don't get set up, we have some further looking to do.

I inserted this line in the following location:

Private Sub Workbook_Open()
:
:
MsgBox "I am a macro, and I am running."
End Sub

No message box appears when I invoke the Excel file.

Oh - you did change the name of the worksheet up in the declarations section
to hold the name of the sheet you've chosen to hold those 4 information
items, didn't you?

Yes, I did adjust the names of the variables to match my details.

One question regarding where to insert the code:

In the VB design window, there's a pane on the left side called Project.
For my Excel file, the Project pane lists three components:

Sheet1 (Roadmap) --> This is the normal worksheet where users will fill in
data.

Sheet2 (Metadata) --> This is the worksheet I created where users will enter
the values for the metadata to appear in the footer. This worksheet serves
no other purpose.

ThisWorkbook

If I double-click on either of the worksheets, I get a Code window. At the
top of the code window there's a drop-down containing the following two
values:

(General)
Worksheet

I pasted the code into Sheet2 (General). Is this the correct location in
which to paste the code? Would this make any difference?

 

[JLatham]

If you've done all this (and I don't doubt that you have) then I suspect the
code is not in the proper place.

I've prepared an Excel 2007 .xlsm file and put it at this location
http://www.jlathamsite.com/uploads/for_kmewing.rename_xlsm.zip

copy and paste the link into your web browser and it should ask if you want
to open or save it. Choose to SAVE it because it really is not a .zip file,
it is an .xlsm file. (Some browsers, mine as setup included) won't upload a
..xls? file they balk with a page not found error. This is a trick to get
around that.

After saving the file rename it something like
for_kmewing.xlsm
and then open it with Excel 2007. It should set up the sheet footers for
you. To double check it, change the "Status" entry in B4 on the
SpecialSheet, close the workbook and open it again and make sure that the
correct status is shown in the footers.

Hope this helps.

Summary: I tried all your suggestions and still no response. The test
message box does not appear either. See my embedded comments and my question
at the end.

You did save the book with the macro in it as a .xlsm file, didn't you? It
needs to be a .xlsm vs .xlsx file.

Ah, no I didn't. But now I have.

Now, back to security settings. Click the Office button, then the [Excel
Options] button at the lower right corner of that window. Go to the Trust
Center. Look at Macro Settings first, and make the "Disable all macros with
notification" button the option of choice. This will cause workbooks not in
"trusted locations" to provide a yellow alert bar near the top of the Excel
worksheet window telling you to "click here for more options" which will
allow you to choose whether to allow the macros in the workbook to run or
not. Naturally you would for books from known/trusted sources, and perhaps
not from unknown/doubtful sources until you can investigate the code.

Settings all seemed to be correct. I do get the yellow bar at the top when
I invoke the Excel file. I click Options and select "Enable this content."
No footer info shows up.

You might also look at the [Trusted Locations] section also, that may give
you some ideas also.

This just contains default stuff. I've never added or deleted anything
manually.

Finally, after dealing with [Macro Settings] and/or [Trusted Locations],
look at the [Message Bar] section, and make sure that the "Show the Message
Bar in all..." option is selected.
Done.

Close Excel and reopen it, then open the workbook again and see if things
work - you should first see the pale yellow Message Bar saying that the book
has macros and asking you to click it to see the "Enable/Disable" window.
Enable them in that window.

Done as directed.

Only other thing that might cause failure to perform at this point is if the
worksheets are protected.

I don't see any protections. I'm querying the originator of the file to
verify.

Let me know how this goes. If things still don't seem right, add this line
of code somewhere within the Workbook_Open() code segment, after all of the
Const and Dim statements:

MsgBox "I am a macro, and I am running!"

And if you see that message when you open the workbook, but footers still
don't get set up, we have some further looking to do.

I inserted this line in the following location:

Private Sub Workbook_Open()
:
:
MsgBox "I am a macro, and I am running."
End Sub

No message box appears when I invoke the Excel file.

Oh - you did change the name of the worksheet up in the declarations section
to hold the name of the sheet you've chosen to hold those 4 information
items, didn't you?

Yes, I did adjust the names of the variables to match my details.

One question regarding where to insert the code:

In the VB design window, there's a pane on the left side called Project.
For my Excel file, the Project pane lists three components:

Sheet1 (Roadmap) --> This is the normal worksheet where users will fill in
data.

Sheet2 (Metadata) --> This is the worksheet I created where users will enter
the values for the metadata to appear in the footer. This worksheet serves
no other purpose.

ThisWorkbook

If I double-click on either of the worksheets, I get a Code window. At the
top of the code window there's a drop-down containing the following two
values:

(General)
Worksheet

I pasted the code into Sheet2 (General). Is this the correct location in
which to paste the code? Would this make any difference?

 

[kmewing]

Thank you. It all seems to be working now. I downloaded your test file and
followed your instructions. The file opened fine and worked as intended.

I looked at the View Code page in your test file and saw that you inserted
your script text in a different place from where I put mine. I opened my own
file and moved the script and now everything works great.

Thanks very much for your persistent follow through.

Ken

 

[JLatham]

Glad it all ended up working.

There are several distinct places for code to go in an Excel file. As you
see with this code, those Subs that begin something like:
Private Sub Workbook_....
must go into the workbook's code area, while ones that begin with something
like
Private Sub Worksheet_...
must go into a sheet's code area. Then other general code routines go into
a standard code modules area. This was just one of those times that the code
needed to go into a very particular place.