Can I create a custom header that depends on cell contents?

[Fred]

I am attempting to populate the center section of a custom header with data
which is dependent upon reference to data in a worksheet cell. In this case,
I am creating a custom order form which is addressed to a different supplier
and will have different ("To:", "Attention:", and phone number information).

 

[Gord Dibben]

Fred

Assumes 3 cells have the data.

If only one cell, do the editing.

Sub CellInFooter()
With ActiveSheet
.PageSetup.CenterFooter = "To: " & .Range("A1").Value & vbLf & _
"Attention: " & .Range("A2").Value & vbLf & _
"Phone Number: " & .Range("A3").Value
End With
End Sub


Gord Dibben MS Excel MVP

 

[Fred]

Do I create via Tools - Macro - Visual Basic Editor?

If so, once I've created the procedure, how do I invoke?

Thanks for the quick reply.

Fred

 

[Gord Dibben]

Alt + F11 to open Visual Basic Editor.

CTRL + r to open Project Explorer.

Select your workbook/project and Right-click>Insert>Module.

Paste the code in there.

Alt + q to return to the Excel and Tools>Macro>Macros to run the macro.

Or you could place the code into Thisworkbook module as a Before_Print event to
run whenever you printed the activesheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterFooter = "To: " & .Range("A1").Value & vbLf & _
"Attention: " & .Range("A2").Value & vbLf & _
"Phone Number: " & .Range("A3").Value
End With
End Sub

To use that method, right-click on the Excel Icon left of "File" and select
"View Code".

Copy/paste the Before_Print code into that module.


Gord