Customizing invoice templates means creating your own custom invoice templates that can interact with the Excel Invoice Manager COM add-in and connect to databases to store and retrieve data.

Please read this chapter carefully before customizing your template, as it contains valuable implementation information.

Contents

Creating New Invoice Templates
Design Mode
Protection
Locking/Unlocking Cells
Formatting Cell/Cells
Gridlines and Row/Column Headers
Names
ActiveX Controls
Printing
Moving Cells/Controls
Extracting ActiveX Controls and Drawing Objects

Creating New Invoice Templates

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

Because of this, you can't create an invoice template from scratch. You must create a new invoice template based on an existing valid invoice template. To do this, copy an existing invoice template using your Windows Explorer, and customize that copy to create your own invoice template.

Design Mode

An invoice template has two states: design mode and run mode. In design mode, the Excel Invoice Manager COM add-in will stop interacting with the invoice template, so that you can move, add, or delete controls or cells.

To switch to design mode:

In Excel 2000 - 2003 In Excel 2007 - 2010

Click the Design Mode button on the Control Toolbox toolbar.

Tip: If the Control Toolbox toolbar is invisible, click Excel menu View -> Toolbars -> Control Toolbox.

Click the Design Mode button in the Controls group of the Developer tab.

TIP: If the Developer tab is not available in Excel 2007, do the following to display it: a) Click Microsoft Office Button, and then click Excel Options; b) In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

TIP: In Excel 2010 to display the Developer tab: a) Click File -> Options -> Customize Ribbon; b) In the area on the right, ensure that the box next to Developer is checked; c) When you click OK, the Developer tab will appear in the ribbon.

After customization, click the button again to exit design mode.

Protection

By default, the Invoice worksheet is protected. You need to unprotect it for most of the customization work. To unprotect the worksheet, in Excel 2000 - 2003 click menu Tools -> Protection -> Unprotect Sheet; in Excel 2007/2010 click the Unprotect Sheet button in the Changes group of the Review tab.

It is strongly recommended that you protect the worksheet again after your customization. To protect a sheet, just click the same button again.

Locking/Unlocking Cells

If a worksheet is protected, locked cells and those cells that contain formulas are not manually editable. To lock or unlock a cell:

  1. Unprotect the worksheet if it is already protected.
  2. Right-click the cell, choose Format Cells.
  3. Click the Protection tab.
  4. Check or uncheck the Locked option.

Formatting Cell/Cells

The Format Cells dialog box allows you to do various formatting, such as currency symbols, scientific options, dates, times, fractions, and more. Formatting also allows you to set font, background color, and borders for selected cells. For example, positive and negative values can have different formats and colors for aiding in keeping track of values. Microsoft Excel also provides a large variety of date and time formats for virtually any time and date format one can think of.

To format a cell:

  1. Unprotect the Invoice worksheet.
  2. Right-click the cell to format, choose Format Cells.
  3. On the Number tab, for example, choose the format you like.
  4. Once done, protect the Invoice worksheet.
  5. Save the template by clicking Excel menu File -> Save.

Gridlines and Row/Column Headers

When customizing a template, it is very helpful to display gridlines and row/column headers. To show gridlines and row/column headers in Excel 2000 - 2003:

  1. Click and activate the worksheet you want to display gridlines and row/column headers for.
  2. On the Tools menu, click Options, and then click the View tab.
  3. Under Window options, check the Gridlines and Row & Column Headers check boxes.

After your customization, go back to the Tools menu, Options, View tab. Under Window options, REMOVE the checks you previously placed by the two check boxes to hide gridlines and row/column headers. This will restore the appearance of your template.

To show/hide gridlines and row/column headers in Excel 2007/2010:

  1. Click and activate the worksheet you want to display gridlines and row/column headers for.
  2. Go to the View tab of the Ribbon.
  3. Click Show/Hide, and then check or uncheck the Gridlines and Headings options.

Names

All objects in an invoice template, including ActiveX controls, drawing objects and cells, are identified by names. There are more than 200 names used by Excel Invoice Manager, and some of them are hidden. By using names, you are free from the restrictions of cell addresses and can move the objects around the worksheet.

To distinguish from other names, all names used by Excel Invoice Manager are prefixed with okn (Office-Kit.com's Name).

In Excel, names are available to any sheet. For example, if the name oknID refers to the range A20:A30 on the first worksheet in a template, you can use the name oknID on any other sheet in the same template to refer to 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 at the left end of the formula bar.

    Excel formula bar

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

To name an ActiveX control object:

  1. Switch your template to design mode.
  2. Right click the ActiveX object.
  3. Select Properties from the menu.
  4. In the Properties window, enter the name in the Name field.

See your Excel help document for more information about using names.

ActiveX Controls

Microsoft Excel has two types of controls. ActiveX controls are appropriate for most situations. Forms Toolbar Controls are compatible with earlier versions of Excel, beginning with Excel version 5.0, and can be used on XLM macro sheets. Excel Invoice Manager uses a number of standard ActiveX controls, such as buttons, check boxes, and graphics.

To add an ActiveX control:

  1. Switch your template to design mode.
  2. In Excel 2000 - 2003, click the control you want to add on the Control Toolbox toolbar; In Excel 2007/2010, go to the Developer tab, click Insert in the Controls group, and then choose the ActiveX control you want to add.
  3. Click on the worksheet at the location where you want to place the control.
  4. Drag the control to the size you want.
  5. Right click the control and select Properties from the menu.
  6. Enter the name, caption, and other properties if needed.
  7. Close the Properties window.
  8. To quit design mode and enable the ActiveX control, click Exit Design Mode on the Control Toolbox toolbar if you are running Excel 2000 - 2003; or click the Design Mode button in the Controls group of the Developer tab if you are running Excel 2007/2010.

Printing

The PRINT_AREA Name

If a worksheet has defined a PRINT_AREA name and it 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.

In the invoice template, each worksheet contains a PRINT_AREA name. 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. In Excel 2000 - 2003, click Excel menu File -> Print Area -> Set Print Area; In Excel 2007/2010, switch to the Page Layout tab, click Print Area in the Page Setup group, and then choose Set Print Area.

Printing ActiveX Objects and Drawing Objects

By default, all objects added to a worksheet are printable. You may want to exclude some of them from printing, such as the Customer Selection button on the Invoice worksheet.

To exclude a Drawing Object from printing in Excel 2000 - 2003:

  1. Right click the drawing object, and select Format AutoShape from the menu.
  2. Click Properties tab.
  3. Clear the Print Object check box.

To exclude a Drawing Object from printing in Excel 2007/2010:

  1. Right click the drawing object, select Size and Properties....
  2. Go to the Properties tab.
  3. Uncheck the Print Object option.

To exclude an ActiveX control from printing:

  1. Switch the template to design mode.
  2. Right click the ActiveX control, and select Properties from the menu.
  3. Scroll down the properties list and find the PrintObject property.
  4. Change the PrintObject property to False.

Moving Cells/Controls

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

  1. Unprotect the Invoice worksheet.
  2. Drag your mouse to select all the cells/fields you want to move.
  3. Push your mouse key on the border of the selected area; drag it to the new location.
  4. Once finished, protect the Invoice worksheet.
  5. Save the template.

To move a control:

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

Extracting ActiveX Controls and Drawing Objects

To make the extracted worksheet as clean as possible, all ActiveX controls and drawing objects will be removed from the extracted worksheet. If you want to keep an ActiveX control or a drawing object, add a prefix oknUser_ or oknWidget_ to its name.