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

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).

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

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

On Mon, 2 Jun 2008 15:34:01 -0700, Fred wrote:

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).

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

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" wrote:

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

On Mon, 2 Jun 2008 15:34:01 -0700, Fred wrote:

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).

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

Alt + F11 to open Visual Basic Editor.

CTRL + r to open Project Explorer.

Select your workbook/project and Right-clickInsertModule.

Paste the code in there.

Alt + q to return to the Excel and ToolsMacroMacros 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

On Mon, 2 Jun 2008 20:27:01 -0700, Fred wrote:

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" wrote:

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

On Mon, 2 Jun 2008 15:34:01 -0700, Fred wrote:

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).