Update header BeforePrint
 

I would like to create a macro that automatically runs when the worksheet is
printed (prior to actually) I currently have a button that runs this macro
when clicked:

Sub Header()
Dim Header As String
Header = Range("D2").Value
With ActiveSheet.PageSetup
.CenterHeader = "&12Service Body & Options" & Chr(10) & "APS Vehicle#"
& _ Header
End With
End Sub

I would like to get rid of this button and have this routine run when the
printer icon or FilePrint is selected. I played with the
App_WorkbookBeforePrint from the help file but could not get it to work right.

Any ideas?

Update header BeforePrint
 

JC,

Try attaching the code to the "This Worksbook". The MVP's
helped me w/this in the past.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Your other code

With ActiveSheet.PageSetup
.CenterHeader = "&B&12 Your Text"
End With

'Your Other code

End Sub

Dan

-----Original Message-----
I would like to create a macro that automatically runs
when the worksheet is
printed (prior to actually) I currently have a button
that runs this macro
when clicked:

Sub Header()
Dim Header As String
Header = Range("D2").Value
With ActiveSheet.PageSetup
.CenterHeader = "&12Service Body & Options" & Chr
(10) & "APS Vehicle#"
& _ Header
End With
End Sub

I would like to get rid of this button and have this
routine run when the
printer icon or FilePrint is selected. I played with the
App_WorkbookBeforePrint from the help file but could not
get it to work right.

Any ideas?
.

Update header BeforePrint
 

Hi

If your sheet name = "Sheet1" you can use this event in the thisworkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
With ActiveSheet.PageSetup
.CenterHeader = "your header"
End With
End If
End Sub

See this code example for all sheets
http://www.rondebruin.nl/print.htm#Saved



--
Regards Ron de Bruin
http://www.rondebruin.nl



"JCanyoneer" wrote in message ...
I would like to create a macro that automatically runs when the worksheet is
printed (prior to actually) I currently have a button that runs this macro
when clicked:

Sub Header()
Dim Header As String
Header = Range("D2").Value
With ActiveSheet.PageSetup
.CenterHeader = "&12Service Body & Options" & Chr(10) & "APS Vehicle#"
& _ Header
End With
End Sub

I would like to get rid of this button and have this routine run when the
printer icon or FilePrint is selected. I played with the
App_WorkbookBeforePrint from the help file but could not get it to work right.

Any ideas?