Question:

Excel Workbook Printing problem?

by  |  earlier

0 LIKES UnLike

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

 Tags:

   Report

3 ANSWERS


  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.


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

  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.  

Question Stats

Latest activity: earlier.
This question has 3 answers.

BECOME A GUIDE

Share your knowledge and help people by answering questions.