VBA to Print First Page Only

[postelrich]

I have made a form on a spreadsheet that is fit to only one page. But when it goes to print, it prints 50 pages of stuff that isn't needed. Since this is going to be used by other people is there a way to either:

1. No matter what, everytime they try print, it will only print the first page of sheet 1?

2. Disable printing all together and force them to use a button that will print the first page only.

I've tried playing around with

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Sheets("Sheet1").PrintOut From:=1, To:=1
End Sub

this but haven't had any luck getting it to work. I even tried an If print activesheet = sheet1 cancel print, but that didn't work either. Any help would be greatly appreciated!

 

[jvanbonn]

What version of excel do you use?

I know that 2007 saves workbooks as either xlsm (macro) or xlsx (no-macro). If I use a xlsm, the file has to be stored in a trusted location to execute the macro.

Just a thought.

 

[postelrich]

I am using Excel 2007 and it is a macro-enabled workbook. Thats not the problem. Something in the code, or the code is wrong all together.

 

[jvanbonn]

Is your code saved in the "ThisWorkbook" and not a Module?

 

[postelrich]

Is your code saved in the "ThisWorkbook" and not a Module?

Yes sir

 

[John Davis]

I am using Excel 2007 and it is a macro-enabled workbook. Thats not the problem. Something in the code, or the code is wrong all together.

It may not be your code. Double check your print preview and see what that shows. You may have to adjust your margins or paging.

 

[postelrich]

I got the option of disabling printing and forcing the user to use a button to print by this in ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
     
    Cancel = (ActiveSheet.Name = "Sheet1")
     
   If Cancel = True Then MsgBox "Sorry, Please use the print button."
End Sub

And this on the button click:

Private Sub CommandButton3_Click()
On Error Resume Next
    Application.EnableEvents = False
        With ActiveWorkbook.Sheets("Sheet1")
                .Range("A1:K41").PrintOut
        End With
    Application.EnableEvents = True
    On Error GoTo 0
End Sub