Forum Discussion
Material usage based on formula
Hi All, I am new to this forum and coming back to using MS project after 5+ years away. The project that i am trying to setup is a home reno project and I have a question relating to materials ...
Well, yeah, its feasible but it will require a combination of custom field formulas, a custom field value list and some VBA. Here's the concept. The painting tasks are laid out with a couple extra custom fields. The Text1 field is a value list allowing selection of paint resource. The Task Number1 field (Sq feet) is used to directly enter the area to be painted, an ability you want to have.
The Resource Sheet also has custom field for entering the paint coverage spec (e.g. 400 sq ft/gal). This is pulled directly from the paint can and manually entered. The material label is usage (e.g. /gal). And of course the cost of a gallon of paint is entered in the Std Rate field.
That's the setup, now the good stuff. In order to sync paints shown on the value list with actual paint resources, macro code will read each paint resource and create the value list for the Task Text1 field. Once that is in place, the macro will calculate how many gallons are needed to paint the area and show that number in the Task Number2 field (Gal), something else you wanted. The macro will then go on to assign that amount of paint to the task with the total cost appearing in the Cost field.
As a side note, although this is all possible to do in Project it is quicker and easier to do what you want in Excel.
Sound good?
John
Thanks John,
This sounds just like what i was looking for.
Are you able to provide any VBA examples of how this could be done?
Thanks Again
Darren
Okay, here ya go. There are actually two macros. Run the first (SetUp_Paint_List) to set up the Task Text1 value list so you can select which material resource (paint) you want for the task. Run the second (MatlCost) to calculate how much paint will be needed based on your input of area in the Task Number1 field and coverage data entered in the Resource Number1 field. It will then calculate the total cost.
John
Option Explicit
'This procedure populates a value list with all material resources in file
Sub Setup_Paint_List()
Dim r As Resource
'First kill value list if it already exists (provides or real time changes to material list)
CustomFieldDelete Field:=pjTaskText1
'create value list with material resources
CustomFieldPropertiesEx FieldID:=pjCustomTaskText1, attribute:=pjFieldAttributeValueList
For Each r In ActiveProject.Resources
If r.Type = pjResourceTypeMaterial Then
CustomFieldValueListAdd FieldID:=pjCustomTaskText1, Value:=r.Name
End If
Next r
End Sub
'This procedure calculates amount of material needed based on user entry into Number1
' and then assigns that material to each task with a selected material (Text1)
Sub MatlCost()
Dim t As Task
Dim PaintType As String
Dim Need As Variant
'assign paints to tasks with entry in Text1
For Each t In ActiveProject.Tasks
If t.Text1 <> "" Then
PaintType = t.Text1
Need = CDec(t.Number1 / ActiveProject.Resources(PaintType).Number1)
t.ResourceNames = PaintType & "[" & Need & "]"
End If
Next t
End Sub