Print Code

[Randy]

Greetings...

I have a little code I am needing some assistance with. The code is suppose
to print out certain sheets based on wether a particular cell on another
sheet is empty or not. If the cell is empty the sheet should not print...if
cell is not empty, the sheet should print.

Here is what I have....any assistance will be greatly appreciated
..................................................................................................
Private Sub Print_All_Click()
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
If Not IsEmpty("D20") Then
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True
Else
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=False
End If
End Su
..................................................................................................

Thanks again and have a wonderful and safe New Year celebration!

 

[JLGWhiz]

Randy, If you have a formula in cell D20 the IsEmpty function will not work.
Also, If you do not want to print, you do not need the Else part of the If
.... Then statement because if it is not true it will not print. Here is my
suggestion.

Private Sub Print_All_Click()
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
If Range("D20").Value > "" Then
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True
End If
End Sub

 

[Mike H]

Randy,

Maybe this. It will print sheet "Commission Pool" with no criteria. Note
I've qualified the D20 name with a sheet name

Private Sub Print_All_Click()
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
If Not IsEmpty(Sheets("YourSheet").Range("D20")) Then
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True
End If
End Sub

Mike

 

[Randy]

And the winner is....Mike H.!!!! lol...just kidding...Thanks a bunch to
everyone who took the time to help me out with this. I really appreciate it!

Now I kind of have one more little question......lets say cells D2023
(below) are all empty/blank...how can I "stop" the "Commission Pool" sheet
from printing if "all" cells are blank, but still print even if one of the
cells in the range is "not" blank?

Maybe there is even a better (shorter) way of doing this:

Private Sub Print_All_Click()
ActiveWorkbook.Unprotect Password:="XXXXXX"
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
If Not IsEmpty(Sheets("Commission Pool").Range("D20")) Then
Sheets("Sr-Area Manager").Visible = True
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True
Sheets("Sr-Area Manager").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D21")) Then
Sheets("Community Manager").Visible = True
Sheets("Community Manager").PrintOut Copies:=1, Collate:=True
Sheets("Community Manager").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D22")) Then
Sheets("Assistant Manager").Visible = True
Sheets("Assistant Manager").PrintOut Copies:=1, Collate:=True
Sheets("Assistant Manager").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D23")) Then
Sheets("Leasing Agent (1)").Visible = True
Sheets("Leasing Agent (1)").PrintOut Copies:=1, Collate:=True
Sheets("Leasing Agent (1)").Visible = False
End If
ActiveWorkbook.Protect Password:="XXXXXX"

 

[JLGWhiz]

Put this:

Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True

In an If statement like this:

If Sheets("Commission Pool").UsedRange.Cells.Count > 1 Then
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
End If

If the UsedRange only contains one cell, then it is likely that the rest are
blank.

 

[JLGWhiz]

Here is how the whole thing would look if I was doing it.

Private Sub Print_All_Click()
ActiveWorkbook.Unprotect Password:="XXXXXX"
If Sheets("Commission Pool").UsedRange.Cells.Count > 1 Then
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D20")) Then
Sheets("Sr-Area Manager").Visible = True
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True
Sheets("Community Manager").Visible = True
Sheets("Community Manager").PrintOut Copies:=1, Collate:=True
Sheets("Assistant Manager").Visible = True
Sheets("Assistant Manager").PrintOut Copies:=1, Collate:=True
Sheets("Leasing Agent (1)").Visible = True
Sheets("Leasing Agent (1)").PrintOut Copies:=1, Collate:=True
Sheets("Sr-Area Manager").Visible = False
Sheets("Community Manager").Visible = False
Sheets("Assistant Manager").Visible = False
Sheets("Leasing Agent (1)").Visible = False
End If
ActiveWorkbook.Protect Password:="XXXXXX"
End Sub

 

[Randy]

Hi there and thank you again for your time and efforts...Your suggestion is
great however, the cells are specific, meaning cell D20 is specific to sheet
"Sr-Area Manager", cell D21 is specific to sheet "Community Manager" and so
on....what I was looking for with the Commission Pool form is if the entire
range of cells D2025 are blank the form will not print or at least display
a message stating the form is missing info....is there is one cell in the
range (D2025) then the form should print along with the others...

Here is what I have...this works good as long as the range only contains one
cell, say D20...if I enter the range D2025 it does not work any
longer...take a look at what I have...

Private Sub Print_All_Click()
Dim r As Long
If IsEmpty(Sheets("Commission Pool").Range("D2025")) Then
r = MsgBox("There are no names on this form. Would you still
like to print the form?", _
vbQuestion + vbOKCancel, "Are you sure?")
If r = vbOK Then
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
End If
Else
Sheets("Commission Pool").PrintOut Copies:=1, Collate:=True
End If

ActiveWorkbook.Unprotect Password:="XXXXXX"

If Not IsEmpty(Sheets("Commission Pool").Range("D20")) Then
Sheets("Sr-Area Manager").Visible = True
Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True
Sheets("Sr-Area Manager").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D21")) Then
Sheets("Community Manager").Visible = True
Sheets("Community Manager").PrintOut Copies:=1, Collate:=True
Sheets("Community Manager").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D22")) Then
Sheets("Assistant Manager").Visible = True
Sheets("Assistant Manager").PrintOut Copies:=1, Collate:=True
Sheets("Assistant Manager").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D23")) Then
Sheets("Leasing Agent (1)").Visible = True
Sheets("Leasing Agent (1)").PrintOut Copies:=1, Collate:=True
Sheets("Leasing Agent (1)").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D24")) Then
Sheets("Leasing Agent (2)").Visible = True
Sheets("Leasing Agent (2)").PrintOut Copies:=1, Collate:=True
Sheets("Leasing Agent (2)").Visible = False
End If
If Not IsEmpty(Sheets("Commission Pool").Range("D25")) Then
Sheets("Leasing Agent (3)").Visible = True
Sheets("Leasing Agent (3)").PrintOut Copies:=1, Collate:=True
Sheets("Leasing Agent (3)").Visible = False
End If

ActiveWorkbook.Protect Password:="XXXXXX

End Sub

 

[Randy]

I got it.....
Dim r As Long
If IsEmpty(Sheets("Commission Pool").Range("C10")) Then
r = MsgBox("There is no Property Name on this form. You must
Enter a Property Name before continuing?", _
vbQuestion + vbOKOnly, "Error?")
If r = vbOK Then
End
End If
ElseIf IsEmpty(Sheets("Commission Pool").Range("D20")) And
IsEmpty(Sheets("Commission Pool").Range("D21")) And
IsEmpty(Sheets("Commission Pool").Range("D22")) And
IsEmpty(Sheets("Commission Pool").Range("D23")) And
IsEmpty(Sheets("Commission Pool").Range("D24")) And
IsEmpty(Sheets("Commission Pool").Range("D25")) Then
r = MsgBox("There are no names on this form. Would you still
like to print the form?", _
vbQuestion + vbOKCancel, "Are you sure?")
If r = vbCancel Then
End
End If
End If


Randy Street
Rancho Cucamonga, CA