Loop through all TableFields in Microsoft Project add to combobox

Question

I'm stuck trying to create a UserForm in VBA with a combobox that lists all possible TableFields(?).

Updated code: Using the code provided by @dbmitch and some freestyle. This lists a two-column combobox with both the Original and the Custom field name (if it exists). It only lists the fields used in the Activeproject. Not all possible fields. But if the field isn't used in the Activeproject anyway... maybe this is for the best!

Public strResult2 As String ' Used for custom field names

Private Sub UserForm_Initialize()
    Dim objProject      As MSProject.Project
    Dim tskTable        As MSProject.Table
    Dim tskTables       As MSProject.Tables
    Dim tskTableField   As MSProject.TableField
    Dim strFieldName    As String


'ComboBoxColA.ListWidth = "180" 'Uncomment for wider dropdown list, without wider box

Set objProject = Application.ActiveProject
Set tskTables = objProject.TaskTables


With ComboBox1 'Adds one blank line at the top
  .ColumnCount = 2
  .AddItem ""
  .Column(1, 0) = "BLANK"
End With

' Loop through all tables
For Each tskTable In tskTables
    ' Loop through each field in each table
    For Each tskTableField In tskTable.TableFields
        strFieldName = GetFieldName(tskTableField)
        If Len(strFieldName) = 0 Then GoTo SKIPHERE
         With ComboBox1
            .Value = strFieldName
            ' Check if allready exists
            If .ListIndex = -1 Then
            ' Then sort alphabetically
                For x = 0 To .ListCount - 1
                    .ListIndex = x
                If strFieldName < .Value Then
                .AddItem strFieldName, x
                .Column(1, x) = strResult2
                    GoTo SKIPHERE
                End If    
              Next x
             .AddItem strFieldName
            End If
        End With
SKIPHERE:
        Next
    Next

Set objProject = Nothing
Set tskTable = Nothing
Set tskTables = Nothing
Set tskTableField = Nothing
End Sub

Function

Private Function GetFieldName(ByVal objField As MSProject.TableField) As String
  ' find the field name and column header for a field (column) in a data table
       'strResult is placed in column 0 in ComboBox
       'strResult2 is placed in column 1 in ComboBox

  Dim lngFieldID As Long
  Dim strResult As String

  lngFieldID = objField.Field

  With objField.Application
    strResult = Trim(.FieldConstantToFieldName(lngFieldID))
    On Error GoTo ErrorIfMinus1 ' CustomField does not handle lngFieldID= -1
    If Len(Trim(CustomFieldGetName(lngFieldID))) > 0 Then strResult2 = " (" & Trim(CustomFieldGetName(lngFieldID)) & ")" Else strResult2 = ""
  End With

  GetFieldName = strResult
Exit Function

ErrorIfMinus1:
  strResult2 = ""
  Resume Next
End Function

@dbmitch helped me on my way getting this code to work. Thanks!

Are you using the code in MS Project? Your example of MSProject.FieldNameList is confusing — dbmitch
If you really mean "Table Fields" then the solution above is sufficient. However, if you want all possible Task fields, this will not work as it will not include fields that are not part of any table, which will be many. For example, the numbered Text, Number, Date, etc. fields are not part of any standard table (e.g. Text10). You could add them to a table, but there are hundreds of fields. Note there is no built-in enumeration for all task fields. — Rachel Hettinger
A better solution is to store the list of field constants for the fields you want to include and iterate over that. See this SO answer for suggestions on how to do that: stackoverflow.com/questions/32209245/loop-through-each-field-for-ms-project.... — Rachel Hettinger
Sorry if my example is more confusing than helping. The VBA code is meant for MS project indeed. I mean all task fields, yes. @RachelHettinger I must believe you when you say MS Project is not able to enumerate all fields, but I have to wonder how Project is able to list all available fields when you try to insert a column? — Doons
I would be fine if I just were able to list all the properties listed on this page: msdn.microsoft.com/en-us/vba/project-vba/articles/… — Doons

dbmitch dbmitch · Accepted Answer · 2018-07-02T23:46:41

That link is useful in that it shows the properties and methods available to you via the MS Project object model. You should be able to modify it to VBA format by changing it slightly.

What would have been more useful was to show your code you mentioned in...

I have found code that let me list all fields in the current table

In any case, see if this code does what you want as described in your question

Sub LoadFieldNames()
    Dim objProject      As MSProject.Project

    Dim tskTable        AS MSProject.Table 
    Dim tskTables       AS MSProject.Tables
    Dim tskTableField   AS MSProject.TableField 

    Dim strFieldName    AS String

    Set objProject = Application.ActiveProject
    Set tskTables  = objProject.TaskTables

    ' Loop thru all tables
    For Each tskTable In tskTables

        ' Loop through each field in each table
        For Each tskTableField in tskTable.TableFields
            strFieldName = GetFieldName(tskTableField)
            ComboBox1.AddItem strFieldName
        Next
    Next

    Set objProject = Nothing
    Set tskTable = Nothing
    Set tskTables = Nothing
    Set tskTableField = Nothing

 End Sub

Try adding the function from this post to create the function GetFieldName ... and it should compile

Private Function GetFieldName(ByVal objField As MSProject.TableField) As String
  ' find the field name (actually colmn heading) for a field (column) in a data table

  Dim lngFieldID As Long
  Dim strResult As String

  lngFieldID = objField.Field

  With objField.Application
    strResult = Trim(objField.Title) ' first choice is to use the title specified for the column in the table

    If Len(strResult) = 0 Then
      ' try to get the custom field name- this will come back blank if it's not a custom field
      strResult = Trim((CustomFieldGetName(lngFieldID)))
    End If

    If Len(strResult) = 0 Then
      strResult = Trim(.FieldConstantToFieldName(lngFieldID)) ' use the field name
    End If
  End With

  GetFieldName = strResult
End Function

Loop through all TableFields in Microsoft Project add to combobox

1 Answers