Advanced Sample: Print One Invoice in Two Different Formats

Designed for vacuum repair shops, this template implements two print buttons: "print with claim" and "Print no claim". Both print the same invoice, but the first button attaches a "claim" portion to the bottom of the invoice.

In this sample, the vacuum repairer wants to include the following claim on the initial invoice:

REPAIR POLICY --- PLEASE READ!
By leaving your repair, you accept and agree to this policy.
A $10.50 ESTIMATE FEE APPLIES TO ALL VACUUMS
MUST BE PICKED UP WITHIN SEVEN  DAYS OF COMPLETION
AFTER 14 DAYS A $1.00 PER DAY FEE WILL ACCRUE.
REPAIRS LEFT OVER 30 DAYS WILL BE SOLD FOR CHARGES.
ESTIMATE FEES ARE POSTED AND ADHERED TO.

On the final invoice, the claim is removed.

We implemented this by adding two new "Print" buttons.

The following section briefly describes the steps involved in creating the buttons.

1. First, save the invoice template in ".xlsm" format by using Excel's "File" / "Save As" commands. The ".xlsm" format enables the Excel template to include VBA macro code.
2. Switch to the "Design Mode" button by pushing down the "Design Mode" button on the "Invoice" ribbon tab.
3. Click to select the "Print" button (be sure to click the border of the button; otherwise, Excel will activate the internal textbox instead of selecting the shape object).
4. Hit the "Del" key on the keyboard to delete the button.
5. Go to the Excel "Insert" ribbon. Pull down the "Shapes" list in the "Illustrations" group and choose the "Rectangle" shape from the list.
6. Click the location where you want to put the first new print button.
7. Enter the button caption "Print w/ claim".
8. Go to the Excel ribbon tab "Format", and then expand the predefined shape styles from the "Shape Styles" group by clicking the "down arrow" icon. Choose one of the styles you like. In this case, we chose the green background style with a white foreground color.
9. Name the button "btnInitial" by entering the name into the name box located on the left corner of the formula bar.
10. Right-click the button and choose "Assign Macro" from the shortcut menu.
11. On the "Assign Macro" dialog box, choose "New".
12. Excel opens the macro editor and creates an empty macro like this:

    Public Sub btnPrintWithClaim_Click()
    
    End Sub
    

13. Update the VBA procedure, like this:

    Public Sub btnPrintWithClaim_Click()
         Range("rgnInitial").PrintOut
    End Sub
    

14. Repeat the above step to create the second button. This time the button caption is "Print No Claim" and the button name is "btnPrintFinal". The macro code for the button looks like this:

    Public Sub btnPrintNoClaim_Click()
         Range("rgnFinal").PrintOut
    End Sub
    

15. Save the template.

The VBA macro code refers to two named ranges "rngInitial" and "rngFinal". When a button is clicked, the named range on the worksheet is printed out. The range "rngInitial" covers the entire central section on the "invoice" worksheet, including the "Claim" section. The range "rngFinal" does not include the "Claim" section. This allows us to print the same invoice with slightly different content.