Insert defined names in header/footer

[Kelie]

Hello group,

Is it possible to insert defined names in header or footer? You can
insert Doc Variables into header/footer in Word. Since Excel doesn't
have Doc Variables, I was hoping I can insert Doc Proproties. But
cannot. It seems defined names can't be used in header/footer either.
Can anyone please confirm, or provide an alternate?

Thank you!

 

[Gord Dibben]

You can insert builtin document properties and custom document properties into a
header/footer.

Requires VBA code.

Sub Last_Saved_Footer()
'Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.RightFooter = "&8Last Saved : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
Next wkSht
End Sub

Here are a couple of examples for adding a custom property and using that custom
property to increment with each successive print out.

Sub Add_Custom_Prop()
Dim dp As DocumentProperties
Set dp = ThisWorkbook.CustomDocumentProperties
dp.Add Name:="gordo", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=0
End Sub


Sub Increment_Gordo_Value()
If ActiveSheet.Name = "Sheet1" Then
n = ThisWorkbook.CustomDocumentProperties("gordo").Value
n = n + 1
ActiveSheet.PageSetup.RightHeader = "gordo " & n
ThisWorkbook.CustomDocumentProperties("gordo").Value = n
End If
ActiveSheet.PrintPreview ''Out
End Sub


Gord Dibben MS Excel MVP

 

[Kelie]

Thanks a lot Gord! Your sample code works great. Exactly what I was
looking for. Sorry I had forgot that I asked this question.

 

[Gord Dibben]

I forgot I answered.

Thanks for the feedback.


Gord

 

[Kelie]

It seems the CDP content does not update once inserted into a
workbook, when you change the CDP afterwards. Here is how I tested: I
used Macro1 to add a CDP called "VAR1"; then I used Macro2 and Macro2
to insert the CDP into one cell and the footer; then I manually
changed CDP "VAR1" to something else, but the workbook does not update
to reflect the change. Btw, I tried saving the workbook. What did I
miss? Thank you!

Sub Macro1()
Dim dp As DocumentProperties
Set dp = ActiveWorkbook.CustomDocumentProperties
dp.Add "VAR1", False, msoPropertyTypeString, "RED & BLUE"
End Sub

Sub Macro2()
Range("A1").Select
ActiveCell.FormulaR1C1 = "BEFORE " &
ActiveWorkbook.CustomDocumentProperties("VAR1") & " AFTER"
End Sub

Sub Macro3()
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets
wkSht.PageSetup.RightFooter =
ActiveWorkbook.CustomDocumentProperties("VAR1")
Next wkSht
End Sub

 

[Gord Dibben]

Kelie

After manually changing "VAR1" in CDP you must run macro2 and 3 again to update
the cell and footer to reflect the change.

Alternative to macros 2 and 3 would be BeforePrint event code in Thisworkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call Macro2
Call Macro3
End Sub


Gord

 

[Kelie]

Kelie

After manually changing "VAR1" in CDP you must run macro2 and 3 again to update
the cell and footer to reflect the change.

Alternative to macros 2 and 3 would be BeforePrint event code in Thisworkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call Macro2
Call Macro3
End Sub

Gord

Thanks again Gord!

Hmm, this is quite different from how Word behaves and I fail to see
the benefits of using CDPs, at least when comparing it with Word.
Suppose I've defined a number of CDPs and inserted them at different
places (cells or headers/footers), and want to change some CDPs and
update the workbook, how do I accomplish that? The problems is how do
I know where these CDPs are? In Word, where a CDP is inserted, you can
use "Toggle Field Codes" to see which CDP it is. That tells me Word
keeps track of CDP used in a document. I didn't find such feature in
Excel. Does that mean Excel does not store the information about where
CDPs are inserted?

 

[Gord Dibben]

I so rarely work with Custom Document Properties that my knowledge and
experience with them is close to nil.

I took a course in Word back at the 6.0 level and have used it rarely since so a
bust there also.

Without help from other posters your queries will probably go unanswered.


Gord

 

[Dave Peterson]

Yep. MSWord and excel behave differently.

In excel, when you change the footer to include the custom property's value,
you're working with its value--not the property itself.

It's kind of the difference between using "May 15, 2008" (as a literal static
string) and &[Date] (which represents the current date--and changes whenever the
date changes).

You can use code to create/delete/modify these custom document values or you can
use File|Properties|Custom tab (in xl2003) to do things manually.

If you want to change the header/footer, you'll have to do it manually or use
code--just like in your previous post.

But excel does offer events that you can tie into--and one that seems fitting is
the workbook_beforeprint event. This will fire (if events and macros are
enabled) whenever the user prints (or print previews).

This code goes behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myProp As DocumentProperty
Dim myPropName As String
Dim myStr As String
Dim wkSht As Worksheet

myPropName = "Var1"

Set myProp = Nothing
On Error Resume Next
Set myProp = Me.CustomDocumentProperties(myPropName)
On Error GoTo 0

If myProp Is Nothing Then
myStr = "Missing Property: " & myPropName
Else
myStr = myProp.Value
End If

For Each wkSht In Me.Worksheets
wkSht.PageSetup.RightFooter = myStr
Next wkSht

End Sub

 

[fortitalia]

It seems the CDP content does not update once inserted into a
workbook, when you change the CDP afterwards. Here is how I tested: I
used Macro1 to add a CDP called "VAR1"; then I used Macro2 and Macro2
to insert the CDP into one cell and the footer; then I manually
changed CDP "VAR1" to something else, but the workbook does not update
to reflect the change. Btw, I tried saving the workbook. What did I
miss? Thank you!

Sub Macro1()
    Dim dp As DocumentProperties
    Set dp = ActiveWorkbook.CustomDocumentProperties
    dp.Add "VAR1", False, msoPropertyTypeString, "RED & BLUE"
End Sub

Sub Macro2()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "BEFORE " &
ActiveWorkbook.CustomDocumentProperties("VAR1") & " AFTER"
End Sub

Sub Macro3()
    Dim wkSht As Worksheet
    For Each wkSht In ActiveWorkbook.Worksheets
        wkSht.PageSetup.RightFooter =
ActiveWorkbook.CustomDocumentProperties("VAR1")
    Next wkSht
End Sub

Good afternoon,

I had the same problem in the past of having created my own functions
to customize my eXcel sheets. The solution seems to be (at least it
worked for me) to have the instruction "Application.Volatile" at the
beginning of the function. Then, each time the sheet is recalculated
(at opening or activating the sheet or printing) then the function is
recalculated.
Example:
In the VBA environment attached to the "ThisWorkbook" space I write
this little sub:
Sub Workbook_BeforePrint(Cancel As Boolean)
Application.Volatile
Dim wkSht As Worksheet
MsgBox "entering sub ***Workbook_BeforePrint(Cancel)"
MsgBox myPageCenterHeader
'For Each wkSht In ThisWorkbook.Worksheets
' wkSht.PageSetup.CenterHeader = myPageCenterHeader
'Next wkSht
Cancel = True
MsgBox "exiting sub ***Workbook_BeforePrint, 'Cancel' set to true,
so printing is canceled!"
End Sub
In the VBA environment I create (if not already done) a "Module":
right click on the VBA project name (if your file is toto.xls your VBA
project should be named "VBAProject (toto.xls)") option "Insert/
Module". This creates a VBA folder "Modules" into the VBA project and
then a module "Module1" into that VBA folder.
Double click on that "Module1" name and VBA opens the code window.
In that window just type a function like this one if you like:
' This function to be called by Sub "Workbook_BeforePrint(Cancel As
Boolean)"
' Workbook_BeforePrint() sub is to be placed into the "ThisWorkbook"
VBA space
' so that it is called each time the print event is generated (when
print action
' is invoked).
Function myPageCenterHeader() As String
Application.Volatile
Dim s As String

With ThisWorkbook.CustomDocumentProperties
s = "Registered Version: " & .Item("Fortis_ICT_DocVersion") &
_
" -- Registered version date: " & _
Format(.Item("Fortis_ICT_DocDate"), "dddd, dd mmmm yyyy",
vbMonday)
End With

myPageCenterHeader = s

End Function

Because the code contains the instruction "Application.Volatile" it
should recalculate each time needed.

I hope it will help you.

Gilles