none
How to programmatically change the color of a cell in the Task Usage view (C # or VBA)? RRS feed

  • Question

  • MS Project 2016
    Questions.
    1. How to programmatically change the color of a cell in the Task Usage view (C # or VBA)?
    2. Where in the documentation of the MS Project object model is the description of the "Using Tasks" view (events, properties, methods)?

    Sunday, December 1, 2019 9:56 PM

All replies

  • prog1003,

    You'll find the Project Object Model descriptions under Developer > Code group > Visual Basic > Object Browser icon (F2). Or, when in the Visual Basic editor window, you can click on the Help menu for the Project object model.

    However, unless the object model was expanded in the latest versions of Project, it is not possible to change the background color of cells in the timescaled data (right side of the Task Usage view). You can only change cell background color in the descriptive data (left side of the view).

    If you really need to highlight timescaled data cells, I suggest you use VBA to export the data to Excel which has much more formatting flexibility.

    Hope this helps.

    John

    Sunday, December 1, 2019 11:16 PM
  • prog1003,

    You'll find the Project Object Model descriptions under Developer > Code group > Visual Basic > Object Browser icon (F2). Or, when in the Visual Basic editor window, you can click on the Help menu for the Project object model.

    However, unless the object model was expanded in the latest versions of Project, it is not possible to change the background color of cells in the timescaled data (right side of the Task Usage view). You can only change cell background color in the descriptive data (left side of the view).

    If you really need to highlight timescaled data cells, I suggest you use VBA to export the data to Excel which has much more formatting flexibility.

    Hope this helps.

    John

    @John - Project

    I am enclosing links to object model documentation.
    Could you help me find the object that the sheet on which the selected cells belong (see the picture of the question)?
    https://docs.microsoft.com/ru-ru/office/vba/api/project.object
    https://docs.microsoft.com/ru-ru/office/vba/project/concepts/project-object-model

    Sunday, December 1, 2019 11:35 PM
  • prog1003,

    First of all, what exactly are you trying to do?

    The screen shots you provided are very small and difficult to read (looks like Russian) but when I zoom in, it looks like the "selected cells" are individual timescale values for a given task. Since the task appears to be a single value one-day task, the timescale value is the same as the value on the left side of the view. I'm guessing the value is Work so for that task it is represented by:

    ActiveProject.Tasks(3).Work

    John

    Monday, December 2, 2019 4:08 PM
  • @John - Project

    I want to do something like "conditional formatting"
    Example:
      - if the cell value is "-100", then the fill color of the cell turns red.

      Used by:
    - MS Project 2016 in Russian.

    If I correctly translated the field name, then the field name that appears in the right part of  - `Cost`



    2019-12-05-00-52-18
    • Edited by prog1003 Wednesday, December 4, 2019 10:17 PM
    Wednesday, December 4, 2019 10:16 PM
  • prog1003,

    Can't help you with the Russian part but I do know that VBA language itself is in English but any reference to Project fields must be in Russian. I ran into that a few years back while trying to help another Russian with some VBA.

    As far as conditional formatting, in order to do what you want, you will need to write code similar to what Ben showed in his response to your post on the Project customization and programming forum (https://social.technet.microsoft.com/Forums/en-US/f92d6d52-6a22-41e4-b5e1-7fad296fb446/how-to-track-the-event-quotchange-of-position-of-the-taskquot-in-the-gantt-chart-c-vba?forum=project2010custprog).

    Screen formatting is controlled by Text Styles and Project is rather limited in what it will do. I suggest you trigger the Project Change Event on changes to the Cost field and then use that to change the state of the Marked field (a "yes" or "no" field). The Marked field is the only one you can use to uniquely identify individual tasks that need custom formatting.

    However, a caution with using the Change Event. It will fire EVERY time a change to Project data occurs, regardless of whether that change is related to the field of interest or not and that can slow things down noticeably if your file is large or you make a lot of changes. You will need to put conditions in the macro such that it only acts upon changes to the desired field, the Event will still fire on all data changes, but at least it will be "short-circuited" (i.e. quick exit).

    Hope this helps.

    John

    Thursday, December 5, 2019 5:00 PM