Visual Basic Print to PDF

[From128Kto1Gig]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I need to have my Visual basic macro button bring up the print menu instead of sending the file right to the printer, so I can select to have the file print to a pdf when I need to.

Here is one of my macros:

Sub AllYears_IS()
'Income Statement
Sheets("Income Statement").Select
With ActiveSheet.PageSetup
.PrintArea = "$B$13:$BB$117"
.PrintTitleRows = "$16:$16"
.PrintTitleColumns = "$A:$A"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.CenterFooter = "&9Page 1"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments



..CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
'.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B6").Select
End Sub

 

[Laroche J]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I need to have my Visual basic macro button bring up the print menu instead of
sending the file right to the printer, so I can select to have the file print
to a pdf when I need to.

Here is one of my macros:

Sub AllYears_IS()
'Income Statement
Sheets("Income Statement").Select
With ActiveSheet.PageSetup
.PrintArea = "$B$13:$BB$117" (...)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B6").Select
End Sub

Replace
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
by
dlgAnswer = Application.Dialogs(xlDialogPrint).Show

dlgAnswer can be used later to determine if the dialog was cancelled, in
which case dlgAnswer would have the value False.

Search Help for xlDialogPrint, which will lead you to all built-in dialog
boxes that can be called from VBA.

Other suggestions, if you're interested:

Replace
..FitToPagesTall = 2
by
..FitToPagesTall = False
unless you really want to limit the printout to 2 pages. By using False you
don't limit the number of pages, while with FitToPagesWide=1 you still
ensure all your data fits to one page wide.

Remove
Range("B6").Select
which seems to only be an artifact from a macro recording.


JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[From128Kto1Gig]

Replace
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
by
dlgAnswer = Application.Dialogs(xlDialogPrint).Show

dlgAnswer can be used later to determine if the dialog was cancelled, in
which case dlgAnswer would have the value False.

Search Help for xlDialogPrint, which will lead you to all built-in dialog
boxes that can be called from VBA.

Other suggestions, if you're interested:

Replace
.FitToPagesTall = 2
by
.FitToPagesTall = False
unless you really want to limit the printout to 2 pages. By using False you
don't limit the number of pages, while with FitToPagesWide=1 you still
ensure all your data fits to one page wide.

Remove
Range("B6").Select
which seems to only be an artifact from a macro recording.


JL
Mac OS X 10.4.11, Office v.X 10.1.9

Thanks for the info! I still need some help. When I tested the line:

dlgAnswer = Application.Dialogs(xlDialogPrint).Show
on it's own, it runs fine, but in my macro it still prints directly to the printer without poping the dialog screen.
Could this code have something to do with it?; Here is the button code associated with my original code I posted:

Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
UserForm3.Hide

Dim LocRow
Dim LocCol
Dim locname
LocRow = ActiveCell.Row
LocCol = ActiveCell.Column
locname = ActiveCell.Worksheet.Name

Sheets("Income Statement").Unprotect

Dim Status
Status = 1
If Range("ISM").EntireColumn.Hidden = False Then
If Range("ISQ").EntireColumn.Hidden = False Then
Status = 1
Else: Status = 2
End If
ElseIf Range("ISQ").EntireColumn.Hidden = False Then
Status = 3
Else: Status = 4
End If

Range("ISM").EntireColumn.Hidden = True
Range("ISQ").EntireColumn.Hidden = True

'PRINT MACRO
AllYears_IS

Select Case Status
Case 1
Range("ISM").EntireColumn.Hidden = False
Range("ISQ").EntireColumn.Hidden = False
Case 2
Range("ISM").EntireColumn.Hidden = False
Range("ISQ").EntireColumn.Hidden = True
Case 3
Range("ISM").EntireColumn.Hidden = True
Range("ISQ").EntireColumn.Hidden = False
Case 4
Range("ISM").EntireColumn.Hidden = True
Range("ISQ").EntireColumn.Hidden = True
End Select

Sheets("Income Statement").Protect

Sheets(locname).Activate
Cells(LocRow, LocCol).Select

End Sub

Please let me know!
Thanks, GK

 

[From128Kto1Gig]

FYI: I am totally new to Visual Basic and Macros. I am just a long time Mac & Spreadsheet user trying to fix this Macro someone else wrote. Thx, GK

 

[Laroche J]

Thanks for the info! I still need some help. When I tested the line:
dlgAnswer = Application.Dialogs(xlDialogPrint).Show
on it's own, it runs fine, but in my macro it still prints directly to the
printer without poping the dialog screen.

The only thing I can see is that you did not remove the line with
ActiveWindow.SelectedSheets.PrintOut from your printing macro. Did you?
Notice that I wrote "Replace".

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[From128Kto1Gig]

The only thing I can see is that you did not remove the line with

ActiveWindow.SelectedSheets.PrintOut from your printing macro. Did you?
Notice that I wrote "Replace".

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

I did replace it.
On the first try, it just printed direct as I said. Now I get this error notice:

"Compile error

Can't find project or library"

then it highlights just the "dlgAnswer =" part in the new line of code.

FYI: There are several other different routines in the module that I didnt want to change yet and still have the old:
ActiveWindow.SelectedSheets.PrintOut code, but they are not called in the specific one I am testing. Would that make any difference?

Thanks, Greg

 

[Laroche J]

I did replace it.
On the first try, it just printed direct as I said. Now I get this error
notice:

"Compile error

Can't find project or library"

then it highlights just the "dlgAnswer =" part in the new line of code.

FYI: There are several other different routines in the module that I didnt
want to change yet and still have the old:
ActiveWindow.SelectedSheets.PrintOut code, but they are not called in the
specific one I am testing. Would that make any difference?

Thanks, Greg

dlgAnswer is just a variable, I don't see why compilation would stop on it
unless Option Explicit is set (but normally it would generate error Variable
not defined). Maybe it's used elsewhere in the program for another purpose.

If you don't intend to test whether the Print dialog was cancelled or not,
just use the dialog call without dlgAnswer:
Application.Dialogs(xlDialogPrint).Show

But if the program continues to print without the print dialog, it comes
from another part of the program, and you've got to identify which one of
the PrintOut commands is doing it (even if you think it's impossible). You
can use one of two techniques, while performing a search of PrintOut in the
current project.

1- Put a break point on each of the PrintOut lines. To put a break point,
click on the grey column to the left of the line of code, which makes a
brown dot appear and turn the line background brown. Code execution will
pause there, and if you don't want to print you can then comment out the
line (by placing a single quote at the beginning of the line) and continue
execution. Come back later to fix it (remember where it was).

2- Comment out all PrintOut commands and add after each one a message box
command. For example:
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
MsgBox "PrintOut 1 in Sub xyz"
Then if you get a message you'll be able to identify from where it was
printing before. Use consistent (but different) text in each MsgBox so
you'll be able to trace them back later for removal.


JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial