none
Macro for a pop up message if resource has a parallel task RRS feed

  • Question

  • Dear All,

    I asked this before in another thread:

    http://social.technet.microsoft.com/Forums/en-US/projectprofessional2010general/thread/84c9e718-51b3-466b-a351-fabd86aa2f02/

    I want to create a macro that create either, a msgbox with a hint or a column with a hint: that a resource is working on a parallel task at the moment.

    For example: I have a schedule (in MS-Project 2003) and if I move a task because of some technical reasons from one week to another, the macro should check after moving the task, if the resource on that task is also invited to another task at the same time (must not be overloaded, even if he works on a parallel task, even if that is only for 1 hour).

    The hint could be something like: "Be careful! Resource XY is also working at that time on task 41".

    Is that in any way possible to do something like that?

    I am very happy about any kind of help.

    Best regards,

    Toby

    Friday, November 30, 2012 7:48 AM

Answers

  • I thought stepping throught the resources would be more efficient than tasks. The following code populates the task's text1 column with a comma seperated list of tasks' unique IDs which contain clashing assignments. You could run it on demand to get the ones that are clashing. Rectify and rerun until the text1 column appears empty.

    Option Explicit
    Sub ChechAssignmentOverlaps()
    Dim r As Resource
    Dim t As task
    Dim i_AssCount As Integer
    Dim a1 As Integer
    Dim a2 As Integer
    For Each t In activeProject.Tasks
        t.Text1 = ""
    Next t
    For Each r In activeProject.Resources
    'if the resource has only one (or none) assignment, then there can't be a clash, so skip to the next resource
    If r.Assignments.count < 2 Then GoTo NextResource
        i_AssCount = r.Assignments.count
        
        'select the subject assignment
        For a1 = 1 To i_AssCount
            
            'select the object assignment (disregarding the subject and the previous subject assignments)
            For a2 = a1 + 1 To i_AssCount
                
                'check if the assignment start date of the subject is within the
                'range of the object assignment start/finish dates
                If r.Assignments(a1).Start < r.Assignments(a2).finish Then
                    If r.Assignments(a1).Start >= r.Assignments(a2).Start Then
                        
                        'if a clash is found, update the task.text1 field
                        r.Assignments(a1).task.Text1 = r.Assignments(a1).task.Text1 & r.Assignments(a2).task.uniqueId & ", "
                        r.Assignments(a2).task.Text1 = r.Assignments(a2).task.Text1 & r.Assignments(a1).task.uniqueId & ", "
                        
                        'and skip to the next object assignment
                        '(no need to check for finish date clash)
                        GoTo NextA2
                    End If
                End If
                
                'Check if the assignment finish date of the subject is within
                'the range of the start/finish dates
                If r.Assignments(a1).finish < r.Assignments(a2).finish Then
                    If r.Assignments(a1).finish > r.Assignments(a2).Start Then
                        
                        'if a clash is found, update the task.text1 field
                        r.Assignments(a1).task.Text1 = r.Assignments(a1).task.Text1 & r.Assignments(a2).task.uniqueId & ", "
                        r.Assignments(a2).task.Text1 = r.Assignments(a2).task.Text1 & r.Assignments(a1).task.uniqueId & ", "
                    End If
                End If
                
    NextA2:
            Next a2
            
        Next a1
    NextResource:
    Next r
    End Sub

    Hope this helps,

    Andrew


    • Edited by Andrew Simpson Tuesday, December 4, 2012 10:12 AM
    • Marked as answer by der_toby Tuesday, December 4, 2012 12:33 PM
    Tuesday, December 4, 2012 10:11 AM

All replies

  • Hi Toby,

    In the other forum I read the comment "This will take a little bit of effort"

    Well, the reason why up to now nobody has come forward to help you is that "a little bit" is to be interpreted as "quite a bit". There are several questions to be answered:

    - How to identify the task or resource this will check?

    - What if there are multiple resources on a task?

    - Which timeframe has to be checked?

    - What is the step you need for the checking? Week? Day? Hour? Minute?

    - I suppose the user will have to fire the macro - if you want it event-driven it's much harder to code.

    You obviously have something in mind but before I propose any snippet or even approach these questions have to be cleared.

    Greetings,

    Friday, November 30, 2012 8:38 AM
    Moderator
  • Jan's right - I did take a look when it was on the other forum to see if I could quickly pull something together, but it turns out the events triggered for new assignments are a bit of a pain to work with, so I gave up as I wasn't able to meet your requirement (I'm not even convinced its feasible).

    Could possibly do a report which loops through all resources to see if their assignments overlap, which the user could trigger on demand, but getting a pop-up after the creation/amendment of a assignment could be problematic/impossible.

    Sorry I can't help more,

    Andrew

    Friday, November 30, 2012 10:08 AM
  • Hi Jan, Hi Andrew,

    for me it is definitely enough if I have a column that shows me that there is an overlap of a resource with another task. So, there must not be a pop up, and it must not be emidiatly after editing a task. It would be also enough, that I have to press a button for running that macro.

    In my case, there is only one resource each task.

    The Timefram should be hours.

    May that can help us be getting a solution.

    I am also trying by myself, but it takes time and I am not sure if the way I am thinking about is correct.

    Best regards,

    Tobias

    Friday, November 30, 2012 3:14 PM
  • My first idea was something like that:

    Sub Ressourcentask()

    j = ActiveProject.Tasks.Count

    For i = 1 To j
        a = ActiveProject.Tasks(i).ResourceNames
        Anfang1 = ActiveProject.Tasks(i).Start
        Ende1 = ActiveProject.Tasks(i).Finish
        
            For Z = i + 1 To j
                l = ActiveProject.Tasks(Z).ResourceNames
                Anfang2 = ActiveProject.Tasks(Z).Start
                Ende2 = ActiveProject.Tasks(Z).Finish
                
                If Anfang2 >= Anfang1 And Anfang2 <= Ende1 Then
                    f = 1
                End If
                
                If Ende2 >= Anfang1 And Ende2 <= Ende1 Then
                    f = 1
                End If
                
                If Anfang2 >= Anfang1 And Ende2 <= Ende1 Then
                    f = 1
                End If
                
                If l = a And f = 1 Then
                    ActiveProject.Tasks(i).Text10 = "Task " & Z
                    'MsgBox ("Ressource " & l & " ist auch noch zu Task Nr " & Z & " zugeordnet")
                End If
            Next Z

    Next i

    End Sub

    But this is not completely and is also not 100% correct.
    What are you all thinking?

    Best regards,
    Tobias
    Friday, November 30, 2012 4:01 PM
  • Without VBA, have a split screen with the Resource Usage View in the lower half. Then after moving a task, select it and all assignments for all resources on the task appear in the lower pane. Check for over-allocation.

    With VBA I would use timescaledata to look at daily work for the resource over all days of the assignment. If any day has more than Max Units allows then use msgbox to say likely over-allocation and then it's up to PM to view the resource usage to find out why.

    Finding the other resources can be done but its more work. As said before, it will take a bit of effort but it can be done. There's no point posting possible code because there are a host of variations that may or may not work with your scenario.

    The code will be a little complex, but not difficult (nothing not in my book for example).


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, November 30, 2012 10:14 PM
    Moderator
  • I thought stepping throught the resources would be more efficient than tasks. The following code populates the task's text1 column with a comma seperated list of tasks' unique IDs which contain clashing assignments. You could run it on demand to get the ones that are clashing. Rectify and rerun until the text1 column appears empty.

    Option Explicit
    Sub ChechAssignmentOverlaps()
    Dim r As Resource
    Dim t As task
    Dim i_AssCount As Integer
    Dim a1 As Integer
    Dim a2 As Integer
    For Each t In activeProject.Tasks
        t.Text1 = ""
    Next t
    For Each r In activeProject.Resources
    'if the resource has only one (or none) assignment, then there can't be a clash, so skip to the next resource
    If r.Assignments.count < 2 Then GoTo NextResource
        i_AssCount = r.Assignments.count
        
        'select the subject assignment
        For a1 = 1 To i_AssCount
            
            'select the object assignment (disregarding the subject and the previous subject assignments)
            For a2 = a1 + 1 To i_AssCount
                
                'check if the assignment start date of the subject is within the
                'range of the object assignment start/finish dates
                If r.Assignments(a1).Start < r.Assignments(a2).finish Then
                    If r.Assignments(a1).Start >= r.Assignments(a2).Start Then
                        
                        'if a clash is found, update the task.text1 field
                        r.Assignments(a1).task.Text1 = r.Assignments(a1).task.Text1 & r.Assignments(a2).task.uniqueId & ", "
                        r.Assignments(a2).task.Text1 = r.Assignments(a2).task.Text1 & r.Assignments(a1).task.uniqueId & ", "
                        
                        'and skip to the next object assignment
                        '(no need to check for finish date clash)
                        GoTo NextA2
                    End If
                End If
                
                'Check if the assignment finish date of the subject is within
                'the range of the start/finish dates
                If r.Assignments(a1).finish < r.Assignments(a2).finish Then
                    If r.Assignments(a1).finish > r.Assignments(a2).Start Then
                        
                        'if a clash is found, update the task.text1 field
                        r.Assignments(a1).task.Text1 = r.Assignments(a1).task.Text1 & r.Assignments(a2).task.uniqueId & ", "
                        r.Assignments(a2).task.Text1 = r.Assignments(a2).task.Text1 & r.Assignments(a1).task.uniqueId & ", "
                    End If
                End If
                
    NextA2:
            Next a2
            
        Next a1
    NextResource:
    Next r
    End Sub

    Hope this helps,

    Andrew


    • Edited by Andrew Simpson Tuesday, December 4, 2012 10:12 AM
    • Marked as answer by der_toby Tuesday, December 4, 2012 12:33 PM
    Tuesday, December 4, 2012 10:11 AM
  • Andrew,

    that is exactly what I want. Thanks for you support!

    Best regards,

    Tobias

    Tuesday, December 4, 2012 12:34 PM