CenterHeader, RightHeader, LeftHeader

[Ross Culver]

I have the following code:

With Worksheets("Report").PageSetup
.RightHeader = xRightHeader
.CenterHeader = xCenterHeader
.LeftHeader = xLeftHeader
End With

The xVariables are defined and populated just prior to this code.

I get the following error: Run-time error '1004' "Unable to set the
LeftHeader property of the PageSetup class".

If I swap the code around like this:

With Worksheets("Report").PageSetup
.RightHeader = xRightHeader
.CenterHeader = xCenterHeader
.LeftHeader = xLeftHeader
End With

I get: Run-time error '1004' "Unable to set the RightHeader property of the
PageSetup class".

If I remove either the right, the left or the center, the code works fine.
Is there some kind of limitation that prevents us from setting all three of
the headers?

Ross

 

[Bill Renaud]

I don't see any difference in your 2 versions of your code. The following
demo code worked fine for me (Excel 2000), even if I set one of them to a
blank string (""):

Public Sub Test()
Const xLeftHeader = "&F"
Const xCenterHeader = "&T"
Const xRightHeader = "&D"

With Worksheets("Report").PageSetup
.RightHeader = xRightHeader
.CenterHeader = xCenterHeader
.LeftHeader = xLeftHeader
End With
End Sub

Usually when I start having these kind of problems, I back up and declare
object variables, so that I can step through the code and verify
everything. In other words, try the following:

Public Sub Test()
Const xLeftHeader = ""
Const xCenterHeader = "&T"
Const xRightHeader = "&D"
Dim wsReport As Worksheet

Set wsReport = ActiveWorkbook.Worksheets("Report")

With wsReport.PageSetup
.RightHeader = xRightHeader
.CenterHeader = xCenterHeader
.LeftHeader = xLeftHeader
End With
End Sub

It may be time to clear the symbol tables by exporting all of your code
modules and worksheet event handlers, saving the workbook without any code,
then re-importing and recompiling the project. After lots of editing (and
variable name changes, etc.), VBA can get confused sometimes.

<<Is there some kind of limitation that prevents us from setting all three
of the headers?>>

Not that I know of. What version of Excel are you running?