Custom Properties in Header

[RichP]

Can anyone tell me how to add a custom property field to the header of a worksheet? In Word I can use the Insert/Field command to insert things like Client name, etc into a document - how can I do this in Excel?

Thanls in advance,
R.

 

[stevebausch]

View/Header and Footer

If you want to automate this, record a macro of your actions.

Tools-Macro-Record New Macro

 

[RichP]

View/H&F, then custom header gives me the option to insert Date, page number, time, file path, etc. However, under File/Properties/Custom, I have a field called "Client Name", do you know how to insert that field into the header?

 

[lenze]

Is your custom property based on a link, or manually input?

Edit: Never mind the question. here's the code. Place it in the ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.LeftFooter = ThisWorkbook.CustomDocumentProperties.Item("Client Name")

End Sub
This message was edited by lenze on 2002-10-30 11:41

 

[stevebausch]

Okay, that was simple....


Recorded a macro of View-Header and Footer, keyed in some 'placeholder' information.

Stopped the macro.


Viewed the macro.


Modified the assignments as desired.


Looks like this. See if you can find my customized code.



Sub CustomHeader()
'
' CustomHeader Macro
' Macro recorded 10/30/2002 by SteveBausch
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = Cells(1, 1).Value
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
End Sub


I recommend removing any code you don't need, since this runs a bit slowly.

 

[RichP]

Lenze,
that works a treat, as always.
Many thanks.

Steve,
I'm not that good at the code stuff, but is the custom bit in Centerfooter = Cells (1,1)?
If it is, does that mean that the content of Center footer is the content of Cell (1,1) (is that A1)? If this is the case, the code doesn't do what I needed, which is more along the lines of ThisWorkbook.CustomDocumentProperties.Item.

Thanks,
RichP