| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Code
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 Sub .................................................. ................................................ Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
Randy;163997 Wrote: 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 Sub .................................................. ................................................ Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA Hello Randy, Your IF statement is tautological. That is it always evaluates to the same result. 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 Change it to this... If Not IsEmpty("D20") Then Sheets("Sr-Area Manager").PrintOut Copies:=1, Collate:=True End If Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45557 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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 "Randy" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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 D20:D23 (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" -- Randy Street Rancho Cucamonga, CA "Mike H" wrote: 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" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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. "Randy" wrote: 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 D20:D23 (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" -- Randy Street Rancho Cucamonga, CA "Mike H" wrote: 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" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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" wrote: 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 D20:D23 (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" -- Randy Street Rancho Cucamonga, CA "Mike H" wrote: 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" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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 D20:D25 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 (D20:D25) 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 D20:D25 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("D20: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 = 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 Street Rancho Cucamonga, CA "JLGWhiz" wrote: 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" wrote: 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 D20:D23 (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" -- Randy Street Rancho Cucamonga, CA "Mike H" wrote: 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" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code
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 "Randy" wrote: 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 D20:D25 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 (D20:D25) 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 D20:D25 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("D20: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 = 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 Street Rancho Cucamonga, CA "JLGWhiz" wrote: 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" wrote: 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 D20:D23 (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" -- Randy Street Rancho Cucamonga, CA "Mike H" wrote: 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" wrote: 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 Sub .................................................. ............................................... Thanks again and have a wonderful and safe New Year celebration! -- Randy Street Rancho Cucamonga, CA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Differentiate between 'Print' and 'Print Preview' in VBA code | Excel Programming | |||
| Code before Print but not Print Preview | Excel Programming | |||
| Need Code To Print From Code Modules | Excel Programming | |||
| Need Code To Print From Code Modules | Excel Programming | |||
| Need print code please | Excel Programming | |||
Hybrid Mode
