Change Roster values

Hi All,

I spend hours a week manually changing the default roster when employee's do any overtime or call in sick.

I normally have a roster for the whole year send this to staff on weekly basis with what they are doing. When ever someone is sick or does any overtime, i find the date it occured on and then change it accordialy manually.

Is there a way to do it automatically for me? Any formula to find a date from the roster and change it if any changes do occur?

The end result I am looking for is that the main roster does not change unless the second sheet has data in it. Do I make sense? I have attached a excel sheet. One sheet has the roster for april and second sheet with dates/reason for roster change.

I appreciate if someone can help me.

Hi Bloodywog
Place this code in a General Module...let me know of issues.

Option Explicit
Sub Test()
    Dim myName As String
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim myDate As Date
    Dim myCol As Range
    Dim myRow As Range
    Dim Rng As Range
    Dim cel As Range
    Dim LR As Long
    
    Set ws1 = Sheets("RosterApril")
    Set ws2 = Sheets("Overtime&Sick")
    With ws2
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        Set Rng = .Range("A2:A" & LR)
    End With

    For Each cel In Rng
        If cel.Offset(0, 3).Value <> "X" Then
            With ws1
                myName = cel.Value
                myDate = Format(cel.Offset(0, 1).Value, "m/dd/yyyy")
                Set myRow = ws1.Columns(1).Find(myName)
                Set myCol = ws1.Rows(2).Find(myDate)
                Intersect(myRow.EntireRow, myCol.EntireColumn) = cel.Offset(0, 2).Value
                cel.Offset(0, 3).Value = "X"
            End With
        End If
    Next cel
End Sub

Hi Bloodywog
Try the code in the attached. Please note, there's no error checking.

Hey.

At the moment everything working great. I can't thank you enough! You have saved me hours of head aches and save tones of time. I really appreciate it!

Thanks heaps mate.

Hi Bloodywog
Something for you to ponder...how would you envision this working

Additional macro to Clear the "X" or to under changes if you make a mistake

Clearing ALL the X's would be quite simple; undoing changes to Roster2012 would be impossible unless O is the default since I have no idea what it was before the change. If you wish to change individual records let me know how you'd like it to work.

I'll need to look at it...my time will be limited this weekend due to the Holiday. I'll get back to you.

Hi Bloodywog
The code in the attached has been modified to accommodate this

Having the ability to add more sheets without any errors. Maybe define only the sheets i require to update the roster? E.g. Overtime, Sick Leave, Personal Leave, Maternity Leave, Shift Swaps & Annual Leave sheets

It also includes stand alone procedures and a UserForm to do this

change individual records

You can use this feature or lose it...your option.
Let me know of issues.

Hi Bloodywog
Based on this

I be reading more into it tonight

I'm going to wait a few days until you've told me that what you have is what you want. We're already well beyond the scope of your original issue with the enhancements we've made. Mind you, I don't mind helping with the enhancements but we've been working on a moving target. Settle it down to where you want it to be...let me know when you've done that then post your file with the final structure...I'll look at it.

Hi

Sorry for making you work on a moving target.

This will be the last post regarding my issue.

I have highlighted in the sheet in yellow of changes that I require.
• I have insert a engine to work out roster automatically
• Menu sheet – The modify form is a Calender Icon to make a calendar pop up. I need the
code to insert it in date field.
• RecLeave sheet - I have from and to dates to insert in the roster. On the side I have the planner to show the dates
• Overtime - Can we these fields be updated from "OTSummary" sheet? So it continues down and updates the roster.
• CleaningRoster – Employee also have cleaning roster. Normally 2 employees clean but not always.
• OTSummary – I need to record all OT worked. When it’s setup, I like “Overtime” sheet to ref to this sheet.

I really do appreciate your help and time. I have learnt from your postings.

Hi Bloodywog
You'll need to beat this up. I believe these issues have been addressed

I have highlighted in the sheet in yellow of changes that I require.
• I have insert a engine to work out roster automatically
• Menu sheet – The modify form is a Calender Icon to make a calendar pop up. I need the
code to insert it in date field.
• RecLeave sheet - I have from and to dates to insert in the roster. On the side I have the planner to show the dates
• Overtime - Can we these fields be updated from "OTSummary" sheet? So it continues down and updates the roster.
• CleaningRoster – Employee also have cleaning roster. Normally 2 employees clean but not always.
• OTSummary – I need to record all OT worked. When it’s setup, I like “Overtime” sheet to ref to this sheet.

You've got a lot going on here...I'd not expect all to be to your liking the first time around...so, let me know of issues.

Hi Bloodywog
Well, for starters, I'm going to consider installing a different date picker.
Regarding this

2.I need to modify some of the employees on the roster. Do I change it from the engine or Roster2012 sheets?

The way your spreadsheets are set up, you'll need to modify on both. I'll think about it.
Regarding this

3.In the OTSummary Sheet, when you add a new row the time cannot be adjusted. They default to 00:00.

On my machine that does not happen. Does this happen on the workbook I uploaded or on another version of the workbook?

Hi

It seems datepicker is not installed on most computers. I noticed that there were two versions of Excel, 2007 and 2010 on the pc's. Does this matter?

In regards to the OTSummary sheet, this happened on the latest workbook you uploaded.

Hi Bloodywog
I've inserted into the code a Date Picker that's not dependent on the Microsoft Version needing to be installed on the User's computer. I believe you'll be satisfied with it.
Regarding Time Entry on OTSummary...enter the Time WITHOUT THE COLON and let me know what happens.
Regarding modifying Employee Names...are you saying MODIFY (as in change) or are you also needing to ADD names?

I use Excel 2007...I don't believe there will be an issue with Excel 2010...but, I honestly don't know...guess we'll find out

Hi Bloodywog
I've not yet posted the workbook that has this

I've inserted into the code a Date Picker that's not dependent on the Microsoft Version

Wish I had as I've got a problem with the file ...I'll get it worked out.

There may well be a rather simple fix for this

modifying Employee names

Regarding this

It be nice if you added some notes (')to help me understand it better

I'll be more that happy to do that...once we know it's working...don't want to have to change code AND notes...the notes will come...when the code works. I need to spend a bit of time to find the issue with my file...it's grown by about 400 KB and I don't know why...could be a corrupted worksheet...don't know. I'll post the new file as soon as I can get the issue resolved.

Hi Bloodywog
Removing the Overtime sheet will have negligible impact on file size and processing speed. Your file is large and processing speed diminished due to the hundreds of formulas and formatting (both conditional and otherwise) contained in your file. The process has to work for you, not for me. I'll work on the existing file for a bit today. Let me know how you wish to proceed.

Hi Bloodywog
While you're pondering the future of this project play with the attached. A new Date Picker is incorporated that does not rely on the presence of the Microsoft Calendar Control. Also notice the Formulas in Roster2012 range A3:A14 which address this issue

modifying Employee names

Your Annual Leave Planner appears to not update properly. I've not worked on this at all so you'll need to look at it.
Let me know how you wish to proceed.

jaslake

DatePicker is working great.

There was one problem, when I press "update roster", is says Run-time error '91': object variable or With block variable not set. It's on Ln 57. Then when I checked the roster, I do see "RL" changes but nothing else (Al, SWAP etc). I looked through your code and changed all the date formats "mm/dd/yyyy" to "dd/mm/yy" and it worked fine. Is this because my computer set to Australia? I double checked and put it to US and it worked. It appears that it's sensitive to formats.

In regards to

Annual Leave Planner appears to not update properly

I looked at it and rectified it.

I understand that due to the hundreds of formulas and formatting there will be some delays. I will not worry about

Removing the Overtime sheet

I need to add 2 more columns in RecLeave sheet. Which line do I change the (row,column)?

So Guru jaslake, it's almost done. I guess we can finish up the codes before were done.

Hi Bloodywog
Attached is a Screen Shot of results I get screen shot.jpg
So, no, I don't get the error message your getting; I get the results expected.
Make sure you clear all X's or the records will be passed by. If you can't get it resolved, attach the offending file...I'll look at it.

I don't know what Line 57 is...show that part of the code with the line of code underlined (I'm color blind so colors won't work).

Hi Bloodywog
Where will you do this?

I need to add 2 more columns in RecLeave sheet

Where will Date From, Date To, Shift & Posted Roster end up? Each shifted two columns to the right?

I guess I assumed Name wll stay in the first column...True?

I need the columns to be:


Name / Time From / Start Date / Time To / End Date / Relief / Posted OT

I can say now once that is done, we can wrap this up. You have done so much already.

Then we can do the notes in the code

I'll be more that happy to do that...once we know it's working

Hi Bloodywog
Currently the code only evaluates the Start Date and the End Date; I assume you want the Start Time and End Time for posterity or you'll use it as you see fit. True?

Hi Guru jaslake

It all make sense to me except for the cleaning roster part. I played around with it, adding columns adjusting it accordingly but no luck. Can you help me adjust cleaning roster sheet to have the following headers:

Date / Start / Finish/ Location / Employee / Employee / Yes or No / Shift / Posted Roster

            Case "CleaningRoster"

                'CleaningRoster follows the same logic as above but the Heading Layout
                'is different so requires different handling.  If it does not make sense
                'let me know
               Set rng = .Range("A3:A" & LR)
                For Each cel In rng
                    If cel.Offset(0, 4).Value <> "X" Then
                        sYear = Year(cel.Offset(0, 2).Value)
                        sMonth = WorksheetFunction.Text(cel.Offset(0, 2).Text, "mmm")
                        With ws1
                            Set myRng = .Range(sMonth & "_" & sYear)
                            myName = cel.Value
                            myName2 = cel.Offset(0, 1).Value
                            myDate = Format(cel.Offset(0, 2).Value, "dd/mm/yy")
                            Set myCol = myRng.Rows(1).Find _
                                    (what:=DateValue(myDate), LookIn:=xlFormulas)

                            On Error Resume Next
                            Set myRow = .Columns(1).Find(what:=myName, After:=.Cells(myRng.Rows(1).Row, 1), LookIn:=xlValues, LookAt:= _
                                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                                    , SearchFormat:=False)
                            On Error GoTo 0

                            On Error Resume Next
                            Set myRow2 = .Columns(1).Find(what:=myName2, After:=.Cells(myRng.Rows(1).Row, 1), LookIn:=xlValues, LookAt:= _
                                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                                    , SearchFormat:=False)
                            On Error GoTo 0
                            Intersect(myRow.EntireRow, myCol.EntireColumn) = cel.Offset(0, 3).Value
                            On Error Resume Next
                            Intersect(myRow2.EntireRow, myCol.EntireColumn) = cel.Offset(0, 3).Value
                            On Error GoTo 0
                            cel.Offset(0, 4).Value = "X"
                        End With
                    End If
                Next cel
            End Select
        End With
    Next vWks
End Sub

Secondly, I was checking the OTSummary sheet code, the "reason" column adds it to the roster. This is not correct because then 2 employees will be OT when suppose to be one. The employee that works the OT is in 1-2 column.

Hi Bloodywog
Sorry...didn't get an email on your edited post #37. One apparently does NOT get notifications on edits. Are Start and Finish times of the day?

Hi Bloodywog
The code in the attached has been modified for this in Cleaning Roster

Date / Start / Finish/ Location / Employee / Employee / Yes or No / Shift / Posted Roster

and this in Overtime

The employee that works the OT is in 1-2 column.

I ran the procedures several times and all appears well to me. As before, you'll need to modify Date Formats as required. Let me know of issues.

Hi Bloodywog
Ha...caught me again!!! In Module UpDate_Roster in the Cleaning Roster section (down toward the bottom) change these two lines of code from

On Error GoTo 0
                            Intersect(myRow.EntireRow, myCol.EntireColumn) = cel.Offset(0, 3).Value 'change this 3 to 7<----
                            On Error Resume Next
                            Intersect(myRow2.EntireRow, myCol.EntireColumn) = cel.Offset(0, 3).Value 'change this 3 to 7<----

                            On Error GoTo 0

to

On Error GoTo 0
                            Intersect(myRow.EntireRow, myCol.EntireColumn) = cel.Offset(0, 7).Value 'to this
                            On Error Resume Next
                            Intersect(myRow2.EntireRow, myCol.EntireColumn) = cel.Offset(0, 7).Value 'to this
                            On Error GoTo 0

Hi Bloodywog
Regarding this

Can I call the same Calender for separate forms

Yes

Regarding this

Can I just copy and paste this code

If your UserForm models the Modify Form, I should think so...may take a bit of tweaking but the code would be substantially the same.

However, if you're ADDING records to the Target Worksheet, while the Calendar Control will be the same and can be called in the same way, you'll need to develop the code to post the ADDED data to the Target worksheet.

Good luck!

Hi ohlalayeah

See this rule...act accordingly
2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread

Hi Bloodywog

I know of no rule that prohibits this

I have few more questions. I didn't see any ruling regarding this

However, this is a rather old Thread. I'd recommend you start a new Thread with your questions...you'll get much more Member interest. Add a link to this Thread if relevant.

Or, if related to this Thread...ask away.