Text field data entry validation RRS feed

  • Question

  • We are using MS Project 2010 and making use of the custom text (and other) fields for data integration. We are integrating the task data from MSP 2010 into our other financial application and the text fields play roles in this integration. In one of the text fields (say Text15), we need to have project managers enter values that, within each project, are unique for each task. These values are different for each project and there is no standard set so we cannot build a lookup set. Now we need to ensure that before the user saves the data by explicitly clicking on save, user has entered value for Text15 and the value are unique for each task. If the user has not entered the values or values are not unique, we need to display message to the user. How do we achieve this?
    Wednesday, October 31, 2012 8:33 PM

All replies

  • Sam,

    Your best option for this is a simple VBA macro that examines the Text15 field for unique entries. You could instruct users to run the audit macro as part of their editing process or you could even set it up to automatically fire on the Save Event.

    Update: This macro should do what you need. It assumes no entries are necessary for Text15 on summary lines. If that is not true you can easily modify the code to include summary line entries.

    This is a simple version of the code. It could be enhanced by storing all missing and duplicate entries for display or printout at the end, or printing them to the immediate window. It's all a matter of a little more sophistication.

    Sub ChkTxt15()
    Dim Txt() As String
    Dim RefID() As Integer
    Dim t As Task
    Dim tskcnt As Integer, i As Integer, j As Integer

    tskcnt = ActiveProject.Tasks.Count
    ReDim Txt(tskcnt), RefID(tskcnt)

    i = 1
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = False Then
                Txt(i) = t.Text15
                If t.Text15 = "" Then
                    MsgBox "Text15 entry at ID " & t.ID & " is missing" & vbCr & vbCr & _
                        "Correct missing entry and run again", vbInformation
                    Exit Sub
                End If
                RefID(i) = t.ID
                If i > 1 Then
                    For j = 1 To i - 1
                        If t.Text15 = Txt(j) Then
                            MsgBox "Text15 entry at ID " & RefID(i) & " is a duplicate of" & vbCr & _
                                "Text15 entry at ID " & RefID(j) & vbCr & vbCr & _
                                "Correct duplicate entry and run again", vbInformation
                            Exit Sub
                        End If
                    Next j
                End If
                i = i + 1
            End If
        End If
    Next t
    End Sub


    Wednesday, October 31, 2012 9:27 PM
  • Hi,

    You will have to write an event-driven VBA procedure in the Application event ProjectBeforeSave. The Project venet BeforeSave won't do because it doesn't have a Cancel possibility. The bad news is that creating Application events is (IMHO) quite complex and whan I have to do it I take the (one and only) book on Project VBA to my side. Google the name of the editor, fellow MVP Rod Gill to read about it.

    As for the logic within the procedure, checking there is an entry is pretty straightforward but ensuring it is unique wil require a double For each task loop.

    Hope this helps,

    Wednesday, October 31, 2012 9:30 PM
  • Who decides what the value list should be? If the only criteria are that the values are unique within a project and that they are different between projects then you could use a formula to combine the project name and task unique ID into a custom task field.
    Friday, November 2, 2012 3:45 PM