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

I think he's asking about the Page Setup header not a "header" row on the worksheet. Code in the BeforeSave event would allow for a custom page header.

Answer 4

Here is the link:

http://wikisend.com/download/122616/2310 v2.5 Commissions July 2012.xlsm

Please ask if you have any questions.

Thank you!

Steve

Answer 5

Yes, you should.

Please upload the file to some server and post the link to the uploaded file here.