Unable to set the LeftHeader property of the PageSetup class

[John]

I keep getting the following message:
"Unable to set the LeftHeader property of the PageSetup class". I'm trying
to reference a cell value in the header with the following
WorkBook_BeforePrint event code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("1").PageSetup.LeftHeader = "&""Arial,Bold""Client No. " &
Worksheets("1").Range("ClientNum1").Value
Worksheets("2").PageSetup.LeftHeader = "&""Arial,Bold""Client No. " &
Worksheets("2").Range("ClientNum2").Value
Worksheets("3").PageSetup.LeftHeader = "&""Arial,Bold""Client No. " &
Worksheets("3").Range("ClientNum3").Value
End Sub

The print precedure (partial) is:

Sub Test()
Worksheets("1").Range("Page1").PrintOut
End Sub

The WorkBook_BeforePrint event fails when I call the Test procedure. Why??
When I print preview, it assigns the range value to the header properly,
which tells me the WorkBook_BeforePrint code is correct.

Please note that I have named my sheets numbers

 

[Barb Reinhardt]

By any chance are you getting error 1004? I wonder if you have a named
range called "Page1".

 

[JLGWhiz]

Try this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets(1).PageSetup.LeftHeader = "&""Arial,Bold""""Client No. """ & _
Worksheets(1).Range("ClientNum1").Value
Worksheets(2).PageSetup.LeftHeader = "&""Arial,Bold""''Client No.'' " & _
Worksheets(2).Range("ClientNum2").Value
Worksheets(3).PageSetup.LeftHeader = "&""Arial,Bold""''Client No. ''" & _
Worksheets(3).Range("ClientNum3").Value
End Sub

 

[JLGWhiz]

John, I thought you needed more quote marks, but actually you did not after I
took a closer look. I put your code in my ThisWorkbook module and used the
print button. I substituted a cell reference for your range reference but it
printed the header without sending a message. I also set up a print command
in the standard module in VBA to print all three pages and it put the headers
on without sending a message. I cannot duplicate your error. I assume,
since you use quote marks on your sheet numbers that you have named the
sheets "1", "2" and "3". If not, the quotes should be removed.

 

[Barb Reinhardt]

As I read this again, I wonder if your events are enabled. But then, it
shouldn't trigger at all if they aren't. I tried something similar and the
beforeprint ran when I did a ActiveSheet.printout.

 

[Barb Reinhardt]

I did some googling and found some comments relative to new printers. Are
you still using the same printer? Also, what version of Excel are you
having a problem with.

 

[ShaneDevenshire]

Hi,

Take a look at the following, it may pertain to your situation:

http://support.microsoft.com/kb/291298/en-us

If this helps, please click the Yes button.

 

[James]

I appreciate all the help. I still get the error message. I'm using Excel
2007, and I have printer drivers installed. And yes, it is run time error
1004. Frustrating...