I have a workbook with about 15 different worksheets. The main sheets
are named week 1 through week 6. I also have a sheet named recap. Here
is the question. When a user goes to print say Week 2 I would like a
msgbox to appear and ask "do you want to print the recap sheet also?"
This is where I am stumped. My thinking is that I should send sub
procdure to call from before print. But then how do I get it to print
the recap. I AM SO CONFUSED!!!!!

Pete, try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Msg = "Do you want to print the recap sheet also ?"
Title = "Print Recap Sheet ?"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub ' Quit the macro
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("recap").PrintOut Copies:=1
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"Pete" wrote in message
om...
I have a workbook with about 15 different worksheets. The main sheets
are named week 1 through week 6. I also have a sheet named recap. Here
is the question. When a user goes to print say Week 2 I would like a
msgbox to appear and ask "do you want to print the recap sheet also?"
This is where I am stumped. My thinking is that I should send sub
procdure to call from before print. But then how do I get it to print
the recap. I AM SO CONFUSED!!!!!

Paul, thanks, this is what I was looking for. Could not figure it out
myself.


"Paul B" wrote in news:O6jtX5mvDHA.2508
@TK2MSFTNGP12.phx.gbl:

Pete, try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Msg = "Do you want to print the recap sheet also ?"
Title = "Print Recap Sheet ?"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub ' Quit the macro
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("recap").PrintOut Copies:=1
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Pete, Your welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"Pete" wrote in message
...
Paul, thanks, this is what I was looking for. Could not figure it out
myself.


"Paul B" wrote in news:O6jtX5mvDHA.2508
@TK2MSFTNGP12.phx.gbl:

Pete, try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Msg = "Do you want to print the recap sheet also ?"
Title = "Print Recap Sheet ?"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub ' Quit the macro
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("recap").PrintOut Copies:=1
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub