VDC Requests - Kevin Brinkerhoff - Winter 2010

http://files.gove.net/shares/files/10w/kgbrink/VDC_Requests.xlsm
http://files.gove.net/shares/files/10w/kgbrink/Final_Project_Writeup.pdf

I have been working in OIT’s Project Management department for the past few years. Because I see myself working in similar departments long term, I wanted to solve a business problem specific to my department. I spoke with a Product Manager who had a need that I could solve with VBA. Below is a summary of the business need and what my program would need to include in order to address the business need.

Business Need: The product manager uses an Access file to store information about various technology-related requests that he receives from campus departments. There are about 15 departments that come to him with technology-related requests. For each of the requests, he stores a lot of information about what the departments need, when they need it by, and any additional notes that need to be captured. He currently stores all of the request information in the Access file. The problem is that the internal website that our office uses is unable to store Access files, and he wants the information available to others who use the website. He asked me to duplicate the functionality that he has in the Access file in Excel through the use of VBA.

The Program: The program is a compilation of several components, including:
1. Two userforms to capture and store information about each request, with two different types of requests (VDC Requests and Resource Requests) that need to be captured. This becomes a bit complicated for me because the VDC Requests and the Resource Requests must be tied to each other, as well as two other tables: Department and Account Manager. Each request consists of many resources. Whenever a request is created, that request needs to be linked to the department that is requesting it.
2. I chose to include website login functionality so that I could stretch my understanding of what we learned in class. Whenever a file is checked out of our internal website, it tracks who checked it out. Closing a checked out file will automatically check it back in. I created a macro that navigates into website based on user credentials and grabs the file where my program is.
3. Report functionality so the user can see a list of all requests per department. This includes cycling through each of the tables, storing the data in arrays, pasting them in a worksheet report, and formatting the report for aesthetics.