I have written below code for custom field 'Product_task' to take values selected for field 'SFDC Product' as options. However this doesn't seem to work always. Please let me know your suggestions.

Sub cutom_lookup()

Dim customfield As Long
Dim message As String
Dim fieldvalue As String
Dim finalvalue() As String
Dim stringer As String
Dim i As Variant

customfield = FieldNameToFieldConstant("SFDC Product", pjProject)
fieldvalue = ActiveProject.ProjectSummaryTask.GetField(customfield)
finalvalue = Split(fieldvalue, "###")

Dim j As Integer
j = 0
Dim lookupfield As Long
lookupfield = FieldNameToFieldConstant("Product_task", pjProject) ' product_task is a text custom field at Task level,
                                                                 ' created locally in project

Debug.Print lookupfield

For Each i In finalvalue
    'Debug.Print i
    'customfieldvaluelistadd FieldID:=lookupfield, Value:=i, Index:=j
    Application.customfieldvaluelistadd FieldID:=lookupfield, Value:=i
Next i

Debug.Print "successful"

End Sub
  • Do you want C2 to be dependent on C1? If so, you'll need to write a macro to redefine the list of options for C2. This macro will need to loop through all tasks to find the C1 values that are used. The macro can be run on every project_change event or on demand (probably better). If this is what you want, look at the CustomFieldValueListAdd method and try to write the code yourself. If you get stuck, edit this question with the code you have done and describe the issue. Sep 3, 2019 at 18:59
  • Hi Rachel, I have edited question with code. I am very close. This code works fine if I create a Custom field(destination one) in Project client itself and use it. However it doesn't work if I create a Custom field in Project Online and use it.
    – PallavN
    Sep 9, 2019 at 14:53
  • great job getting your code to work with the Project client! Sorry I can't help you debug that issue with Project Online. Sep 9, 2019 at 17:52
  • Thanks, but after this I realized that it is not enough. 1. For the code I have written, a Task level field should be created, then run macro. ALso, this field doesn't show up in data for reporting. 2. If I save it at enterprise level, then this code doesn't work. Also if it would have worked then it will set the lookup values to enterprise level in first run. Moving forward it will show same values in other projects. 3. Another approach could be, define all lookup values at enterpise level, show/hide lookup values based on values selected in first custom field.
    – PallavN
    Sep 10, 2019 at 11:02
  • 3
    I'm voting to close this question as off-topic because it seems like it'd be a better fit for Stack Overflow.
    – Sarov
    Sep 10, 2019 at 13:11


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.