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?

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?
.

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?