Print Button Macro with Multiple Conditions

[Gerard Sanchez]

Hi,

'Here is what I am trying to do:

Sub PrintWithConditions()

If Range("G59") = "Select Customer" And _
Range("F64") = "Select User" And _
Range("F65") = "Not Balanced !" And _
Count(Range(C61,C62,F61,F62,I61,I62) > 0 < 2 Then

MsgBox "Required information not filled and/or Discrepancy exists!"

Else
Range("A18:I69").PrintOut Copies:=1

End If
End Sub


'For ranges C61,C62,F61,F62,I61,I62, I just want one (1) of these cells with
a value

'I am working on a worksheets that have 25 pages and that
'hopefully if I can get this right, I can just change the range values and
assign it to
'other buttons on separate pages of the same worksheet.

'Can anyone help me on this ??

 

[OssieMac]

Hi Gerard,

When I compare this post to your earlier one I am wondering exactly what
conditions you want met or (Not met) to fail the print. The code you have
posted here requires all of the conditions set to be met before print fails.

I thought in your earlier post that any one of the conditions should cause
failure to print so should it be And or should it be Or?

Anyway to answer this post I assume you simply want the syntax for the last
part of the If test so try the following code. If you want the print to fail
if only one of the conditions are met then change And to Or. Also with count
function if you want print to FAIL when =1 then code OK but if you want it to
print when = 1 then change =1 to <>1.

Note that COUNT function counts cells with numbers. COUNTA functions counts
the number of non empty cells (Includes cells containing numbers as well as
other non empty cells.)

Sub PrintWithConditionsTest()

If Range("G59") = "Select Customer" And _
Range("F64") = "Select User" And _
Range("F65") = "Not Balanced !" And _
WorksheetFunction.Count(Range("C61"), _
Range("C62"), Range("F61"), Range("F62"), _
Range("I61"), Range("I62")) = 1 Then

MsgBox "Required information not filled and/or Discrepancy exists!"

Else

Range("A18:I69").PrintOut Copies:=1

End If
End Sub


Re-post as a reply to this if not what you want rather than create a new post.

 

[Gerard Sanchez]

When I compare this post to your earlier one I am wondering exactly what
conditions you want met or (Not met) to fail the print. The code you have
posted here requires all of the conditions set to be met before print
fails.
I thought in your earlier post that any one of the conditions should cause
failure to print so should it be And or should it be Or?

I think you're right, it should be OR instead, so that if any one of the
condition is true, print macro fails.

I copied and pasted the codes exactly as shown:

Sub PrintWithConditionsTest()

If Range("G59") = "Select Customer" Or _
Range("F64") = "Select User" Or _
Range("F65") = "Not Balanced !" Or _
WorksheetFunction.Count(Range("C61"), Range("C62"), Range("F61"),
Range("F62"), Range("I61"), Range("I62")) = 1 Then

MsgBox "Required information not filled and/or Discrepancy exists!"

Else

Range("A18:I69").PrintOut Copies:=1

End If
End Sub


How come it is still printing even though 2 of the conditions are true,
should it not print and display dialogue box?

G59 = Select Customer
F65 = Not Balanced !

 

[Gerard Sanchez]

Nevermind . . .

I think you're right, it should be OR instead, so that if any one of the
condition is true, print macro fails.

I copied and pasted the codes exactly as shown:

Sub PrintWithConditionsTest()

If Range("G59") = "Select Customer" Or _
Range("F64") = "Select User" Or _
Range("F65") = "Not Balanced !" Or _
WorksheetFunction.Count(Range("C61"), Range("C62"), Range("F61"),
Range("F62"), Range("I61"), Range("I62")) = 1 Then

MsgBox "Required information not filled and/or Discrepancy exists!"

Else

Range("A18:I69").PrintOut Copies:=1

End If
End Sub


How come it is still printing even though 2 of the conditions are true,
should it not print and display dialogue box?

G59 = Select Customer
F65 = Not Balanced !

 

[OssieMac]

Hi again Gerard,

I had just finished re-testing the code since getting your previous post
when I got your last post. I assume that you have found the problem.

My testing of the code appears OK so I assume that you had mis-spelled
something or had an added space or something in some of the values either on
the worksheet or in the strings in the code.

By the way with the COUNT function do you want it to fail print if =1
because that is what the code does. If you wanted it to print when =1 then
you need to change that to <> 1.

Hope it now goes well for you.

 

[Gerard Sanchez]

Thank you for checking, I was starting to get really frustrated. Yes, there
was an extra space in there.
I did change it to <>1 from =0. I think if worked out much better that way.

Thank you for your patience walking me through it. I really appreciate it.

--Gerard