Print based on condition

[Thomas Kuriakose]

Respected Sirs,

We have three worksheets in one workbook that need to be printed if the listed check boxes are checked and listed cells are not blank, else it should not print the worksheets.

Kindly find attached the workbook with conditions mentioned.

I found a code on the internet for printing which does not have the conditions, kindly guide on how this code can be used to print along with the conditions.

Thank you very much for your support always,

with regards,

Sub Print_Worksheets()
Application.Dialogs(xlDialogPrinterSetup).Show
'ActiveSheet.PrintPreview
Worksheets.PrintOut
ActiveSheet.Select
    'Dim CurVis As Long
    'Dim sh As Worksheet
    'For Each sh In ActiveWorkbook.Worksheets
        'With sh
            'CurVis = .Visible
            '.Visible = xlSheetVisible
    'Application.Dialogs(xlDialogPrinterSetup).Show
    'ActiveSheet.PrintPreview
            '.PrintOut
            '.Visible = CurVis
        'End With
    'Next sh
End Sub

thomas

 

[Marc L]

Hi !

The easy way is just to check cells value
(but you forgot to associate cells for checkboxes in Sheet2.[G18:G19] !) :​

Sub PrintIfOK(Rg As Range)
     Dim OK%, Rc As Range
         OK = 1
    For Each Rc In Rg
          If Rc.Value = False Or Rc.Value = "" Then OK = 0: Set Rc = Nothing: Exit For
    Next
      If OK Then Rg.Parent.PrintOut
End Sub

Sub Demo1()
    PrintIfOK Sheet1.[E7:F7]
    PrintIfOK Sheet2.[F2,F3,F32,G32,F57,G57,F58,F59,F63,G63,F64]
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you so much for your support on this query.

Sir, missed to link the cells in Sheet 2, apologies.

Sir, one more help, kindly guide on where to add the below two lines of code along with your code.

Sub Print_Worksheets()

Application.Dialogs(xlDialogPrinterSetup).Show

Worksheets.PrintOut

End Sub

Thank you very much once again,

with regards,
thomas

 

[Marc L]

First line before the loop.
Second line is useless as it prints all worksheets without any condition ‼
In this case my code is good for the trash !

You must associate cells for each Sheet2 checkboxes
and mod code accordingly …
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this insight and your support,

Much appreciated,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

Apologies for asking a change again, how can we give a message box showing the missed condition.

Thank you very much,

with regards,
thomas

 

[Marc L]

Ok, that needs a brand new code !
Better is to well think about the need before to create a thread …

Post first a corrected workbook with all checkboxes linked to cells.
​

 

[Thomas Kuriakose]

Respected Sir,

Apologies for the incomplete requirement. New requirements are from my line manager.

Kindly find attached the corrected workbook with all checkboxes linked. I copied your code in Module 1.

Sir, in the check boxes the requirement is if one check box is checked, then print should materialize. If both are not checked, it should not print.

Thank you very much for your support,

with regards,
thomas

 

[Marc L]

New requirements are from my line manager.

So wait until project is well defined …

the requirement is if one check box is checked, then print should materialize. If both are not checked, it should not print.

Written in worksheet : If either of check boxes is unchecked do not print !
So now if i well understand if only one check box is checked then print, ok ?
If ok, what to do if both check boxes are checked ?
If not ok, display a message or just skip to next worksheet ?

If all rows are checked except one, what to do ?
Print, no print, display a message ?

Still do no print if either F2 ou F3 is blank in Sheet2 ?

Print only if all conditions are good for all worksheets
or print only worksheets filling well conditions ?

As a crystal clear initial presentation is required,
the reason why often when missing I directly move to next thread
without posting any question …
​

 

[Marc L]

Next code (v2) is a base you can mod if needed :​

Function CheckRange(Rg As Range, Optional Rb As Range) As Boolean
                Dim Rc As Range, C%
If Not Rb Is Nothing Then
    For Each Rc In Rb
        If Rc.Value = "" Then
            Rc.Parent.Activate
            MsgBox "Cell " & Rc.Address(False, False) & " is blank !", vbExclamation, "  Control !"
            Set Rc = Nothing
            Exit Function
        End If
    Next
End If
    For Each Rc In Rg.Rows
           C = Application.CountIf(Rc, True)
        If C <> 1 Then
            Rc.Parent.Activate
            MsgBox IIf(C, "Only one", "Missing a") & " check in row #" & Rc.Row & " …", vbExclamation, "  Control !"
            Set Rc = Nothing
            Exit Function
        End If
    Next
          CheckRange = True
End Function

Sub Demo2()
    If CheckRange(Sheet1.[E7:F7]) Then _
        If CheckRange(Sheet2.[F10:G10,F17:G19,F23:G23,F24], Sheet2.[F2:F3]) Then _
            If Application.Dialogs(xlDialogPrinterSetup).Show Then ThisWorkbook.PrintOut
End Sub

You may Like it !
​

 

[Thomas Kuriakose]

Respected Sir,

I apologies for the partial information.

still learning and trying to pick up everyday bit by bit from this forum only.

Kindly find below the feedback -

So now if i well understand if only one check box is checked then print, ok ? Yes Sir, if one check box is clicked print

If ok, what to do if both check boxes are checked ? In this scenario a message to check only one check box

If not ok, display a message or just skip to next worksheet ? Display message and skip

If all rows are checked except one, what to do ?
Print, no print, display a message ? Display a message

Still do no print if either F2 ou F3 is blank in Sheet2 ? Yes Sir, do no print but display a message

Print only if all conditions are good for all worksheets
or print only worksheets filling well conditions ? Yes Sir correct, print only when all conditions are good

Apologies once again,

Thank you very much,

with regards,
thomas

 

[Marc L]

If ok, what to do if both check boxes are checked ?
In this scenario a message to check only one check box

See amended code in post #10 …
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the revised code and your support on this.

Much appreciated,

with regards,
thomas