print from macro, using if formula

[Hog1]

I currently use a macro to print several sheets from one workbook. I would
like the option to print some of the sheets with the condition of the value
of a cell in the first sheet being >0. My current Macro looks like this,

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True

I've been trying to use the following,

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=IF(Title!D9>0,"True")
and
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=(IF(Title!D9>0,"True"))
Any help is greatly appreciated.

 

[mikelee101]

You might try either of these and see what you think:

======

Dim CollVar as Boolean

If Thisworkbook.Worksheets("Title").Range("D9").Value > 0 then
CollVar = True
else
CollVar = False
End if

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=CollVar

Or===

If Thisworkbook.Worksheets("Title").Range("D9").Value > 0 then

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True

Else

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=False

End if

=====

Hope that helps.

 

[Hog1]

I have tried both of your suggestions and with both, the print will be
performed even when the value is 0.

 

[mikelee101]

Ahhh...I may have misunderstood what you were trying to accomplish. I
thought that you only wanted to turn off the collating feature if the value
was zero. If you want to skip the entire print operation, you could use
something like this:

======

If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then GoTo
SkipPrinting

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True

SkipPrinting:

======

That assumes that there is more to the macro than just the print statement.
If not, you can just insert this before the print statement:

====
If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then Exit Sub
====

Lastly, if you wanted some sort of notification (so that the macro doesn't
just end in the background) you could do something like:

===

If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then
Msgbox "Printing has been canceled since Title!D9=0"
Exit Sub
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True
End If

===

Hope that helps.

 

[Hog1]

Thank You MikeLee!
Your help is greatly appreciated. I will be using this macro and variations
of it on dozens of workbooks which I use daily. I've used a combination of
your suggestions. If your interested the following is my complete macro.
' Macro4 Macro
' Macro recorded 2/2/2007 by Bob Dubray
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Application.Goto Reference:="Paint"
Application.ActivePrinter = "hp deskjet 990c series on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"hp deskjet 990c series on Ne01:", Collate:=True
Application.Goto Reference:="Yard"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Machine"
Application.Goto Reference:="RollCutter"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Bandsaw"
Application.Goto Reference:="Ironworker"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Prep"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Plasma"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="WeldingBay"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Shear"
Application.Goto Reference:="Purchasing"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Cover"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Drawing"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="DrawingHD4"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="DrawingHD5"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Title"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="Labels5HD"

If ThisWorkbook.Worksheets("Title").Range("D13").Value = 0 Then
MsgBox "Printing has been canceled since Title!D13=0"
SkipPrint:
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True
End If

Application.Goto Reference:="Labels4HD"

If ThisWorkbook.Worksheets("Title").Range("D11").Value = 0 Then
MsgBox "Printing has been canceled since Title!D11=0"
SkipPrintOut:
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True
End If

Application.Goto Reference:="LabelsCorral"

If ThisWorkbook.Worksheets("Title").Range("D9").Value = 0 Then
MsgBox "Printing has been canceled since Title!D9=0"
SkipPrints:
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"EPSON Stylus C88 Series on Ne03:", Collate:=True
End If

Application.ActivePrinter = "hp deskjet 990c series on Ne01:"
Sheets("Recent").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Sheets("Title").Select
Range("C4:E4").Select
Selection.Copy
Sheets("Recent").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Title").Select
Application.CutCopyMode = False
Range("D9:E9").Select
Selection.Copy
Sheets("Recent").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Title").Select
Application.CutCopyMode = False
Range("D11:E11").Select
Selection.Copy
Sheets("Recent").Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Title").Select
Application.CutCopyMode = False
Range("D13:E13").Select
Selection.Copy
Sheets("Recent").Select
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Title").Select
Application.CutCopyMode = False
Range("I4").Select
Selection.Copy
Sheets("Recent").Select
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rows("3:3").Select
Application.CutCopyMode = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Sheets("Title").Select
Range("A1").Select
End Sub