VBA to Print First Page Only

P

postelrich

New Member
Joined
Feb 24, 2011
Messages
31
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
Code: 
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
postelrich said:
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.
Click to expand...

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.
 
Upvote 0
I got the option of disabling printing and forcing the user to use a button to print by this in ThisWorkbook:
Code: 
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:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
New to VBA - vba if a cell contains certain text then print that specific page
Replies
2
Views
114
JDSOuth49
J
D
  • Question
Very weird print margin problem, need help !
Replies
0
Views
132
dicklim39
D
tlc53
  • Question
Don't print blank pages, where page breaks are contained between hidden rows
Replies
0
Views
611
tlc53
tlc53
N
  • Solved
Script to determine number of pages to save/print "Microsoft Print to PDF"
Replies
3
Views
548
noveske
N
D
  • Question
VBA Multiple "conditional" page breaks
Replies
3
Views
769
Domenic
D

Forum statistics

Threads
1,214,650
Messages
6,120,736
Members
448,988
Latest member
BB_Unlv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top