Using Excel for Boat Inventory
As part of a project initiated on the Pearson 424 Owners maillist I developed an inventory workbook to keep track of all of the items and pieces of equipment we bring on our boats and then promptly lose because we can't remember in which locker we put those items.  There are several forms of the workbook to accommodate the various cabin layouts that were used by Pearson on the 424.  Three versions of the workbook are available below for download.  Click on the links to download the workbooks.  The latest version of this application is 2.8.  Links to User Documentation are provided at the bottom of this page.
I have begun development on the integration of the Inventory workbook with the Maintenance Log workbook application.  This initial effort has been released as InventoryMaint workbook version 3.02.  That application can be downloaded from the documentation page on this workbook at Integrating the Inventory & Maintenance Workbooks.  With the implementation of this integration workbook I will no longer update the individual Inventory and Maintenance applications.  Version 2.8 is the final release for the Inventory application.  Future Inventory updates will be released through the integrated application.  I have removed the download links (other than Sarah's Inventory) below because I've made many changes to the Inventory application since version 2.8.  The integrated application can be downloaded from here.  This page and those linked to it will remain as documentation.  The documentation will be updated to the current version ASAP.
  1. Layout A.  This is the original P424 layout with an "L"-shaped galley.
  2. Layout B.   This is the most common P424 layout with the "C"-shaped galley
  3. Sarah's Inventory.  This is the Layout A format that I customized for Sarah and added much of my onboard stuff to the inventory database.  The databases in the other two workbooks are empty.  Sarah's Inventory can be used to exercise the application and see how it can be used and customized.  This is version 2.86, which adds a number of features and fixes several problems in 2.8.  I've held off updating the blank inventory workbooks until I get to version 2.9.  I've updated some of the documentation to demonstrate some of the features that are not in 2.8, but will be included in 2.9.
 
This workbook is intended to be customized by each user to their boat layout and their inventory needs.  All of the tables used by the program can be added to, modified or removed based on a given boat's configuration without having to modify the underlying Visual Basic for Applications (VBA) code.  However the boat layout diagrams in each of these workbooks is specific to the Pearson 424 and would have to be significantly modified to for any other model sailboat (including others from Pearson).  These diagrams were developed by another P424 owner, Cada Grove, using MS PowerPoint.  Pete Dubler transferred the diagram and the locker identification to an Excel spreadsheet, and then I got hold of it.  MS PowerPoint has a limited by fairly easy to use drawing facility, but other packages could be used to produce a suitable layout diagram for a different sailboat.  The workbook application doesn't depend on this diagram.  It's main benefit is to provide a visual cross-reference between the physical storage spaces on the boat and the locker IDs used by the workbook.  If you would like to modify one of the drawings to conform to your boats storage configuration, the PowerPoint files can be downloaded from here.
The Inventory application is made up of six user forms that are opened via a small CommandBar that is placed in the Excel workspace when the workbook is opened.  The user forms are identified below.
  1. Enter.  This is the data entry form that is used to build the Inventory database.
  2. Edit.  This form is used to modify entries in the database
  3. Search.  This form is used to search the database for one or multiple items.
  4. Kits.  This workbook uses the concept of a set of Kits to organize many items in manner to help identify them and maintain a sufficient quantity of them on board.  This form is used to update and maintain those kits.
  5. Print.  Paper printouts of the Inventory can be used to update the database whenever an item is depleted or moved without having to start the computer.
  6. Save.  This user form provides a means to maintain multiple backups to the Inventory.
  7. Category.  This form is used add, edit and delete Categories and Sub-Categories.  This form is available in version 2.9
The CommandBar
Click on screen capture to view at full resolutionWhen you first open the workbook it will display the Layout sheet and a small CommandBar (Red Ellipse in the screen on the left) will appear on the workbook.  This CommandBar (shown on the right) consist of 6 buttons that activate each of the forms that provide the workbook's functionality.
You can leave it floating over the workbook, but it is usually more convenient drag the bar onto the Excel toolbar at the top of the screen (Red Arrow) using the mouse.
Someday I may attempt to automatically place this CommandBar in the Excel Toolbar, but for now I'll just let the user decide where it should be located.
Click on screen capture to view at full resolutionIn this screen the Inventory CommandBar has been placed on the second row of Toolbars in my Excel.  When the CommandBar is moved to the Toolbar its appearance changes to that shown on the right.  You may have more or less toolbars in your Excel and the Inventory CommandBar may be placed elsewhere.
This image of the CommandBar can be used to navigate throughout this documentation.  To go to the documentation page on each of the CommandBar buttons just click on the button in the image where every it appears on these pages.
The CommandBar exists only while the Inventory workbook is open.  When you close the Inventory Workbook the CommandBar is removed.
If the Inventory workbook is prevented from closing normally (program error in the inventory software, Excel abort, etc.) the ComandBar may be left on your Excel Toolbar.  It will not do any harm and can be easily removed by opening the Inventory workbook then closing it normally.  The other way to remove this CommandBar is to use the Tools/Customize Excel menu item to delete the Inventory toolbar.
With version 2.9 the new CommandBar is automatically docked at the bottom of the Excel screen.
User Documentation
I have written several pages documenting how to use the Inventory workbook.  Those pages are listed and linked below.  This documentation is current to version 2.4 (2.5 for the Enter/Edit forms, 2.9 for the Save and Category forms).
  • Entering and Editing Data in the Inventory.  This page describes how to enter items into the database and then go back and modify those entries.
  • Searching for Items.  There is a search form that can be used to find those "lost" items on board (if you accurately inventoried them in the first place).
  • Managing Inventory Kits.  Inventory Kits are groupings of items from the inventory to identify and maintain those items for critical and essential requirements or just to manage them as a group (e.g., a Toolbox).  The kits are completely customizable, examples include a First Aid Kit, an Abandon Ship Bag, or a list of critical engine spares.
  • Printing the Inventory.  The print function is primarily used to keep the inventory up-to-date by providing printed lists that can be annotated and updated without having to start up the computer.
  • Saving and Backing Up the Inventory.  Although MS Excel does an excellent job of saving spreadsheets and preventing the loss of data due to software or hardware errors, The Save & Backup form provide and additional level of protection from losing the entire database.  This form has not been fully implemented, and no documentation is currently provided.  When the form is fully functional a documentation page will be linked from here.  In the meantime use the Excel workbook save function.
  • Customizing the Inventory.  Other than the layout diagram, all of the configuration parameters used by the Inventory application can be modified without ever touching the VBA code.  Modifying the layout diagram does not involve VBA coding, but it does require the use of a graphics package.