Chapter 8  Customizing the Financial Statements.xls Workbook and Presenting Information

About This Chapter

This chapter explains how to use various techniques to customize the Financial Statements.xls workbook according to your needs, and includes the following sections:

F       Overview, page 118, introduces the various techniques you can use to customize your worksheets.

F       Creating a Custom Menu, page 119, describes how to add Custom Menus to your worksheet.

F       Using Custom Views, page 129, describes the advantages of using Custom Views.

F       Inserting the Workbook's File Path into the Title Bar, page 131, describes how you can add the full path of the file to the title bar.

F       Using the Watch Window, page 132, describes the new and exciting feature in Excel 2002 and higher that enables you to view cell location, formula, value or data in any cell in any open workbook, as well as move between cells in any open worksheet.

F       Using Comments to Save and Show Data and Charts, page 134, describes how to add, delete and print Comments in your worksheet and use the Camera feature.

F       Adding a Custom Menu to display requested reports and select any worksheet, as well as to print or mail Financial Statements reports.

F       Adding Custom Views.

F       Inserting the workbook file's full name (including the path) into the title bar.

F       Using the Watch window to view data and information in cells, as well as move between them.

F       Adding Comments to cells.

F       Adding pictures to Comments and to the worksheet using the Camera feature.

Creating a Custom Menu

Figure 8‑1: Custom Menu

The steps in this section will teach you how to create, modify, and delete a Custom Menu, even if you are not familiar with VBA macro language.

Creating a Custom Menu

Ø     To copy a Custom Menu:

1.       Download John Walkenbach's menumakr.xls file from the following Web site: http://j-walk.com/ss/excel/tips/tip53.htm.

2.       Open the file, and select the MenuSheet worksheet, as shown below (the Menu Sheet in the figure is shown after modifying).

3.       Right-click the MenuSheet tab and select Move or Copy from the shortcut menu displayed.

4.       In Move or Copy window that opens, choose Financial Statements.xls from To book.

5.       Select the Create a copy check box and click OK.

Step 2: Copying the Code Lines to a New Module 

After you have copied the Custom Menu worksheetto the Financial Statements.xls workbook, copy the Macros' code lines to a new module within the workbook.

Ø     To copy the macros' code lines to a new module:

1.       Press <Alt+F11> to open the Visual Basic Editor window.

2.       In the VBAProject pane, double-click Module1 in the menumakr.xls project to open it (if Module1 is not visible, click the plus sign to the left of Modules,shown below, to expand the tree).

3.       Move the cursor to any of the code lines in the rightmost pane, press <Ctrl+A> and then <Ctrl+C>.

4.       In the VBAProject pane, select Financial Statements.xls.

5.       From the Insert menu, select Module. A blank pane will appear on the right side of the window.

6.       Click any place in the blank pane and press <Ctrl+V> to paste the code lines into the new module.

7.       Change the module name by selecting it in the VBAProject pane and pressing <F4>.

8.       In the Properties window that appears, type Menu in the Name text box and then close the Properties window.

9.       Press <Alt+F11> to return to the Excel window, and select the MenuSheet worksheet tab.

Step 3: Changing the Macro Addresses

The next step in creating a Custom Menu is changing the addresses of the macros attached to the Create Menu and Delete Menu buttons in the MenuSheet worksheet (as shown in Step 1: Copying a Pre-existing Custom Menu, page 120).

Ø     To change the macro addresses:

1.       Select the Create Menu button, right-click and select Assign Macro from shortcut menu displayed.

2.       In the Assign Macro window that appears, select the CreateMenu macro from the list, as shown below, and click OK.

3.       Repeat step 1 for the Delete Menu button.

4.       In the Assign Macro window, select the DeleteMenu macro from the list and click OK.

5.       Save the file.

6.       Click the Create Menu button and check if a new menu has been added to the Excel menu bar.

7.       Click the Delete Menu button and check if the new menu has been deleted.

8.       Close the menumakr.xls file.

Step 4: Modifying the Custom Menu

The next step in creating a Custom Menu is modifying it.

Ø     To modify the Custom Menu:

F       This technique uses a template table stored in the MenuSheet worksheet, as shown in the figure below. To modify the Custom Menu, simply modify the data in the template table.

F       Level (column A): This is the level of the particular item. Valid values are 1, 2, and 3. A level of 1 is for a menu, 2 is for a menu item, and 3 is for a submenu item. In general, you will have one level 1 item, with level 2 items below it. A level 2 item may or may not have level 3 (submenus) items.

F       Caption (column B): This is the text that appears in the menu, menu item or submenu.

F       Position/Macro (column C): For level 1 items, this should be an integer that represents the position in the menu bar. For level 2 or level 3 items, this will be the macro executed when the item is selected. If a level 2 item has one or more level 3 items, the level 2 item may not have a macro associated with it.

F       Divider (column D): This displays TRUE if a divider should be placed before the menu or submenu items.

F       FaceID (column E): (Optional) This is a code number that represents the built-in graphic images displayed next to an item, as explained below. The ShowFaceIDs macro determines the FaceID code numbers.

Ø     To determine FaceID code numbers:

1.       Press <Alt+F11>, select the Menu module and look at the ShowFaceIDs macro below.

Excel has thousands of icons and each one has its own unique FaceID number; however, the limitations of the computer's memory do not allow them all to be displayed. Approximately 250 different icons can be displayed at any given time.

2.       Change the IDStart and IDStop number after each execution of the macro to display more icons.

3.       Execute the macro by pressing <F5> from inside the module. The following window is displayed:

4.       Place your cursor over an icon, and notice that a FaceID number appears. Write this number in column E of the MenuSheet worksheet.

Step 5: Adding the Macro's Name to the Custom Menu

Ø     To add the macro name to the Custom Menu:

1.       Copy and paste the macro name to column C, Position/Macro, as shown in the template table, page 123.

2.       To add macros to select a worksheet, simply write a macro based on one of the following three examples in a regular module, then copy the macro name and paste it into the appropriate cell in column C:

3.       To add all other macros names, as shown in the template table on page 123, refer to the appropriate chapters, as follows:

F       To sort worksheets, refer to Chapter 1, Introducing Financial Statements.xls Worksheets.

F       To update the worksheets list, refer to Chapter 1, Introducing Financial Statements.xls Worksheets.

F       To print Financial Statements reports, refer to Chapter 10, Printing and Mailing Financial Statements Reports.

F       To print profit center Income Statement reports, refer to Chapter 15, Analyzing Profit Centers.

F       To save Financial Statements reports versions, refer to Chapter 10, Printing and Mailing Financial Statements Reports.

Step 6: Adding and Deleting Custom Menus Automatically

The final step is to add the Custom Menu automatically while the Financial Statements.xls workbook is being opened, and to delete the Custom Menu while the Financial Statements.xls workbook is closing.

Ø     To add open and close events:

1.       Press <Alt+F11> to open the Visual Basic Editor.

2.       In the VBAProject pane, at VBAProject (Financial Statements.xls), double-click the ThisWorkbook module to open it.

3.       Two Combo Boxes, General and Declarations, appear at the top of the module sheet (the right pane). From the General Combo Box, select Workbook.

4.       From the Combo Box on the right, select Open, as shown.

5.       In the ThisWorkbook module sheet, type the following code:

6.       From the Combo Box on the right, select Workbook_BeforeClose.

7.       In the module sheet, type the following code:

8.       Save the Financial Statements workbook and then close it.

9.       Reopen the workbook and check whether the Custom Menu appears in Excel's menu bar.

The Workbook_Open event is now activated when the workbook is opened, and the Workbook_BeforeClose event deletes the Custom Menu while Financial Statements.xls is being closed.

Using Custom Views

F       For display purposes. This is done by selecting the view from a list of previously saved views in Custom Views,as explained in this section.

F       For saving the print settings for re-printing. For more details, refer to Chapter 10, Printing and Mailing Financial Statements Reports.

Adding the Custom Views Box Icon

The procedure below explains how to add the Custom Views box icon to the Excel menu.

Ø     To add the Custom Views box icon:

1.       Right-click one of the toolbars on the top of the worksheet and select Customize. The Customize window appears.

2.       Select the Commands tab, as shown below.

3.       Select View from the Categories box, and drag the Custom Views box icon to the Excel menu.

4.       Click Close. The Custom Views Combo Box is now located within the Excel menu.

Adding a Custom View

After you have added the Custom Views box icon to the Excel menu, add new Custom View to it.

Ø     To add a Custom View:

1.       Open worksheet 31 – Balance Sheetand define the Page Setup options for the page to be printed.

2.       Select the Custom Views box icon, type Balance Sheet into the box, and then press <Enter>. The Add View window appears.

3.       Click OK.

4.       Repeat these steps for as many Custom Views as required.

5.       After adding the Custom Views, you can select any View from Custom Views box icon as shown below:

Inserting the Workbook's File Path into the Title Bar

As can be seen from Figure 8‑2, the full path of the file, D:\Financial Statements, is displayedbefore the file name Financial Statements.

Ø     To add the full path to the title bar:

F      Add the following code line to the Workbook_Open event in the ThisWorkbook module and to the Workbook_BeforeClose event (described in Step 5: Adding the Macro's Name to the Custom Menu, page 126), while carefully avoiding duplication of the event's name.

 

Using the Watch Window 

The Watch Window, shown below, is a new and exciting feature in Excel 2002 and higher versions. The Watch Window enables you to view cell location, formula, value or data in any cell in any open workbook, in either an active or inactive workbook, as well as the ability to move between cellsadded to the window. You can watch the result calculations, formulas, links and the name defined for the cell.

Figure 8‑3: Watch Window

Ø     To open the Watch Window:

F      From the Tools menu, select Formula Auditing, and then Show Watch Window,

OR

Right-click a cell, and select Add Watch from the displayed menu.

Ø     To add a cell to the Watch Window:

F      With the Watch Windowopen, select the cell in the worksheet that you want to add and click Add Watch on the top left of the Watch Window.

Ø     To move between cells:

F      Double-click any row in the Watch Windowto select the cell.

Ø     To toggle between the toolbars:

F      Double-click the caption bar of the Watch Window (where the text WatchWindow is).The Watch Windowmoves between the toolbars and the formula bar, as shown.

Using Comments to Save and Show Data and Charts

Ø     To add a Comment:

1.       Select a cell.

2.       Press <Shift+F2>,

3.       In the Comment box, type the required text.

By default, Excel does not display Comments. The Comment is displayed only when you place the cursor over the small red triangle in the upper-right corner of any cell with a Comment.

Changing the Name of the Comment's Author

Ø     To change or cancel the name of the author:

1.       From the Tools menu, select Options and select the General tab.

2.       In the User name text box, change or delete the user name, as required. The change will only apply to new Comments that you insert.

Changing the Default Comment Format

Changes to the default Comment format are done from the Display Properties window.

Ø     To change the default Comment format:

1.       Press <ÿ+M> to minimize all open programs.

2.       Right-click the desktop and select Properties from the displayed menu. The Display Properties window appears.

3.       Select the Appearance tab.

4.       Click the Advanced button and from the Item Combo Box, select ToolTip and change the color, as required.

5.       From the Font Combo Box, change the font, as required, and then change the font size and color.

6.       Click OK to accept the new selection.

7.       Click OK again at the bottom of the window to close it.

Viewing Comments

Ø     To set the Comment viewing properties:

F      From the Tools menu, select Options and then select the View tab. Excel offers three display options:

v      None: The Comment indicator (red triangle) does not appear and Comments are not displayed.

v      Comment indicator only: A small red triangle in the upper-right corner of the cell indicates a Comment. The Comment is displayed when the cell is selected.

v      Comment & indicator: All Comments inserted in the worksheet are displayed.

Displaying a Single Comment

Ø     To display a single Comment:

F      Right-click a cell with a Comment, and select Show Comment from the displayed menu.

1.       Select a cell.

2.       From the Data menu, choose Validation.

3.       Select the Input Message tab, and select the Show input message when cell is selected check box.

4.       Type the appropriate information in the Title and Input Message boxes, and click OK.