+ Reply to Thread
Results 1 to 2 of 2

Create Controls at Runtime/Expanding Forms/Floating Toolbar/PTO Calendar

  1. #1
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Create Controls at Runtime/Expanding Forms/Floating Toolbar/PTO Calendar

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Create Controls at Runtime/Expanding Forms/Floating Toolbar/PTO Calendar

    I'm going to list out many of the other features in the workbook, but I will not get into too many details to prevent BOREDOM! However, if anyone has any interest/comments/suggestions, I'd be glad to discuss.

    || Use of Commandbars. There is the obvious Floating Toolbar that serves as a dashboard. Also, if you right click on a cell in the NAME column in DATA sheet, you have two other options at the bottom: save and email.

    || Interaction: The workbook pulls information from the PTO sheet and DATA sheet and moves them to corresponding fields on the CALENDAR sheet when certain events are run (See below).

    || Save options. This feature creates a new folder in your temp directory named Attendance. If the folder already exists, it saves to the folder.

    || Email: Excel creates a temporary attachment, attaches it to an Outlook email, then deletes the attachment (I believe I learned this from Ron de Bruin's site.

    || Userforms to add to a database sheet. "Add PTO" in the dashboard is a userform that adds information to a PTO sheet. It includes comboboxes, textboxes, error checking, and a togglebutton that expands the userform to show a monthly summary by employee!!

    || Double click feature and interaction between workbooks. Double click on any name in the DATA sheet, and the information will automatically be pulled from both the PTO sheet and the DATA sheet, and be moved into the calendar format that employees are accustomed to.

    || Use of weekdays/Update Holidays: This macro sources the information in the RULES sheet under holidays. It then modifies the calendar to identify Sat/Sun/Holiday

    || Formulas: The workbook uses a number of different formulas in conjunction with named ranges. See Column L on Data sheet to see a column that incorporates many of these features including calculation to prorate based on hire date.

    Hope you guys enjoy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1