Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

Are you sure you have the correct addresses in your code?

(You may want to give them nice range names and use those instead--it may be
safer if rows/columns can be inserted/deleted.)

Are you sure you still have .enableevents set to true before you print/print
preview?

It could be other code that turns that off--and doesn't turn it back on.

Karen53 wrote:

Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

--

Dave Peterson

Hi Dave,

Thanks for your help.

Yes, enable events is on and it is the correct address. I have found that
if I select 'Print Preview' it corrects the problem and the page prints
correctly. If I change the cell value and do not 'Print Preview' first, the
header does not update with the new value. What is this? Do you know?
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Are you sure you have the correct addresses in your code?

(You may want to give them nice range names and use those instead--it may be
safer if rows/columns can be inserted/deleted.)

Are you sure you still have .enableevents set to true before you print/print
preview?

It could be other code that turns that off--and doesn't turn it back on.

Karen53 wrote:

Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

--

Dave Peterson

Hi Dave,

I have added these two statements right before my print command and I am
still having the same problem..

Application.EnableEvents = True
Application.ScreenUpdating = True

I've named the range as you suggested and am using that instead. So, it is
the correct location.

Do you have any other suggestions?
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Are you sure you have the correct addresses in your code?

(You may want to give them nice range names and use those instead--it may be
safer if rows/columns can be inserted/deleted.)

Are you sure you still have .enableevents set to true before you print/print
preview?

It could be other code that turns that off--and doesn't turn it back on.

Karen53 wrote:

Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

--

Dave Peterson

The named ranges won't fix the problem. But it may avoid future problems.



Karen53 wrote:

Hi Dave,

I have added these two statements right before my print command and I am
still having the same problem..

Application.EnableEvents = True
Application.ScreenUpdating = True

I've named the range as you suggested and am using that instead. So, it is
the correct location.

Do you have any other suggestions?
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Are you sure you have the correct addresses in your code?

(You may want to give them nice range names and use those instead--it may be
safer if rows/columns can be inserted/deleted.)

Are you sure you still have .enableevents set to true before you print/print
preview?

It could be other code that turns that off--and doesn't turn it back on.

Karen53 wrote:

Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson

Without any testing at all...

How about adding this right before the End Sub

DoEvents

Maybe it'll allow some time for excel to catch up before the actually printing.

But I really don't have a good reason why print preview works and print doesn't.

Karen53 wrote:

Hi Dave,

Thanks for your help.

Yes, enable events is on and it is the correct address. I have found that
if I select 'Print Preview' it corrects the problem and the page prints
correctly. If I change the cell value and do not 'Print Preview' first, the
header does not update with the new value. What is this? Do you know?
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Are you sure you have the correct addresses in your code?

(You may want to give them nice range names and use those instead--it may be
safer if rows/columns can be inserted/deleted.)

Are you sure you still have .enableevents set to true before you print/print
preview?

It could be other code that turns that off--and doesn't turn it back on.

Karen53 wrote:

Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson

Hi Dave,

I added DoEvents right before the End Sub of my BeforePrint routine but it
didn't make any difference. This is really frustrating.

--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Without any testing at all...

How about adding this right before the End Sub

DoEvents

Maybe it'll allow some time for excel to catch up before the actually printing.

But I really don't have a good reason why print preview works and print doesn't.

Karen53 wrote:

Hi Dave,

Thanks for your help.

Yes, enable events is on and it is the correct address. I have found that
if I select 'Print Preview' it corrects the problem and the page prints
correctly. If I change the cell value and do not 'Print Preview' first, the
header does not update with the new value. What is this? Do you know?
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Are you sure you have the correct addresses in your code?

(You may want to give them nice range names and use those instead--it may be
safer if rows/columns can be inserted/deleted.)

Are you sure you still have .enableevents set to true before you print/print
preview?

It could be other code that turns that off--and doesn't turn it back on.

Karen53 wrote:

Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet. It
worked except now if the value of the cell changes, it is not picking up the
change and stays with the old value in the header. This there something I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

Hi,

Thank you for the suggestion, Tim. It was a great idea. My immediate
window shows the code executing and returns the correct Header, but the
printed copy still shows the old header.

Here are the results from my immediate window...

Starting BeforePrint, HdrVal = &C&14&BDayna's Mall&4
&12&A

&C&14&BDayna's Mall&4
&12&A

--
Thanks for your help.
Karen53


"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53


"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

Hmm, it works for me. If you try this in a new workbook, does the same
behavior occur? Does this occur for a different printer? I'm wondering if
there's a page setup configuration / zoom factor / page breaks / preview
mode / which sheet is active / how is printing being invoked or some other
page-setup issue that is causing the code to be buggy.


--
Tim Zych
SF, CA

"Karen53" wrote in message
...
This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for
the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53


"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before
it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module
which
places a custom header on pages based on the value of cells in the
sheet.
It
worked except now if the value of the cell changes, it is not picking
up
the
change and stays with the old value in the header. This there
something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value &
_
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson

Hi,

Ok, it works as long as I use the print button on the toolbar. As soon as
I use code to print the page it stops working. I have tried the printing
code two ways.

With Sheets(ShName)
.Range(Startcol & StartRow & ":" & EndCol & RptEndRow).PrintOut _


Copies:=1, Collate:=True
End With

With Sheets(ShName)
.Range(Startcol & StartRow & ":" & EndCol & RptEndRow).Select
.PageSetup.PrintArea = Startcol & StartRow & ":" & EndCol & RptEndRow
Selection.PrintOut Copies:=1, Collate:=True
End With

--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson

I wouldn't use the .select and Selection lines. To select a range, the sheet
that owns that range has to be active--and that may not be true.

But I'm not sure what you mean by stops working. Does this mean that the code
doesn't even run--or it runs, but the headers don't change (or printareas don't
change).

And if you're running this stuff using code, is there any chance that you've
disabled events before doing the printout. If you did, then the
workbook_beforeprint won't even fire.

Karen53 wrote:

Hi,

Ok, it works as long as I use the print button on the toolbar. As soon as
I use code to print the page it stops working. I have tried the printing
code two ways.

With Sheets(ShName)
.Range(Startcol & StartRow & ":" & EndCol & RptEndRow).PrintOut _


Copies:=1, Collate:=True
End With

With Sheets(ShName)
.Range(Startcol & StartRow & ":" & EndCol & RptEndRow).Select
.PageSetup.PrintArea = Startcol & StartRow & ":" & EndCol & RptEndRow
Selection.PrintOut Copies:=1, Collate:=True
End With

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Does it work when you use:
File|print
or
File|Print preview



Karen53 wrote:

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

Yes, what I mean is the code executes but the header does not change. The
immediate window shows the new header but the page's header does not change.

I tried adding an Application.EnableEvents = True before it but it still
did not change.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

I wouldn't use the .select and Selection lines. To select a range, the sheet
that owns that range has to be active--and that may not be true.

But I'm not sure what you mean by stops working. Does this mean that the code
doesn't even run--or it runs, but the headers don't change (or printareas don't
change).

And if you're running this stuff using code, is there any chance that you've
disabled events before doing the printout. If you did, then the
workbook_beforeprint won't even fire.

Karen53 wrote:

Hi,

Ok, it works as long as I use the print button on the toolbar. As soon as
I use code to print the page it stops working. I have tried the printing
code two ways.

With Sheets(ShName)
.Range(Startcol & StartRow & ":" & EndCol & RptEndRow).PrintOut _


Copies:=1, Collate:=True
End With

With Sheets(ShName)
.Range(Startcol & StartRow & ":" & EndCol & RptEndRow).Select
.PageSetup.PrintArea = Startcol & StartRow & ":" & EndCol & RptEndRow
Selection.PrintOut Copies:=1, Collate:=True
End With

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

Yes, it changes if I use either File/Print or Print Preview/Print. It works
everywhere but for my code.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Does it work when you use:
File|print
or
File|Print preview



Karen53 wrote:

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

It will not work if it is in ThisWorkbook's 'BeforePrint' routine. However,
if I move the code into the individual print routines in the general modules,
it works just fine so far.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Does it work when you use:
File|print
or
File|Print preview



Karen53 wrote:

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I still don't have a guess.

Sorry.

Karen53 wrote:

Hi Dave,

It will not work if it is in ThisWorkbook's 'BeforePrint' routine. However,
if I move the code into the individual print routines in the general modules,
it works just fine so far.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Does it work when you use:
File|print
or
File|Print preview



Karen53 wrote:

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

HI Dave,

Since it's working in the general modules, I just dumped the 'BeforePrint'.
Why fight city hall.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

I still don't have a guess.

Sorry.

Karen53 wrote:

Hi Dave,

It will not work if it is in ThisWorkbook's 'BeforePrint' routine. However,
if I move the code into the individual print routines in the general modules,
it works just fine so far.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Does it work when you use:
File|print
or
File|Print preview



Karen53 wrote:

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Well, you lasted longer than I would have <vbg.


Karen53 wrote:

HI Dave,

Since it's working in the general modules, I just dumped the 'BeforePrint'.
Why fight city hall.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still don't have a guess.

Sorry.

Karen53 wrote:

Hi Dave,

It will not work if it is in ThisWorkbook's 'BeforePrint' routine. However,
if I move the code into the individual print routines in the general modules,
it works just fine so far.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Does it work when you use:
File|print
or
File|Print preview



Karen53 wrote:

Hi,

Also it's the same for both workbooks. It was working because I was using
the print button on the toolbar.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I still can't duplicate it. I don't have another guess.

Karen53 wrote:

Hi,

So far I have tried this at home and at work with the same result. I have
created a new workbook and the code works. I thought perhaps it was because
the workbook was protected. So, I tried it and it still worked. There has
to be something different with this workbook. I am going through what I can
think of one at a time to see if it makes a difference.

It executes the code but doesn't make the change. So far I haven't fouind
the difference. I'll continue tying difference things tomorrow. There are
only so many things I know of to try, though.

I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as
well. I'll check tomorrow.

This is so strange. I don't have a lot of experience with this. Your help
is very appreciated.
---
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Just to muddy the waters...

What version of excel are you using?
What type of view are you using: Normal, page break preview, print layout?
If you create a new workbook with just enough data for testing and plop the code
in there, does that look ok?

I don't have a guess why it's not working for you.

Karen53 wrote:

This is so frustrating! What is going on?

The immediate window shows the code executing with the correct value for the
header variable, yet the header on the worksheet does not change.

If I do a print preview, the worksheet header changes.

I've run out of things to try.

--
Thanks for your help.
Karen53

"Tim Zych" wrote:

Try assigning it to a variable so the string can be fully resolved before it
goes into the header, e.g.

Dim HdrVal As String
HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
Debug.Print HdrVal
.CenterHeader = HdrVal

Then you can inspect it too to help narrow down the problem.

--
Tim Zych
SF, CA

"Karen53" wrote in message
...
Hi,

I have the following BeforePrint routine in my ThisWorkbook module which
places a custom header on pages based on the value of cells in the sheet.
It
worked except now if the value of the cell changes, it is not picking up
the
change and stays with the old value in the header. This there something
I
am missing?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'add custom header to pages before printing

With SumbyLineItempg.PageSetup
.CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With LineItemspg.PageSetup
.CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

With MainPagepg.PageSetup
.CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _
"&4" & vbCrLf & "&12&A" & vbCrLf & " "
End With

End Sub
--
Thanks for your help.
Karen53




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson