Share via

Excel VBA: CenterHeader doesn't work

Anonymous
2020-12-17T02:41:43+00:00
  1. Record an Excel macro that puts text into the three sections of the header: Left, Center, and Right.

Excerpt:

Sub Write_Header()

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .LeftHeader = "FISH"

        .CenterHeader = "CAT"

        .RightHeader = "DOG"

    End With

    Application.PrintCommunication = True

End Sub

  1. Change the text that gets written in.

Sub Write_Header()

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .LeftHeader = "LEFT"

        .CenterHeader = "CENTER"

        .RightHeader = "RIGHT"

    End With

    Application.PrintCommunication = True

End Sub

  1. Run the macro.

The header says "LEFT" "CAT" "RIGHT" and the Center code doesn't work. This was repeatable for Microsoft reps.

How do I write to the center section of the header in Excel using VBA?

My actual code pulls in data from cells, but likewise, Center doesn't work.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2020-12-17T04:40:02+00:00

    Hi Rob. I’m RN, an Independent Advisor and Microsoft user like you.

    I'm sorry to hear about this issue.

    I encountered this issue before so what I do is I removed the code below.

     Application.PrintCommunication = False

    Then Run the macro again then check the print preview of it.

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-12-17T14:22:00+00:00

    RN, Thank you for a rapid reply and a solution that works. Now I can finish my project.

    This solution does work, and I will use it today, but it doesn't make sense. Why would turning off communication with the printer clobber 1/3 of the header? I know there is some WYSIWYG between Office programs and printers, but yikes.

    So I tried doing the same thing recording a macro that types into the three sections of the header in Page Layout view (not into the dialog box). Turning off communication with the printer (which the recorder still does) and running the macro clobbers 2/ 3 of the header functionality.

    This feels like a bug, doesn't it? Perhaps the macro recorder should skip adding the Application.PrintCommunication toggle and let us research how to speed up the run rather than covertly clobber some of the code's functionality.

    Since I'm not turning off print communication, I don't need to turn it back on, so I'll remove that line as well.

    Thank you again.

    =Rob

    0 comments