After you have set the properties and arranged the controls on the form, switch to the code window for the form. There are only three events that we care deeply about (or even care minimally about) in this form. They are the following:
• When the user Clicks on cmd Cancel
• When the user Clicks on cmdApp1yLink
• When the form loads
Sound familiar? You will find that,most normal dialog boxes need only these three events.
The Update macra relies on cells that are named. Excel has a collection object of all the names in the workbook. The ComboBox will list all the names that the Update macro will accept as valid. The main purpose of the Initialize event procedure is to load the names from the names collection into the cmb Cell Names list. The event procedure cmdCancel_Click is Identical to that used in the UserForm you designed earlier,The last event will link an Excel cell 0 the fixed cost field of the currently selected task. The comple~ed code for the user form follows
Before you release the application, you would create a custom command bar with menu items or buttons for the seven macros. See for examples and instructions. You will need to create a command bar with a button or menu item to run each .of the following macros, which were discussed earlier in the
You’ll also need to display the custom fields and fixed cost fields in a task view (such as the Gantt Sheet). Refer to Chapter 16 for information on displaying fields
Further Suggestions for the Linked Expense Appiication
With the current macros, you Me limited to pulling cells from a single Excel workbook. It would be nice if you could determine the workbook on a per field basis. Obviously this would require some modifications to the User Form, but it will also require some additional custom fields or a method of pasting the information from a single field.
A true Open File dialog box would be superior to the input box. However, this is .’not a feature built Into VBA To get access to standard dialog boxes, you would need to make Windows API calls or use an ActiveX control such as the Microsoft Common Dialog Control.
Using the Time Scaled Values Collection
Project does not include travel time or travel costs in the budget unless you include them as fixed costs or per-use costs, either of which must be hand calculated when a project task requires more than one trip. When we were initially brainstorming project ideas for this chapter, we considered creating a travel-cost calculator, which is a Project application that we’d use on a regular basis. The functionality that we desire includes the following:
• Calculating the. total travel cost for a particular task” based on mileage to and from the work site and the number of days a resource is assigned to the site
• Calculating mean travel time for tasks completed at remote work sites
• Calculating duration, work, and units in a manner that accurately reflects mean . travel time for project scheduling and cost analysis
The first two are The last, however, is really nasty. In our spare time, we’re still working on the application … and working and working. It didn’t take us long to discover that calculate. g travel costs for a project is a little trickier then it would first appear, so this is an application that’s still in development But as part of our work, we’ve spent a considerable A amount of time with a new collection in Project 2000, the collection.
The Time Scaled Values collection provides a means for examining time-phased information on a field of an item (Task,Resource or Assignment). The Ti me Scaled Data Method of an item retuns a Ti me Scaled Values collection
Some final advice on creating applications: Don’t become discouraged if it does not do everything you want it to immediately; the first version does riot need to do it all. Build your applications in phases. If you tackle too much at once, you’ll never finish the application. (But you’re already a master at project management, so you knew that.)
VBA is a very powerful tool that will let you build incredibly sophisticated applications. Developing a large Project application is, itself, a project. Project 2000 is an excellent tool for project management; use it to manage your application development as well.
From this point forward, you’re on your own. Unlike using VBA with Excel, Word, and Access, you won’t find hundreds or even dozens of articles in developers’ magazines on how to automate Project or a score of downloadable applications on the Microsoft Web
site. If you like what you’ve seen in this chapter and want to. create more Project 2000 applications, here are some resources that might be helpful:
Mastering Visual Basic for Applications by Guy Hart-Davis is the premiere guide for the Visual Basic for Applications programming language. Most VB books spend 90 to. 95% of their content on creating standalone applications, This book focuses precisely on VB for automation with programs such as Project and the Office suite.
Every application that can be automated with VB includes the application’s object model. With most applications, you can access the model by firing up VB. If that doesn’t work, open the VB IDE in any application (for example, Project 2000) and load the application’s library. Use the Object Browser to. examine the model.
For detailed information about automating to and from a specific