+ Reply to Thread
Results 1 to 28 of 28

Creating a staffing board for work

  1. #1
    Registered User
    Join Date
    07-15-2007
    Posts
    32

    Creating a staffing board for work

    I would like to create a new more effective quick staffing board for work.

    Name Start Time Finish Time Break Allowance Break Time 8 (then time period from 6 till 2300

    BUT when I enter say a shift of say 09:00 17:00 I need this time period to block out on the board, hard to explain

    9 -> 5
    e.g Bob 09:00 17:00 x x x x x x x x
    Sue 10:00 14:00 x x x x
    Kev 12:00 16:00 x x x x

    I need a box for each time frame 9-10, 10-11 etc as I will need to write letters to abbreviate daily tasks

    HOPE ALL THIS MAKES SENSE!!?

    Please ask any questions!

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi robbo747,

    Welcome to the forum.

    See if the attached helps.


    VBA Noob
    Attached Files Attached Files
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    Quote Originally Posted by VBA Noob
    Hi robbo747,

    Welcome to the forum.

    See if the attached helps.


    VBA Noob
    THANK YOU SO MUCH!

    Thats exactly what I'm looking for

    how did you do that so I can learn?

    Also, as the area worked shades grey. is there any way to have the whole time area in black, and it turns white when you put in the time?

    Is there a way for it to update automatically in real time without pressing 'fill board'? Hey now I'm being picky I know!

  4. #4
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    Quote Originally Posted by robbo747
    THANK YOU SO MUCH!

    Thats exactly what I'm looking for

    how did you do that so I can learn?

    Also, as the area worked shades grey. is there any way to have the whole time area in black, and it turns white when you put in the time?

    Is there a way for it to update automatically in real time without pressing 'fill board'? Hey now I'm being picky I know!
    UPDATE

    If I set up the staffing board exactly how i want it can you sort the formula's/macros?
    Last edited by VBA Noob; 07-15-2007 at 01:59 PM.

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try this version

    Slight mode to code

    VBA Noob
    Attached Files Attached Files
    Last edited by VBA Noob; 07-15-2007 at 02:08 PM.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Nice Macro VB Noob

    would be great to kick it in on worksheet change or calculate as well

  7. #7
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    Quote Originally Posted by VBA Noob
    Try this version

    Slight mode to code

    VBA Noob
    Thanks, that's great.

    When I change the size of the columns though to fit it on the page, it doesn't work anymore?!

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Thx Dave,

    Better than I was a few months ago but still learning.

    Last post has the worksheet change macro

    VBA Noob

  9. #9
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See if this helps

    VBA Noob
    Attached Files Attached Files

  10. #10
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Sorry,

    Version 4 now. Deals with times starting 06:00

    VBA Noob
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-15-2007
    Posts
    32

    Smile

    here's my template, see what you can do!

    thanks for all your efforts pal!
    Attached Files Attached Files

  12. #12
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See if this works

    Note I use a function to count Interior and this needs Ctrl + Alt + F9 to recalculate

    Or you can select another sheet and go back in to trigger the recalc

    If you don't want to tab between sheets just remove this code from the worksheet module

    Please Login or Register  to view this content.
    HTH.

    VBA Noob
    Attached Files Attached Files
    Last edited by VBA Noob; 07-15-2007 at 06:23 PM.

  13. #13
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    WOW!

    Gotta take my hat to you - nice work! Absolutely fantastic!

    Things I've noticed:

    'cover' box counts black squares and not white, so if 2 people are at work (white) and 5 people are not (black) it counts the cover as 5

    unfortunatley peoples shifts do not always start on the hour, can this worsheet recognise 00:15, 0:30 and 0:45? If I type in a start time of 17:15, the cells don't change colour.

  14. #14
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    This seems to work apart from times before 07:00

    will try to look tomorrow

    VBA Noob
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    excellent work, this will make a real difference at work - i cant thank you enough

    tested the 0:15's, if I put 08:15 in, it fills all the 09:00 box too which is incorrect

    maybe will have to put a box in for every half hour? i don't know really, you're the expert!

    -----------

    WOW - just noticed the 'time list' drop down menu - that's gonna save me LOADS of time! SUPERB!
    Last edited by robbo747; 07-15-2007 at 07:28 PM.

  16. #16
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Can you confirm the rules then

    If 15 mins e.g 08:15 should 08:00 be filled all blank etc for 30 mins and 45 mins. Same for finish time

    A few examples with expected results

    VBA Noob

  17. #17
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    hey, just got in from work

    i think ideally it would be great to divide each cell into 4 quarter of 15 mins each (these quarters are hidden so the cell appears as one box instead of four)

    But if there was a start time of 08:30, then half of the 08:00 box is filled.

    I'll do an example and post it on here in about 10 mins

    thanks

  18. #18
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    here you go, hope you can understand what i mean?

    is this going to cause a lot of extra work?
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    just noticed as well, when there is a finish time of 21:00, that box turns white, when in fact they finished at 21:00 and so are no longer here.

    think we will have to go back to 08:00 - 09:00, 09:00 - 10:00 boxes etc.

  20. #20
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See if this works for you

    VBA Noob
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    hey thanks for that

    yes that works fine

    - no need for the 5:00 time
    - boxes could be slightly smaller - I need it to idealy fill most of an A4 landscape page, so the less 'long' the better - I have women who need reading glasses lol! (i.e pretty much the same dimensions as in my original draft)
    - don't need 'cover' for every 15 minutes, every half hour will suffice
    - need 25 rows for staff names (to allow for Xmas time when we get extra staff)
    - can you apply this to my original template/design?

    so are you up for it?

    just for my own ihnformation could you link me to a help or tutorial page that explains the processes and fuctions you are using? thanks
    Last edited by robbo747; 07-17-2007 at 05:06 PM.

  22. #22
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    How about you adapt your original sheet and I will do the rest

    VBA Noob

  23. #23
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    sounds good to me!


    i'll be back my friend!

  24. #24
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    here you go....

    I have been thinking about the use of toner in our printers - would it be possible to replace the black shading in with a LIGHT GREY (GRAY)? That way we would use less toner.

    The drop down menu time list only include times up to 22:00, can this be extended to 23:00 please?

    Oh damn, I just realised I havent copied and pasted the new template to the MON. TUE. WED. THU. FRI or SAT. tabs! Will that be a problem!?

    PLUS - can you please not include/hide the 'total shift time' column - this will save space! (haven't included it on my new template)

    GOOD LUCK I CAN'T WAIT TO SEE THE RESULTS!
    Attached Files Attached Files

  25. #25
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hopefully this is what your after

    VBA Noob
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    yes thats perfect!

    ok so one more thing lol

    the cover at the top where there is one box every hour empty - can I have the cover every half hour instead?
    so for example the 9:00 box says 4 4, instead of _ 4

    thanks

    or maybe if its a quick fix and you could tell me how - I could amend it?

  27. #27
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try this

    Starts at 06:15 every 30 mins

    VBA Noob
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    07-15-2007
    Posts
    32
    LOL VERSION 7!

    THATS PERFECT THANKS!

    Big up VBA Noob - this guy is a superstar!

+ 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