Identify last task(s) edited for error checking RRS feed

  • Question

  • I have several error checking routines that are needed to prevent invalid entries being made in some fields of MS Project e.g. partial completion of a milestone, non forward completion of gate milestones in a stage-gate process model etc.  In order to apply the error checking, I need to know the task, or range of tasks, that were edited. This is simple if the menus are used to change one or more tasks because the ActiveSelection range can be used to check for valid changes.  But this does not work in cases where the user edits a cell, then clicks another random cell upon which the change is captured and processed by MS Project.  I have found no method by which the edited task or tasks can be determined. I have tried using the App_ProjectBeforeTaskChange event to capture the edited tasks which works but this event is not always triggered e.g. if links are added.  In Excel, there is SheetSelectionChange event that can be used to determine when a new cell is selected.  This could be used to track the last cell (or task in MS Project) selected, but there is no equivalent event in MS Project.  We could scan all tasks in the project every time a change is made using the Project_Change event but if the project has thousands of rows, this will slow down the interface.  Any suggestions would be much appreciated.
    Friday, January 13, 2017 8:44 PM

All replies

  • Pete,

    It sounds like your error checking is on-the-fly and of course that's the best time to catch an error but as you find, it just can't be done for all changes.

    In my mind the best way to guard, (notice I didn't say prevent), against errors is user training. Make sure all users know what is and isn't acceptable when editing/updating a project file. However, inadvertent errors may still creep in and an after-the-fact set of audit checks can ferret out the problems which can then be corrected. You can even make the audit checks available to the users so they can self-audit. We used this process in our environment and it proved very effective.

    My thoughts.


    Friday, January 13, 2017 11:25 PM
  • Many thanks for the reply John.  I was wondering if I could use a public variable that would keep track of the last task selected i.e. not the current selected task but the one before that.  This would solve my problem because I'd then know which task to check for errors.  This is possible in Excel using the SelectionChange trigger in ThisWorkbook module.  But I can't find anything equivalent in MS Project that would allow this.  I just want to be sure that there is no work around here before we give up on this.  The inability to have real time error checking is a major drawback.
    Wednesday, January 18, 2017 1:47 AM
  • Another thought.  The Undo List can be accessed to find out what action was taken on the last edited task, but I don't see any way to find out which task the action was applied to, and yet MS Project obviously has this information to support the Undo function. Is there any way to access this information through VBA?
    Wednesday, January 18, 2017 3:25 AM
  • Pete,

    Unfortunately there are many things you can do in Excel that you can't in Project. Even though an operation is possible (e.g. Undo), it doesn't mean the operation has been exposed for VBA. However, in doing some research on another issue I ran across a VBA method which you may be able to use. Take a look at the OpenUndoTransaction Method.

    Let me know if it is useful for you.


    Wednesday, January 18, 2017 3:27 PM
  • John - I already experimented with that function.  It appears to be useful for capturing changes made by code in the Undo list, allowing the user to undo those changes, but it doesn't appear to help in identifying the last task edited by the user.  Let me know if you have any other thoughts.
    Tuesday, January 24, 2017 4:39 PM
  • Pete,

    Okay, it was just something I happened to see. I don't have any other ideas.


    Tuesday, January 24, 2017 8:25 PM