Home Inventory
Introduction
Home Inventory ia an Excel-based utility, designed to catalogue items in boxes you keep in the shed or your office, as well as things like your furniture, appliance or electronics. In short: you can catalogue everything in your home. It is designed to help you find stuff you need without having to look through a lot of boxes. The information lives on a main sheet: “Items”, and a secondary sheet: “Boxes”.
Home Inventory extends Excel through forms and code written in Visual-Basic for Applications, which is part of the Microsoft Excel product - it includes a form for easy cataloguing of your items.
Items are recorded on the Items worksheet. The information for items includes:
an 8-digit item number
the name of the item
the box the item is in (if applicable)
the name of the picture of the item
the item’s status: Sighted, Repairing, Restock, and so forth.
How to Best Use Help
While Home Inventory’s use seems pretty straight-forward to the developer, the realisation that any approach to managing a home inventory may seem less than intuitive to other inhabitants of the solar system: hence, this Help file.
As Microsoft in their wisdom didn’t see fit to include a means to provide a very flexible custom help documentation system without installing questionable add-ons an approach had to be come up with that would provide the information quickly and accurately. Therefore, I have chosen HTML (think: Web) pages to display documentation.
When the user clicks on the Help button, that user will be offered this help file to read. It is suggested to make use of the Quick Search feature first, entering the name of the button or control on the form the user has a question about. Help should display a number of context-specific help options.
TO REVIEW
For example, let’s say help is needed on the purpose of this button:
Moving the mouse cursor over the button will display the name of the control. Sometimes the tool-tip text will appear beneath the control instead of next to the cursor:
Make a note of this name, click on the Help button, and then, in the Quick Search field:
…type the name of the button or control and press <Enter>.
If Quick Search does not produce any results, please notify the developer.
END REVIEW
Conventions and Glossary of Terms
This help file assumes certain conventions:
Physical keys you type on
<Enter> - the Enter key, at the right side of the keyboard
<Tab> - the Tab key, above the CapsLock key, to move from field to field
Representations in the text of screen objects to click on
[ Close ] - represents a clickable button
(_o_) - represents a selected radio button
(___) - represents a deselected radio button
[_X_] - represents a selected tick box
[___] - represents a deselected tick box
Names of objects in a form
Textbox - allows entry of free text. Some text boxes automatically convert text to upper-case. Example shown: the Item field on the “Add or Edit Item” form.
Dropdown - Also known as a “Combobox”, it appears to be a textbox with a down-arrow on the right-hand side. Contains a listbox of selectable items, but will also allow keyboard entry of text. Example shown: the ItemType dropdown on the “Add or Edit Item” form.
Tickbox - acts as a toggle to turn a condition on or off. Example shown: the New Item tickbox on the “Add or Edit Item” form.
Radio button - used to select one - and only one - of several choices. Example shown: the Item Status radio-buttons on the “Add or Edit Item” form.
Toggle - turns a condition on or off. In this example from the “Item Management” form, the “All” toggle, when depressed, displays items from all locations whilst displaying a Location column in the Items List, with the Location dropdown empty and disabled. When the “All” toggle is not depressed, the Location dropdown is enabled, and a Location displays in the dropdown, with all items of that type and in that location displaying in the Items listbox.
Listbox - contains a list of items or list of information on an item. Example shown: the Items List on the “Item Management” form.
Button - also known as a “command button”, used to invoke actions, such as closing the form, opening forms, and saving information, as the above button does when clicked. Example shown: the Save button on the “Add or Edit Item” form.
History, Caveats and How to Contact Me
History
Home Inventory is a tweaked (read: improved) version of an Asset Manager tool I developed as a response to my accepting the Item Manager portfolio in PACU at QEII Hospital near Brisbane, Queensland. At the time, when I asked what means were provided to track items, I was given a ledger with paper in it: lined paper.
Um, no. That wasn’t going to work.
Right about the time I was given that portfolio, inventory on non-consumables was being conducted in our Operating Theatres and PACU/Day Surgery by the Materiel Management Department. As that inventory process was nearing completion, I asked those responsible whether I could obtain a copy of the data. I was sent a CSV file, which I imported into Excel. This provided the framework for the main Items worksheet.
The overall structure of this workbook is based on that tool. However, much if not most of the code has been optimised and simplified to suit a Home Inventory purpose.
Caveats
Home Inventory is all contained in a stand-alone Excel workbook. Although I would have preferred a proper database, Excel was all I had available within the organisation I was developing in, so I commandeered it to act as a flat-file database. Excel as a database is not ideal, but through the quirky magic of Visual Basic for Applications, it generally works as expected. No question it is definitely an improvement over a physical ledger.
VBA itself is not the stellar language to write solutions in one could hope or wish for, and my knowledge on how it works is a bit less than comprehensive. Therefore, someone following me in trying to maintain/fix the code may wonder at some of the kludges I employed. As I learn more on how to do this all properly, the kludges are being removed/replaced, but whilst I try to be as professional as I could developing this tool, Home Inventory is pedestrian code at best.
The developer of this product makes no claims as to the accuracy or reliability of the code, nor are there any guarantees, implied or real, as to the document’s fitness for purpose. I.e., it is presented and delivered as-is. Please keep this in mind when using this product. I ask the user’s patience with the code… it is what it is.
The code supporting the use of this document - i.e., the “software” - is FOSS: free/open-source software. Please see the GNU General Public Licence v3 page for the licence agreement associated with this software.
If you manage to break it, you get to keep both pieces.
How to Contact the Developer
Whilst software developers endeavour to write clean, robust software with consistent, predictable behaviour, bugs do happen. A number of means exist to contact the developer:
To find the most current documentation - a (probably) updated version of this document - go to:
Updating text for Home Inventory
Getting Started
System Requirements
“Home Inventory” is an Excel workbook using Visual Basic for Applications (a scripting language) to facilitate managing home inventory items, as well as cataloguing the location in the home of pretty much everything the is inedible. As the software was written in and runs in Excel, there is no installation required except for Excel itself. This workbook can be opened anywhere Excel is available.
The software makes use of images to help the user identify items. Help can be opened as long as the user opens “Home Inventory” on a computer connected to the Internet.
Starting Home Inventory
When you open “Home Inventory” the first time in Excel, security settings on your computer may issue a security warning:
Some active content has been disabled. Click for more details.
Next to this warning is a button labelled [Enable Content]. This button must be clicked in order for the forms to appear and the macros (scripts) to work. When you click this button, another security warning may appear, asking whether you wish to make this file a Trusted Document. If you wish to no longer see these warnings, click [Yes]. If you wish to disable warnings for networked files (any files that live on the server instead of on the PC you’re working on), tick the box in the lower left-hand corner.
You should see a new Menu tab in the Excel Menu near the Help menu tab: Home Inventory. This tab contains a number of buttons:
Show Inventory
and:
About
Help
Clicking on the button will bring up the relevant form, or this Help file in your browser.
Item Management - The Forms
Overview - What Home Inventory Does
Home Inventory was developed to manage all the information you need - location, desciption and pictures - to keep track of items in your home. Ideally, items are entered into Home Inventory when they are purchased. The item will be assigned an 8-digit number: the Item Number.
These numbers identify items in Home Inventory, as Item numbers are unique for each item. When conducting a search for a specific item in Home Inventory, you can use this number, although other means to search are available.
Updating Home Inventory–Reviewing
To enter the item into Home Inventory is a simple process: conduct a search for the item by Item by entering the Item name into the search field and pressing <ENTER>. If the item does not exist, the user simply follows the prompts to enter new item information and save the information into a new record.
The Main Item Form
The main Home Inventory form…
has six basic areas:
The Edit Item Form
This form is launched by the main form when adding new equipment to the Items sheet or editing existing item information on the Items and Boxes sheets. It can be launched from two buttons - to either add or edit a item - from the Button Bar or from the “Manage Items” section of The Menu.
The Edit Item form has five basic areas:
Item Management
Item Search
An Item number is made up of eight numeric digits. To find a item in the system, type the number into the search field:
and either click on the Search button:
or simply press <Enter> on the keyboard.
Note:
the entry into the search field is checked for non-numeric (alphabet letters) characters, and then that the entry is eight digits long. If the entry does not meet those conditions, the user is notified; otherwise, a search is performed.
If the number was entered correctly and the corresponding Item exists in the Items worksheet (a record exists for the item in Items) in Home Inventory, the software will find it and display it in a list of similar items. If the Item number cannot not be found, the user will be prompted via a dialogue to add a new item using the entered Item number:
It’s a good idea to double-check that the Item number was typed in correctly. If so, the user has a choice: select [ Cancel ] to cancel adding a item with that Item number or [ Ok ] to open the Edit Item Information form to add a new item to Home Inventory.
Item Types and Locations
Next to the Search field and button is a dropdown box with a list of item types. The main items list-box displays a list of items that are part of a item type group. The item type dropdown box looks like a textbox, except that it has a down-pointing arrow at the right of the field:
Select from this dropdown to display items that correspond to the selected item type in the list below. Possible types include:
Audio
Appliance
Maintenance
PC
Sundry
and so on.
Button Bar
The button bar’s buttons provide a quick and easy way to do the most common tasks.
NewItem - The New Item button opens the Edit Item form to allow entry of a new item into Home Inventory.
EditItem - The Edit Item button opens the Edit Item form to allow entry of information on an existing item in Home Inventory. Note: if an entry in the Events Listbox is selected, that entry will be opened for editing; otherwise, it is assumed that the user wishes to enter a new Event for the selected item.
ShowSheet - The Show Sheet button closes the Manage Item form, makes the Items worksheet the active worksheet and drops the user onto the record in that sheet corresponding to the last selected item in the Item List.
Help - Opens Firefox and displays this document.
Close - Closes the Manage Item form. Note: this button and Menu –> File –> Exit are the only methods to close this form. Clicking on the red ‘X’ in the upper right-hand corner of the form does nothing. This is by design.
Item List
The list of items in the upper listbox show all items that correspond to the item type selected in the Item Type dropdown:
The currently selected item is the one that is highlighted. The item’s Item number will display in the Search field.
The columns in that listbox are:
Column |
Description |
|---|---|
Item |
A unique identifying number issued by Materiel Management |
Owner |
Location: OT - AN - PA [1] |
Status |
“In Service”, “To Repairs”, and so forth |
Description |
The name of the item, conforming to Materiel Management / BTS |
Test-n-Tag Date |
Date of last BTS Test-n-Tag |
Located-Sighted |
Where (or if) the item was last sighted |
The last two columns were added to help identify items that had not been sighted - and thus may have disappeared from the unit or have through some other means gone missing. This is necessary for yearly inventory purposes.
As the user can see from the above table, one of the approaches to managing items is by assigning the item a status. No item is infinitely useful: thus, it has a lifespan and a status within that lifespan: for example, “In Service”, “To Repairs”, “Decommissioned” or “On Loan”.
The second column - Owner - refers more to the most likely location the item would occupy. The view can be modified to list only items with a specific owner. Above the list, to the right of the Item Type dropdown, is a Owner toggle button. When set to [On] (the default setting):
the list view will display all items of type. [All] toggled [On] also clears and disables the Owners dropdown. Toggling [All] to [Off]:
enables the Owners dropdown: the user can then select which owner’s items to display in the Items List (and the owner column will no longer display in the list).
The Items List only displays items that are active in the system and does not display items with a status of “Decommissioned”, so as to minimise clutter. In order to see those items, from the Filter Menu, select “All Items” from the bottom of that dropdown. To remove “Decommissioned” items from view, select “Active” from the Filter dropdown.
Box List
In the course of its lifespan, a item will see a number of events. By definition, when a item changes status, it is considered a new event. Recording a change of item status is done in the “Add or Edit Item” form.
This listbox displays all events for the currently selected item in the Items List. Changing which item is selected will change the events displayed.
Edit Item Information
‘Edit Item’ is launched from the ‘Item Management’ form. The information on this form and the controls will reflect the context in which the form was launched. For example, if the user launches ‘Edit Item’ as a result of following the prompt to add new equipment when searching for an item not currently in the database, the Item number will be the one typed into the search field. If the form was launched from the New Item button, no Item number will be in the Item field: the user will need to enter this or the record cannot be saved.
Item Name and Type
The name of a item
The main items list-box displays a list of items that are part of a item type group. The item types are in a special field called a “dropdown”, characterised by a textbox with a down-pointing arrow at the right of the field:
Selecting from this dropdown will display all items in the list below that correspond to the selected item type. Some of the possible types include: “Antithrombotic”, “Gases-Medical”, “Medication Delivery”, “Patient Warming”, “Monitoring” and so on.
Item Status
The list of items in the upper listbox show all items that correspond to the item type selected in the Item Type dropdown:
The currently selected item is the one that is highlighted. The item’s Item number will display in the Search field.
The columns in that listbox are:
Column |
Description |
|---|---|
Item |
A unique identifying number issued by Materiel Mgmt |
Owner |
Location: OT (Operating), AN (Anaesthetics), PA (PACU) - this column will not display if the [Owner Toggle] is toggled off and the dropdown next to it displays an Owner |
Status |
“In Service”, “To Repairs”, etc |
Description |
The name of the item, as recognised by Mat Mgmt and BTS |
Test-n-Tag Date |
Date of last BTS Test-n-Tag |
Located-Sighted |
Where (or if) the item was last sighted |
The last two columns were added to help identify items that had not been sighted - and thus may have disappeared from the unit or have through some other means gone missing. This is necessary for yearly inventory purposes.
As the user can see from the above table, one of the approaches to managing items is by assigning the item a status. No item is infinitely useful: thus, it has a lifespan and a status within that lifespan: i.e., “In Service”, “To Repairs”, “Decommissioned” or “On Loan”, for example.
The second column - Owner - refers more to the most likely location the item would occupy. The view can be modified to list only items with a specific owner. Above the list, to the right of the Item Type dropdown, is a Owner toggle button. When set to [On] (the default setting):
the list view will display all items of type. [All] toggled [On] also clears and disables the Owners dropdown. Toggling [All] to [Off]:
enables the Owners dropdown: the user can then select which owner’s items to display in the Items List (and the owner column will no longer display in the list).
The Items List only displays items that are active in the system and does not display items with a status of “Decommissioned”, so as to minimise clutter. In order to see those items, from the Filter Menu, select “All Items” from the bottom of that dropdown. To remove “Decommissioned” items from view, select “Active” from the Filter dropdown.
Item Details
In the course of its lifespan, a item will see a number of events. By definition, when a item changes status, it is considered a new event. Recording a change of item status is done in the “Add or Edit Item” form.
This listbox displays all events for the currently selected item in the Items List. Changing which item is selected will change the events displayed.
Button Bar
There is some duplication of button functions in the traditional menu at the top of the main form.
The button bar’s buttons provide a quick and easy way to do the most common tasks.
Save - The Save button saves the event information to the EventData worksheet. If this was an edit of existing information, it will save over that record.
ShowSheet - The Show Sheet button closes the Manage Item form, makes the Items worksheet the active worksheet and drops the user onto the record in that sheet corresponding to the last selected item in the Item List.
Help - Opens Firefox and displays this document.
Close - Closes the Manage Item form. Note: this button and Menu –> File –> Exit are the only methods to close this form. Clicking on the red ‘X’ in the upper right-hand corner of the form does nothing. This is by design.
Item Image
When available, the image displayed corresponds to the currently selected item in the Item List. If no item is selected, or no picture exists for the selected item, the image is just a question mark, the default image.
Unlike on the Item Management form, the image is clickable, allowing the user to select a new image from the images folder. [8]
If a record for the item exists in the workbook, it will appear in …
Footnotes
Additional notes
Main Worksheets
Home Inventory is an Excel workbook comprised of several worksheets.
Two worksheets - Items and EVENTDATA - contain the actual data. The records are “linked” by a column (field) both sheets share: the Item number field. In Items, the Item Number field contains unique records: each Item represents one item. While in EVENTDATA, the Item references the item in Items, each record is a unique event for the items, so as a item can see numerous events, the Item number will not be a unique identifier for records in EVENTDATA.
Unfortunately, Excel doesn’t support SQL, so there is no way to set up data relationships as a proper relational database would, with key fields and SELECT statements, so relationships have to be done somewhat manually with code. Which mostly works, albeit much slower.
Item Data
Items contains the item information originally supplied by Materiel Management. For that reason, several of the columns have little relevance for our purposes. The columns that are used by the program are the one in italics in the following list:
Name |
Description |
|---|---|
ItemNumber |
Unique asset number assigned by BTS/Mat Management |
ItemDescription |
Name of the item |
Status |
In Service, To Repairs, Decommissioned, Stored, etc |
Location |
Most recent site item was sighted |
Owner |
Used if on loan from another site |
Asset |
–Not used– |
FuncLocation |
–Not used– |
FuncLocationDesc |
–Not used– |
CostCentre |
Unreliable, not used |
Manufacturer |
All caps for consistency |
ModelNumber |
Inconsistently documented in BTS, helps identify item |
SerialNumber |
Inconsistently documented in BTS, helps identify item |
AcquisitionDate |
–Not used– |
AcquisitionValue |
–Not used– |
CurrentAge |
–Not used– |
ItemType |
Classification, used to group items within the software |
ItemImage |
Optional: image of the item, displayed in the software |
Test-n-Tag |
Date of last BTS Test-and-Tag |
Event Data
EVENTDATA contains event information for items, i.e., things that have happened, like “put into service”, “sent to repair”, “retired” and so forth. This data is original to Home Inventory and is not readily available through other information outlets, such as Materiel Management or BTS. Thus, all columns have total relevance. The columns are:
Name |
Description |
|---|---|
ItemNumber |
Number assigned by BTS/Mat Management, also the key field |
EditedBy |
Name of the person preforming the documentation |
ChangedDate |
Event date |
DatedStatus |
Item status on this date: In Service, To Repairs, Decommissioned, etc. |
Notes |
Details regarding that status |
EventID |
Unique identifier for that event |
Supporting Worksheets
The rest of the tabs at the bottom of the workbook represent support worksheets:
Name |
Description |
|---|---|
Meta |
Data lists used by the software - generally, not to be edited directly |
Contacts |
Vendors, suppliers and other contacts (sort-of a virtual Rolodex) |
ByType |
Report |
DevHist |
Report |
Repair |
Temporary report sheet |
Images |
Image list and location - its future is in doubt |
In short, the worksheets each serve a purpose. The main ones are summarised below:
BoxData: – A set of records of non-consummable (assetable) Items
ItemData: – Records of events for those items.
Meta: – contains names and associated abbreviations. It also stores cross-session information, like last opened item record and details, last user and whether the form is running in debug mode or not.
Images: – URL for image of item
Licence Agreement
GNU General Public Licence v3
Terms of General Public Licence, version 3:
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public Licence as published by the Free Software Foundation, either version 3 of the Licence, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public Licence for more details.
You should have received a copy of the GNU General Public Licence along with this programme. If not, see:
Full GNU GPL (need online access to view this document)