Print problem
 

I'm using Excel 2000 in WinXP.
When I press the print preview button the BeforePrint routine below works
fine.

ThisWorkbook:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Set header &
footer
ActiveSheet.PageSetup.CenterHeader = "&30 &B" & "" & Format("Laser
Department" & Chr(10) _
& "CHROMM Usage - " & ActiveSheet.Name)
ActiveSheet.PageSetup.CenterFooter = "&16 " & "" & Format(Now(),
"mmmm-dd-yyyy,") & " &T"
End Sub

But when the BeforePrint routine is triggered with the macro below the
header and date don't update although the time updates fine....
(the macro is assigned to a custom toolbar button)

Sub Print_Sheets()
Dim Printer As String

Printer = Application.Dialogs(xlDialogPrinterSetup).Show 'printer
select
If Printer = "False" Then Exit Sub
'cancel, exit

Sheets(Sheets.Count - 1).Activate
'210 active
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False,
ActivePrinter:=Printer
Sheets(Sheets.Count).Activate
'130 active
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False
End Sub

Can anyone explain this or point me in the right direction ???
Thanks!

RJH

Print problem
 

Perhaps a well placed Application.DoEvents might help? Maybe after this
line?

Sheets(Sheets.Count - 1).Activate


Just a thought....

Print problem
 

? Application.Dialogs(xlDialogPrinterSetup).Show
True

So printer will hold a value of "True" or "False" not the printer
selected - although the printer selected will be set as the default if it
equals True.

--
Regards,
Tom Ogilvy





"RJH" wrote in message
...
I'm using Excel 2000 in WinXP.
When I press the print preview button the BeforePrint routine below works
fine.

ThisWorkbook:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Set header &
footer
ActiveSheet.PageSetup.CenterHeader = "&30 &B" & "" & Format("Laser
Department" & Chr(10) _
& "CHROMM Usage - " & ActiveSheet.Name)
ActiveSheet.PageSetup.CenterFooter = "&16 " & "" & Format(Now(),
"mmmm-dd-yyyy,") & " &T"
End Sub

But when the BeforePrint routine is triggered with the macro below the
header and date don't update although the time updates fine....
(the macro is assigned to a custom toolbar button)

Sub Print_Sheets()
Dim Printer As String

Printer = Application.Dialogs(xlDialogPrinterSetup).Show 'printer
select
If Printer = "False" Then Exit Sub
'cancel, exit

Sheets(Sheets.Count - 1).Activate
'210 active
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False,
ActivePrinter:=Printer
Sheets(Sheets.Count).Activate
'130 active
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False
End Sub

Can anyone explain this or point me in the right direction ???
Thanks!

RJH

Print problem
 

I've solved the problem.
I put the footer in the page setup.
I moved the header into the 'Print Sheets' macro.
Now, everything works fine.

I tried the DoEvents but it didn't help.
Tom, I didn't really understand what you were trying to tell me.

Thanks for the help.

RJH


"RJH" wrote in message
...
I'm using Excel 2000 in WinXP.
When I press the print preview button the BeforePrint routine below works
fine.

ThisWorkbook:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Set header &
footer
ActiveSheet.PageSetup.CenterHeader = "&30 &B" & "" & Format("Laser
Department" & Chr(10) _
& "CHROMM Usage - " & ActiveSheet.Name)
ActiveSheet.PageSetup.CenterFooter = "&16 " & "" & Format(Now(),
"mmmm-dd-yyyy,") & " &T"
End Sub

But when the BeforePrint routine is triggered with the macro below the
header and date don't update although the time updates fine....
(the macro is assigned to a custom toolbar button)

Sub Print_Sheets()
Dim Printer As String

Printer = Application.Dialogs(xlDialogPrinterSetup).Show 'printer
select
If Printer = "False" Then Exit Sub 'cancel, exit

Sheets(Sheets.Count - 1).Activate '210 active
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False,
ActivePrinter:=Printer
Sheets(Sheets.Count).Activate '130 active
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False
End Sub

Can anyone explain this or point me in the right direction ???
Thanks!

RJH