none
Using Variables for Custom Fields RRS feed

  • Question

  • I have a series of VBA macros set up in MS project that use various specific custom fields to generate reports. For our local group I was able to dictate what fields they could and couldnt use so it was easy to make updates and revisions. However, we are now trying to roll out these macros to other groups who had their own custom fields that they use some of which conflict with the ones we were using.

     I am trying to create user form that they fill in stating what custom field they are using for various inputs. For Example is a person's name is in Text1 in the menu they would pick Text1 for the name field. 

    In the background I would need to pass Text1 to a variable which be something like Username which be used in the execution code.

    I have been playing around with Application.CustomFieldGetName(PJResourceText1) but when I run MsgBox there is no value stored.

    Below is the code I was trying to use. So the user provides TotalBudgt as ResourceNumber4

    Private Sub DefineCustomFields() Dim TotalBudget As String TotalBudget = Application.CustomFieldGetName(pjResourceText4) MsgBox (TotalBudget) MsgBox (Application.CustomFieldGetFormula(pjCustomResourceNumber3)) End SuB

    xlBook.Worksheets(Shtname).Cells(xlRowIndex, 9).FormulaR1C1 = currTask.TotalBudget   'Total Budget
    '
                                        
    xlBook.Worksheets(Shtname).Cells(xlRowIndex, 3).Value = currTask.Text1           'USER

    Tuesday, March 19, 2019 6:34 PM

Answers

  • EVM321,

    First of all, you talk about creating a user form (UserForm) but a message box (MsgBox) has nothing to do with UserForms. Did something get lost in translation?

    Second, did you customize the Resource Text4 field by renaming it or did you simply enter a value into the Resource Text4 field? If you entered a value and did not rename the field then the CustomFieldGetName Method will yield nothing.

    Third, if you're looking for an input from the user, then you need to use the InputBox Function, not the MsgBox Function.

    If there are a bunch of custom fields in question, you may indeed want to create a UserForm that lists each of the fields of interest and provides a selection list for the user. The inputs from that UserForm can then be used in your macros. If it's just a couple of custom fields, then a series of InputBox statements can get the info.

    Hope this helps.

    John

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Tuesday, March 19, 2019 8:09 PM
  • Hi,

    in addition to what John wrote:

    Application.CustomFieldGetName(pjResourceText4) will only give you the internal ID of field Resource.Text4.

    See this simple excerpt:

    Dim R As Resource
    
    'get the internalt Id of a field to work with later on
    v_IdResourceText4 = Application.CustomFieldGetName(pjResourceText4)
    
    'just to get one resource
    Set R = ActiveProject.Resources(1)
    
    'Read a vaule
    MsgBox ("Current value: " & R.GetField(v_IdResourceText4))
    
    'set a new value
    R.SetField FieldID:=vIDResourceText4, Value:="New Value"

    Regards
    Barbara

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Tuesday, March 19, 2019 11:41 PM
    Moderator
  • EVM321,

    That's good information from John and Barbara.  When reviewing your original question, I think the key to the answer is remembering the Application.FieldNameToFieldConstant method.  That allows you to collect a field name (a string value) from the user, then use that string to refer to the field.  For example, the following code has the user identify the resource field containing the resource's budget, then it reads all the resource budgets and lists them in a message box. (Both generic names and custom names are accepted.)  

    Sub GetResourceValue()
        Dim FName0 As String
        Dim Output As String
        Dim R As Resource
        
        FName0 = InputBox("Enter name of Resource field that contains the resource budget:")
        For Each R In ActiveProject.Resources
            Output = Output & "Resource: " & R.Name & vbTab & "Budget: " & _
            R.GetField(Application.FieldNameToFieldConstant(FName0, 1)) & vbCrLf
        Next R
        MsgBox Output
    End Sub
    Good luck, tom

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Wednesday, March 20, 2019 2:37 PM
  • EVM321,

    Aside from your question, I'd recommend you develop a standardized set of fields for all users. Not only will that provide consistency but you may be able to delete the need for a UserForm entirely.

    However, to address your existing question, you can define CAM as a double and set its value when the user selects a particular field. Then in your main macro, use that value with the GetField statement. Here is a simple test procedure that shows it. I use the Print.Debug for test but in your case that would be the statement that writes the value to Excel.

    Hope this helps.

    John

    Public CAM As Double
    Public t As Task
    Sub test()
    CAM = pjTaskText1
    For Each t In ActiveProject.Tasks
        Debug.Print t.GetField(CAM)
    Next t
    CAM = pjTaskText2
    For Each t In ActiveProject.Tasks
        Debug.Print t.GetField(CAM)
    Next t
    End Sub

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Wednesday, March 27, 2019 5:06 PM

All replies

  • EVM321,

    First of all, you talk about creating a user form (UserForm) but a message box (MsgBox) has nothing to do with UserForms. Did something get lost in translation?

    Second, did you customize the Resource Text4 field by renaming it or did you simply enter a value into the Resource Text4 field? If you entered a value and did not rename the field then the CustomFieldGetName Method will yield nothing.

    Third, if you're looking for an input from the user, then you need to use the InputBox Function, not the MsgBox Function.

    If there are a bunch of custom fields in question, you may indeed want to create a UserForm that lists each of the fields of interest and provides a selection list for the user. The inputs from that UserForm can then be used in your macros. If it's just a couple of custom fields, then a series of InputBox statements can get the info.

    Hope this helps.

    John

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Tuesday, March 19, 2019 8:09 PM
  • Hi,

    in addition to what John wrote:

    Application.CustomFieldGetName(pjResourceText4) will only give you the internal ID of field Resource.Text4.

    See this simple excerpt:

    Dim R As Resource
    
    'get the internalt Id of a field to work with later on
    v_IdResourceText4 = Application.CustomFieldGetName(pjResourceText4)
    
    'just to get one resource
    Set R = ActiveProject.Resources(1)
    
    'Read a vaule
    MsgBox ("Current value: " & R.GetField(v_IdResourceText4))
    
    'set a new value
    R.SetField FieldID:=vIDResourceText4, Value:="New Value"

    Regards
    Barbara

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Tuesday, March 19, 2019 11:41 PM
    Moderator
  • EVM321,

    That's good information from John and Barbara.  When reviewing your original question, I think the key to the answer is remembering the Application.FieldNameToFieldConstant method.  That allows you to collect a field name (a string value) from the user, then use that string to refer to the field.  For example, the following code has the user identify the resource field containing the resource's budget, then it reads all the resource budgets and lists them in a message box. (Both generic names and custom names are accepted.)  

    Sub GetResourceValue()
        Dim FName0 As String
        Dim Output As String
        Dim R As Resource
        
        FName0 = InputBox("Enter name of Resource field that contains the resource budget:")
        For Each R In ActiveProject.Resources
            Output = Output & "Resource: " & R.Name & vbTab & "Budget: " & _
            R.GetField(Application.FieldNameToFieldConstant(FName0, 1)) & vbCrLf
        Next R
        MsgBox Output
    End Sub
    Good luck, tom

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Wednesday, March 20, 2019 2:37 PM
  • Thank you for the answer. Yes there was some lost in translation.  I have a userform already created which works to execute to code already developed. However, it is very specific to a configuration used by only my group.

    What I am trying to do is create a new interface which I know how to do that will take inputs from the user assign them to a variable and then instead of using for example text4 in my code would use a variable "totalbudget" so that if instead of using text4 the user has information in text3 the code will still execute.

    I was using the msgbox just to show that I was simply trying to get the defined name to display. It turns out I was calling the wrong custom field. I was calling at the resource rather than task which was the first problem.

    Thursday, March 21, 2019 2:52 PM
  • Thank you
    Thursday, March 21, 2019 2:52 PM
  • EVM321,

    You're welcome and thanks for the feedback.

    If you already have a UserForm you might want to consider modifying it so it asks the user which field(s) to use. From there it's a simple matter of storing or reading the data to or from that field and using that variable in your macro code.

    Do you still have a question or did our collective responses point you in the right direction? If we did, then please consider marking any or all of our responses as the answer. However, if you still have an issue, please re-state, or post a new, question and we'll go from there.

    John

    Thursday, March 21, 2019 3:45 PM
  • Restated Question

    I can't post an image but I have a useform with combobox array's that provide the user with a series of fields that they can choose from. John as you had indicated the userform is the input method I intended to use.

    I think the answer to my question has been provided but I just want to clarify my question so that I am sure I understand the answers.

    Current Code

    xlBook.Worksheets("CAM Turnaround").Cells(xlRowIndex, 2).Value = currTask.Text4            'Cam.

    Code I am trying to Achieve

    xlBook.Worksheets("CAM Turnaround").Cells(xlRowIndex, 2).Value = currTask.CAM           'Cam.
    Instead of using Text4 in the code I want to use CAM which is a public variable that is defined and then calls the combobox array and sets the shown value to the variable. The problem I am having is making text4 a variable. I was operating on the assumption that I needed to use CustomGetField but if I should be going about this in a different way that would also be helpful to know.  One other clarification is the custom fields are in the task not resource as I originally had in my post. I figured that out after reviewing the provided answers.
    • Edited by EVM321 Tuesday, March 26, 2019 7:14 PM
    Tuesday, March 26, 2019 7:13 PM
  • EVM321,

    In your code, it looks like currTask is an task object (i.e. Dim currTask as Task). As such Text4 is a property of that object. It is a "variable" in that Text4 can have any value given to it either manually or with code. On the other hand CAM is a variable and not a property (of any object) so you need to take another approach. Why don't you simply say the value is CAM?

    xlBook.Worksheets("CAM Turnaround").Cells(xlRowIndex, 2).Value = CAM 

    I would assume it is already indexed to represent the appropriate task data you are trying to write to Excel, but maybe not. If it isn't, then I need more information on how you assign values to the variable CAM.

    John

    Tuesday, March 26, 2019 8:36 PM
  • You are correct in that CurrTask is a task object.  What is happening is I have a loop that is running through every task in the the project file and if it meets preset requirements exporting it to an excel worksheet.  I have a list of fields that need to exported for each task  ie the CAM, Budget, % complete etc. All that information is stored in userdefined task fields. My goal is that if the user used text 1 instead of text 4 that they can still get the information out. Where I am struggling is how I tell the code that instead of using text4 it uses text1. 

    CAM right now is just a variable name. I havent even defined what kind as that is what I was trying to determine if I needed to call it an object etc. My intention is that in the userform that there are 10 fields and from that they have the drop down menu. I then use the dropdown value menu selections to assign what user field has the CAM info etc.

    Thanks for you help with this.

    Tuesday, March 26, 2019 8:51 PM
  • EVM321,

    Aside from your question, I'd recommend you develop a standardized set of fields for all users. Not only will that provide consistency but you may be able to delete the need for a UserForm entirely.

    However, to address your existing question, you can define CAM as a double and set its value when the user selects a particular field. Then in your main macro, use that value with the GetField statement. Here is a simple test procedure that shows it. I use the Print.Debug for test but in your case that would be the statement that writes the value to Excel.

    Hope this helps.

    John

    Public CAM As Double
    Public t As Task
    Sub test()
    CAM = pjTaskText1
    For Each t In ActiveProject.Tasks
        Debug.Print t.GetField(CAM)
    Next t
    CAM = pjTaskText2
    For Each t In ActiveProject.Tasks
        Debug.Print t.GetField(CAM)
    Next t
    End Sub

    • Marked as answer by EVM321 Wednesday, March 27, 2019 6:22 PM
    Wednesday, March 27, 2019 5:06 PM