Custom Properties in Header

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
View/Header and Footer

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

Tools-Macro-Record New Macro
 

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Well-known Member
Joined
May 11, 2002
Messages
810
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

Board Regular
Joined
Jul 31, 2002
Messages
63
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
 

Forum statistics

Threads
1,148,020
Messages
5,744,361
Members
423,864
Latest member
GolfingTitan116

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top