ms project vba assignments

• Search forums
• Newsgroup Archive
• Project Newsgroups
• Project Developer

adding tasks, resources and assignments to a project using VBA

• Thread starter Mark VII
• Start date May 3, 2007
• May 3, 2007

Greetings -- I'm working up a program to take a Excel list of tasks and resources and bring it into Project. (We have a standard estimating Excel template that is currently entered into Project manually, and am trying to automate this.) Am having trouble assigning resources to tasks. Here's a high level of how the code looks. dim tsk as Task dim rst as Resource dim asn as Assignment dim proj as Project set proj as Application.Projects.Add For each row on the input spreadsheet set tsk = proj.tasks.add tsk.Name = < task name from spreadsheet > '* this goes OK proj.Resources.Add (< resource name from spreadsheet > ) '* this goes OK lngResourceId = RetreiveResouceId(name) '* function to find resource and get its id '* trouble starts here Set asn = tsk.Assignments.Add(tsk.ID, lngResourceId, <units from spreadsheet>) asn.Work = < hours from spreadsheet > next input row Creating the task records goes OK. So does adding the resource and retrieving the resulting ID. Where it gets messy is creating the resource assignments. The "Set asn = tsk.Assingment...." gives 1101, The Argument Value is not Valid if I retrieve the units value from the spreadsheet (even if it's 1). However, the statement works OK if I hard code the units value to 1. If I run with units hard coded to 1, and reach the "asn.Work = < value from spreadsheet >" line, I get the 1101 error again. Have searched high and low for an example of the correct way to specify these values and come up dry. Does anyone have any suggestions? Thanks a million. Mark  

Mark VII said: Greetings -- I'm working up a program to take a Excel list of tasks and resources and bring it into Project. (We have a standard estimating Excel template that is currently entered into Project manually, and am trying to automate this.) Am having trouble assigning resources to tasks. Here's a high level of how the code looks. dim tsk as Task dim rst as Resource dim asn as Assignment dim proj as Project set proj as Application.Projects.Add For each row on the input spreadsheet set tsk = proj.tasks.add tsk.Name = < task name from spreadsheet > '* this goes OK proj.Resources.Add (< resource name from spreadsheet > ) '* this goes OK lngResourceId = RetreiveResouceId(name) '* function to find resource and get its id '* trouble starts here Set asn = tsk.Assignments.Add(tsk.ID, lngResourceId, <units from spreadsheet>) asn.Work = < hours from spreadsheet > next input row Creating the task records goes OK. So does adding the resource and retrieving the resulting ID. Where it gets messy is creating the resource assignments. The "Set asn = tsk.Assingment...." gives 1101, The Argument Value is not Valid if I retrieve the units value from the spreadsheet (even if it's 1). However, the statement works OK if I hard code the units value to 1. If I run with units hard coded to 1, and reach the "asn.Work = < value from spreadsheet >" line, I get the 1101 error again. Have searched high and low for an example of the correct way to specify these values and come up dry. Does anyone have any suggestions? Thanks a million. Mark Click to expand...

John said: My first guess is that the units value in Excel is of the wrong data type, like maybe a text value. < Click to expand...

• May 4, 2007

Mark VII said: Hi John -- Good point. If I assign the contents of the relevant spreadsheet cell to a variable, then assign the variable to the Work property, it works. Go figure. Have run into a new problem, though. As I loop through the resources and populate their work values, numbers start changing strangely. For example, I assign resource1 to the task and let Units default to 100%. Then, I set his Work property to specify his hours. So far so good. (At this point, the task Work = resource1's work.) Then, I assign resource2 to the task, let his units default to 100%. Still OK. When I set resource2's work hours, I would expect project to set resource2's hours to the specified value, leave resource1's hours alone, and set the task work hours to the sum of resource1 and resource2's hours. Instead, project reduces resource1's work hours. Also, the task total work hours is a value lower than expected. Any thoughts here? Thanks, Mark Click to expand...

John said: The "numbers" are probably changing because of the default task type. < Click to expand...

• May 5, 2007

Mark VII said: I think that was it. I changed my code, and though not exactly as expected, the hours allocations for multiple resources per task are now much closer. Thanks a million for helping me get this program on line. It's going to save us a TON of time. Mark Click to expand...

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Similar Threads

Project Management, Software, Training...

« Securing Your MS Project Files and Macro Code | Main | Just needs a little dog to ride on the tank »

Working with Task and Assignment Fields VBA

One common problem people face with project is that there are three classes of custom fields; task fields, assignment fields and resource fields. If you are in a resource view and you are looking at the Text1 field it won't have the same information as if you are looking at the Text1 field in a task view. This is true with reports as well. The solution is to copy over the items from the one field to the other. This is painful unless you automate it. So, to reduce the pain here is VBA code which does it for you: Sub CopyTaskFieldToAssignment() 'This macro copies information in the task text5 field 'into the assignment text5 field so that is can 'be displayed in a usage view or in a report. 'Modify the line noted below to fit your needs Dim t As Task Dim ts As Tasks Dim a As Assignment Set ts = ActiveProject.Tasks For Each t In ts If Not t Is Nothing Then For Each a In t.Assignments 'change the following line to use 'for a different custom field a.Text5 = t.Text5 Next a End If Next t End Sub

Pretty easy. This one should have no problems because each assignment only has a single task that it references. However, going the other way could be a problem as each task can have several assignments. To sidestep the issue we can simply concatenate all of the text from all of the assignments. The code would then look like this:

Sub CopyAssignmentFieldToTask() Dim t As Task Dim ts As Tasks Dim a As Assignment Set ts = ActiveProject.Tasks For Each t In ts If Not t Is Nothing Then t.Text5 = "" For Each a In t.Assignments 'change the following line to use 'for a different custom field t.Text5 = t.Text5 & ", " & a.Text5 Next a End If Next t End Sub

The line t.Text5 = t.Text5 & ", " & a.Text5 appends whatever is in the assignment field to whatever is already existing in the task field.

Some simple modifications can make it work to copy from the resource fields.

Posted on August 2, 2005 4:39 PM | Permalink

Comments (3)

Disculpe que le escriba en español, mi consulta es: como puedo poner el valor de una variable creada en visual basic (ejemplo: avance) en la barra de gantt, hasta ahora solo puedo hacerlo con los campos que tiene ms project, pero no encuentro la manera de asignarlo desde una variable personalizada.

Se puede realizar la operacion??

Le agradeceria alguna recomendacion o sugerencia.

Posted by Manuel Acosta | February 15, 2006 9:12 AM

Posted on February 15, 2006 09:12

Is there a quick macro to copy the custom fields that were created in one project to another? Going through each field, importing, then changing the name to be the same is rather annoying.

It would seem that this would be a simple macro, yet I haven't been able to figure it out.

(Note from Jack: You can use the organizer to do this. Try recording a macro of moving the fields over and see what happens.)

Posted by Brad Earle | June 6, 2006 7:29 PM

Posted on June 6, 2006 19:29

Is it possible to copy the overallocation flag from the resource fields to the task fields. What I would like to achieve is highlighting in the Gantt view any tasks that have an overallocated resource assigned to it. I know this can be seen by opening a separate resource graph window but I would like to present it in a printed Gantt. Thanks,

-------------------- Yes it probably is possible, but I'm not sure you would like the results. The general code would be to walk through all the tasks and then the resources on that task and flag any tasks which have an overallocated resource: Sub resoverload() Dim t As Task Dim r As Resource For Each t In ActiveProject.Tasks t.Flag1 = False For Each r In t.Resources If r.Overallocated Then t.Flag1 = True Next Resource Next Task End Sub Once you have this then you can filter or format based on Flag1. -Jack

Posted by Malcolm Manning | March 26, 2008 10:50 AM

Posted on March 26, 2008 10:50

Post a comment

(Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

Email Address:

Remember personal info?

Comments: (you may use HTML tags for style)

The previous article is Securing Your MS Project Files and Macro Code .

The next article is Just needs a little dog to ride on the tank .

Current articles are in the main index page and you can find a complete list of articles in the archives .

Enable or disable macros in Microsoft 365 files

A macro is a series of commands used to automate a repeated task and can be run when you have to perform the task. However, some macros can pose a security risk by introducing viruses or malicious software to your computer. 

Warning:  Never enable macros in a Microsoft 365 file unless you're sure you know what those macros do and you want the functionality they provide. You don't need to enable macros to view or edit the file.  For more info see  Protect yourself from macro viruses.

Make a document trusted to enable macros

If you see a security warning when you open a document or try to run a macro, you can choose to make it a trusted document and enable macros. This example is on an Excel workbook. 

Select Enable Content .

In the Security Warning dialog, select Yes to make the document trusted.

Macros are now enabled on this document. To revoke a trusted document, you need to clear all trusted documents. For more info, see Trusted documents .

Tip:  Rather than enabling macros for each document, you can create a trusted location to store trusted documents. Microsoft 365 will then not check them with the Trust Center. For more info, see Add, remove, or change a trusted location in Microsoft Office .

Change macro settings in the Trust Center

Macro settings are located in the Trust Center. However, if your device is managed by your work or school the system administrator might prevent anyone from changing settings.

Important:  When you change your macro settings in the Trust Center, they are changed only for the Microsoft 365 app that you are currently using. The macro settings are not changed for all your Microsoft 365 apps.

Select the File tab and choose  Options .

Select  Trust Center , and then choose  Trust Center Settings .

In the Trust Center , select  Macro Settings .

Make the selections that you want, then select  OK .

The following are Macro setting options. Not all apps have the same choices. 

Note:  The options are slightly different in Excel, we'll call those out as we go.

Disable all macros without notification     Macros and security alerts about macros are disabled. In Excel this option is Disable VBA macros without notification and it only applies to VBA macros.

Disable all macros with notification     Macros are disabled, but security alerts appear if there are macros present. 

Use this setting to enable macros on a case-by-case basis. In Excel this option is Disable VBA macros with notification and it only applies to VBA macros.

Disable all macros except digitally signed macros     Macros are disabled, and security alerts appear if there are unsigned macros present. However, if the macro is digitally signed by a trusted publisher, the macro just runs. If the macro is signed by a publisher you haven't trusted yet, you are given the opportunity to enable the signed macro and trust the publisher. In Excel this option is Disable VBA macros except digitally signed macros and it only applies to VBA macros.

Enable all macros (not recommended, potentially dangerous code can run)     All macros run without confirmation. This setting makes your computer vulnerable to malicious code. In Excel this option is Enable VBA macros (not recommended, potentially dangerous code can run)  and it only applies to VBA macros.

Excel also has a checkbox for Enable Excel 4.0 macros when VBA macros are enabled . If you select this checkbox all of the above settings for VBA macros will also apply to Excel 4.0 (XLM) macros. If this checkbox is not selected XLM macros are disabled without notification.

Trust access to the VBA project object model    Block or allow programmatic access to the Visual Basic for Applications (VBA) object model from an automation client. This security option is for code written to automate a Microsoft 365 program and manipulate the VBA environment and object model. It is a per-user and per-application setting, and denies access by default, hindering unauthorized programs from building harmful self-replicating code. For automation clients to access the VBA object model, the user running the code must grant access. To turn on access, select the check box.

Note:  Microsoft Access has no Trust access to the VBA project model object option.

Change macro security settings in Excel

Quick start: Create a macro in Excel .

Block suspicious macros in Office on Windows 10 S.

How malware can infect your PC

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Assignment.Project property (Project)

• 5 contributors

Gets the name of the project containing the Assignment . Read-only String .

expression . Project

expression A variable that represents an Assignment object.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

• Stack Overflow Public questions & answers
• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Talent Build your employer brand
• Advertising Reach developers & technologists worldwide
• Labs The future of collective knowledge sharing
• About the company

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

How to get the value of a cell in VBA MS Project

I need a code that gives a message depending the different values of some particular cells in a Project, I have already used VBA in Excel, but never in Project, so I don´t know how to do it

I did a code in VBA Excel that do the same thing that I need to do in Project, but I need the code but with the infomation of a Project, but the functions that I use in Excel, are not defined in Project, so I don´t know how to addecuate the code

I am not sure if the way how I get the values of the cells are the only thing that will change about the code, but knowing how to do that, will be a big help

To get the values of a task, use the Task object. In this case you'll want to loop through all of the tasks using the Tasks object (collection of all tasks). It is unclear what task fields you need, but this should get you started:

Note that the Percent Complete and Physical Percent Complete properties return a value from 0 to 100, so don't multiply by 100 later on.

• I did the changes you told me, but I can't make it work, I get an error message number 438, it says something like: the object does not support this property or method –  DianaLV Commented Oct 23, 2019 at 19:38
• What line is causing the error? BTW: this code is intended to run in MS Project, not Excel. –  Rachel Hettinger Commented Oct 23, 2019 at 19:51
• 1 I already make it work! Thank u so much. I think that the error was because I write the tasks in spanish, but the code recognize them only in english –  DianaLV Commented Oct 23, 2019 at 20:53

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

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

Not the answer you're looking for? Browse other questions tagged vba ms-project or ask your own question .

• Featured on Meta
• Upcoming sign-up experiments related to tags
• The 2024 Developer Survey Is Live
• Policy: Generative AI (e.g., ChatGPT) is banned
• The return of Staging Ground to Stack Overflow

Hot Network Questions

• What was Jessica and the Bene Gesserit's game plan if Paul failed the test?
• Why focus on T gates and not some other single qubit rotation R making Clifford + R universal?
• What is the history and meaning of letters “v” and “e” in expressions +ve and -ve?
• What does 'bean honey' refer to, in Dorothy L. Sayers' 1928 story
• What is the meaning of this black/white (likely non-traffic) sign seen on German highways?
• Why did the UNIVAC 1100-series Exec-8 O/S call the @ character "master space?"
• Could an Alien decipher human languages using only comms traffic?
• How can the CMOS version of 555 timer have the output current tested at 2 mA while its maximum supply is 250 μA?
• Did the NES CPU save die area by omitting BCD?
• Is there any way to play Runescape singleplayer?
• Why is the Newcomb problem confusing?
• Wrappers around write() and read() and a function to copy file permissions
• Seeking a classification of Bach's preludes (WTC, "little", "great", ...)
• A Fantasy movie with a powerful humanoid being that lives in water
• Am I getting scammed? (Linking accounts to send money to someone's daughter)
• How should I end a campaign only the passive players are enjoying?
• Will a Google Chrome extension read my password
• Should I practise a piece at a metronome tempo that is faster than required?
• Short story in which the main character buys a robot psychotherapist to get rid of the obsessive desire to kill
• How can non-residents apply for rejsegaranti with Nordjyllands Trafikselskab?
• Is a possessive apostrophe appropriate in the verb phrase 'to save someone something'?
• I'm a web developer but I am being asked to automate testing in Selenium
• Which CAS can do basic non-commutative differential algebra?
• How do I perform pandas cumsum while skipping rows that are duplicated in another field?