Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a form (2000-sr1)

    Hello,

    I use excel a little and so for just for very basic stuff. Everything on the sheet attached, I just learned by making this sheet. I do not have much programming knowledge except for what others have taught me or told me to type in a certain spot. <img src=/S/artist.gif border=0 alt=artist width=34 height=29>

    I know there is a way to make a form so when entering data it makes it easier for the user. I have 8 sheets. The first sheet is a totals sheet of all the information in the other sheets. I have a name column which is a concatenation of column 1 and 2. Then in the top row I have the name of each sheet which is a particular work area. The name column is linked to all the other sheets. The top row of each of the work area sheets is the date. The user finds the employee's name who is working in that area on that day and enters the number of hours worked there.

    Each row is linked to the main sheet so on the main sheet the number of hours a particular employee works is totaled forever at this point.

    I want to make the form so the user can easily enter the data from this past month and so the form pops up when the workbook is opened or maybe just adding a toolbutton would be more of something I would be capable of.....My first task is to make a form that doesn't go away and also to figure out how to make the layout of the form.

    I have attached the sheet and if you have any ideas about anything, let me know. I also want to have a report that will sort by each area and the people with the least amount of hours would be at the top of the list so the supervisor knows who to sent to a particular work area. We want everyone to get "about" equal time in a work area, this is why I made the sheet, it is getting hard to do by hand....Also, work areas are not assigned ahead of time, they are assigned each day at the beginning of the shift. This sheet is only to keep track and give the supervisor an idea of who may be put in a particular area.

    Thanks,
    Linda

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Making a form (2000-sr1)

    To me the simplest thing is to revamp the spreadsheet.
    One sheet with headings:
    Date
    Shift
    Name
    Work Area
    Hours

    Whenever someone is working enter in one line with all info. No sheet changing all in one place.

    Perhaps use data validation for Shift, name and work area to be pulled from list

    Using autofilter with subtotals could reduce list for entry and viewing (you could even include subtotals above headers for autofilter summary).
    Summary of all could be easliy done with a pivot table

    None of this requires ANY programming!
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a form (2000-sr1)

    Linda,

    Attached is your workbook. This is by no means a solution but merely an example of what can be done with Excel and VBA.

    When the workbook is opened a custom menu is created to the right of the existing Help menu. One of the new menu selections is a user form for entering hours worked.

    With the form open, the user would select the desired shift from the option buttons. This action populates the Employee Name combobox from the Totals worksheet. The user then selects the appropriate department from the Work Areas combobox, then by clicking the Find button the department sheet and employee name are found. The user would then select either week or bi-weekly to show the data entry fields. The fields reflect what is on the specific sheet for that employee name. Any entries to be made or modifications to existing values are entered, then by clicking the Enter button, these values are placed on the sheet.

    To take this further, you would have to decide how to handle each completed two week period, (delete the colums or archive the data). Data validation rules and error handling should be incorporated into the code. The code itself could be cleaned up to run more efficiently. The reporting piece would be handled nicely using pivot tables.

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a form (2000-sr1)

    Thank-you. I'll need to look at it tomorrow cause it's time for bed. This is a very busy week...it's the last week of school and the kids have many functions. I think the weekend is clear. I hope.....<s>

Posting Permissions

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