Customize Invoice Template - Invoice Manager for Excel

Creating new templates

An invoice template uses a number of hidden properties and cells to identify itself as a valid template so that Invoice Manager for Excel can and will interact with it.

Because of this, it is generally not recommended to create a template or workbook from scratch using Microsoft Excel. It's better to create an template based on an existing, valid template. To do this, copy an existing template using Windows Explorer and customize that copy to create your own one.

However, if you still want to integrate your existing template with Invoice Manager for Excel or create a new format from scratch, the steps are detailed in the sample at Simple Sample: Building and Remodeling Invoice (c4056).

Design mode

A template has two states: design mode and running mode. In design mode, Invoice Manager for Excel stops interacting with the form, so that you can move, add, or delete controls or cells.

To switch to design mode, click the Design Mode button on the ribbon tab. Click this button again to exit design mode.

Names

All objects in the template, including ActiveX controls, drawing objects, and cells, are identified by names.

To distinguish them from other names, all names used by Invoice Manager for Excel are prefixed with "okn".

Names defined this way by defalt are available on any sheet. For example, if the name oknID refers to the range A20:A30 on the first worksheet in a workbook, you can use the name oknID on any other sheet in the same workbook to refer to the range A20:A30 on the first worksheet.

To name a drawing object or cell,

1. Click and select the cell or drawing object.
2. Click the Name box located at the left end of the formula bar.

   

3. Type the name in the Name box.
4. Press ENTER.

Note that Excel does not allow you to reuse an existing name in this way. For example, if a cell has already been named "oknTaxType", and you click and select an empty cell, then enter the name "oknType" into the name box, Excel activates the cell with the name "oknTaxType", instead of naming the empty cell "oknTaxType".

To name a new (empty) cell with an existing name, you have to first delete the exiting cell name and then use this name to name the new cell. To delete an existing cell name, the Name Manager can be launched by going to the "Formulas" ribbon tab.

Shapes

Here is how to use Excel shapes or SmartArt objects in your own template. In Microsoft Excel, you can add shapes, such as boxes, circles, and arrows, to your documents, email messages, slide shows, and spreadsheets. To add a shape, click Insert, click Shapes, select a shape, and then click and drag to draw the shape. After you add one or more shapes, you can add text, bullets, numbering, and Quick Styles to them.

Printing

If a worksheet has a PRINT_AREA name that refers to a range of cells, Excel will print the specified area instead of the whole sheet when you click the Print command on the Excel menu.

You can view what the name refers to by selecting PRINT_AREA from the Name box at the left end of the formula bar. If you want to redefine the PRINT_AREA name, follow the steps below:

1. Select the area you want to print.
2. Switch to the Page Layout tab, click Print Area in the Page Setup group, and then choose Set Print Area.

Moving cells and controls

Since all the cells, fields and controls are identified by names, you can easily redesign the layout by moving them. For example, if you don´t want to show the PAID and TOTAL DUE cells, you can move them out of the Print_Area:

1. Switch to design mode
2. Drag your mouse to select all the cells and fields you want to move.
3. Push your mouse key on the border of the selected area and drag it to the new location.
4. Once finished, exit design mode.
5. Save the template.

To move a control:

1. Switch to design mode.
2. Click and drag the control to the new location you like.
3. Once finished, exit design mode.
4. Save the template.

Extracting drawing objects

To make the extracted worksheet as clean as possible, all drawing objects will be removed from the extracted worksheet. If you want to keep a drawing object, add the prefix "oknUser_" or "oknWidget_" to its name. For example, a logo image could be named "oknWidget_logo".

Resizing columns and rows

Let's say you want to change the width of column A.

• Make sure the template is in design mode.
• Place the mouse pointer on the line between columns A and B in the column header. The pointer will change to a double-headed arrow.
• Click with the left mouse button and drag the double-headed arrow to the right to widen column A or to the left to make it narrower.

See Microsoft's video: Resize rows and columns.

Color schema and palette

To change a color theme, follow the steps below.

• Push down the "Design Mode" button on the ribbon tab.
• Go to the Excel "Page Layout" ribbon tab.
• In the "Themes" group, pull down "Themes" to choose a new theme (this changes colors, fonts, and effects), or pull down "Coslors" to use a new group of colors.
• To create a new theme color, pull down "Colors" and choose "Customize Colors".
• Once done, exit design mode.
• Save the template.

Currency symbols

Invoice Manager for Excel itself is currency-neutral. It does not record currency symbols in the database, and you can change your template to show any currency symbol you like.

• Switch to design mode.
• Select the cells (fields) for which you want to modify the currency symbol by dragging your mouse to select multiple cells. You can also push the CTRL key on the keyboard and click each cell one by one.
• Right-click one of the selected cells and choose "Format Cells".
• From the "Number" tab, you can choose either "Currency" or "Accounting" from the Category list. Note that currency formats are used for general monetary values. Use accounting formats to align decimal points in a column.
• Once you have finished setting currency symbols, exit design mode.
• Save the template.

Inserting a new column

Inserting a new column into the printable form is not as easy as inserting a new row. This is because the horizontal space is limited, and inserting a new column usually means rearranging or adjusting existing columns.

To adjust the existing columns to leave space for a new column, we first unmerge the existing column, such as the description column. Then select the proper cells and execute the "Merge and Center" command again to create a less-wide "Description" column.

Now you have a new (empty) column created by narrowing the "Description" column. Name the cells in the empty column properly to map them to a database field.

A step-by-step detailed example could be found at Simple Sample: Adding a Discount Column.

Adding the 'Next Invoice#' button

To add a "Next Invoice#" button to your invoice form:

1. Open the template as usual.
2. Push down the Design Mode button.
3. Right-click any button to select it; push down CTRL + C on the keyboard to copy it; push down CTRL + P on the keyboard to paste it.
4. Select the new button and name it "oknCmdGetNextInvoiceID" by entering the name into the name box at the left side of the formula bar.
5. Change the button text to "Get Next Invoice#".
6. Move the button to a proper location, usually near the "Invoice#" cell.
7. Exit design mode
8. Now if you click the "oknCmdGetNextInvoiceID" button, it retrieves the next invoice number.

Note that if you click this button, the newly generated number is seen as used. So if you generate a new number and later discard it (for example, close the template without saving it), there might be an inconsistency with the numbers that you save to the database.