Conditional Header

Question

Is it possible to have a conditional header based on the value in cell B2? For example, if the value in cell B2 is:

02  then Carroll & Associates to appear in the header,

03  then Hydraulic Components,

05  then Casting Sales, etc.

Please share your ideas.

Answer 1

You could do either:

Public Sub Workbook_BeforePrint(Cancel As Boolean)

' This will load the header for each sheet according to the value of A2 on each sheet.'    Dim i As Long

    For i = 1 To ThisWorkbook.Worksheets.Count

        With ThisWorkbook.Worksheets(i).PageSetup

            Select Case LCase(ThisWorkbook.Worksheets(i).Range("A2").Text)

                Case "02"

                    .LeftHeader = "H. G. Carroll & Associates"

                Case "03"

                    .LeftHeader = "International Hydraulic Components"

                Case "05"

                    .LeftHeader = "Casting Sales"

                ' continue with other cases                Case Else

                    .LeftHeader = vbNullString

            End Select

        End With

    Next i

End Sub

or:

Public Sub Workbook_BeforePrint(Cancel As Boolean)

' This will load the header for each sheet according to the value of A2 on "Sheet1".'    Dim i As Long

    Dim strLHeader As String

    Select Case LCase(ThisWorkbook.Worksheets("Sheet1").Range("A2").Text)

        Case "02"

            strLHeader = "H. G. Carroll & Associates"

        Case "03"

            strLHeader = "International Hydraulic Components"

        Case "05"

            strLHeader = "Casting Sales"

      ' continue with other cases        Case Else

            strLHeader = vbNullString

    End Select

    For i = 1 To ThisWorkbook.Worksheets.Count

        ThisWorkbook.Worksheets(i).PageSetup.LeftHeader = strLHeader

    Next i

End Sub

Hope that helps.

Cheers

Rich

Answer 2

I was able to solve the "Run-time error '9' by expanding the code for each rep but  this does not appear to be an affective use of resources. Is there a more efficient way to code the following?

Public Sub Workbook_BeforePrint(Cancel As Boolean)

With ThisWorkbook.Worksheets("Rep - 02").PageSetup

    Select Case LCase(Range("A2").Text)

        Case "02"

            .LeftHeader = "H. G. Carroll & Associates"

        Case "03"

            .LeftHeader = "International Hydraulic Components"

        Case "05"

            .LeftHeader = "Casting Sales"

        ' continue with other cases

        Case Else

            .LeftHeader = vbNullString

    End Select

End With

With ThisWorkbook.Worksheets("Rep - 03").PageSetup

    Select Case LCase(Range("A2").Text)

        Case "02"

            .LeftHeader = "H. G. Carroll & Associates"

        Case "03"

            .LeftHeader = "International Hydraulic Components"

        Case "05"

            .LeftHeader = "Casting Sales"

        ' continue with other cases

        Case Else

            .LeftHeader = vbNullString

    End Select

End With

With ThisWorkbook.Worksheets("Rep - 05").PageSetup

    Select Case LCase(Range("A2").Text)

        Case "02"

            .LeftHeader = "H. G. Carroll & Associates"

        Case "03"

            .LeftHeader = "International Hydraulic Components"

        Case "05"

            .LeftHeader = "Casting Sales"

        ' continue with other cases

        Case Else

            .LeftHeader = vbNullString

    End Select

End With

End Sub

Answer 3

Chip,

After implementing your suggestion, I now get the following error message "Run-time error '9': Subscript out of range".

Any further suggestions?

Thanks!

Steve

Answer 4

You can only do what you want using VBA code. There is nothing the Excel to link headers to cells. Open the VBA Editor (ALT F11) and then open the Project window (CTRL R) if it is not already open on the left side of the VBA edtior. Pick your workbook in the Project window, double-click it to open the module list, and expand the "folder" Microsoft Excel Objects. There, double-click the ThisWorkbook item.Paste in the following code:

Public Sub Workbook_BeforePrint(Cancel As Boolean)

With ThisWorkbook.Worksheets("Sheet1").PageSetup

    Select Case LCase(Range("A2").Text)

        Case "02"

            .CenterHeader = "Carroll & Associates"

        Case "03"

            .CenterHeader = "Hydraulic Components"

        Case "05"

            .CenterHeader = "Casting Sales"

        ' continue with other cases

        Case Else

            .CenterHeader = vbNullString

    End Select

End With

End Sub

Answer 5

Yes Chip, you are correct. I need the Page Setup Header to display the proper text based on the contents of cell A2. Is this possible?

I don't understand what you mean "Code in the BeforeSave event would allow for a custom page header". Please explain further.

Thank you!

Steve