Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dropdown field in Rollup Milestones (2000/SR1)

    In continuing my macro to roll up milestones and show the name of each milestone task as BottomText in the rollup, my team has discovered that the milestone task text is so long that it writes over other milestone task text on the Top Level Tasks. Current line of code for this is as follows:

    'Format milestones on rolled up bars to show Milestone as Bottom Text
    For Each T In ActiveProject.Tasks
    var = T.ID
    If T.Milestone Then
    GanttBarFormat TaskID:=var, GanttStyle:=10, StartShape:=3, StartType:=1, _
    StartColor:=0, MiddleShape:=0, MiddlePattern:=1, MiddleColor:=0, _
    EndShape:=0, EndType:=0, EndColor:=0, BottomText:="Text22"
    End If
    Next T


    We have a set of standard milestones so have added a custom text field (Text22) to select the associated milestone via a dropdown list. The dropdown list shows the full milestone name for clarification but I want to show an abbreviation for it in the rollup. I can see possible two ways to do this:

    1. Enter the abbreviation in the description field for each dropdown item and in the macro, insert "Text22.Description" or whatever the full field name would be instead of inserting just "Text22".

    2. Through the macro, substitute the description (or a text string which is the abbreviation) for Text22 as BottomText in the GanttBarFormat.

    OK, so this is another adventure in Project VBA. Can anyone recommend which would be the best way to go and how to write the code to do it?

    Thanks,
    Caroline

  2. #2
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown field in Rollup Milestones (2000/SR1)

    OK, I've gotten a bit further and am using Select Case in this code, as follows:

    For Each T In ActiveProject.Tasks
    var = T.ID
    var1 = T.Text22
    If T.Milestone Then
    Select Case var1
    Case "Engagement Meeting"
    var2 = "Eng Mtg"
    Case "SOW/Release Definition"
    var2 = "SOW/RDef"
    Case "Requirements"
    var2 = "Reqs"
    Case "Infrastructure Specs"
    var2 = "Infr Specs"
    Case "Design"
    var2 = "Des"
    Case "Dev/Conf/Internal Pgm Test"
    var2 = "Dev/Config/IPT"
    Case "QA Start"
    var2 = "QAS"
    Case "QA Test Complete"
    var2 = "QAE"
    Case "QA Sign Off"
    var2 = "QA-SO"
    Case "UAT SignOff"
    var2 = "UAT-SO"
    Case "Training"
    var2 = "Trn"
    Case "Impl Prep"
    var2 = "Impl Prep"
    Case "Go Live"
    var2 = "GL"
    Case "Trans to Support"
    var2 = "Supt"
    Case Else
    MsgBox "No milestone selected for Task: " & T.ID
    End Select
    GanttBarFormat TaskID:=var, GanttStyle:=10, StartShape:=3, StartType:=1, _
    StartColor:=0, MiddleShape:=0, MiddlePattern:=1, MiddleColor:=0, _
    EndShape:=0, EndType:=0, EndColor:=0, BottomText:=var2
    End If
    Next T

    However, when running this code, at the GanttBarFormat line (as best as I can tell), I get an error that "The argument is not valid." I suspect it has something to do with the BottomText:=var2 but I don't know how to fix it.

    Any ideas, please?

    Thanks,
    Caroline

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown field in Rollup Milestones (2000/SR1)

    Unfortunately the Project 2000 VBA help was not installed originally by my company. They've since upgraded almost everyone to 2002 but my client is using 2000 so I am still using it. I haven't been able to get the Project 2000 VBA help from any other source to install. I've got standard VBA help but that isn't much help with Project specifics.

    In any case, from the Project 98 VBA Training material, here is an excerpt on GanttBarFormat:

    GanttBarFormat Method
    Formats Gantt bars.

    Syntax
    expression.GanttBarFormat(TaskID, GanttStyle, StartShape, StartType, StartColor, MiddleShape, MiddlePattern, MiddleColor, EndShape, EndType, EndColor, LeftText, RightText, TopText, BottomText, InsideText, Reset, ProjectName)

    expression Optional. An expression that returns an Application object.

    TaskID Optional Long. The identification number of the task represented by the Gantt bar to be changed. The default is to change the Gantt bars of the selected tasks.

    GanttStyle Optional Integer. The style applied to the Gantt bar to be formatted. The value for GanttStyle is based on the position of the bar style in the list. For example, the value 3 returns the third bar style in the list.

    BottomText Optional String. The task field to display below the Gantt bar.

    Reset Optional Boolean. True if the bar formatting is reset to the default formatting of the style in the Bar Styles dialog box.

    Caroline



    > (Edited by jscher2000 on 18-Mar-04 13:41. )

    > What does the online help say for GanttBarFormat: does it specify a variable type for this argument, or can you assign this argument using some other method?
    > I can't seem to find the help file up at Microsoft.com.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dropdown field in Rollup Milestones (2000/SR1)

    Caroline,

    Just and idea to try.

    After your select case statement ends, try setting text22 to var2 and then set bottom text to text22:

    T.text22 = var2
    BottomText:=text22
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown field in Rollup Milestones (2000/SR1)

    I couldn't get this to work but with the wider dropdown list (per my other email), I'm able to use the following code and only have one piece to work out, my ElseIf statement so that if the task is a milestone but nothing has been selected for a value in Text22, an error message will display. I haven't been able to figure out how to check two conditions at the same time.

    'Format milestones on rolled up bars to show milestone abbreviation from Text22 dropdown
    'as Bottom Text
    For Each T In ActiveProject.Tasks
    var = T.ID
    If T.Milestone = True Then
    GanttBarFormat TaskID:=var, GanttStyle:=10, StartShape:=3, StartType:=1, _
    StartColor:=0, MiddleShape:=0, MiddlePattern:=1, MiddleColor:=0, _
    EndShape:=0, EndType:=0, EndColor:=0, BottomText:="Text22"
    ElseIf T.Milestone = True And Text22 = "" Then
    MsgBox "No milestone selected for Task: " & T.ID
    End If
    Next T

    I'll be glad when I have enough experience and knowledge that simple things like this don't stymie me!

    Thanks,
    Caroline

  6. #6
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown field in Rollup Milestones (2000/SR1)

    Hip hip hooray! I got it to work. Here is the final code:

    Sub Rollup_Milestones()
    Dim T As task
    Dim var As String 'Task ID
    Dim var1 As String 'Text22 - Milestone Name
    Dim var2 As String 'Milestone Abbreviation
    Dim EmptyMilestone

    On Error GoTo Err_Rollup_Milestones

    'Change view to WM Gannt view
    ViewApply Name:="_Gantt Milestones"

    ' Display milestones in summary bars
    ' Cycle through tasks in active project.
    For Each T In ActiveProject.Tasks
    ' If task is a milestone or a summary, set its Rollup property to True.
    If T.Summary Or T.Milestone Then
    T.Rollup = True
    'Tasknumber = ActiveCell.task.ID
    'If task isn't a summary task or milestone, set its Rollup property to False.
    Else
    T.Rollup = False
    End If
    Next T

    'Format milestones on rolled up bars to show milestone abbreviation from Text22 dropdown
    'as Bottom Text
    For Each T In ActiveProject.Tasks
    var = T.ID
    If T.Milestone = True Then
    GanttBarFormat TaskID:=var, GanttStyle:=10, StartShape:=3, StartType:=1, _
    StartColor:=0, MiddleShape:=0, MiddlePattern:=1, MiddleColor:=0, _
    EndShape:=0, EndType:=0, EndColor:=0, BottomText:="Text22"
    EmptyMilestone = T.Milestone = True And T.Text22 = "" ' Returns True.
    If EmptyMilestone = True Then
    MsgBox "No milestone selected for Task: " & T.ID
    End If
    End If
    Next T

    'Apply filter for only project names (with Release IDs)
    FilterEdit Name:="_Release ID", TaskFilter:=True, Create:=True, OverwriteExisting:=True, FieldName:="Text30", Test:="is greater than", Value:="0", ShowInMenu:=False, ShowSummaryTasks:=False
    FilterApply Name:="_Release ID"

    Exit Sub
    Err_Rollup_Milestones:

    MsgBox "Error occured within Rollup_Milestones. Error: " & Err.Description, _
    vbCritical, "Macro Error"

    End Sub

    So, a final question - is there any way to post code to wopr so that it maintains the indentations? It shows it when I add it to the email but not after I've posted it.

    Thanks,
    Caroline

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dropdown field in Rollup Milestones (2000/SR1)

    Caroline,

    Glad to see you got the code to work and thanks for posting it so others can benefit. To keep you code in its original format, go to thte 1-Click TagPanel and select PRE. Then paste your code inbetween the pre and /pre.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •