create a inventory spreadsheet

Here is something I put together as an exercise in user forms. If you get nothing else out of this, it should be the concept of tracking inventory through a series of transactions rather than updating quantity on hand. Quantity on hand should be the sum of the transactions, something a pivot table can do for you.

I looked at your requirement and decided that something simpler could be done.

The Scan In-Out sheet has one active cell: B13. The item should scan into here. Since I don't have a scanner, I set up a data validation lookup for testing. You may have to remove this for the production sytem.

The items sheet is the list of items and whatever other metrics you would like to track. I suck with just two: category and description.

The transactions sheet shows the transactions and does calculation to compute the current status of the item.

The report is a pivot table that shows the current status. You can also do other things with the data such as count the number of times an item is checked in and out, how many transactions you've had in a day, or average time that an item has been checked out.

The way it should work is you scan an item and it shows up in Cell B13. Click a button for the appropriate transaction. If an item is already in and you scan it in or it is already out and you scan it out, the system will warn you about it, but continue the transaction.

To clear the transaction table, undo all filters, select all rows (except the header) then right click and select Delete -> Table Rows. This will assure that the table "remembers" the formulas.

Then Start using. Scan in everything on hand. Scan it out when it leaves and in when it comes back.