none
Macro on row insertion RRS feed

  • Question

  • I want to automatically fill up some of the fields, with certain values, everytime I insert a row in MS Project. i.e., insert -> New Task. How to write a macro for it? How to insert an icon in the tool bar so that if I click it the macro is run to insert this special row

    Thanks

    Ram

    Saturday, January 18, 2014 2:15 PM

All replies

  • Ram,

    A couple of questions first. Which version of Project are you using? Is it updated with the latest service packs? Note: it may also be advisable to install certain cumulative updates when working with VBA. The exact ones depend on the version.

    With regard to your question, are you manually inserting the new task or is that something you want to do with the macro? If with a macro, you will need to tell us the criteria for determining where the new task should be inserted. What are the data fields you wish to insert pre-determined values?

    Setting up the macro to run via a toolbar button is fairly straightforward but it does require a few steps. It's easier if the macro is created first, then it can be "tied" to the new toolbar/ribbon button.

    John

    Saturday, January 18, 2014 7:11 PM
  • Hi,

    You will need Application events. Now to implement them is not easy and to be honest, I always take Rod Gill's book when I need it. Once you can write Application events, the name of the event is ProjectBeforeTaskNew.

    Greetings,

    Saturday, January 18, 2014 7:34 PM
    Moderator
  • Hello,

    If you take the following code sample and paste it into the "ThisProject" module (then restart project), it will set the text1 and number1 modules everytime you insert a new task:

    Option Explicit
    
    Public WithEvents MSPApp As Application
    
    Private Sub MSPApp_ProjectTaskNew(ByVal pj As Project, ByVal ID As Long)
    
      Dim tsk As Task
      Set tsk = pj.Tasks(ID)
    
        With tsk
            .Text1 = "Test"
            .Number1 = 1234
        End With
    
    End Sub
    
    Private Sub Project_Activate(ByVal pj As Project)
        If MSPApp Is Nothing Then
            Set MSPApp = MSProject.Application
        End If
    End Sub
    
    Private Sub Project_Open(ByVal pj As Project)
        If MSPApp Is Nothing Then
            Set MSPApp = MSProject.Application
        End If
    End Sub

    I hope that gets you started,
    Andrew


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    Saturday, January 18, 2014 9:34 PM
  • Project 2007 SP3

    New task, with predefined fields, should be inserted manually when I click an appropriate toolbar/ ribbon button. i.e) run macro at this instance

    Thanks

    Ram 

    Sunday, January 19, 2014 12:21 PM
  • Ram,

    Thanks for the info about which version. However, your statement about manual insertion being done with a toolbar, (the ribbon was introduced in Project 2010), button that initiates a macro is contradictory. If the task is inserted via a macro it is not a manual operation. When I said manual I meant the user manually selects a location for the new task, and then selects, Insert/New Task from the menu

    To summarize the three responses (myself, Jan and Andrew).

    I assumed the user would insert the new task manually as described above. Then the user would run a toolbar initiated macro that would populate the desired fields.

    Jan's approach uses what I assume would be an Event based macro using the TaskChange Event. Again the task would most likely be inserted manually by the user but as soon as the task was inserted the event macro would automatically fire and populate the desired fields.

    Andrew's posted code basically implements Jan's approach but unfortunately it does not work for Project 2007. It could work with Project 2007 but there is more to it (i.e. complexity), as Jan notes in his response. I suspect Andrew wrote it assuming you were using Project 2010 or later.

    So, bottom line. If you want an automated approach that enters the desired data into the desired fields as soon as the user adds a new task, then you will need an event based macro and for Project 2007 setting that up is a little complex. I've done it and I'm sure Jan has done it many times but I haven't done it in a few years and would need to bone up on the full process. However if you would be satisfied with inserting a new task and then using a toolbar button initiated macro to populate the desired fields after the task is in place, then a fairly simple macro can be developed depending on which fields and what data. We would be happy to help you with that.

    John


    Sunday, January 19, 2014 5:57 PM
  • Hi,

    First I would like to thank John, Jan and Andrew for such wonderful responses. I appreciate it.

    I agree with the suggested approach to a) insert new task manually at the desired location b) populate the fields with a macro triggered by a toolbar button. This will work for me. I request some sample code and the procedure to insert a toolbar button.

    I would like to capture some of the field data in the new task thru forms - commandbutton, dropdown etc. Is it possible to include this requirement as well in the sample code. I know the details to create forms and the associated code for controls.

    Once again thank you very much

    Ram

    Monday, January 20, 2014 8:34 AM
  • You're asking rather a lot there. Given the amount of code on the internet, how about you try creating what you can and ask for help when you get stuck?

    As a tip, try recording a macro of what you want for the basics, but you will need to create the UserForm manually.


    <p>Rod Gill</p> <p><a href="http://www.project-systems.co.nz/project-vba-book/index.html/">The one and only Project VBA Book</a> <a href="http://www.project-systems.co.nz/"></p> <p>Rod Gill Project Management</a></p>

    Monday, January 20, 2014 9:17 AM
    Moderator
  • Ram,

    You're welcome and thanks for the feedback.

    The first part, the basic macro to update the fields is easy, and for that matter, setting up a toolbar button to run it is also easy in Project 2007, the latter gets a little bit more involved in Project 2010 and later. However the exact structure of the basic macro depends on a couple of things. Will more than one task be added before the fields are updated or are tasks being added one at a time? For example, if more than one task is added, the code could contain a filter to isolate the new tasks using the Created field (assuming the macro is run the same day as the tasks are added). If one one task at a time is added, the Find Method, or a filter, might be used to locate the task for update.

    As Rod notes in his response setting up the userform with command buttons is a little more involved, but then from the last sentence in your 3rd paragraph, its sounds like you already know how to do that.

    John

    Monday, January 20, 2014 3:47 PM