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" <(E-Mail Removed)> wrote in message
news:56D947AB-2738-440F-B202-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:56D947AB-2738-440F-B202-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:56D947AB-2738-440F-B202-(E-Mail Removed)...
> > 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
>
>
>