Hi All,
I have been working on the attached project for a short time, and figured I'd share it with the group. To briefly summarize, my organization had a previously semi outdated method of tracking Paid Time Off for employees. There was a calendar built into Excel (similar to the Calendar sheet in my attached file). An administrator would essentially input directly into the calendar, which, as you can imagine, became a relatively cumbersome task as the organization grew. I put together many of the Excel VBA ideas that I've had from prior experience into completing this database. I'll make a few notes here: I understand that on a macroscopic level, this may not be as refined as acquiring a dedicated solution, but of course, that isn't an option at the time. Also, the code is a bit messy in places because it hasn't been fully vetted and improved. Finally, there are some things in some forms that may seem odd (checkboxes that go nowhere). These are likely a casualty of me trimming down the file to remove any existing confidential data.
As the title [ominously] suggested, there are a number of items going on in the Workbook. If I had to pick one item to focus on, I'd suggest taking a look at the first item: creating controls at runtime. One issue with transitioning to a database structure was a loss of departmental overviews to be provided to management. Sure, individual departmental sheets can be linked, but that would create a more sluggish and less scalable model. Additionally, different managers request different types of information, so it's barely a "one size fits all application." Userform3 (I know, creative titles!) allows for the workbook administrator to export certain information to reports based on need. When the userform launches, a listbox populates that includes the current departments. In addition, toggle buttons are generated that correspond to each field in the DATA worksheet allowing that field to be exported to a new book. The true benefit, as columns are added or deleted (for example I removed our budget numbers), the userform expands and contracts and adds/deletes buttons based on need. Give it a try. Add a column, then launch the form to see that it is now included in the togglebuttons.
In addition, there is a checkbox on this form to Print items, which will print out a report as well as export it to a new workbook.
When you open the workbook, there should be a floating toolbar, with an option for Reports (from what I understand, this doesn't always work in Excel 2007, but the toolbar can be found in the Developer tab of the ribbon). If it's still not visible, you can always press Alt+F8, then choose macro ShowForm3 to see the reports userform.
I've left all of the code in the workbook unlocked for the curious observer, and I'm happy to answer any relevant questions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
ummm, don't see the file