http://files.gove.net/shares/files/15w/lingzhiz/Searching_books_and_assign_emlpoyees_to_work.xlsm
In
March 2015, one of the Orem public library's staff members had requested that I initiate a
VBA project with the intent of streamlining and automating a process via VBA
that will ultimately help visitors locate and check-out their desired book or
collection from the library's vast book shelves. The project also would assist
library administrators to more efficiently scheduling work shifts for library
staff, a task currently taking 20 minutes per schedule. Below is a list of the
project's objectives:
a.
A drop-box list with the
names of all of books. When people click the
title of books, all information related to the said books, including its
complete title, author’s name (s), call#, annotation, and page will pop up
immediately in different cells with the proper formatting.
b.
A shift schedule. A
sheet will be created and named “Worker_schedule”. On this sheet, there are 3
tables. The table is located in the top left side which details the five
workdays for the library. The second row of the table provide the number of
workers and staff person’s name are displayed. The second table is on the
bottom left side. Names of available workers are marked in red. The third table
is on right side, it will show the most efficient shift schedule directly when
cells in the first table are changed. Based on the city’s needs, I
applied the VBA knowledge that I have learned from school to solve their
problems. I will introduce the data I got from the Orem city as following.
The
city of Orem has a datasheet like the first figure below (until now, they have
about 200 rows and 10 columns, but this database is constantly getting bigger).
Based on the client's requirements, I will first use VBA macro to reformat the
information to another sheet. They require that once the book name or other
items are inputted in the datasheet, the macro will automatically update the
information accordingly and into another sheet with specific formatting for
each row. When users select any book from the database, they will immediately see
a table like the second figure below.
No comments:
Post a Comment