Commercial Template Sample - Using Payment Method Checkboxes

This sample commercial invoice template explains and demonstrates how to add and use checkboxes to our Excel format commercial invoice form.

If you have visited Commercial Invoice - FedEx Style and Commercial Invoice - FedEx Style (Landscape), you may notice that the two templates use check boxes on the bottom of the form. Many other invoicing forms here on InvoicingTemplate.com use checkboxes too. If you have installed Invoice Manager for Excel, you may noticed that the "taxable" column is a checkbox column which controls whether each item on the invoice detail section is taxable - if an item is taxable, the Excel formula set for the template calculates tax or VAT for the current item.

A checkbox is an ideal way to present a "yes/no" option. The FedEx style of commercial invoice template, which we added here on InvoicingTemplate.com to mimic the layout of the PDF version of FedEx blank commercial invoicing template, uses 3 checkboxes on the bottom of the form in the section tilted with "Check One". There are 3 options there - FOB, C&F and CIF. On the Adobe PDF version of the commercial invoices converted from the Excel-formatted document, the checkboxes will work well too.

With the sample, a payment method option is added, which comprises 3 checkbox - "L/C", "T/T" and "Other" - where T/T means telegraphic transfer, or simply wire transfer; and L/C Letter of credit (See the screenshot). For international trade, this payment detail should be useful when it appear on the commercial invoices. Generally, every dutiable shipment sent through FedEx, DHL or UPS must be accompanied with an invoice. The invoice must identify the seller and buyer. It must also clearly indicate the terms and date of sale, quantity, weight and volume of the shipment. It must also indicate a complete description of the shipped goods, shipping, insurance and other charges, unit value and total value as applicable.

$GroupDescription

So how is this designed? What is the key point to implement it and make sure they work with Invoice Manager for Excel?

First, 3 custom fields will be created to store the value of the checkboxes. To do this, click the Invoices button on the "Invoice" ribbon tab. Click Custom Fields, and then Add Field. Enter "PaymentMethodTT" in the "Name" box, and then choose "Integer" from the "Type" list, click OK. Click "Add Field" again, Enter "PaymentMethodLC" in the "Name" box, and then choose "Integer" from the "Type" list, click OK. Click "Add Field" again, Enter "PaymentMethodOther" in the "Name" box, and then choose "Integer" from the "Type" list, click OK.

Now switch to design mode by pushing the "Design Mode" button on the "Invoice" ribbon tab. You have to choose 3 cells that will show the value of the "PaymentMethodTT", "PaymentMethodLC" and "PaymentMethodOther" fields. In this sample, we choose the $AA$40:$AA$42 range for this purpose, and named 3 cell "oknPaymentMethodTT","oknPaymentMethodLC" and "oknPaymentMethodOther" respectively. As you can see from this commercial invoice sample, the map (or link) between the database field and the Excel cell is archived by using names - you name the cell by adding the prefix "okn" to the field name.

Note that these 3 cells must be unlocked so that when the Invoice worksheet is protected, Invoice Manager for Excel is able to write to these cells. To do this, select the 3 cells and right-click one of them, choose Format Cells. On the Format Cells dialog box, clear the "Locked" boxes from the "Protection" tab. Click OK.

In Microsoft Excel, there are in fact two ways to add checkbox to a spreadsheet - by using a form control, or by using an ActiveX control. Both options are located on Excel's "Developer" ribbon tab. This tab is not shown by default in Microsoft Excel. To make it visible, firstly you right-click an empty area on the ribbon and then choose "Customize the Ribbon" command from the shortcut menu. On the "Customize the Ribbon" section of the Excel Options dialog box, check the "Developer" option and click OK.

To add a checkbox to the commercial invoicing template, click "Insert" in the "Controls" group on the "Developer" tab. Choose the checkbox from "Form Controls" or "ActiveX Controls". And then, click the location where you want to put the checkbox control. A new checkbox appears on the form. Change its label to "T/T", "L/C" or "Others".

Assuming you choose the "Form Control" version of the checkboxes, now right-click the one of the newly added box and choose Format Control. On the "Control" tab, enter "oknPaymentMethodTT" (or "oknPaymentMethodLC", or "oknPaymentMethodOther") into the "Cell Link" property. By setting this property, you link the state of the check box to the specified cell value, which in turn links to a database field as specified by the name.

The last thing is that, if you want to be able to extract the commercial invoice (this means to create a new Excel spreadsheet file for the current invoice) along with the new checkbox, then the checkbox must be named with "oknWidget_" prefix. In this commercial invoice sample, we named the controls "oknWidget_checkTT", "oknWidget_checkLC" and "oknWidget_checkOther" respectively. More information could be found in the documentation that comes with the Invoice Manager for Excel installer program.