Previous Next

Description

Summary

This extension is derived from the original Personal Checkbook Register, by Ron Faile. Changes include the following:

• Conditional formatting, so that the alternating gray lines in the register are maintained as rows are inserted or deleted.
• Addition of a Bank Balance column, linked to the Rec column. A line item is only included in the bank balance once the Rec column has been checked. This makes easy visualization of outstanding transactions.
• Change to the formula for calculating Balance and Bank Balance to allow for insertion and deletion of rows. The new formulas use offsets instead of absolute row references, so they remain valid if rows are inserted or deleted.
• An optional macro in file checkbook_register_macro_insert_row_below.txt to insert a new row below an existing row.  This macro will automatically update formulas in the newly inserted row so inter-row references are properly maintained.  This macro is supplied as a separate text file so individuals can read it to verify no harmful actions are taken.

To install and use the macro

I found that defining the macro within the sheet resulted in Libre Office popping up a warning every time I opened the sheet.  To avoid this, install the macro into Libre Office directly, independent of the sheet.  To do this, here are some instructions:

1. From the LibreOffice Calc menu, select Tools - Macros - Organize Macros - Basic.
2. On the Basic Macros dialog, in the left Macro From column, under My Macros, select Standard.
3. Click the New button on the right.  If you haven't created a Standard macro previously, you should be prompted to create a module.  You can choose any name you wish for the module; I named mine "checkbook_register_module." You can create any number of modules you wish, so pick a name that is specific to this checkbook register macro.
4. LibreOffice Calc will now open an editor window.  Insert the text from the included macro text file into the editor.  To do that, you can either (1) from the File menu, select Import BASIC, or (2) copy and paste the text of the macro file using any text editor you have handy. Remember to save your changes.
5. To make this macro easier to use, you may want to assign a keyboard shortcut to invoke it.  To do that, from the menu, select Tools - Customize.  In the Customize dialog, click the Keyboard tab.  On the bottom, under Category, select LibreOffice Macros - My Macros - Standard - checkbook_register_module. (Make any adjustments to reflect your naming choices.)  Under Function, select the insert_row_below function.Next, on the top, under Shortcut Keys, find the shortcut key you'd like to use; I selected Alt+Insert.  On the right, click the Calc radiobutton, then click the Modify button. You should now see the highlighted function from below assigned to your chosen shortcut key in the top section.

If you prefer not to use the macro

Here are the steps to insert a row manually: These are the same steps the macro uses.

1. Select the row below which you wish to insert a new row.
2. Insert a new row below by selecting from the menu Sheet - Insert Rows - Rows Below.  When you do this, rows below the newly inserted empty row will have invalid values in the Balance and Bank Balance columns. Don't worry about that; they will be corrected when you finish these steps.
3. In the row the cursor bar is still on, select columns G and H, Balance and Bank Balance respectively, and copy.
4. Select column G, Balance, in the newly inserted empty row, right click, and select Paste Special - Paste Special...
5. In the Paste Special dialog, in the Paste section, select only Formulas and Formats; everything else in that section should be unchecked.  Click OK.
6. All rows below the newly inserted row should now have valid entries in the Balance and Bank Balance columns.

Release List