Unknown Label Generator - Zachary Buchanan

Executive Summary
In the Department of Chemistry and Biochemistry, one of the largest enrolled in classes is Chem 107, General College Chemistry Lab. This semester, there were 783 students enrolled in the class. Each week, the students participate in a lab where they have to determine the properties of some “unknown” Substance. Employees of the stockroom prepare stocks of these unknowns, distribute them in individual test tubes, and label the test tubes with randomly generated codes for grading purposes.
One of the least effective uses of time is the preparing of these labels. For each section there is a separate site required to input data on the unknowns. Once the data is entered, the label codes are generated. These are then copied to Excel for each section, where a script is run to add section letters, and organize them into 10x10 tables for printing onto stickers.
This process is very repetitive. While some of the data changes week to week, the process for producing the labels is the same. Inputs such as number of unknown types, number of vials, values for each unknown and due date are required to generate each set of labels. This is done for each section, for a total of 8 labs. Each lab is done again each semester.
This process was a great candidate to be automated. My plan was to create a set of user forms connected to worksheets that would save information from semester to semester. Each time the program was run, the user would be allowed to select a lab to make labels for, indicate the number of unknown types, and the next form would automatically fill in the previous semester’s values for the values of the unknowns. If there were changes to be made, the user could make them, then go on to the next form, which would also automatically fill in the previous semester’s data for number of vials for each section.
After accepting this data from the user, internet explorer uses a password and username supplied by the user to access and automate inputting data and retrieving data from the Chem 107 web site that generates the codes. These are then processed, adding the unknown letter to the labels and organizing them to be ready to print.

Project Paper: http://files.gove.net/shares/files/14w/buchanaz/Final_Project_Writeup.pdf

Project Files: http://files.gove.net/shares/files/14w/buchanaz/Unknown_Code_Generator.xlsm