1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - user input form

Discussion in 'Business Applications' started by matt_hills81, Feb 11, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. matt_hills81

    matt_hills81 Thread Starter

    Joined:
    Feb 11, 2008
    Messages:
    5
    Hi,

    I want to create an excel based calendar system for monitoring staff holiday!

    Basically I want staff to be able to open a file, and be presented with an input screen where they indicate their name, date and have a small description box which will then get pulled through to a weekly calendar sheet.

    I have created how I would like to weekly sheet to look, but have no clue on how to get the above description to work!!

    i have attached the file so far!! Any help would be greatly appreciated!!
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Outstanding primer here.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can see a few issues with what you currently have.
    First of all, how does the user put in the Date(s), i.e. if they are taking 3 days off do they have to make 3 entries or will you have a "From" and "To" date.
    If so how do you translate that on to your sheet(s)?
    What happens if their holiday stretches across 2 week's of sheets i.e. Friday and Monday?
    What about holidays greater than 1 or 2 weeks?
    It can be done using VBA macros to place the Description on the correct sheet and in the correct Date(s).
    A usefull overview of Holidays can also be achieved using a "Ghantt" chart, which could show either a month, quarter or year to ensure that the users do not all book the same time off.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Agree with OBP's "I can see a few issues". Your basic structure is all-important.

    Have a look at the table layout on Sheet5 of the attached. Cell F2 is for the week commencing date (must be a Monday). Note that the coloured area on Week is clear.

    Switch back to Sheet5. Note: I see F2 as Monday 11 Feb 08, your settings may be different.

    Run the test macro, then check Week.
     

    Attached Files:

  5. matt_hills81

    matt_hills81 Thread Starter

    Joined:
    Feb 11, 2008
    Messages:
    5
    Thanks bomb, that is the type of input that i want to achieve!

    I am seeing the same date as you - Monday 11 Feb 08, and the inputted data is coming through where I want it - I just need to be able to apply that type of set up to an entire year!

    Am happy to take suggestions as to a better layout - as you may have guessed I'm fairly amatuer at this!!
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    To go back to "Excel - user input form", there are input forms and input forms.

    See the new attached. Records has a table with a header row plus 3 "records". Switch to Input, click the button, switch back to Records to see the table updated with the data from the "form" area.

    So you could use the records table to generate per week "displays" as "controlled" by Week!F2.

    You would need to add code to validate inputs, e.g. that Input!C2 is a valid name (because data validation can be overwritten), that Input!C3 is a valid date, etc.
     

    Attached Files:

  7. matt_hills81

    matt_hills81 Thread Starter

    Joined:
    Feb 11, 2008
    Messages:
    5
    OK - so i have realised that the format I was going for wasn't really going to work that smoothly, and I came across a suitable template (at least for the time being).

    All I want to do now is create some sort of userform on Sheet2 where the holiday info can be input, then automatically taken through to the year1 sheet to the range starting A4.

    I have made a start at it, but the calendar option that i have added doesn't quite do what i want it to. Is there a way to get a pop up calendar similar to that used in Outlook (where it just drops down from the date cell).

    Also, I have the input cells directly on the sheet - as I can't figure a way to get this data into a user form where the dates can be entered from a calendar option (if we can fix it so that the end date cannot be < the start date that would be great or so that it defaulted to at least the start date, but could be changed to a later date??)

    My thinking is that if I can at least get this started, I may then be able to develop it further with different reasons for days of etc!!
     

    Attached Files:

  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I couldn't figure what on earth kind of calendar controls you were using! :eek:

    Plus I couldn't see that form3 was being used for anything much, so I kind of just *******ised it.

    HTH :)

    Private Sub CommandButton1_Click()
    Start = IsDate(TextBox1)
    Finish = IsDate(TextBox2)
    If ListBox1.ListIndex = -1 Then
    MsgBox "No name selected."
    ElseIf Start = False Then
    MsgBox "Invalid start date."
    ElseIf Finish = False Then
    MsgBox "Invalid end date."
    ElseIf TextBox2 < TextBox1 Then
    MsgBox "End date before start date."
    Else
    x = Sheets("Year 1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Year 1").Cells(x, 1) = ListBox1
    Sheets("Year 1").Cells(x, 2) = TextBox1
    Sheets("Year 1").Cells(x, 3) = TextBox2
    Unload UserForm3
    End If
    End Sub
     

    Attached Files:

  9. matt_hills81

    matt_hills81 Thread Starter

    Joined:
    Feb 11, 2008
    Messages:
    5
    Excellent bomb!

    That form is doing what i wanted, the only problem is that the dates aren't being recognised as dates in the year 1 sheet, they appear as text (tried changing the cell formats but didn't seem to make a difference) so the data isn't being updated onto the monthly planner.

    The calendar controls were the ones built into excel - Calander Controls 11.0.

    I've had a bit of a play myself (but still can't get it into the userform)
     

    Attached Files:

  10. matt_hills81

    matt_hills81 Thread Starter

    Joined:
    Feb 11, 2008
    Messages:
    5
    oh - and that form 3 wasn't doing anything on my version before!!
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Oops. Tweak a couple of lines:

    Sheets("Year 1").Cells(x, 2) = CDate(TextBox1)
    Sheets("Year 1").Cells(x, 3) = CDate(TextBox2)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/681892

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice