Print If Certain Cell Range(s) Are Greater Than "0"

[GarySmith]

I have one simple worksheet in a workbook. It consists of three print ranges.

++++++

Now I have a custom print button:

Sub Button27_Click()
Application.ScreenUpdating = False
Sheets("sheet1").Visible = True
Sheets("sheet1").Select


ActiveSheet.PageSetup.PrintArea = "$A$1:$E$120"
ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True
Sheets("sheet1").Visible = True
Application.ScreenUpdating = True


End Sub

It's set to print my entire range, and works like a champ! ...but I'd like to set it up to print when certain conditions are met.

I'd like to say ::

if E39>0 and E77<=0 and E118<=0 ...then set print range: A1:E39

if E39>0 [AND] E77>0 [AND] E118<=0 ... then set print range: A1:E79

if E39>0 [AND] E77>0 [AND] E118>0 ... then print a specific range: A1:E120

++++++

I'm getting lost on the print via specific conditions...

Anyone have an idea how this might be best handled?

 

[SMB Cooper]

Anyone have an idea how this might be best handled?

I handle similar case by inputing range via input box...

Sub TestMacro()

 Dim selectedrng As String
  selectedrng = InputBox("Select Range", "Enter range", "A1:E39")
  If selectedrng = vbNullString Then Exit Sub ' Cancel button
  
  Range(selectedrng).Select
  If Selection.PrintPreview = False Then 'close the Print preview
     MsgBox "Printing was Cancelled, the CLOSE button was pressed."
     ActiveCell.Select
 End If
End Sub

 

[GarySmith]

Input sounds interesting, but the file is used by someone who won't know how to place a range in a print input box. Was hoping to get this one solved via a conditional statement. Does that make sense?

 

[SMB Cooper]

Input sounds interesting, but the file is used by someone who won't know how to place a range in a print input box. Was hoping to get this one solved via a conditional statement. Does that make sense?

please test this in a sample file...

Sub Test()

If Range("E77").Value > 0 And Range("E118").Value <= 0 Then
Range("A1:E79").Select
Dim printrng1 As String
  printrng1 = MsgBox("Print Range: " & Selection.Address)
  If Selection.PrintPreview = False Then 'close the Print preview
     MsgBox "Printing was Cancelled, the CLOSE button was pressed."
     ActiveCell.Select
  End If


Else


If Range("E77").Value > 0 And Range("E118").Value > 0 Then
Range("A1:E120").Select
Dim printrng2 As String
  printrng2 = MsgBox("Print Range: " & Selection.Address)
  If Selection.PrintPreview = False Then 'close the Print preview
     MsgBox "Printing was Cancelled, the CLOSE button was pressed."
     ActiveCell.Select
  End If


Else
 
If Range("E77").Value <= 0 And Range("E118").Value <= 0 Then
Range("A1:E39").Select
Dim printrng3 As String
  printrng3 = MsgBox("Print Range: " & Selection.Address)
  If Selection.PrintPreview = False Then 'close the Print preview
     MsgBox "Printing was Cancelled, the CLOSE button was pressed."
     ActiveCell.Select
   End If
  End If
 End If
End If
End Sub

 

[GarySmith]

SMB - that worked without a hitch! Didn't consider the MsgBox features...!! Customized the Messages to fit the Workbook's function!