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

Hi,

Ensure the following:

1. There is an = symbol before the formula; and
2. The formula should be formatted as General.  Press Ctrl+1 on that cell and under Number, ensure that General is selected

Press F2 on the cell and press Enter.

Does it work now?

Answer 4

Wow, I must be missing something!

This formula does not return the desired results, Instead, the formula as shown above appears in the report header. What am I missing?

Steve

Answer 5

Hi,

Try this

In the header cell, enter this formula

=IF(B2="02","Carroll & Associates",IF(B2="03","Hydraulic Components",IF(B2="05",Casting Sales","")))

Hope this helps.