+ Reply to Thread
Results 1 to 65 of 65

excel userform calendar for tracking leave

  1. #1
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    excel userform calendar for tracking leave

    Hi,

    I'm creating this form in excel to track leave. What i have now is just some basic codes, hopefully someone can help me with it.

    In the userform, there are 2 calendars for picking dates. The dates will be populated into 2 textboxes. How do I transfer the dates picked onto the spreadsheet which correspond to the correct date? EG: If I picked 1st Sep to 3rd Sep, the leave will be populated into the cell that is 1st and 3rd Sep.

    And regarding the calendar, even thou i set the date format to dd/mm/yyyy, it appears that some of the dates will switch to mm/dd/yyyy for no apparent reason.

    Attached is the file for reference
    Attached Files Attached Files

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try returned file See Notes in sheet.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, thanks for offering your help. I attached the file with some suggestions which I hope you can help me with.
    Attached Files Attached Files

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Have a play with this:-
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, please take a look at the attached file, thank you
    Attached Files Attached Files

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try this:-
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi mick, please take a look at the files. Thank you
    Attached Files Attached Files

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    In the line of code below in the "Workbook_Open" Event you need to try changing the "VbMonday" (IN RED) to "VbSunday"
    This gives you the start of the week. I think that is your problem.
    Please Login or Register  to view this content.
    Ref :- Filter

    In the lookuplist, i added more employees, and they come from different department. Can I have a filter button in cell A3 to show employees from departments?
    Can you explain how you want this to work, for example do you want it on each sheet on just "LookUpList"
    An Example of what it will show/do would help.
    I can't get a feel for what you expect to see and where !!!

    All the other things I have done.

  9. #9
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi mick, sorry for not being more specific. I would like the filer to be in cell A3 of every month. The drop down list will be the department names (HR, Finance etc). If I select HR from the drop down list, the names of the employees who are from HR will appear in cell A4 onwards with their corresponding leave taken (if any). Hope it is clear enough

    I changed the code to VbSunday and everything is fine now.

  10. #10
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try this:-
    Regrds Mick
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, the filter looks great. I noticed that the names in the list gets mixed up if I select one of the departments from the filter followed by another department. Example: I selected "HR", the names in "HR" shows up correctly. I now select "Finance", extra names appear in "Finance" which are from other departments.
    Can you please help me with that?

  12. #12
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    In the module "Thisworkbook" there is a Change_Event code, as shown below.
    Move the line "Range("A:A").EntireRow.Hidden = False" 3 rows above as shown.

    Please Login or Register  to view this content.
    Regards Mick

  13. #13
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, thank you so much for your time and effort in helping me with this project. Will let you know if I encounter further problems. Thank you once again

  14. #14
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    You're welcome
    Regrds Mick

  15. #15
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi mick, sorry to disturb you again. I tried changing the year in cell A1 in lookuplist and reopened the workbook. The year in the commandbutton changed, but the calendar days and dates in the worksheet did not change to reflect the year. The days and dates still follow year 2013. Can you please help me see what is wrong?

  16. #16
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    In the "ThisWorkbook" module "Workbook_Open" Event, change the line as shown below:-
    Please Login or Register  to view this content.
    Regrds Mick

  17. #17
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, thank you, the above code works. But I discovered another problem which will require your help again. After I used the above code and changed the year to 2014 and try to submit a leave in Jan, it does not appear in Jan 2014. Instead, it appeared in nJan. Please help me with this.

  18. #18
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try changing the line as below:-
    The code is in the "Submit" Button in theUserform Module.
    Please Login or Register  to view this content.
    Regrds Mick

  19. #19
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, it works smoothly now. Thanks for your help

  20. #20
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, I have received some feedbacks from staff who have tried the calendar.

    They would like the end date calendar to change date automatically depending on the date selected in the start date calendar.
    Eg: Today is 8th Nov, both calendars are at today's date. Staff A selects annual leave in the start date calendar on 19th Nov. The end date calendar automatically changes to 19th Nov instead of staying on 8th Nov.

    Can you please help me with this change if what I'm asking for is possible.

  21. #21
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try this:-
    Click "Alt+F11", VB window appears.
    Click "Ctrl + R" , Properties window show on left of Vb window
    Double click "Userform1" in properties list.
    Useform Appears in VBWindow.
    Double click on Calendar1, Code for Calendar1 appears.
    Change the Code as below:-
    NB:_ Leave the Textbox2 bit out , if you don't want it to (initially) show in Textbox2.
    Please Login or Register  to view this content.
    Regrds M ick

  22. #22
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Thank you so much Mick!!

  23. #23
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Your're Welcome
    Regrds Mick

  24. #24
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick,

    Sorry to disturb you again. I was wondering if its possible to add in Public Holidays into the calendar. The day of the public holidays will be grayed off with the name of the holiday going veritcally across all rows of the names of employees. Hope Im clear enough

  25. #25
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Is that Singapore Bank holidays ??
    Can you can provide a list or reference. It can placed it in sheet "LookupList" and the code altered to Grey out the Public Holidays and insert their Names.

  26. #26
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick,

    you can take a look at this link for Singapore's public holidays

    http://www.mom.gov.sg/newsroom/Pages...spx?listid=493

  27. #27
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try this:-
    I have added a new sheet "PH" to hold the Bank Holiday Data in column "A to C", you will need to format any future Holiday data in the same Format.
    Other data on that sheet is not important.
    You will need to set the "VBDay" to you local setting, VbSaturday or VbSunday to suit you. See "WorkBook_Open ". I had to alter it for my Local Use.
    Regrds Mick
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Thanks Mick, its working great

  29. #29
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Thank for the (good News) feedback
    Regrds Mick

  30. #30
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, I just received more feedbacks regarding the calendar and I hope you can help me with it.
    1) Push the names down 1 row to A5. Insert the words "Lab Event" in column A row 4. Entire Row A4 to be editable such that when there are events, staff can type the name of the event into a particular period (eg. Internal Audit from 1 jan to 6 jan, staff will type in "Internal Audit" in row A4 from 1 jan to 6 jan)
    2) Insert a column at B for initials of the employee (eg. Employee A has initials AMK)

    Let me know if Im not clear on the things mentioned above

    Thanks

  31. #31
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try this:-
    Regrds Mick
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, Thank you for the codes. Can you please allow for merging in the lab events row? If an "Audit" event is taking place from monday to friday, I would like to be able to merge those 5 days together instead of typing "Audit" in all 5 days. Another improvement is can you remove the lab events wording from the dropdown list in the userform?

    Thanks

  33. #33
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    I'm not too sure what you want !!
    Do you want to enter a particular "Lab Event" Event such as "Audit" through ,input into the Userform Or by Manual input to the Sheet.
    If you want to merge the Name of the "Lab event", I would think this should be done through the Userform, in which case why would you want to remove "Lab Event" from the Drop down list in the Userform. If the cells were merged, You would also need to mark the period length by colouring, or some other method.
    Merging the cells would also be problematic if the merged cells covered a WeekEnd Or Public Holiday.
    It would be Easier to code if you used the same Method as used for entering employees Leave. What problems do you see with that.???

    Please explain more fully how you would like the "Lab Event" procedure to work.
    Regrds Mick

  34. #34
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    I was thinking more of manual entry, thats why i didnt understand why the "Lab Event" was in the drop down list haha.

    It was only after you replied that I realised it was there for a reason. I have sent out the file to my colleagues for scrutiny and see what kind of feedbacks i will get. If they are happy with the userform entry for Lab Event, then there's no need to trouble you.

    Will keep you posted. Thanks!!

  35. #35
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi mick,

    regarding the "Lab Event" post above, my colleagues are satisfied with the way it works. Thank you for that.

    Hope you can help me with another issue. Some times I find the excel file being messed up by my colleagues who try to manual key in data or trying to change formatting even thou I specifically told them to input through the user form and not do any other changes. So, is it possible to lock the cells from editing? I tried protecting the sheet, but it ends up with an error. If it is possible, it will mean that accidental entry cannot be deleted through manual deletion. Can you help me add another command button on the user form for that purpose? Let me outline the process for deletion in case im not clear:

    Open user form > Select name from drop down list > Select date that was entered wrongly > execute command button

    Cheers!

  36. #36
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi mick, waiting for your expert advise

  37. #37
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    In Userform1 Module add the following code shown in red.
    Please Login or Register  to view this content.
    In each of the 12 Months Tabs. Right click the Tab Select view code , and paste inthe following:-

    Please Login or Register  to view this content.
    This should protect each sheet but allow you to alter the Input through the Userform.
    Also you can now only close the Userform with the "Close" Button.
    Regrds Mick

  38. #38
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi mick,

    there is an error after inserting the codes.

    With Sheets(Mth)
    .cells.unmerge is highlighted

    Please help me solve this problem

  39. #39
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Oh and another thing, how do i change the wording in the command button from "Calendar Show 2014" to "Click me to apply leave"?

  40. #40
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    To Change the CommandButton Captions :- "Unprotect sheet if protected"
    Right click CommandButton in "Design Mode", Choose "Properties from Drop down, Alter Caption in Properties Box.

    To alter Protection:- Remove the individual Sheet Protection in "Sheet Activate" Code
    In the "Thisworkbook" code, basically as below, add the lines shown in red.
    Please Login or Register  to view this content.
    To Change the CommadButton Captions :-
    Right click commandButton in "Design Mode", Choose "Properties from Drop down, Alter Caption in Properties Box.

  41. #41
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, Thank you for your help, much appreciated. Now that the cells are locked, anyone who entered a leave wrongly or not taking leave anymore, he / she cannot delete the leave from the file. Can you help me add a blank leave type for people to delete wrongly entered leave? I made a blank cell in the lookuplist but when i tried using it, theres a msg box "data missing or incorrect"

  42. #42
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try adding a "Leave Delete Button" on the Userform, then attach the code below, you will need to select the "Employee" and the dates to be deleted , click the Button and the Initials and Colouring of those cells will be removed.
    Please Login or Register  to view this content.

  43. #43
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick,

    Thank you for your help the other time. Can you please help me with this situation? Is there a macro for inserting comments into the cell selected for leave application? Example: a staff going on course on 28th March will select leave type "OC", then in the next textbox he will type in what course he is going for, say First Aid. So the cell will reflect "OC" with a comment saying First Aid. Hope you can extend your help on this

  44. #44
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    I've added Textbox4 to the Userform for adding "Comments"
    Add the line shown in red to the code:-
    The comment Box will appear in the first day of the leave.
    Please Login or Register  to view this content.

  45. #45
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, sorry to disturb you again. Is there a way to make this file compatible with office 2010? I noticed that the calendar in the user form is not supported by 2010 and when users of office 2010 copy and paste their leave in the file and save, the calendar in the user form goes missing. Hope you can help me iron out this error.

  46. #46
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Sorry i don't have 2010, but you may be able download the calender Here:-
    https://www.google.co.uk/url?sa=t&rc...78OWBSkXIxYMZg

    Or by Googling similar!!!

  47. #47
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, the IT department couldn't fix the problem even after downloading the add-on. So after some searching for alternatives, I discovered that both 2007 and 2010 versions have a Microsoft date and time picker. Could you help me with the coding if I were to replace the calendar control with the date picker?

  48. #48
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Unfortunately I have 2007 but I don't have "Date and Time Picker" , only "Calendar Control 12".

  49. #49
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Quote Originally Posted by MickG View Post
    I've added Textbox4 to the Userform for adding "Comments"
    Add the line shown in red to the code:-
    The comment Box will appear in the first day of the leave.
    Please Login or Register  to view this content.
    Hi Mick, I tried your code with the adding of comments to the first day of leave, but the comment doesn't seem to show up after submitting the leave. Can you please help me with that?

  50. #50
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Perhaps this , "In Bold"
    Please Login or Register  to view this content.

  51. #51
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Thanks Mick, its working now

  52. #52
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    You're welcome

  53. #53
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, I would like to check with you on something. Is there a work around for this code?
    Please Login or Register  to view this content.
    I am facing a problem where Office 2007 and Office 2010 users sees the dates and days differently. Is there another way of writing this code so that the problem can be eliminated? Attached the screen shot for your reference
    Attached Files Attached Files

  54. #54
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    Try adding the code below:-
    The new variables need to go to the top of the code page
    When you run the code you will see a msgbox showing the "The date" The "New Code WeekDay" and the "Old code Weekday" these weekdays should be the same.
    I have amended the code to show the new "Weekday" ,
    NB:- You may need to change you "Weekday" in the following "Msgbox" line " WhatYr, M, n), vbMonday)" , to suit your own code
    When you are happy your getting the right "Weekday", remove the msgbox.
    Please Login or Register  to view this content.

  55. #55
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, I have found the problem for the cause in staff seeing the dates differently. It is due to their regional setting in the control panel (see attached). Is there a code to detect if staff's computer have the wrong setting and change it for them through a macro? Need to set it to English (Singapore) instead of English (UK). I saw this Application.International(xlCountrySetting), but i do no know if this is the solution nor do I know how to apply it here. Can you please help me?
    Attached Files Attached Files

  56. #56
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    My previous post should provide a solution without the need to change the Regional Settings, Have you tried it ?????
    Last edited by MickG; 10-31-2014 at 08:03 AM.

  57. #57
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, Im not sure if I pasted the code in the correct location, but i ended up getting a popup box that has a date and a "ok" button which i had to hold down the enter button as it scrolled through the entire year (meaning each date was a popup box which meant pressing the enter key 365 times). Was it suppose to work like that?

  58. #58
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    The msgbox was there to see the Initial results:-
    Reference my previous post:-
    When you are happy your getting the right "Weekday", remove the msgbox.
    That Msgbox is a line of code in the new bit of code, that Msgbox wants Removing/commenting out.
    Last edited by MickG; 11-02-2014 at 07:10 AM.

  59. #59
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, I ended getting something like this. Did i pasted the code in the wrong place?
    Attached Files Attached Files

  60. #60
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    double posted

  61. #61
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    The VBA project appears Hidden and password protected , so I can't see it, But I get a compiler error on opening the file, so something is not quite right.!!!!

  62. #62
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    sorry abt that Mick, have removed the protection.

    Was also wondering if there is a code to exit the file without saving if the user's computer setting does not meet criteria
    Attached Files Attached Files

  63. #63
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: excel userform calendar for tracking leave

    The code looks OK for loading the Dates and Days on the sheet and appears to show the correct dates.
    When I load the File or the a Userform I get a VBAProject Reference Error (something missing!!! ) Perhaps something to do with "DatePicker", But the reference Library is password protected.
    I notice you now have a bespoke Calender . If that covers your needs perhaps you can remove the reference to "DatePicker", other than that I'm not sure how you would check for a computer compatibility errors.

  64. #64
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    alright Mick, thank you.

  65. #65
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: excel userform calendar for tracking leave

    Hi Mick, wonder if you can help me with this. I added an extra sheet named "Masterlist". It tracks the amount of time offs each staff is eligible to take. If lets say a staff has 3 hours time off available and keys it into December for example, the "Masterlist" will reflect the balance left, which should be 0. I'm thinking along the line of sum and lookup, but not very sure how to do it across multiple sheets. Hope you will be able to help out.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA to automatically populate Excel calendar with staff vacation (leave) data
    By batteredveg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2013, 04:09 PM
  2. Find Criteria and then Sum Alphanumeric Cells (Tracking Leave)
    By ksews612 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-11-2012, 10:30 AM
  3. Using a userform Calendar to fill in an excel calendar, and also a log
    By 00Able in forum Excel Programming / VBA / Macros
    Replies: 64
    Last Post: 09-08-2011, 08:50 PM
  4. [SOLVED] I need a template for Employee annual leave tracking
    By Pamela Aranguiz in forum Excel General
    Replies: 1
    Last Post: 01-11-2006, 09:40 PM
  5. Tracking annual leave
    By opos in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-15-2005, 07:05 AM

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