none
How to enter data in Task.Text(x) field ? RRS feed

  • Question

  • Hi,

    The operator needs to run a proc which will fill data in Task.Text3 field. Another time it will select Task.Text4

    For example : How to rename the Task.Text & 4 field with the Project "Status Date" ?

    Thanks for your help

    Tuesday, July 21, 2020 1:01 PM

Answers

  • WLID1966,

    OKay, try this (pardon my French, it's all via Google translator)

    John

    Sub ProgressPoints()
    Dim t As Task
    Dim FldVal As Long
    Dim i As Integer

    If ActiveProject.StatusDate = "SUR" Then
        MsgBox "Oups! Aucune date de statut Definie. Reessayer", vbCritical
        Exit Sub
    End If

    i = 1
    FldVal = pjTaskText1
        While ActiveProject.Tasks(1).GetField(FldVal) <> ""
            i = i + 1
            FldVal = FieldNameToFieldConstant("Text" & i, pjTask)
            
            If i > 30 Then
                MsgBox "Le nombre maximum de periodes de " & vbCr _
                    & "de progresion a ete atteint", vbInformation
                Exit Sub
            End If
        Wend
        CustomFieldRename FieldID:=FldVal, newname:="a partir de " & CStr(DateValue(ActiveProject.StatusDate))
    For Each t In ActiveProject.Tasks
        t.SetField FieldID:=FldVal, Value:=t.PercentComplete
    Next t
    End Sub

    • Marked as answer by WLID1966 Tuesday, July 21, 2020 8:42 PM
    Tuesday, July 21, 2020 8:31 PM

All replies

  • WLID1966,

    I don't quite understand what you want to do. In the first sentence you talk about running a procedure (i.e. macro) to put data into two Text fields. Then the second sentence talks about renaming the Text4 field with "Status Date". Please clarify with more details and I'll be glad to help you.

    John

    Tuesday, July 21, 2020 1:32 PM
  • Hello John,

    I'll try to explain: 

    Via an InputBox, the operator enter "2" or"3" or "10" because he needs that the VBA proc enter data into the column Task.Text2 or Text3 or texte10 for every tasks of the project.

    First, he want to rename the selected  Text field with the Project.Status date.

    Second, he want the proc to copy the % complete in every row (Task) of the specific column, the Text field.

    Thanks for your help

    Tuesday, July 21, 2020 1:50 PM
  • WLID1966,

    Still a little fuzzy but lets see if I kinda get the gist.

    If the user enters "2" in the InputBox that value goes into the Text2 field. If a "3" is entered, it goes into the Text3 field and if a "10" is entered, it goes into the Text10 field. Correct? If so, and just curious, since numerical values are entered, why not use the Number2, Number3, and Number10 fields?

    Now, about that "Status Date" thing. You call it the Project.Staus date which is a date field. It is a date, how is that to be used as the name for something? That just doesn't compute. Or, are you using the term "Status Date" generically as a column name that has nothing to do with the Status Date field? If so, poor choice of name in my opinion. And, if you are using the "Status Date" as a generic term how does it relate to the three Text fields? The water is still pretty muddy on this item.

    With regard to copying the % Complete value, say again.

    Remember, all this is very clear in your mind but you are trying to explain it to a third party who can't read your mind, so what may seem obvious to you is not obvious to someone else. Nonetheless, let me go out on a limb and take a guess as to what you want to do.

    You have a macro with an InputBox. When the user enters a numerical value, 2, 3 or 10, you want the code to write the % Complete value into Text2, Text3 or Text10 respectively and you also want whatever Text field is written to have the column name "Status Date". Is that it? If that's not it, then please try again, we'll get there eventually.

    Sorry if I sound rather pointed in my response, please realize I'm as frustrated as you are. I want to help but I can only work with clear details.

    John

    Tuesday, July 21, 2020 3:16 PM
  • This code will set a Flag field (in this case Flag1). If the task Name field contains the desired text the Flag will be set to Yes, otherwise it will be No.

    Sub FlagTasks()

        Dim txt As String
        txt = InputBox("Flag tasks that include the following text in their names:")

        Dim tsk As Task
        For Each tsk In ActiveProject.Tasks
            tsk.Flag1 = (0 < InStr(1, tsk.Name, txt, 1))
        Next tsk

    End Sub
    Tuesday, July 21, 2020 3:28 PM
  • John,

    I'll try to explain better what I want: the user want to keep track of each progress point in time:

    Tracking progress

    For the first month, he uses Text1 for the first progress point in percentage. In fact, the VBA proc copies the % from the % Complete column. The code renames the column with the Status date (or the current date)

    What I don't know is how to concatenate "Text" & "2" (for the second progress point) when the user want to use the Text2 column and to rename it. Next month the code will look for Text3 etc...

    Is it a little clearer ? Sorry for my poor english :(


    • Edited by WLID1966 Tuesday, July 21, 2020 4:21 PM
    Tuesday, July 21, 2020 4:16 PM
  • WLID1966,

    Yes, that's a lot better, now it makes sense. Let's make sure I got it and then I can write the code for you.

    Through an InputBox, the user enters 1, 2, 3, etc which represents a particular progress point (i.e. 1st, 2nd, 3rd, etc.). Based on the numerical value, the code copies the % Complete value to the respective Text field (i.e. Text1, Text2, Text3, etc.) and then captures the date in the Status Date field and uses that as the column heading. The end result after the 3rd week might look something like this.

    Is that what you want? If so, how many progress points do you want? You of course can have up to 30.

    John

    Tuesday, July 21, 2020 4:45 PM
  • John,

    That's exactly what the user want. You explained it better than me!

    If the limit is 30  Text fields, he will use all of them.

    Thanks again.

    Tuesday, July 21, 2020 4:53 PM
  • WLID1966,

    Okay, I'll code it up for you. I need a couple more clarification details. You said Status Date or current date as the column heading, which do you want? Also, do you want the percent complete values for summary lines as well as task lines, or just for task lines?

    Here's another thought. If progress points are sequential then there really isn't a need for a user interface (i.e. InputBox). The code could simply test which Text field was last used and then use the next Text field. Will that work?

    John


    Tuesday, July 21, 2020 4:59 PM
  • John,

    I prefer Status Date (not current date)

    Yes, the user want the % progress for every row : Summaries as well as elementary tasks.

    Thanks

    Tuesday, July 21, 2020 5:09 PM
  • WLID1966,

    How about my follow on comment about needing any kind of user input?

    John

    Tuesday, July 21, 2020 5:29 PM
  • John,

    They are using only "% Complete".

    Thanks

    Tuesday, July 21, 2020 5:38 PM
  • WLID1966,

    Let me try again. As you envisioned it the user will enter a value via an InputBox telling the code which progress point (i.e. which Text field) to transfer the % complete value. I'm say, why bother with an InputBox. The first time the macro is run it will put the % complete in Text1 and set the column heading. The next time the macro is run it will look in Text1 and if there is a value, it will "know" this is progress point 2 and put the date into Text2. So each time the macro is run, it will detect which Text field was used last and then automatically transfer the data into the next Text field.

    Sound good?

    John

    Tuesday, July 21, 2020 5:45 PM
  • Sound very good !!!
    Tuesday, July 21, 2020 6:38 PM
  • WLID1966,

    OKay, try this (pardon my French, it's all via Google translator)

    John

    Sub ProgressPoints()
    Dim t As Task
    Dim FldVal As Long
    Dim i As Integer

    If ActiveProject.StatusDate = "SUR" Then
        MsgBox "Oups! Aucune date de statut Definie. Reessayer", vbCritical
        Exit Sub
    End If

    i = 1
    FldVal = pjTaskText1
        While ActiveProject.Tasks(1).GetField(FldVal) <> ""
            i = i + 1
            FldVal = FieldNameToFieldConstant("Text" & i, pjTask)
            
            If i > 30 Then
                MsgBox "Le nombre maximum de periodes de " & vbCr _
                    & "de progresion a ete atteint", vbInformation
                Exit Sub
            End If
        Wend
        CustomFieldRename FieldID:=FldVal, newname:="a partir de " & CStr(DateValue(ActiveProject.StatusDate))
    For Each t In ActiveProject.Tasks
        t.SetField FieldID:=FldVal, Value:=t.PercentComplete
    Next t
    End Sub

    • Marked as answer by WLID1966 Tuesday, July 21, 2020 8:42 PM
    Tuesday, July 21, 2020 8:31 PM
  • Great !

    Thanks a lot John ! that works !

    And thanks a lot for your patience ! :)
    • Edited by WLID1966 Tuesday, July 21, 2020 8:50 PM
    Tuesday, July 21, 2020 8:43 PM
  • WLID1966,

    You're welcome and thanks for the feedback.

    John

    Tuesday, July 21, 2020 9:12 PM