Excel Workbook Printing problem?

Question

I would like to know how to do the following:

In a excel workbook with multiple spreadsheets (sheet1,sheet2,sheet3, etc), I want ONLY the first sheet (sheet1) to print out regardless of which sheet the user is on. So if they are on sheet2 or sheet3 the intended output sheet that prints is always sheet1.

I tried changing the print area, but a dialogue box pops up saying that the print area reference must be on the same sheet.

Does anyone know how to make this work?

Thanks

Answer 1

Here is a way to prevent printing anything but Sheet1.

Open your workbook.

Copy these macros to the clipboard.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CommandBars("File"). _

Controls("Print...").Enabled = True

Application.CommandBars("standard"). _

Controls(6).Enabled = True

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.CodeName <> "Sheet1" Then

Cancel = True

Else

Sheets("Sheet1").Select

ActiveSheet.PrintOut

End If

End Sub

Private Sub Workbook_Open()

Application.CommandBars("File"). _

Controls("Print...").Enabled = False

Application.CommandBars("standard"). _

Controls(6).Enabled = False

End Sub

Sub Print_Pg1()

If ActiveSheet.UsedRange = "" Then

MsgBox "There is nothing to print.", _

vbOKOnly, "Print Terminated"

Exit Sub

End If

Sheets("Sheet1").Select

ActiveSheet.PrintOut

End Sub

Press ALT + F11

Double click on This Workbook in the Microsoft Excel Objects, upper left.

Paste the macros into the space to the right.

Close back to Excel.

Go to View  >  Toolbars

Click on the Forms Toolbar.

Select Sheet1

Select the Button icon, column 2 second from the top.

Click on Sheet1 and drag a button to the size you want it to be.

When the Assign Macro window opens, click on the Print_Pg1 macro.

Click on the button and replace Button 1 with 'Print'.  

Move the button to a location you like.  If you wish, you can copy it and paste it on other pages.  When clicked, it will only print Sheet1.

Save the workbook.

Now, when you open this workbook, File>Print will be disabled, the printer icon in the standard toolbar will be disabled, and Control + P will be disabled.  They will all be re-enabled when you close this workbook and available to any other workbook you want to open.

Answer 2

You can't use the print short cut, you have to go to file, print, and then select the page you want.

Answer 3

As far as I know you only get a choice of the whole workbook or active sheet. Click on the tab of sheet on and choose active sheet.