Is it possible to intercept the print button with a routine that will print
out the contents of 2 worksheets?

I'm trying to idiot-proof the printing out of the contents of two worksheets
when the user either hits the print button or selects "print" from the file
menu.

In the ThisWorbook module there is a before_print event that may be taught
to do this. UNtested

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet12" Then
Sheets("sheet12").PrintOut 'Preview
Sheets("sheet9").PrintOut 'Preview
End If
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
Is it possible to intercept the print button with a routine that will
print
out the contents of 2 worksheets?

I'm trying to idiot-proof the printing out of the contents of two
worksheets
when the user either hits the print button or selects "print" from the
file
menu.

This code would cause any use of the print option to print the two sheets you
specify in it - has the disadvantages of:
Always printing those two, even if you want to only print one or the other,
or some totally different sheet(s).
Leaving the two sheets selected - so anything the user might change without
first making sure that only a single sheet is selected would appear in the
same cells on both sheets - that could be very problematic.

the code

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets(Array("FirstSheet Name", "Second SheetName")).Select
End Sub

This is a Workbook event processor and must go into the Workbook's code
module. Easy way to get there is to right-click on the Excel Icon
immediately to the left of the word "File" in the main Excel menu and choose
[View Code] from the list that pops up. Copy the code above and paste it
into the module presented to you in the VB Editor. Edit the two worksheet
names in the code to match what your sheets are named. Close the VB Editor
and give it a try.
"fedude" wrote:

Is it possible to intercept the print button with a routine that will print
out the contents of 2 worksheets?

I'm trying to idiot-proof the printing out of the contents of two worksheets
when the user either hits the print button or selects "print" from the file
menu.

J,

Bingo. This worked fine. As you and Don suggested, intercepting the
Workbook_BeforePrint event accomplishes exactly what I asked for Thank You!.

You raise a couple of good points.

Maybe a better solution would be to create another custom button for the
toolbar that when pressed prints out the two ranges from the 2 sheets in
question. Is this even possible?

Or is there another way to idiot-proof the printing operation

Yes, you can do much of what you have asked regarding a custom button.

Here's what a lot of people, including myself, do with such special things
instead of creating a custom toolbar icon or adding a new menu item:

Start by recording a macro while selecting and printing the two pages.
Use View | Toolbars and bring the Forms toolbar into view. Choose the
command button from that menu and draw one on one of the sheets. Once it's
drawn it will bring up an "Assign Macro?" dialog and offer a default macro
name, don't use that, click on the name of the one you recorded. Edit the
label on the button. You're done!

If you want something a little more colorful, you can use a shape from the
Drawing toolbar in that same fashion: once you've drawn it on the sheet and
added text to it, right-click it and choose Assign Macro and choose the
appropriate macro.

If you do either of these, be sure and remove the Workbook_BeforePrint()
code that we gave you earlier.

"fedude" wrote:

J,

Bingo. This worked fine. As you and Don suggested, intercepting the
Workbook_BeforePrint event accomplishes exactly what I asked for Thank You!.

You raise a couple of good points.

Maybe a better solution would be to create another custom button for the
toolbar that when pressed prints out the two ranges from the 2 sheets in
question. Is this even possible?

Or is there another way to idiot-proof the printing operation