Combining Print Areas

[tbeards]

Happy Friday Excel Wizards!

There are similar posts on the boards but I haven't found one that quite gets me to where I need to be and I am getting desperate! What I want to do is have a userform that includes checkboxes and upon selection of the checkbox, it will print the quote for the particular fruit or fruits selected. The Quote template is on one tab in the workbook where cells A1:S60 contain the quote for Oranges, cells A68:S128 contain the quote for Apples, cells A129:S189 contain the quote for Watermellon, etc. going down for all six fruits...

What I want my code to do is to set the print range based on the fruits selected so if the user picks Oranges and Watermelons, it won't set the print area for Apples. Each quote is a named range so cells A1:S60 is named Quote_Oranges_Print_Area".

The following code works but as you can probably guess where I am going, there are 36 different combinations of print areas based on whether the user selects one fruit or a combination. I do not want to enter in all of the combinations nor put a lookup table in the worksheet (matrix based on true/false values) and instead am looking for code to set the print area.

Private Sub cmdTestPrintArea_Button_Click()
'************* put in the code here to select the print areas for the quotes ****************
If frmQuote.chkQuoteOranges.Value = True And frmQuote.chkQuoteApples.Value = False And _
frmQuote.chkQuoteWatermelon.Value = False And frmQuote.chkQuoteCherries.Value = True And _
frmQuote.chkQuotePapaya.Value = True And frmQuote.chkQuoteGrapes.Value = False Then
 
ActiveSheet.PageSetup.PrintArea = "Quote_Oranges_Print_Area,Quote_Cherries_Print_Area,Quote_Papaya_Print_Area,Quote_Grapes_Print_Area,"
 
End Sub
 
*** I am thinking I could do this with either a select case or an If Then such as the code below but it doesn't work
 
If frmQuote.chkQuoteOranges.Value = True Then 
strOrangePrintArea = ThisWorkbook.Names("Quote_Oranges_Print_Area") Else
""
End If
 
If frmQuote.chkQuoteApples.Value = True Then 
strApplesPrintArea = ThisWorkbook.Names("Quote_Apples_Print_Area") Else
""
End If
 
If frmQuote.chkQuoteWatermelon.Value = True Then 
strWatermelonPrintArea = ThisWorkbook.Names("Quote_Watermelon_Print_Area") Else
""
End If
 
'***** and the same for the rest of the fruit ******
'***** then *****
 
ActiveSheet.PageSetup.PrintArea = strOrangePrintArea & strApplesPrintArea & strWatermelonPrintArea

Any ideas???? Thank you beforehand

 

[tbeards]

I made a typo in my code and this line:

ActiveSheet.PageSetup.PrintArea = "Quote_Oranges_Print_Area,Quote_Cherries_Print_Area,Quote_Papaya_Print_Area,Quote_Grapes_Print_Area,"

should be
ActiveSheet.PageSetup.PrintArea = "Quote_Oranges_Print_Area,Quote_Cherries_Print_Area,Quote_Papaya_Print_Area"

 

[tbeards]

I am not sure how to edit my original thread; however, I don't think my example was very clean in my original thread so I am pasting the cleaned up code that works but I don't want 56 different combinations, just one. Any ideas? My code works and I think I have all of the combinations but it is long and I would like to shorten the code since I may include quotes for other countries besides the US.

Sub SetQuotePrintAreaFruit()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Application.ScreenUpdating = False<o></o>
Sheets(constSheetUSQuote).Activate<o></o>
<o></o>
'************** Clear Print Area **************
ActiveSheet.PageSetup.PrintArea = ""<o></o>
<o></o>
'**************************************************<o></o>
'********* Select the print areas for the quotes *************
'**************************************************
<o></o>
'Scenario #1 <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Oranges</st1lace></st1:City>,Apples,Watermelon,Cherries,Grapes,Papaya<o></o>
If frmQuote.chkQuoteOranges.Value = True And frmQuote.chkQuoteApples.Value = True And _<o></o>
frmQuote.chkQuoteWatermelon.Value = True And frmQuote.chkQuoteGrapes.Value = True And _<o></o>
frmQuote.chkQuoteCherries.Value = True And frmQuote.chkQuotePapaya.Value = True Then
<o></o>
ActiveSheet.PageSetup.PrintArea = "Quote_Oranges_Print_Area,Quote_Apples_Print_Area,Quote_Watermelon_Print_Area,Quote_Cherries_Print_Area,Quote_Papaya_Print_Area,Quote_Grapes_Print_Area"<o></o>

Else<o></o>
<o></o>
'Scenario #2 <st1:City w:st="on"><st1lace w:st="on">Oranges</st1lace></st1:City>,Apples,Grapes<o></o>
If frmQuote.chkQuoteOranges.Value = True And frmQuote.chkQuoteApples.Value = False And _<o></o>
frmQuote.chkQuoteWatermelon.Value = False And frmQuote.chkQuoteGrapes.Value = True And _<o></o>
frmQuote.chkQuoteCherries.Value = False And frmQuote.chkQuotePapaya.Value = False Then<o></o>

ActiveSheet.PageSetup.PrintArea = "Quote_Oranges_Print_Area,Quote_Grapes_Print_Area"<o></o>

Else<o></o>
<o></o>
'************** And so on for Scenarios #3 to #55 *************<o></o>
<o></o>
'Scenario #56 Cherries,Papaya<o></o>
If frmQuote.chkQuoteOranges.Value = False And frmQuote.chkQuoteApples.Value = True And _<o></o>
frmQuote.chkQuoteWatermelon.Value = False And frmQuote.chkQuoteGrapes.Value = False And _<o></o>
frmQuote.chkQuoteCherries.Value = True And frmQuote.chkQuotePapaya.Value = False Then<o></o>
ActiveSheet.PageSetup.PrintArea = "Quote_Cherries_Print_Area,Quote_Papaya_Print_Area"<o></o>
Else<o></o>
<o></o>
Call MsgBox("This Quote Combination isn't set up for printing, contact your administrator to have added", vbOKOnly)<o></o>
<o></o>
End If<o></o>
End If<o></o>
End If<o></o>
<o>'*********** And I have all of the End Ifs for the other scenarios ******* </o>

End Sub