none
Find a selected row number in the Gantt chart that is BELOW the lowest row that holds a task, i.e. row_number > maximum task.ID RRS feed

  • Question

  • In my VBA subroutine, I need to find a way to determine which row in the text part of the Gantt chart of MS Project has been clicked by the user. This is easy if the row is one of those rows that already hold a task of my project, i.e. the row number where the user clicks is an integer that is not greater than the highest task.ID in my project ( ActiveSelection.Tasks(1)). However, I want to create a subroutine that detects which row the user clicks when the row is actually below the active area, i.e. the number of the clicked row IS higher than the greatest task.ID; If I use ActiveSelection.Tasks() for this, I get a run-time error. How can this be accomplished?
    Wednesday, November 16, 2016 5:23 PM

Answers

  • John,

    thanks for the elaboration. The simple test project I outlined in my response was just for showing the issue, since I did not have the chance to include an image in my reply. Yes, for those blank task rows that occur between other active task rows, the ActiveSelection.tasks(1) method works just fine. Only in case I choose to place more tasks in an area at the bottom, this turns out to be a problem. From all I learned from you it seems I will have to change my procedure, here. The current procedure is to use a subroutine to find out which row is clicked (activated), then open a userform that is asking for the project name, creating a summary taks with this name, then opening the userform again, now asking for all the tasks within that project, with some "smart" comboboxes that e.g. would take a "wwdd" as two weeks and two days as a duration, then ask for a resource and then for how much effort this resource would have to spend for the task (again with a predefined syntax to enter effort numbers more quickly), then insert all this at the position that was originally clicked on by the user. I have been using this procedure for some time. It helps me enter new projects and groups of tasks and the required resources much more efficiently than doing it all "by hand". The subroutine works just fine, with the one exception that the clicked row - as I explained - cannot be an empty row below the "active" area of the project. I guess what I will do is following your advice and get rid of empty rows altogether. Then, instead of trying to find which row was clicked, just always add a new project at the bottom, on the first empty line below all other projects. Then, I could just drag this new project to whatever location in the list seems suitable (trying to position most important project/subprojects towards the top of the list). By doing this, I can completely circumvent the problem that was the reason for starting this thread. I am aware of the possibility of using separate files and linking them together, but this makes it more complicated, so I guess I need to get more familiar with the tool before I'll have the courage to go down this path. Once again, thank you so much for your kind support. Microsoft tools and VBA are not always easy to use, so it is very helpful to get the chance to get help from experienced users!

    Rudiger

    • Marked as answer by Rudiger0815 Monday, November 21, 2016 1:59 PM
    Monday, November 21, 2016 1:59 PM

All replies

  • Rudinger0815,

    Glad to help but your description is a little confusing. You talk about a user clicking a task and then you talk about "my project", I don't understand what  that means. Are you working with a master project with inserted subprojects? A screen shot or a more detailed example would go a long way in me, (or anybody else), being able to help you.

    John

    Wednesday, November 16, 2016 7:52 PM
  • Hi John,

    for some reason I seem to be unable to include images in my reply ("Body text cannot contain images or links until we are able to verify your account" - which I did, but I still got the error message...). So one simple way to demonstrate my problem is like this:

    - set up a new project with 5 tasks (covering rows 1-5 in the text part of the Gantt chart, having task.ID 1-5)

    - move the mouse down by three rows and click. Now row number eight is selected.

    --> What I need to accomplish is find a way that a VBA subroutine can detect the number of this clicked row (8). Using "ActiveSelection.Tasks(1)" works only for finding selected rows for rows 1-5 in this example. It does not work for row 8, since MS Project only "knows" about rows 1-5, which hold some tasks, but rows 6 and higher are "unknown" because they don't hold a taks and thus cannot be part of the tasks.collection.

    Rudiger 

    Thursday, November 17, 2016 9:02 AM
  • Rudiger0815,

    If rows 6,7, & 8 are null rows (i.e. not task objects), then there is no way to detect when any of those cells are selected, at least using VBA. So, sorry, no can do.

    Just curious though, in what scenario would you need to know a user clicked in a blank area of the view?

    John

    Thursday, November 17, 2016 2:12 PM
  • Hi John,

    I understand. So I need to find a different approach. As of recently, I decided to use MS Project for managing all the ongoing projects and tasks in my group. I manage a group of six engineers (hardware, software, SWQA), and I need to have a tool for tracking progress with all the different ongoing tasks as well make sure people are not overloaded or idling. Therefore, it is not just one project that I maintain within one MS Project file, but rather a multitude of different projects, sometimes related to one another somehow, sometimes not. In order to maintain a minimum degree of visibility over this 'chaos', I decided to use null rows in combination with filtering and coloring. Also, I basically don't filter or enter new tasks/projects manually, since this is time-consuming, and I would always have to repeat the same steps over and over again. Instead, I wrote a number of functions and subroutines, linked to custom symbols in the task bar, that would do all this in a 'semi-automatic' way. Now that you confirmed that null rows are difficult to handle by VBA in MS Project, I will think about alternative ways to maintain good visibility and at the same time give me the overview over everything that I am looking for. Thanks for helping me out, this is highly appreciated!

    Rudiger

    Friday, November 18, 2016 7:19 AM
  • Rudiger0815,

    Thanks for the detailed explanation. The example you described previously did not have any tasks beyond the end of the first group (i.e. after task ID 5), so what you are describing now is a different structure.

    I have a few suggestions.

    First if your plans are all in one file but separated by blank task rows, you will be able to use the ActiveSelection.Tasks(1) to find out what row has been selected, as long as it's not one of the blank rows. If a blank row is selected, you could use some form of the On Error Resume Next statement.

    Second, I recommend you do not have blank rows between plans as they are really not necessary because the whole plan can easily be filtered or grouped to highlight/isolate certain areas of the overall file. But, if you still want blank plan separation rows the best way to handle those in VBA is with the following structure

    Sub test()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            'your code here
        End If
    Next t
    End Sub

    Third, if you have multiple plans, you might want to consider having each as a separate project file. Then when you need to view them all at once, create a static master by inserting each of the plans into a blank file. Be sure to un-check the "Link to project" option in the lower right corner of the Insert Project window. You could create a dynamic master by leaving the option checked but that opens up a whole realm of file corruption opportunities. And if you need to have links between tasks in different plans (i.e. external predecessors/successors), it gets even more interesting but with a heavy dose of discipline, it can be done with minimal risk or corruption.

    John

    Friday, November 18, 2016 2:14 PM
  • John,

    thanks for the elaboration. The simple test project I outlined in my response was just for showing the issue, since I did not have the chance to include an image in my reply. Yes, for those blank task rows that occur between other active task rows, the ActiveSelection.tasks(1) method works just fine. Only in case I choose to place more tasks in an area at the bottom, this turns out to be a problem. From all I learned from you it seems I will have to change my procedure, here. The current procedure is to use a subroutine to find out which row is clicked (activated), then open a userform that is asking for the project name, creating a summary taks with this name, then opening the userform again, now asking for all the tasks within that project, with some "smart" comboboxes that e.g. would take a "wwdd" as two weeks and two days as a duration, then ask for a resource and then for how much effort this resource would have to spend for the task (again with a predefined syntax to enter effort numbers more quickly), then insert all this at the position that was originally clicked on by the user. I have been using this procedure for some time. It helps me enter new projects and groups of tasks and the required resources much more efficiently than doing it all "by hand". The subroutine works just fine, with the one exception that the clicked row - as I explained - cannot be an empty row below the "active" area of the project. I guess what I will do is following your advice and get rid of empty rows altogether. Then, instead of trying to find which row was clicked, just always add a new project at the bottom, on the first empty line below all other projects. Then, I could just drag this new project to whatever location in the list seems suitable (trying to position most important project/subprojects towards the top of the list). By doing this, I can completely circumvent the problem that was the reason for starting this thread. I am aware of the possibility of using separate files and linking them together, but this makes it more complicated, so I guess I need to get more familiar with the tool before I'll have the courage to go down this path. Once again, thank you so much for your kind support. Microsoft tools and VBA are not always easy to use, so it is very helpful to get the chance to get help from experienced users!

    Rudiger

    • Marked as answer by Rudiger0815 Monday, November 21, 2016 1:59 PM
    Monday, November 21, 2016 1:59 PM
  • Rudiger,

    You're welcome and thanks for the feedback.

    John

    Monday, November 21, 2016 2:37 PM