I'm using the Workbook_BeforePrint event to place a value from a worksheet
cell in a print header. The cell is located in a different worksheet from
the one that contains the print range, and I'm using an expression of the
form

With ActiveSheet.PageSetup
.CenterHeader = ""
.LeftHeader = Worksheets("Sheet1").Range("Range1")
End With

This woks fine except for one problem: This code doesn't always update the
print header with the most current values in Range1. Even if I save the
workbook after making the change in Range1, the printed page doesn't reflect
the change right away, and prints the previous value that was in Range1.
The one thing I noticed that causes the latest change to appear in the
printout is if I go into Print Preview before printing the page. In that
case, Print Preview seems to somehow update the process so that when I print
out the hard copy, I've got the current data.

It almost seems like there's some kind of a buffer somewhere for print
settings, which isn't getting cleared until some other event like Print
Preview occurs. Does anyone know what I can do to get my headers to reflect
the current values in the referenced cell without having to open Print
Preview first?

Thanks in advance.

Paul

Paste this in the worksheet code module you wish to change the header in.
A1 is the cell that changes the header, you can change this to any cell of
course.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
ActiveSheet.PageSetup.CenterHeader = "&""Arial,Italic""&18" & Range("a1")
End If
End Sub

--
Regards,
Rocky McKinley


"Paul James" wrote in message
...
I'm using the Workbook_BeforePrint event to place a value from a worksheet
cell in a print header. The cell is located in a different worksheet from
the one that contains the print range, and I'm using an expression of the
form

With ActiveSheet.PageSetup
.CenterHeader = ""
.LeftHeader = Worksheets("Sheet1").Range("Range1")
End With

This woks fine except for one problem: This code doesn't always update the
print header with the most current values in Range1. Even if I save the
workbook after making the change in Range1, the printed page doesn't
reflect
the change right away, and prints the previous value that was in Range1.
The one thing I noticed that causes the latest change to appear in the
printout is if I go into Print Preview before printing the page. In that
case, Print Preview seems to somehow update the process so that when I
print
out the hard copy, I've got the current data.

It almost seems like there's some kind of a buffer somewhere for print
settings, which isn't getting cleared until some other event like Print
Preview occurs. Does anyone know what I can do to get my headers to
reflect
the current values in the referenced cell without having to open Print
Preview first?

Thanks in advance.

Paul

Thanks for the reply, Rocky. Unfortunately, when I try to run this code
(after replacing Range("A1") with the range I want to put in the print
header, I get the following error message:

Run time error 424:
Object Required

Any idea what could be wrong?

Also, what is "Target" in this context? I put a message box line at the
beginning of the sub (Msgbox = Target) and it comes up blank, without
displaying anything but the OK button.




"Rocky McKinley" wrote in message
...
Paste this in the worksheet code module you wish to change the header in.
A1 is the cell that changes the header, you can change this to any cell of
course.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
ActiveSheet.PageSetup.CenterHeader = "&""Arial,Italic""&18" & Range("a1")
End If
End Sub

--
Regards,
Rocky McKinley


"Paul James" wrote in message
...
I'm using the Workbook_BeforePrint event to place a value from a
worksheet
cell in a print header. The cell is located in a different worksheet
from
the one that contains the print range, and I'm using an expression of
the
form

With ActiveSheet.PageSetup
.CenterHeader = ""
.LeftHeader = Worksheets("Sheet1").Range("Range1")
End With

This woks fine except for one problem: This code doesn't always update
the
print header with the most current values in Range1. Even if I save the
workbook after making the change in Range1, the printed page doesn't
reflect
the change right away, and prints the previous value that was in Range1.
The one thing I noticed that causes the latest change to appear in the
printout is if I go into Print Preview before printing the page. In
that
case, Print Preview seems to somehow update the process so that when I
print
out the hard copy, I've got the current data.

It almost seems like there's some kind of a buffer somewhere for print
settings, which isn't getting cleared until some other event like Print
Preview occurs. Does anyone know what I can do to get my headers to
reflect
the current values in the referenced cell without having to open Print
Preview first?

Thanks in advance.

Paul

Where did you put the code. Paste your actual code and perhaps someone can
see your error.

--
Regards,
Tom Ogilvy

Paul James wrote in message
...
Thanks for the reply, Rocky. Unfortunately, when I try to run this code
(after replacing Range("A1") with the range I want to put in the print
header, I get the following error message:

Run time error 424:
Object Required

Any idea what could be wrong?

Also, what is "Target" in this context? I put a message box line at the
beginning of the sub (Msgbox = Target) and it comes up blank, without
displaying anything but the OK button.




"Rocky McKinley" wrote in message
...
Paste this in the worksheet code module you wish to change the header
in.
A1 is the cell that changes the header, you can change this to any cell
of
course.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
ActiveSheet.PageSetup.CenterHeader = "&""Arial,Italic""&18" &
Range("a1")
End If
End Sub

--
Regards,
Rocky McKinley


"Paul James" wrote in message
...
I'm using the Workbook_BeforePrint event to place a value from a
worksheet
cell in a print header. The cell is located in a different worksheet
from
the one that contains the print range, and I'm using an expression of
the
form

With ActiveSheet.PageSetup
.CenterHeader = ""
.LeftHeader = Worksheets("Sheet1").Range("Range1")
End With

This woks fine except for one problem: This code doesn't always update
the
print header with the most current values in Range1. Even if I save
the
workbook after making the change in Range1, the printed page doesn't
reflect
the change right away, and prints the previous value that was in
Range1.
The one thing I noticed that causes the latest change to appear in the
printout is if I go into Print Preview before printing the page. In
that
case, Print Preview seems to somehow update the process so that when I
print
out the hard copy, I've got the current data.

It almost seems like there's some kind of a buffer somewhere for print
settings, which isn't getting cleared until some other event like
Print
Preview occurs. Does anyone know what I can do to get my headers to
reflect
the current values in the referenced cell without having to open Print
Preview first?

Thanks in advance.

Paul

Thanks, Tom.

Here's the code I've been using:

Sub Workbook_BeforePrint(cancel As Boolean)
If ActiveSheet.Name = "dataEntry" Then
With ActiveSheet.PageSetup
.LeftHeader = "&12" & Worksheets("Licensee").Range("LicenseeName") &
Chr(10) & _
"&10" & "ID: " & Worksheets("Licensee").Range("LicenseeID")
End With
End If
End Sub

It works fine - the only problem is that it doesn't update the print header
until I open Print Preview, either manually or with VBA. But I don't want
my users to have to do that every time they want to print the page.

Since my original post, I discovered a workaround, although it's not ideal.
If I put that code in a separate Sub and call that Sub from my procedure
that prints the page (right before the lines that print), it will update the
page header. My preference, however, would be to get it to update the page
header from the Workbook_BeforePrint event (without having to open Print
Preview).

BeforePrint fires when you print or when you do printpreview. I haven't
heard of any inherent problem in BeforePrint that would cause it not to
update - you are correct however, that you can not go into View Headers and
Footers and see the change until the print or printpreview has taken place.
Except for curiosity, seeing the change shouldn't be required.

--
Regards,
Tom Ogilvy

Paul James wrote in message
news:QUvQb.139837$xy6.589159@attbi_s02...
Thanks, Tom.

Here's the code I've been using:

Sub Workbook_BeforePrint(cancel As Boolean)
If ActiveSheet.Name = "dataEntry" Then
With ActiveSheet.PageSetup
.LeftHeader = "&12" & Worksheets("Licensee").Range("LicenseeName")
&
Chr(10) & _
"&10" & "ID: " & Worksheets("Licensee").Range("LicenseeID")
End With
End If
End Sub

It works fine - the only problem is that it doesn't update the print
header
until I open Print Preview, either manually or with VBA. But I don't want
my users to have to do that every time they want to print the page.

Since my original post, I discovered a workaround, although it's not
ideal.
If I put that code in a separate Sub and call that Sub from my procedure
that prints the page (right before the lines that print), it will update
the
page header. My preference, however, would be to get it to update the
page
header from the Workbook_BeforePrint event (without having to open Print
Preview).

The strange thing about my application, Tom, is that I can print multiple
copies of the page and yet the headers don't reset until I open Print
Preview. I suppose I'll just continue to use the workaround I described
earlier by calling a different Sub.

Paul