Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HolidayPlanner (2003 SP2)

    Good afternoon

    I want to make a Holiday / Sickness planner and would like to know if there is a way by which a cell can be coloured according to a letter being typed in, for instance an H for Holiday may produce Red, an S for Sickness may produce Green etc. for Maternity, Training etc. If anybody has already started down this road and has a rough template that I could work on it would be appreciated to avoid me making early pitfalls, I love the look of the commercial ones but I think these must be done with 'User Forms' and 'Pivot Charts' which are way, way above my knowledge (the last time I tried to write a Macro Bill Clinton called me as I had set the American defence system at Defcom 1 <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )

    Any advice / pointers will as usual be greatfully received

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HolidayPlanner (2003 SP2)

    If you need at most 3 colours apart from the default one, you can use Conditional Formatting. No programming needed at all:
    - Select the cells you are going to enter codes in.
    - Select Format | Conditional Formatting...
    - The first dropdown already has "Cell Value Is" - leave it that way.
    - Select "equals" in the second dropdown.
    - Enter H in the box next to it.
    - Click Format...
    - Specify the formatting you want - you can set the font, borders and background colour (pattern).
    - Click OK when done.
    - Click Add>>.
    - Specify a second condition and format.
    - If desired, click Add>> again, and specify a third condition and format.
    - Finally, click OK.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner (2003 SP2)

    Thanks Hans

    I would have preferred more, but will now settle for 3 because of the ease of use, thanks for the pointer

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner (2003 SP2)

    More can be done with code in the worksheet change event routine. We will help if you provide a sample workbook for testing and a more detailed explaination of exactly what you want.
    Legare Coleman

  5. #5
    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: HolidayPlanner (2003 SP2)

    <post:=338,306>post 338,306</post:> has some VB code to format a range of cells using a lookup table of values.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner (2003 SP2)

    Hi Legare and Steve

    Thanks for your offers, I have never really done any coding as I normally well and truly mess it up to the point that nothing ever works but I am willing to give it a go

    I think that the attached will give an idea of the route that I have headed down although to upload it to the Lounge I have had to savagely cut it as it covers about 8 departments and 90 employee's.

    Any pointers to a better design or way to operate the workbook would be as usual greatfully received.

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HolidayPlanner (2003 SP2)

    I'd let the tables for each month begin in the same column (now, the 1st of January is in column D, and the 1st of February in column C.
    I'd place the number of holiday days etc. in the same columns in each sheet, i.e. in columns AI and on, regardless of the length of the month.
    This has the following advantages:
    1) You can use the same formulas in these columns on all sheets, e.g. in AI6: =COUNTIF(D6:AH6,"H"). It doesn't matter that some months don't have days in column AH - there'll be no "H" then.
    2) You can use a formula such as
    <code>
    =SUM(Januaryecember!AI6)
    </code>
    to get the total number of Holiday days for the 1st employee.

    I'd stick with conditional formatting for the three most frequently used codes. The other ones will simply be visible as letters. The downside of using code to change the color of a cell is that you'll lose the ability to undo actions (Excel empties the undo stack when VBA code is executed).

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner (2003 SP2)

    Hi Hans

    I have taken your advice and on each worksheet - January - December I have made the cells consistent and this has made it Immeasurably easier to change, amend and insert data, so thanks for that, now that it so t looks effecient, can I make it effecient? Could I do things now such as have a user form whereby I could use fields to select the employees name, the dates required and the purpose of the leave/absence to fill the worksheets (I am just thinking aloud as I am typing, I am not expecting a finished job from anybody) or is that a whole different kettle of 'roll mops' that I am opening

    Each month now looks like the attached and accomodates 153 employee's across 31 days / months

    Do you think I can go anywhere else with this?

    Thanks

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HolidayPlanner (2003 SP2)

    A userform always involves VBA programming, and you've stated repeatedly that you would like to stay away from that if possible.

    If you want the user to be able to select names and options from a dropdown list, you can use Data | Validation with the List option; Validation also lets you provide a short instruction when the user selects a cell, and display a rude message if the user enters an incorrect value.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner (2003 SP2)

    Thanks Hans

    I will read up and go down the data valadation route if I am able (life is grand when you hit semi retirment, when I was working I could never get around to experimenting with Excel, now after all these years I can have a go and if it works fine, and if it does'nt, well, erm, fine :-) )

    BTW and perhaps in the wrong lounge, as a great advisor on Excel what do you think of Bill Jelen, he has a couple of seminars in the Uk in November and I have a free invite (I helped proof a book, not I hasten to add for any thing technical!!) and as I am on holiday near Southampton on the date of the first one I wondered if it would be worthwhile to visit.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HolidayPlanner (2003 SP2)

    It would certainly be interesting to go and see Bill Jelen aka MrExcel. He's a Microsoft MVP for Excel. His website Mr. Excel is a fount of information on Excel.

Posting Permissions

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