Receiving Log

At my workplace before I came onboard, they were tracking incoming packages on paper. They had to write down items such as date, courier, waybill/tracking number, supplier, and purchase order number. Keeping these sheets around and trying to look for any particular delivery at a later date was a nightmare.

I figured there had to a be simple way to keep track of the packages in Excel using an input form. So I set out a task for myself to come up with the solution.

Creating the input form was the easy part.

Fig.1 - Receiving form

For the dropdown fields of Courier, Supplier, and Staff, I wanted an easy way for anyone to modify the lists without having to go in and edit the code or the form itself. Placing the lists on separate worksheets accomplished this.

Fig.2 - Courier sheet

To populate the dropdowns with their respective lists required assigning the range of cells from the appropriate sheet. This has to be done in the Userform's Initialize() function. The code for this is shown below.

				Dim rCourierData As Range
				With Worksheets("Couriers")
   					Set rCourierData = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
				End With
				Me.cboCourier.List = rCourierData.Value

The final piece was to take the data on the form and place it on worksheet in the next empty row.

				'Write Form data to next blank row on Sheet
				lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
				With ws
					.Cells(lRow, 1).Value = Me.Label8.Caption
					.Cells(lRow, 2).Value = Me.cboCourier.Value
					.Cells(lRow, 3).Value = tmpTrack
					.Cells(lRow, 4).Value = Me.cboSupplier.Value
					.Cells(lRow, 5).Value = Me.txtPO.Value
					.Cells(lRow, 6).Value = Me.cboStaff.Value
				End With

And the result looks like this:

Fig.3 - Receiving log sheet

Solvent Dispensing Log

One of my duties at work is refilling solvent bottles in the Solvent Dispensing Facility. Bulk solvent is being transferred from 200 L drums into 4 L bottles. Sometimes impurities are discovered after dispensing and there needs to be a way to determine from which drum the bottle was refilled. Before I took over these duties, there was no way to track this.

Building upon the concepts in the Receiving Log described above, I created a spreadsheet to track the drums and the individual solvent refills. The HECHMET items shown are hazardous substance inventory barcodes that are the key to matching the refills to the drums.

Fig.1 - Drum Sheet

The highlighted colours represented the drums in one of three possible states: green - opened, white - new unopened, red - emptied.

The buttons Add Drum, Edit Drum and Refill then called their respective forms Figs. 2 - 4.

Fig.2 - Add Drum form
Fig.3 - Edit Drum form
Fig.4 - Refill form

The solvent refill sheets capture the source drum barcode so that the number of refills taken from the drum can be totalled.

Fig.5 - Acetone refill sheet

Tax Client Information Sheet

A friend that prepares taxes was keeping her client list in Excel. She wanted an easier way to keep the list updated and keep track of what tax information slips the client provided. I agreed to help and this is what I came up with.

The VBA coding was a lot more involved due to the nature of some of the fields that had to be exchanged between the forms and the sheet.

Fig.1 - Client information sheet
Fig.2 - Client information form
Fig.3 - Client tax slip form