none
VBA for MS Project, loop in Selected Range RRS feed

  • Question

  • Hello friendly experts!

    I've been having a wonderful time putting your combined advice through these threads to use.

    I've come a little un-stuck trying to adapt a macro I use for looping through the entire project on only the selected range.

    I'm concerned that perhaps this isn't possible. Dale Howard of MSP fame on the Microsoft forums alludes to this in his post here: http://answers.microsoft.com/en-us/office/forum/office_2003-project/when-i-highlight-and-sort-some-tasks-the-entire/f04d645a-a150-e011-8dfc-68b599b31bf5

    (Sorry, they won't let me link until my account is verified. Which I haven't figured out how to do!)

    So far I've discovered that i cannot "Dim Rng As Range" because "As Range" doesn't exist!!!

    This is what I have tried:

    Sub HighlightNightsSelected()
    
    Dim t As Task
    Dim SubProjId As Long
    Dim TskSub As Task
    
        For Each t In ActiveSelection.Tasks
    
            If Not t Is Nothing Then
                If t.Subproject <> "" Then
                    SubProjId = t.ID
                ElseIf Not t.Summary Then
                    SelectRow Row:=SubProjId + t.ID, rowrelative:=False
                    Select Case t.Calendar
                        Case "Night Shift"
                            Font32Ex CellColor:=12611584
                        Case "Standard"
                            Font32Ex CellColor:=13036780
                        Case "None"
                            Font32Ex CellColor:=-16777216
                    End Select
                End If
            End If
        Next t
        EditGoTo ID:=1
    End Sub
    

    Surely I'm missing a trick?


    • Edited by PhilipDay Thursday, September 8, 2016 9:54 AM Added the VB Code that I've tried
    • Moved by Chenchen LiModerator Friday, September 9, 2016 6:20 AM
    Thursday, September 8, 2016 9:01 AM

Answers

  • Ok, the problem is you have sub-projects. If you include the sub-project summary task in your selected tasks it works. If you don't, then the macro doesn't know that Task A is a linked sub-project task.

    Task A is Id1 in the sub-project, but id 1 as far as the VBA macro is concerned is Id 1 for the whole project, which is task 1 above.

    So interim solution is to include the sub-project summary tasks in your selection, otherwise you will need to process all outline levels above the selected tasks to level 1 to confim which project they belong to and adjust SubProjId accordingly..


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    • Marked as answer by PhilipDay Wednesday, September 14, 2016 8:09 AM
    Tuesday, September 13, 2016 11:13 PM

All replies

  • That code works? What isn't happening that you expect?

    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Thursday, September 8, 2016 8:26 PM
  • It doesn't only function in the range of cells which I select before I run it. I've tried a few permutations, but highlighting cells with my cursor (and shift or Ctrl) isn't seeming to work for me.

    Phil

    Friday, September 9, 2016 6:45 AM
  • Works perfectly for me, only processes teh selected tasks and ignores the rest.

    Are you sure you have the latest updates for Project installed.

    What version are you using?


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Friday, September 9, 2016 11:01 PM
  • Hi Rod,

    I'll check the updates again when I get back to work on Monday. But I think we're good on that front.

    I'm running Project 2013

    As always, Thanks for your help

    Phil

    Saturday, September 10, 2016 6:08 PM
  • Hi PhilipDay,

    Have you resolved this issue? If not, based on my testing, I am not able to reproduce this issue, so I also suggest that you could reinstall MS Project or test codes on an other machine.

    Thanks for your understanding.
    Monday, September 12, 2016 6:21 AM
  • Hello gents,

    I've checked my update status. I have updates enabled and am fully up to date after trying to run another update manually.

    I've just tried re-installing and still no joy on this one.

    Phil

    Monday, September 12, 2016 2:58 PM
  • So what's happening on your PC? What do you expect to happen?

    On my PC I select a number of tasks, only 2 of which have night calendar applied. Other tasks with night calendar do not get formatted.


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Tuesday, September 13, 2016 2:52 AM
  • Hi Philip Day,

    According to your description, could you provide screenshot to illustrate what you expect to happen, that will help us reproduce and resolve your issue.

    Thanks for your understanding.

    Tuesday, September 13, 2016 6:20 AM
  • I re-installed again today, now it works perfectly on a project with no sub-projects.

    I'm not sure what the problem was before... It's still not functioning as expected on a project 'with'sub-projects.

    I'm not sure how to provide screenshots that show that it isn't working any better than explaining, so here goes:

    Summary Task

    Task 1 - Anything

    Task 2 - Anything

    Task 3 - Anything

    Summary Task (new sub-project)

    Task A - Night

    Task B - Standard

    Task C - Standard

    Tasks A-C are selected. I run the macro. Tasks 1-3 in the first sub-project are highlighted as though they are Tasks A-C.

    i.e. Task 1 is now highlighted as a Night Shift (regardless of it's own calendar). Task A is left unaffected.

    Thanks to you for help

    Phil


    • Edited by PhilipDay Tuesday, September 13, 2016 9:20 AM explained problem
    Tuesday, September 13, 2016 7:34 AM
  • Ok, the problem is you have sub-projects. If you include the sub-project summary task in your selected tasks it works. If you don't, then the macro doesn't know that Task A is a linked sub-project task.

    Task A is Id1 in the sub-project, but id 1 as far as the VBA macro is concerned is Id 1 for the whole project, which is task 1 above.

    So interim solution is to include the sub-project summary tasks in your selection, otherwise you will need to process all outline levels above the selected tasks to level 1 to confim which project they belong to and adjust SubProjId accordingly..


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    • Marked as answer by PhilipDay Wednesday, September 14, 2016 8:09 AM
    Tuesday, September 13, 2016 11:13 PM
  • Rod, you are a man of unusual perception and skill!

    Thanks very much. Another thread answered

    Wednesday, September 14, 2016 8:09 AM