Author: Ira Brown

Ira Brown is a leader in the field of project management and a recognized Microsoft Project expert, Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their client's unique business requirements.  This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers, by creating Microsoft Project solutions that are tailored to an organization's unique needs.  They even have several free, downloadable widgets available on their website that you can begin using right away.

Enhanced Project Management in Microsoft Project: Incorporating Graphical Indicators for Task Dependencies and Date Constraints

As a proficient project manager, you’re well-versed in the complexities of task dependencies in Microsoft Project. The strategic implementation of graphical indicators to flag tasks without predecessors or successors significantly enhances project tracking efficiency. This comprehensive guide provides detailed instructions on creating these indicators and steps to identify tasks with date constraints, offering a sophisticated approach to project management. Step 1: Define the Custom Field for Dependency Check Step 2: Construct the Formula for Dependency Check Step 3: Set Up the Graphical Indicator for Dependency Check Step 4: Implementing the Dependency Indicator in Your Project Views Enhancing Project Management with Date Constraint Indicators To further augment your project management capabilities, integrating graphical indicators for date constraints in Microsoft Project is a significant enhancement. This feature brings another layer of visual clarity and efficiency to the management of project schedules. Crucial in determining the flow and success of a project, date constraints can often be lost in the myriad of tasks and milestones. By visually highlighting these constraints, you gain an immediate awareness of critical deadlines and scheduling commitments. This integration not only aids in maintaining a clear view of the project’s timeline but also serves as a proactive tool for identifying potential scheduling conflicts. It enables project managers to quickly spot where rigid deadlines may impact other aspects of the project, allowing for timely adjustments and negotiations. Moreover, in large-scale projects with numerous stakeholders, these indicators provide a straightforward way to communicate the urgency and importance of specific tasks, fostering a shared understanding of project priorities. In essence, the incorporation of graphical indicators for date constraints is not just a tool for simplification; it’s a strategic asset that enhances your ability to manage, adapt, and communicate the complexities of project timelines more effectively. Step 1: Define a Custom Field for Date Constraint Check Step 2: Crafting the Formula for Date Constraint Check Step 3: Configuring Graphical Indicators for Date Constraint Check Step 4: Implementing the Date Constraint Indicator in Views Real-World Application In complex projects or those with continuous task additions, these indicators serve as an essential tool for ensuring no task is overlooked, maintaining the integrity of your project’s timeline. The additional layer of date constraint indicators aids in highlighting potential rigidity in your schedule, allowing for proactive adjustments. Conclusion For the seasoned project manager, such customizations in Microsoft Project not only streamline the management process but also enhance the accuracy and efficiency of project execution. By implementing these graphical indicators, you ensure a proactive approach to task management, keeping your projects on track and aligned with planned timelines and dependencies.

2023 MPUG Vendor Showcase Project Widgets

Project Widgets: Where There’s A Widget, There’s A Way: Overcoming Real Life Project Management Challenges

Are you looking for a widget or add in that will support your MS Project reporting, scheduling, risks and issues? Look no further!

Where There’s a Widget, There’s a Way: Overcoming Real Life Project Management Challenges

Discover how Project Widgets' Task Validation Widget extends Microsoft Project's capabilities to overcome real-life project management challenges. Join our webinar for a real-life case study and Widgets Bonus Pack!

Best Kept Secrets for Microsoft Project Governance – PMO Edition

Best Kept Secrets for Microsoft Project Governance – PMO Edition

Have you wondered about the governance features included in MSP? This webinar teaches you tips and tricks for using MSP to your advantage!

Microsoft Project Visualization

WebNLearn: Now You See It! Microsoft Project Visualization Magic

In this webinar you will learn techniques for how to best display data using Microsoft Project including gantt charts, fonts, styles and more!

Exploring VBA Part III: Using VBA to Integrate Microsoft Project with other Microsoft Tools – Transcription

Please find below a transcription of the audio portion of Ira Brown’s session, Exploring VBA: Microsoft Project’s Macro Language Part III, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the recording of this webinar at your convenience. Melanie: Welcome, everyone. We are going to start our session today. It’s Melanie here with team MPUG. Welcome again to VBA. Today we are doing part three, Integration with Other Applications, which is something all of our MPUGers are always looking for. I’d like to welcome back our VBA widget and solution expert, Ira Brown, to continue this excellent series. If you missed part one or part two, I’ll chat out the link to the recordings, and I’ll also send it after in an email. You can also find these sessions and 600 other hours of training and courses on demand on mpug.com anytime. If you have questions today, please type these into the questions chat window, and we will hold those to the end to present to Ira. We’ve gotten some great questions from last two sessions, so I’m looking forward to that. Again, today’s session is part three of the three part series. If you missed one and two, they’re available on demand on mpug.com, just go to our homepage and click on on demand videos and I will send those out to you as well. I am now going to hand the presentation over to Ira. Ira Brown: Thank you very much, Melanie. I’d like to welcome everyone to part three of our series called Exploring VBA: The Microsoft Project Macro Language. So thank you all for attending today’s session and hopefully many of you attended the previous two sessions as well. As Melanie mentioned, they are available on the MPUG website, if you want to get a refresher or if you didn’t have a chance to attend. This will be the final part of the series and this is where it all comes together and we have probably the most fun. That is, we’re going to take a look at how we can integrate Microsoft Project with other office applications. What if you wanted to create some solution to create an automated status report in Microsoft Word by pulling in tasks from your project, or what if you wanted to create some PowerPoint presentation with the timeline in your project? So all kinds of interesting examples we’ll look at together, and I think that you’ll find these to be really useful. Ira Brown: Just a quick reminder about Project widgets. We are a Microsoft Project gold partner specializing in project and portfolio management. In fact, that’s really all we do. Everything we do is based on Microsoft Project. We specialize in creating Microsoft Project custom solutions, which frequently turn into our widgets. As you probably learned from the previous two sessions, that even something like recording a macro can serve as the foundation for a new widget or something much more sophisticated than just what that macro recording provided. So with that, let’s get into today’s session. Ira Brown: Okay. So in today’s session where we’re going to be integrating Microsoft Project with other office applications, the first thing we’re going to take a look at is how do we go about exporting our project to Microsoft Excel? Then we’ll take a look at how we can integrate Project with Microsoft Word by creating an automated status report. And then finally, we’ll see how we’re able to integrate Microsoft Project with PowerPoint where we could take our timeline that we have in Microsoft Project and actually create a PowerPoint presentation that includes our project timeline. Ira Brown: So just as a run reminder, we’re going to be giving away two free copies of our Driving Path Widget at the end of today’s session. Just like we’ve been doing in all of these other sessions, today we’re going to give away two more copies. That’s absolutely free. All you have to do is send me an email at the end of the session, and then you’ll be entered into that drawing. Then also, all of the macros that we’re going to be creating during today’s session, I will make available to you, including the presentation. If you just shoot me an email, I’ll give you that information at the end of the presentation and I’ll be happy to send you everything that we did today. Ira Brown: Okay. So just as a bit of a refresher, we talked about this during the first session about why would you want to create a macro? What’s the benefit? The idea would be that it’s really helpful if you have activities you perform in Microsoft Project that tend to be time consuming, error prone and repetitive, those are perfect candidates for creating a macro. You create the macro, very often you can just record the macro and maybe with a little bit of editing, you can get exactly what you want. You can even assign that macro to a button in the ribbon in Microsoft Project so that when you want to run it, you just click the button. You can share your macros with other people within your organization. Especially if you’re running Project Online, you have the ability to put your macros in what’s referred to as the enterprise global, and then they become available for all users of Project Online. Ira Brown: Today we’re going to be focused on standalone Microsoft Project. That’s what I’ll be using, but everything that we’re talking about today can also be done in Project Server, as well as Project Online and basically any version that you’re running. So the first example, we’re going to build ourselves a widget, and the first example is exporting a project to Microsoft Excel. I guess the question I always like to ask is, well, what’s the business reason why you might want to do this? Well, frequently, I hear from a lot of my clients that not everybody has Microsoft Project on their computer or not everyone has access to the Project web app or maybe people just don’t, or they’re not as comfortable using Project. Maybe you want to be able to share your schedule, but you want to give someone an Excel copy of it just to be able to view the schedule. That’s why you might want to be able to do something like this. Ira Brown: You remember how we said that, well, the first thing is in order to build any macro or automation, you’d have to say, how would I accomplish this manually if I had to do it manually? You need to understand that first and then you can automate it. The way we would go about doing that is, and I’m going to demonstrate it for you right now, I’m going to go ahead and start off by opening up one of the templates that comes with Microsoft Project, this software development plan. So we’ll just create that. I’ll just close that up a little bit there, and then we’ll just expand out all the tasks. So we go to View, Outline, show all sub-tasks. Ira Brown: Suppose I wanted to be able to provide someone with a copy of what we’re looking at right now, basically this schedule, but I want to be able to give it to them in an Excel file? Now, the Excel file is not going to display the Gantt bars. Although I should say that I have come up with some techniques to emulate a Gantt chart in Microsoft Excel. Because if you think of maybe a time scale as a bunch of columns and then maybe you can shade some of the cells. So you could emulate what a Gantt chart looks like, but that’s not what we’re going to try to do today. Ira Brown: I just want to keep it pretty basic. We want to be able to take this project and we want to be able to export it to Excel. I’m going to walk you through the steps to do, but as I walk you through the steps, I am going to first turn on the macro recorder so that everything that I’m doing is being captured by the macro recorder. Remember how we do that? We click on the View tab here, then we go to Macros, drop that down and we say record macro. I’m just going to call this, ExportToExcelDemo. So notice how I’m just putting all those words together, no spaces. I’m capitalizing the first letter of each word there just to make it more readable. Now I click Okay. Our macro recorder is now running. Ira Brown: So how do we go about what’s the first step in exporting this to Excel? Well, it turns out there is a built-in feature in Microsoft Project that lets you export your project to Excel. We’re going to take a look at that right now. We’re going to click on File, Save as, I’m going to go ahead and browse and then I’ll go to the folder where I’d like to be able to save this. I’m just going to go to my C drive and I have this data folder here. Notice where it says Save as type. Well, by default, it wants to save this as an MPP file. However, if I drop that down, notice there’s lots of other options here. Some of these you may not have seen before, and I think it might be worth your while to check out some of the other formats that are available. But we’re going to pick this one right here called Excel Workbook. I’m going to go ahead and give this a name, I’ll just go ahead and call this Export Project MPUG, and then we’ll click Save. Ira Brown: When I do that, it knows I want to save this as an Excel file. So it brings up what’s called the export wizard. This is a built-in feature in Microsoft Project. So I’m going to go ahead and click the next button, and this is going to step me through a series of steps for exporting my project to Excel. It says, what is the format of the data? I’m just going to go ahead and say, I just want to export the selected data, meaning the data that’s in my project, click Next. Then it says, do you want to create a new map or use an existing map? A map is something we can create in Microsoft Project that if I want to be able to do this on an ongoing basis, I can utilize this map over and over again. Essentially the map app says, which fields would you like to be able to export from Microsoft Project to? In this case Excel. Ira Brown: All right. We’re just going to create a brand new map, so the default here is fine. I’ll click Next. Then it says, what type of data do you want to export? In our case, we just want to export tasks. So I’ll check that off. And then it goes on to say, if you’re going to Excel, do you want to also include the headers? In other words, the names of the columns, the names of the field. Yes I do. So we’ll keep that checked, and I’ll just go ahead and click Next again. All right. So this screen here says, okay, well tell me a little bit more about which fields you’d like to be able to export. This is where I could go through and basically just pick the fields one by one. Just by hitting my dropdown here, I see a whole list of fields and I can pick the ones I want. But there’s actually an easier way to do this. Ira Brown: Remember, when you’re in Microsoft Project and when you’re looking at the Gantt chart view as we were a moment ago, well, that structure that you see on the left side, that tabular structure is actually referred to as a table in Microsoft Project. In this case, it happens to be called the entry table. So when you’re creating a map, instead of having to specify each individual field one at a time, I can just say base on table. I’ll click that button, and then it says, which table would you like to base the map on? So I’m going to pick the table called Entry. That’s just a list of all the tables. I’ll pick the one I want and then click Okay. Notice what happens, it brings in all of the columns that are defined as part of that table. That really will save you a bunch of time when you’re doing this. Because that’s exactly what I want. Ira Brown: Now, there are a couple of additional columns that I want to add to this table and there’s also a couple that I actually don’t need. I’m not going to bring over the indicators column since that doesn’t do me any good in Excel. That’s only, it’s that graphical indicator that you have in Project. That won’t come over the way we want it to. So I’m going to just hit my delete key here and that’ll get rid of that one. Also, task mode that tells me if a task is auto scheduled or manually scheduled. I don’t really need that one. So I’m going to delete that one. But I do want to bring over name, duration, start, finish, predecessors, resource names. I’m going to add two more fields here at the end. I want to bring over whether or not this is a summary task. Ira Brown: There’s a field in Microsoft Project called Summary and it’s really just a yes/no field that’s set automatically. If a task is a summary task, then Summary gets set to yes. And if it’s not a summary task, then the field called Summary gets set to no. So I want to bring that over and I’ll show you why in just a minute. I want to bring over one more field and that field is going to be called Outline Level. Notice if I just type in the first letter and then drop it down, it takes me to the Os and there’s that field called Outline Level that I want. Here’s a little preview of what the data will look like. Shows you the first couple of rows just to make sure that, yeah, that looks pretty much like what I’d expect. Now I’m going to go ahead and click Next, and it asks me, do I want to save my map for future use? In this particular case I don’t, so I won’t save the map. Now I’ll just go ahead and click Finish. Ira Brown: All right. Hopefully if that worked correctly and did what it was supposed to do, I would’ve just created an Excel file. Now I need to just go to the location where I told it to create the Excel file. So I’m just going to go to my data folder here on the C drive, and there’s the file I just created called Export Project MPUG. I’m going to go ahead and open that up, and there it is. That’s exactly what I asked for almost. All the data’s there, but it doesn’t look all that great. Does it? What I would like to be able to do is I’d like to make this look a little bit more presentable. I want it to look a little bit closer to the way Microsoft Project looks. I want to be able to preserve some of that formatting that we have in Microsoft Project. Ira Brown: Looking at this, well, what are the things that are wrong with this? Well, for one, the column widths are not what they need to be. We need to increase some of the column widths. How would we do that in Excel? Well, I have a pretty good idea of how to do that, but before I do any of these things, I’m going turn on the macro recorder in Excel. Remember how we recorded macros in Project? Well, we can record macros in Excel and it’s works the same way. What I’m going to do is I’m going to click the View tab. Sure enough, there’s that Macros button again. We’ll drop it down and we’ll say record macro, and we’ll give it a name. So how about if we call this something like Format Excel MPUG. Click Okay, and now the macro recorder is going. Ira Brown: The first thing I want to do is I want to adjust the column widths here so that everything is as big as it needs to be. So how would you do that manually in Excel? Remember the macro’s recording. So anything I do manually, it should pick up. I’m going to do that by clicking on this little corner button right here to select the entire worksheet. Then I’ll just double click right on top of one of the borders of a column. When I do that, it does what’s called a best fit. Notice how that every column now has been adjusted based upon the data that’s in that column. So it’s already looking much better. What else might I want to do? Maybe I want to bold the column headers. I can do that by selecting row one and then just clicking the Bold button. Right now. We’ve made that look a little bit better. Ira Brown: Notice there’s not really any hierarchy here. Everything is just this flat list. I’d like to try to deal with that in some way. Also, my summary tasks are not bold. But remember I said how we’re going to bring over the field called Summary and we’re going to bring over the field called Outline Level? Well, we brought those over because they are going to help us right now, do what we want to be able to do with our macro. But again, let’s see how we would do it manually. Essentially, any place where summary equals yes, I want to make the task bold. So let’s just do that manually for now. Let’s just click on Software Development. I’ll just focus on the task name column, and then I’ll just click the Bold button again. That looks pretty good. Ira Brown: Now, this scope, this is also a summary task. So I’ll make that bold. However, in Microsoft Project, that is actually indented underneath the Software Development, which is the project summary test, that top level, row zero. I’d like to indent that. Well, again, Excel has the ability to indent. Notice right there, there’s a little indent button. I’m just going to go ahead and click that, and notice that it just indented. Now the next task here is a sub-task that doesn’t need to be bold. It’s not going to be a summary, because summary equals no here, but you see how the outline level says two. Well, that outline level two tells me how many times do I need to indent the task to give it that appearance that we want? Well, in this case, it’s twice. So I’m going to hit the indent button twice. One, two. Ira Brown: You see how we’re now starting to have more of that outline look that we want. So I would imagine that if I were to go through every single task in Excel, and if my logic said something like, well, if I’m on a summary task, let’s make it bold. Then based upon this outline level, whatever that number is there, that’s how many times I want to indent the task name. That’s the approach that I think ought to accomplish this. So, I’m not going to take it any farther in terms of the macro recording, I’m going to stop the recorder and let’s take a look at the code, that macro code that was generated in Excel. Basically, everything I’ve done so far has been captured, hopefully. So let’s click on View and then go back to Macros and then say stop record. Ira Brown: Now we want to take a look at the macro code. Remember how we do that? We click on Macros again. It’s a little bit different in Excel than it was in Project. I don’t have the ability to go directly to the Visual Basic Editor here, but if I choose View Macros, here I can see the macro that I just recorded. And now if I click Edit, that will take me to the Visual Basic Editor. Here it is. I’ll maximize that screen. You can see right here, this is the macro that was just created. Now, let’s see if we can analyze some of this a little bit and see what it’s doing. Some of these lines here, we probably don’t really need. So it looks to me like this cells select, I may not need that one. So I’m probably just going to go ahead and delete it. Now, how do I know that? Ira Brown: Well, you’ll get more familiar with what can be deleted as you work with this over time. But remember, I was clicking around in Excel and every time I clicked in the worksheet somewhere, it generated that line of code that said cell select. So in this case, I’m going to get rid of it. This line right here that says cells.entirecolumn.autofit, well, what that is going to do, that is the line of code that actually did that best fit that made everything fit the way it was supposed to. In other words, adjusted all the column widths. That looks like it’s pretty important. Then notice, it says rows one, column one select. Remember I selected the first row. And then once I selected the first row, it says, now take that selection and let’s make the font bold. That’s what this line of code here does. Selection.font.bold equals true. Make that line bold. Ira Brown: Remember how we found that helpful last time when we put some comments in? Let me do that here also. Remember, I’ll just put an apostrophe character in and I’ll just say, best fit all columns. Notice it makes that green text. That’s just the way you can comment this to make it easier to read, to make it more understandable, especially when you’re looking at it down the road. This is going to say something like, make the first row bold. Now the next line here says, select cell B2. So B2 was that name column. Let me just drag this over a little bit so you can see it. When I clicked on Software Development, that was me selecting B2. And then what happened after I selected that cell? Well, again, selection.font.bold equals true. That made that cell bold. Then didn’t I do that again for B3? So it looks like that is the technique, where I can basically just select what I want. Then if I want to make it bold, this line here makes it bold. Okay. Ira Brown: Now let’s take a look at this next line. Selection.insertindent with a one. Well, remember how I indented this scope and I hit the indent button one time? Well, that’s the line of code that got generated. Then I went down to B4. Remember how I indented that twice? Well, it’s interesting what the macro recorder gave us. It actually gave us indent once, indent once again. But it turns out, once you dig a little deeper into the macro language, that this one here is actually what is referred to as an argument. What that means in this case, then I’ll just backspace over, hit my space key. Well, actually it doesn’t… Nevermind. But once you get into it, if you actually look at the documentation, you’ll discover that that one there actually represents the number of times that you want to indent. So instead of having to repeat this line twice, I could just change that to a two. That tells it to indent twice. Ira Brown: Well, isn’t that two really, can I get that number from the field called Outline Level? Because that’s tells me how many times that I want to press the Indent button. So let me just go back to VBA. It’s just here at the bottom again. All right. So I think I have… Oops, sorry. I’m in Microsoft Project. I need to be in Excel. I think I have a pretty good framework for what I want to be able to do here. So now what I would like to do is, using the code that I recorded as the framework, let’s see if we can make that a little more sophisticated to get the automation to work exactly the way we need to. Just in the interest of time here, I’ve actually already created that routine. I’m just going to copy it, and I am going to paste it on in here, getting rid of the one we did before. This is actually what we want. I’ve put some other logic in here. Ira Brown: I’m going to walk you through this right now, just to help you understand exactly how this works. Actually, before I do that, I guess it would make sense, because I’ve already made a lot of changes here. I’m wondering if it’s going to let me undo these. Let’s see. I might be able to get away with, say undo, basically everything that we’ve done. There we go. Hey, I got lucky. All right, so this is how it’s started. When we first opened Excel, this is what we saw. Let’s take a look at this routine here now and let’s see if we can figure out what each line is going to do here. I think it will seem quite logical to you as we go through it. So remember, whenever you create one of these macros, remember it’s also referred to as a subroutine, it starts off with the word sub and then you give it the name that you want. Ira Brown: In this case, we can call it whatever we want. I’m going to call this Format Excel. Now, these here, these are referred to as our variables. I have a few variables I had to create. In fact, all three of them are integer variables. Just whole numbers and [inaudible 00:27:48] just as a way of declaring those variables. Then this is the name of the variable, Int Row. So since it’s an integer, I started off with that prefix, INT. Int Indent Levels is another one I created, and then Int Last Row. Okay. So let’s step through the logic here. The first line here where I have a comment, it says, get the last row in Excel. So why do I want to get the last row? Well, I want to be able to essentially loop through this Excel file here row by row. Ira Brown: How do I know how many rows there are? Well, I can scroll down and I can see in this case, there’s 88 rows altogether. But I need to be able to figure that out within the macro language. So it turns out this is the line that will tell us that. Active workbook, that’s referring to the Excel workbook, .worksheet one. Well, worksheet one is this particular worksheet. That’s the one we’re referring to. And then there’s this object referred to as used range. In other words, like in Excel have thousands of rows, but most of them are blank. What is the used range? What are the used rows? Well, that’s what used range tells us, .rows.count. This is basically going to tell me how many rows are actually being used in Excel. When I get that number, I’m going to assign it to that Int Last Row variable. So now I know how many rows I have. Ira Brown: This section right here selects the first row where we have our column headers. So rows one, one select. While it’s selected, let’s make it bold. We already saw that one. I added one more line here, not only do I want to bold it, but I’d like to actually increase the font size a little bit just on that first row. So selection.font.size equals 12. This is where we can do a best fit for the entire worksheet. It looks like I actually did need that line here, cells.select. So that probably wasn’t a good idea that I deleted that before, but it’s back again. That selects the entire worksheet and then does that best fit. So it makes all the columns as wide as they need to be. Ira Brown: You can see how these comments are really helpful. Now, notice the next thing we’re going to say. Loop through each row in order to bold the summary tasks and to indent the sub-tasks. So this is what is referred to as a loop, specifically a four next loop. It is a way of iterating through each row within the Excel spreadsheet, one row at a time. And while you’re processing that row, let’s do some things to it. Well, for example, starting with row two, it’s going to say, let’s determine the number of times we’re going to need to actually click that Indent button. Well, notice what this says, int indent levels, that’s that variable is going to be equal to the cells, meaning looking at particular cell. Ira Brown: Here’s the particular cell to look at. Well, row says, remember, that’s the row, that’s the particular row that I’m processing. In this case, it would be equal to two when we’re processing row two. And then this nine here represents the column that I want. So what is in row two column nine? Well, let’s go take a look. There’s row two, and let’s just count our way over one, two, three, four, five, six, seven, eight, nine. Notice it’s the outline level. That’ll tell us how many times we need to indent. Then the next line says that if that number is greater than zero, because remember, the first row here which has a zero in it, we can’t indent zero times. That doesn’t make any sense. So we have to have this little extra check here. Ira Brown: I’ll put a comment in that if the int indent levels is greater than zero, then indent the name of the task. That’s what this is doing here. If that is greater than zero, what does it say to do? Well, again, in this case it would be row two. And then in column two, well, that’s where we have the name. Perform that indent command, and then how many times should it do it? Well, int indent levels tells us how many times the indent button should be pressed essentially, just like when we do this manually. Then this section right here says that if the column called summary, remember we brought over summary, if that equals yes, then make the name column bold. We basically repeat that for every single row in Excel. Then when we’re done, it just says, select the beginning cells, one, one, that’s basically the first row, first column, select it and then we’re done. Ira Brown: What I’m going to do here is I am going to do what’s referred to as stepping the code. Meaning that I can run this one line at a time. Just you can see it through its thing. So I do that by pressing the F8 function key, and notice it starts working its way through. I’m going to run that line of code right there. And if I hold my mouse over int last row, see how it shows me 88. That’s how many rows there are. Let’s keep going. We’re going to select the first row. Notice we can see on the left, exactly what’s happening. Let’s make it bold. There we go. Let’s increase the font size, made it a little bit bigger. Ira Brown: Let’s select the entire worksheet and let’s do that best fit. There we go. Now we’re going to loop through each row in Excel. We get the indent level. In this case, it’s zero. So if it’s zero, remember we don’t want to actually do any indent. That worked the way it was supposed to. And now if that summary is equal to yes, let’s make it bold. So watch what happens now in row two in that task name column, see how we just made it bold. Now, go back and do the next row. Work our way through. Now, watch it indent scope. See what just happened? Should we be bolding that also? I think, yes, let’s bold it. So it’s really doing exactly what we want. Ira Brown: This is pretty cool. Now we don’t have to do it one line at a time. When you actually are ready to run the whole thing, we can just press the F5 function key or press this button right here, which is the same thing. That’s what I’m going to do and watch what happens. We’re done. So notice it just made the formatting in Excel exactly the way we wanted it to look. So doesn’t this look a whole lot closer to what your Microsoft Project schedule look like? If someone wanted to look at your schedule in Excel, well, this is a pretty good thing to hand to them. It has that nice format to it. So you can see that we have this macro language in Excel, and it works very much the same way, except instead of working with tasks and resources and things like that, you’re working with rows and columns when you’re in Excel, but the basic techniques are the same. Ira Brown: All right, let’s take this one step further. So I’m just going to go ahead and close this. Let’s go back to Microsoft Project and it gets even better. In Microsoft Project, I have created a macro and I’m going to share this with you. Let’s go to the macro, the Visual Basic Editor. Remember we click on macros visual basic, and I will show you here in this MPUG part three. I created this routine called Export to Excel, but notice that everything is now running from within Microsoft Project. What I’m able to do is I can take that exact Excel macro that I created in Excel. I can copy and paste that Excel code, that VBA code from Excel into Microsoft Project. I can actually do all of that Excel formatting in Microsoft Project. Essentially Microsoft Project is controlling, is performing those commands on Microsoft Excel. And it all could be in one consolidated macro. Ira Brown: Let’s take a look at it real quick here. Notice this says export to Excel. Well, remember when we created that map. Remember, this part of the code was created in Microsoft Project, where we told Microsoft Project which fields do we want to be able to send over to Excel. Here’s the line of code here where it says file save as. I’ll just put a comment there, save Excel file. So all of that happens on the project side. Now this is where the Excel portion of it kicks in. This line of code right here that says create object, “Excel.application,” that is the technique for programmatically starting the Excel application just as if you opened it manually. This is how we can programmatically start Microsoft Excel. Notice that we have this line that says set OBJ Excel equal to that. Ira Brown: Well, OBJ Excel is what’s referred to as an object. In this particular case, the object is the Excel application and I’ve named it OBJ Excel. That’s how I can programmatically do these things in Excel from Microsoft Project. So let’s just take a look at what some of these things are doing. Well, this is how we can make Excel visible, because it is possible to do everything that we just did, but Excel doesn’t even have to be visible to the user. But in this case, I would like it to be visible. So we say .visible equals true. This is where we open the Excel file. Now, once we have an open, this code here should look very familiar to you because this is the exact code that we just reviewed over on the Excel side. The only difference is that every line had to be proceeded with OBJ Excel. Ira Brown: Remember how we selected the row and we made the font bold and we made it 12 points? Well, over on the Excel side, it just said, selection.font.bold. I had to put here OBJ Excel.selection, OBJ Excel. Every line of that VBA code has to have the prefix OBJ Excel. When you do that, it knows that that command runs against Excel rather than Microsoft Project. But other than that, it’s the exact logic that we just reviewed. Notice how this is called Export to Excel? Let’s remember that name. But what I did previously was I assigned that to a ribbon button. So let me go ahead and close this. There’s our ribbon that we created last time, MPUG, and you know what, maybe I did not assign it to the ribbon button. Ira Brown: Well, that’s okay. Let me do it right now. I’m going to right click, customize the ribbon. Remember, this is just a review from last time. This is exactly what we did last time. I’m going to choose Macros here, Export to Excel. That’s the one that I want. I want to add that to my list of widgets here. Remember that widgets group. So I’m going to click Add, and let me right click on Export to Excel. I’ll rename it, give it a little bit of a friendlier name. Maybe I’ll call it Export Project to Excel. And I give it a nice button. Like maybe that guy right there, click Okay, click Okay. And there it is, Export Project to Excel. Just want to make sure Excel is not running. It does not appear to be. Let’s give this a try. Ira Brown: I’m going to click that button right here and let’s see what happens. So what would we expect to happen? Well, the first part of the code is it’s going to take the project, save it to Excel, then start up Excel and then do all that really cool formatting. So in one click of a button, I’m hoping that we get everything that we need. Let’s give it a try. Let’s click the button. Here we go. Starts up Excel, does all my formatting. Done. How about that? That was pretty easy. That’s a perfect use for [inaudible 00:41:45]. Anytime somebody ever says to you, “Hey, can you give me a copy of your project in Excel?” You can just say, “Oh, sure, no problem.” You can click your button here and you send them the Excel file. It’s easy as that. I really like this example because I think you’d be able to put it to good use. Ira Brown: So, we have some time left here. Let’s take a look at a couple of other examples. This time we’re going to see how we can do something with Microsoft Word and how we can also do some automation with PowerPoint. The first Microsoft Word example we’re going to take a look at is we are going to create an automated status report. I’m going to go ahead and open up this project here called Status Report Widget Demo. By the way, again, I’m going to be sending this to you. All you have to do is just send me an email and I’ll send you everything that we’re doing here today. Let me go to VBA for a minute, click on View, Macros, Visual Basic, and on top here, I’m going to just select the macro called Show Status Report Widget. Ira Brown: When I run the line Show Status Report Widget, it actually says, okay, show me the form. So this is actually something that we do within a form. Just like we did last time, this is a little bit more sophisticated than the one we did last time. So we’re going to be able to run this form and the user will be able to supply a date range for the report. So we want to start our report on this date and the report on this date. I can put in an overall status, red, green, or yellow. I can specify who the project manager is. And then I click Okay, and that actually runs all this code right here. So we’re not going to go through every single line here, but just generally, what we’re doing is we’re going to launch Microsoft Word programmatically. Ira Brown: This is how we do it. One of the things I just want to point out is that another technique that you can use to make it even easier to create this VBA code is if you go to the Tools menu, once you’re in VBA, and choose References. Notice how I can specify a reference to Excel or Word or PowerPoint. They’re all listed here alphabetically until I check them. Then it moves them to the top. Well, when you create that reference, it makes it even easier to write the code because it’ll give you suggestions as you’re creating your code. I just want to mention that. In this case it says, start Microsoft Word, make it visible, open up, we’re going to start off with a Word template that I’m going to show you in a minute here. That’s going to be the template that we’ll use to build the status report. Ira Brown: We’re going to utilize this technique in Word. If you’re familiar with bookmarks in Word, bookmarks are really handy to go to a particular location in a Word document. Let me go ahead, before I keep going here in the code, let me show you what that template looks like. So I’m going to go to my data folder here, and I’m going to open up this project, WidgetsStatusReport.doc. This is essentially what the format of the status report is going to look like. It’s going to have several different sections in it, like who is the project manager? Sorry, who’s the project manager? What’s the name of the project? What’s the reporting period? What is the overall status of our project? And then we have a section for key tasks and milestones, tasks completed this tracking period, tasks that were scheduled to be completed but did not. Ira Brown: We could programmatically produce this status report by taking information from Microsoft Project and actually just writing it to this format that we have right here. One of the techniques that we use, how does it know where to write the data to? Well, in Microsoft Word, we have something called a bookmark. If I click on Insert and then bookmark, notice I have several bookmarks already listed here. So for example, see where it says project manager, if I were to click, Go To, watch what happens to my cursor in the Word document. I’ll click Go To and let me just click Close here. My cursor is now positioned right next to project manager. I use these different bookmarks strategically. So I say, before you write the key tasks and milestones to this table in Word, position yourself right there, and then write in the name, write the duration, write the start, write the finish, and write the percent complete. Ira Brown: Then to go to the next row, I have to send another tab command that will add an additional row here, and then I write the next task and the next milestone, et cetera. I do that for each section. So we go back to project. That’s what all of this code is doing, and it’s all documented for you. It goes to the bookmark that we specify, and then it does this thing called OBJWord.selection.typetext. And it’s as if you typed the text, but in this case, it’s the project name or it is the duration or start date or finish date or percent complete. Get the idea? So this will produce our status report for us. Let’s give it a try. Let me close this code. Let me close the Microsoft Word template. I am now going to click the Status Report Widget button, and there’s our form. Ira Brown: Let’s put in the start date. In this particular case, I want my start date to be the beginning of this week. That would be 9/27. Let’s put that in there, 9/27/2021. The report finish date, I’ll use 10/3 for that, 10/3/2021. Overall status, I’ll select, let’s give ourselves a green status here. And then project manager, let’s see, project manager, I’ll make myself the project manager. I wrote Brown. Okay. Now, I’m going to click Okay, and let’s see what happens here. I click Okay, it will programmatically launch Microsoft Word, and right before your eyes, it builds the status report. So Project is now controlling Word, and it’s writing this information to the key tasks, to the tasks that were completed this tracking period and the tasks that were supposed to be completed but did not. So notice that here, percent complete says 100% they were completed. Ira Brown: Notice the date range falls within our tracking period. Here are some more tasks that we’re supposed to finish, but they’re not 100%. So there you go. We just build ourselves a status report. You can see there’s the name of the project, there’s the project manager, there’s the reporting period, and there’s our green. We shaded that so green for our overall status. This is pretty cool. This is doing exactly what we want it to do. Let’s take a look at one more example, and then we’re going to open it up for some questions. So, the last example is going to be, I’m just going to close PowerPoint here, because I don’t want PowerPoint open in this case, we are going to programmatically control PowerPoint. What I want to do is I want to create a presentation in PowerPoint to take this timeline that I have here at the top of this view. Ira Brown: I want to be able to bring that timeline over into PowerPoint. So let’s take a look at VBA for a moment. View, Macros, Visual Basic. And then I’m going to show you right here, this routine called add timeline to PowerPoint. So just like we started Excel, programmatically and Word programmatically, this is how we start PowerPoint programmatically, pretty much the same concept. But before we start it, these lines of code here actually copy the timeline. So it basically puts that timeline in the Windows clipboard. Starts PowerPoint, makes PowerPoint visible. It opens up my presentation file. So I’m basically using a presentation as a template. Let me just go to that folder. I’ll show you that real quick. This is what the template looks like. It’s just the project widgets logo at the top and essentially a blank slide. And then once we open that template, we then do a paste special. Ira Brown: Essentially, we’re doing a paste of the timeline as a bitmap image. That’s what this does right here. Then we position it where we want it. We’re sending the left and the top so that it’s centered on the slide. And then finally, we actually run the slideshow. Shall we give this a try? Let’s go back to our ribbon. Let’s click Export Timeline to PowerPoint. Here we go. Click the button, fire up PowerPoint, bring over my timeline, put it in the slide, go into presentation mode. And there it is. So all that was done programmatically. I realized this is a very simple example or fairly simple example, but basically anything that you can do manually in PowerPoint for the most part, you can do programmatically. That’s the good news. The bad news about PowerPoint, it does not have a macro recorder. So you actually have to refer to some online documentation. Ira Brown: When I click on Macro, see there’s no recorder here. I can create my macros if I know what I’m doing. But the good news is once you get good at this with Project, once you get good at this with Excel, you can apply those same skills to PowerPoint. Instead of working with worksheets or tasks in PowerPoint, you work with things like presentations and slides and fonts and things like that. But again, no recorder, but you can still automate pretty much anything that you want in PowerPoint, just takes a little bit more work to do it. So with that, folks, I think we have completed the material. Let me just remind you that we’re going to open up for questions in a second. But let me just remind you that if you, I’ll just put this slide up here real quick, if you send me an email, I’m going to put my email address up right now. Ira Brown: You will be entered into our… Oh, looking at it. Somehow the quantity just went from two to three. Three free copies of the Driving Path of Widget. And then all you have to do is send me an email. My email address is ira.brown@projectwidgets.com. I encourage you to go out to our website, you can learn a lot about our services and all the different widgets that we have available. There’s even some free ones you can download as well. If you’re interested in seeing a demo of any of our other widgets that are on our website, please reach out to me. I’d be happy to arrange a time to meet with you. If there’s any that you’re interested in, I’d be happy to give you a demo. So again, thank you so much for joining the session today. Melanie, do we have any questions? Melanie: Ira, we do. Ira Brown: Okay. Melanie: Steve, “Would it be easier or harder to create a table in Excel to refer to column names rather than numbers?” Ira Brown: Yeah, I think that’s a good technique. Because oftentimes, if you’re just talking about column one, column two, you can easily say, well, A is one, B is two, et cetera. But there is a little technique that I find pretty useful in Excel, that if you go into your File options in Excel and then click on Formulas, you can see where this is option that’s unchecked, R1, C1 reference style. If you check that, notice what will happen is instead of seeing ABCDE at the top there, you actually see the column number. So I find that pretty useful if you’re trying to figure out what column to use. But yes, if you create a table and you can refer to that column by a name rather than the number or even the letter, then that could make this even easier. I agree. Melanie: Super thank you. Thank you, Ira. Thank you, Steve. So from Teresa, can you change the date from text to an actual date format? Ira Brown: Yes. That’s a great question. Well, the answer is yes. However, it’s maybe not quite as easy as you’d like it to be. Let me open up this Excel file for a second. This is the one we used. Notice that when we do our export, this is how it brings it over. Now, there is that technique in Excel where you can say, let’s apply a formatting to this. But the problem is that even if we format this as a date, I’ll pick date here and then I’ll say, let’s show it like that for example, it doesn’t do it because it thinks this is text. So what I’ve had to do is I’ve had to strip out the time element here. Once I get rid of the time, like I get rid of where it says 8:00 AM, then all of a sudden Excel sees it as an actual date. Ira Brown: What I’ve been able to do is, there’s lots of ways of doing it, but an easy technique might be to do a find and replace. Because most of what we’re looking to replace you can see is either eight o’clock, one o’clock or 12 o’clock. Well, there’s a three o’clock there too. So this is not the perfect solution. There’s other techniques as well. Well, you can look at maybe the number of spaces and basically you just have to do some string manipulation here. But if I search for space eight o’clock, space am, and replace it with nothing and then do a replace all, you see how I’ve just fixed the problem for 20 of them, and then I’d have to do it for the other time. So there are other techniques as well, but essentially once you converted from text to a date, then you can apply whatever format you want. Melanie: That was a great question. Okay. So from Mike, he wants to know, can you automate project data into Visio and then Gantt chart template? Ira Brown: Yes. Maybe in a future session, we’ll have one that actually explores how VBA from Project can integrate with Visio. The bottom line is, I don’t know if I have Visio installed on my computer or not. Let’s find out if I do. It looks like I probably do not, but Visio also has VBA built into it. I don’t remember if it has a recorder or not, but it has VBA. So you can programmatically manipulate and integrate with Visio as well. Melanie: On that note, Ira, I have had people asking as we’re in the session, can we do another session, a full course with you even deeper on this subject? So I will be harassing you about that. Ira Brown: Want to do a part four and a part five, you think? Melanie: We’re just going to keep going. Ira Brown: All right. Melanie: Another question from Dan, in the PowerPoint code, you declared OBJ PowerPoint as the PowerPoint application rather than as an object as you did in the Excel code. What is the difference between the two approaches? Ira Brown: That’s a great question. Well, remember how I talked for a moment about setting a reference when you go to Tools references, and then I picked PowerPoint? If I set a reference to PowerPoint, then I can declare, I can start the application differently. Instead of saying, create object. I can actually say equals new PowerPoint.application. It basically does the same thing. But because I’ve declared an object, sorry, because I declared a reference to PowerPoint, now as I am typing my code, like if I say OBJ PowerPoint, I hit a period here, see how it gives me these choices. By having the reference, it gives me all the possible commands that I can choose from. So if you just declared it as an object by doing create object, you don’t get that added benefit. So this would be the preferred technique. Once you get a little better at it, once you get more experience, I would say, this would be the preferred technique to make the code writing even easier. Melanie: Excellent. Excellent. That looks like it for the questions. Ira Brown: All right. Very good. Well, I know we’re at the top of the hour here. So again, I just want to thank everybody for attending the session. Again, feel free to reach out, ira.brown@projectwidgets.com. I’ll send you the presentation. I’ll send you all the VBA code we generated. Again, I want to thank Melanie for her hospitality. This was a lot of fun. Hopefully we’ll do it again sometime. Melanie: Thank you. A big, big thank you to Ira. This is an excellent series, and we will try to get him back to go even farther. A big thank you to the MPUG community. Thank you for choosing MPUG to grow your skills with today. Again, the PDU code, I have up on the screen here. Following today’s event, I will send a link to the recording. I’ve chatted those out as well. And we’re going to send a quiz, covering the entire series. The quiz is solely to test your knowledge and for your records, you can take it as many times as you want. So please save a copy of your results. If you’d like to keep it along with your transcripts, we will not save that information. So that is all up to you if you keep that. Melanie: I will also send out a super short survey shortly. Please let us know what you think. We invite you as well to join us back here for some other sessions. We’ve asked you what your frustrations are. Some of these sessions that are coming up specifically answer those frustrations. So our first session in October, that’s coming up, is about modifying custom fields and project for the web. You can do that, and we’re going to answer your frustrations in a session covering all you need to know about task types. That is pretty exciting. You can sign up for any of these today and save your seat MPUG.com/events. Again, thank you for joining us today. I will leave the PDU code up on the screen so you don’t miss that. We’ll look forward to hearing from you in our surveys and seeing you at a future event. Thank you again.

Exploring VBA: Microsoft Project’s Macro Language: Part III Using VBA to Integrate Microsoft Project with other Microsoft Tools

Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle. Event Description: The session is geared toward a Microsoft Project business user and will provide practical, real-world illustrations for solving common challenges a user faces with Microsoft Project. In this third and final session of this series, you will learn how to use VBA to integrate Microsoft Project with other Microsoft Office applications, including Word and Excel. Learning Objectives: Part 3: Using VBA to Integrate Microsoft Project with other Microsoft applications * Using VBA to integrate Microsoft Project with Excel * Using VBA to integrate Microsoft Project with Word * Using VBA to integrate Microsoft Project with PowerPoint Presenter: Ira Brown is a leader in the field of project management and a recognized Microsoft Project expert, Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their client’s unique business requirements.  This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers, by creating Microsoft Project solutions that are tailored to an organization’s unique needs.  They even have several free, downloadable widgets available on their website that you can begin using right away. Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]

Exploring VBA Part II: Using VBA Forms to Create a Custom Interface – Transcription

Please find below a transcription of the audio portion of Ira Brown’s session, Exploring VBA: Microsoft Project’s Macro Language Part II, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the recording of this webinar at your convenience. Melanie: This is Melanie here with Team MPUG. Thank you for joining us today. We are going to get started with our session today. We are covering VBA: Microsoft Project’s Macro Language, using VBA forms to create a custom user interface, part two. Again, this session is eligible for one PMI PDU in the technical category. Melanie: I’ve chatted the code out. I’d like to welcome back VBA widget and solution expert Ira Brown to continue his excellent series. We had great feedback from part one. If you missed part one, I’ll chat out the link to the recording and I’ll also send it in the follow-up email today. You can find part one and 600 other hours of training and courses on mpug.com anytime. Melanie: If you have questions today, please type those in on the questions chat window. I’ll hold those till towards later in the session and present those to Ira then. I’ll also be making a random selection today. Let’s see, we have 47 on so far. Attendee number 50 that signs on is going to get an always appropriate, always cute MPUG pug as a thank-you today. Melanie: Again, today’s session is part two of a three-part series. If you haven’t registered for three, please save your seat by registering today. You could do that from the homepage mpug.com. I am now going to hand the presentation over to Ira’s capable hands. And thank you again for joining us today. Ira, welcome. Ira Brown: Thank you, Melanie. And welcome back everyone who is joining us for part two. Let me go ahead and share my screen with you. Melanie, I think you may need to make me the presenter. There we go. Perfect. All right. So I am sharing my screen, so everybody should be able to see my PowerPoint deck here. Ira Brown: That’s kind of interesting. Yeah, it’s not letting me go in full presentation mode. Okay. Well, we’ll just have to deal with it the way it is. Ira Brown: So let me get started here. So this is part two of our session on Exploring VBA: Microsoft Project’s Macro Language. Last time we met about a week ago, we covered a lot of the fundamentals of using VBA, including how to go about recording macros and assigning the macros that you record to a button in the ribbon so that it makes it easier to run. Ira Brown: And we saw how we can go about editing those macros and even writing some very basic routines ourselves without even using the recorder. So that recording is available, in case you missed that session. And today is also being recorded. Ira Brown: I want to just mention upfront that all of the macros that I’ll be creating during today’s session, I will happily make available to you. You just have to send me an email with your contact information and we’ll get back to you with a copy of today’s presentation, along with all of the macros that we’re going to create during this session. Okay. Ira Brown: Just quick, something about Project Widgets, we are a Microsoft Project Gold Partner and everything that we do revolves around the use of Microsoft Project. And I’ve been at this now for well over 25 years, back before there was even a Project Server or Project Online, just using standalone Microsoft Project. So we could do macros way back when, and we can still use them in Microsoft Project today. Ira Brown: I work with companies and Project Widgets works with companies in many different industries, and we specialize in doing full Microsoft Project implementations, Project Server, Project Online, Project for the web. We also do consulting, training. Ira Brown: Primarily, I guess the main thing that we tend to do more often is create custom solutions for Microsoft Project, which then often turn into our widgets. And that’s what we’re going to be doing today. We’re going to be creating custom Microsoft Project solutions. And who knows? Maybe one day one of these will turn into a widget as well, that will be available for Project Widgets. Ira Brown: So let’s get right into the content for today. We’re going to start off by just going over some helpful hints for recording macros and even working with macros in general. Just a little extra information that is helpful to throw in when you’re doing this to make your macros even better and run more efficiently and that sort of thing. Ira Brown: But where the real fun begins today is when we get into how we go about creating our own user-friendly interfaces via what’s called a VBA form. We’ll be able to design a user interface and actually create a really nice, friendly form to be able to run our custom functionality. So we’re going to take a look at how we do that. Ira Brown: Once we’ve created that form, I’ll show you how you can then create a very simple routine to say, “Now go ahead and present the form to the user,” so they can actually the functionality. And how we’re able to then even assign that to a button in the ribbon as well. All right. So that’s what we’re going to be doing today over the next hour. Ira Brown: And just as a reminder, just like we did last time, we’re going to be giving away three free copies of our Driving Path Widget, pretty useful tool. If you’re trying to figure out what is causing a particular task in your schedule to be scheduled the way it is, this will locate what that driving path is and present it to you right on the screen within your project. So then you can better analyze what’s going on with your schedule. Ira Brown: So we’re giving away three free copies of that. You just need to send me an email at the end, again, requesting the content, and that will automatically enter you in this drawing. Okay. So with that, let’s get started. Ira Brown: Okay. So helpful hints for recording macros. The first one, and it’s really important to get into this habit, is to always back up your project or work with your project in read-only mode before you run any macros against it. Ira Brown: Oftentimes you may be running a macro that may adjust some of the data in your project. Maybe you’re changing durations, maybe you’re changing work, maybe you’re changing dates, whatever it may be. And especially for a brand new macro that you haven’t actually tested yet, you don’t want to actually run it against a project where you could maybe lose some important work that you’ve already done. Ira Brown: So always good to first back up your project, or the very least, open it up in read-only mode so that you’re not actually going to be making any permanent changes to your project. Okay. Ira Brown: Next is, if you are using a filter table or view as part of your macro, it is a good idea to actually build that filter table or view as part of the macro that you are recording. And we’re going to actually go through an example of what I mean by that so you can understand it a little bit better. Ira Brown: So with that, let me go to Microsoft Project. And I’m going to go ahead and open up one of the templates that comes with Microsoft Project, this software development plan. So let’s go ahead and create a new project from that template. There we go. And then we’ll go ahead and expand out all the tasks like that. Okay. Ira Brown: So one of the things that you may do from time to time is when you’re in a view like this is maybe you don’t always want to see the names of the resources along with the Gantt bar. Maybe you might want to suppress that. Ira Brown: So like we talked about last time, in order to create a macro to do that, you would first have to understand how do I accomplish that manually? Right? So the technique for accomplishing it manually is to, if you right-click anywhere over in the Gantt area and you bring up the Bar Styles dialogue box, then the first row here which says Task up top, if you go down to the bottom and you click on the Text tab… Ira Brown: That’s sort of weird. I’m clicking on the Text tab and nothing’s happening. There we go. Okay, good. Notice that you can see resource names that are appearing to the right of the Gantt bar. So we would just really delete out where it says Resource Names there, and that would remove the resource names from the bar. So that’s how we would accomplish it manually. All right. Ira Brown: So now we want to actually create a macro to do that. So how do we create the macro? Well, remember last time what we talked about is you would just click on the View tab, go to the Macros button, and then just select Record Macro. Okay. Ira Brown: And then you want to name your macro. So I’ll just call this something like “Remove Names From Gantt Bars.” So notice all the different words there, I’m capitalizing the first letter just to make it a little bit easier to read. Okay. And then I’ll just go ahead and click Okay. And now the macro is recording. All right. Ira Brown: So now we will go through those same steps that I showed you a moment ago. While the macro recorder is running, I’m going to right-click and I’ll choose Bar Styles. Right? And then I’ll click on Task and then I’ll select Text down below. And once again, I’m clicking on it and nothing’s happening. There we go. All right. Ira Brown: And then I’m going to just click on Resource Names. I’ll hit my Delete key. Gone. Click Okay. Notice that the resource names are no longer displaying next to the bar. So I did exactly what I was hoping to do. Now I’m going to go ahead and stop my recorder. So I just click on Macros, Stop Recording. Okay. Ira Brown: And let’s take a look at the macro that we just created. So how do you do that? Well, you click on the Macros button again. And I’ll show you a little bit of a different technique this time. I’ll choose View Macros, and notice it brings up all the macros that we created during our last session, as well as here’s the new one that I just created. Ira Brown: So if I want to run that macro, I can actually click Run. But if I want to view the code, look at the actual macro code that was created, I can just click the Edit button. That’s what I’m going to do. All right. Ira Brown: And notice that the macro was created within Module 1. So it creates that module for me, which is that container for the macros. And then you can see that it named this, what’s called a sub-routine. So it starts off with the word Sub, Remove Names From Gantt Bars. It shows a comment that it was recorded by me. Ira Brown: And then you can see, these are the lines that were recorded, where we’re basically removing the right text associated with item number one in the list. All right. The first line here, I’m thinking actually may not even be necessary, but I’ll leave it there just for now. And I don’t think it’ll hurt anything. All right. Ira Brown: So there’s my macro. And it’s a pretty good macro. It’s doing exactly what I want it to do. So let’s say I now distribute this to other users and now I want them to be able to benefit from that same macro. So let me close this project, and I’ll recreate it from the template again. There we go. And we’ll pick the software development plan. All right. Ira Brown: But one of the things that we really don’t know is what view is this user going to be in when they decide they want to run the macro? They may not be in the Gantt chart view, right? They might be in the Task Sheet view, they might be in the Resource sheet. We have no clue. Ira Brown: So I’m going to just simulate that by saying, well, maybe the user is in the Resource sheet. Okay. And now we see all the resources in the project. And then they decide they want to run that macro. So how about if we just review real quick, remember how we assigned it to the ribbon? So let’s assign this to the ribbon again like we did last time. Ira Brown: So how did we do that? Well, we right-click in the ribbon area. We click Customize The Ribbon. This brings up this Customize The Ribbon screen. And then I’ll choose Macros here in that dropdown. There’s my Remove Names From Gantt Bars. And then I’ll just add it into that same tab, MPUG, and that same group I created last time. Right? So I’ll just click the Add button. There it is. Ira Brown: If I select it, I can say Rename. Now I can give it a little bit of a friendlier name. So really what I’ll do for now is I’ll just add some spaces there so it looks a little bit nicer in the ribbon; Remove Names From Gantt Bars, like that. And then I just have to decide what image do I want to associate. So maybe that looks like it might be a good one. So I’ll pick that image there. Click Okay. Click Okay. There we go. So now we have a button called Remove Names From Gantt Bar. Ira Brown: Shall we give it a try? Let’s see what happens. So we want to click the button. Oh no, what happened? We got an error message. The method is not available in this situation. Okay. Yeah, it kind of makes sense, right? Because we are not in a Gantt view right now, so there are no bars to adjust. So the macro would fail, and we certainly don’t want it to fail. Ira Brown: So what can we do to ensure that the macro is much more foolproof, that it’s not going to fail when the user runs it? Well, one thing we can do is we can apply the view or possibly even create a brand new view to ensure that the user is in the correct view when they go to run this particular macro. So I’m going to go ahead and I’m going to click End here. Okay. And I’m going to record a new macro. Ira Brown: So I’m going to bring up the macro recorder again, record macro. And I’ll just say Remove Names V2. Right? Something like that. So that’ll be the name. Click Okay. Now what I’m going to do is I’m actually going to apply the Gantt chart. So I’ll click the button here that says Gantt chart. Notice it puts me in the Gantt chart view. Now I can basically just go through those same series of steps again. Ira Brown: I’ll go to Bar Styles. I’ll click on Task. I’ll click on Text. Hope it works this time. It does not. That’s interesting. But if I click here for some reason and then click back, it seems to work. Happy to have discovered that during a Live session. Ira Brown: I’m going to just go ahead now and click Delete. Remove the resource names. Click Okay. And then I can turn off the recorder. There we go. So now if we go and take a look at the macro code, it’ll look a little bit different. Ira Brown: Let’s go back and say View Macros. We’ll pick the new version that we just created, Remove Names V2. Click Edit. All right. And notice this time we have one additional line of code there called ViewApplyEx Name:= “Gantt chart”. So that was now part of the code that got captured by the macro recorder, where it’s actually applying the Gantt chart view. Ira Brown: And now that it’s applied the Gantt chart view, now we’re in a much better position to be able to adjust the bars. So let’s give it a quick test. Okay. So once again, I’m going to close out of this project. Ira Brown: Let’s go up to the ribbon. Right-click, Customize The Ribbon. I’m going to click on Remove Names From Gantt Bars. I’ll click Rename here just to grab that text. And then I’ll just say… I’ll right-click it and I’ll say Remove. All right. Ira Brown: So now I’ll click the other macro that we just created, the V2 one, and I’ll bring that one over. There we go. And now if I say Rename, I can just paste in that same text that I had before. I’ll pick the image that I want, which I believe was that person right there. Click Okay. Click Okay. Ira Brown: Okay, so now we just assigned the new macro to the button. Let’s open up the project again, or the template rather. We’ll create that new project. Well, I don’t know what happened there, but we’ll try one more time. We’ll pick Software Development Plan, click Create. And that time it really believed me that I wanted to do it. Good. Ira Brown: So now what I’ll do is I’ll expand out all the tasks. And you can see there’s the resource names. Let me put myself in some view other than the Gantt chart, like the Resource sheet. Okay. And now let’s try running our macro, Remove Names From Gantt Bars. We’ll click the button. And look at that, it works. So this time, no error message. The macro ensured that we were in the correct view and then removed the names from the Gantt bars. Okay. Ira Brown: So just always keep that in mind when you’re creating a macro, is there any reason that this macro would fail? Do I have to be in a particular view? Is Microsoft Project expecting a certain table to be applied? Any of those kinds of things. And if so, then include those steps in the macro that you’re recording to ensure that the user is in that correct mode so that the macro doesn’t fail. Ira Brown: Now, there are other things we can do as well as we get more advanced with using the macro language. We can put in what are called error handlers, where we can actually trap for some particular error that could happen, and then we could take the appropriate steps. Ira Brown: For example, we could trap if the user was not in the Gantt chart view and then apply the Gantt chart view on their behalf, and then continue running our macro. So those are some things that we can do once we get a little bit more sophisticated down the road here. Okay. All right. Ira Brown: Let’s go through a couple more helpful hints. So the next one, when you are creating a macro, sometimes you’ll be navigating through tasks and you want to make sure that after the macro runs, that you return the user back to the beginning of the project so that their cursor is not positioned on some task somewhere in the middle or end of the project. Ira Brown: So it’s a nice thing to say, “Okay, yeah. The macro runs. They’re in a nice view.” First task in the project is selected, and it’s a cleaner way of creating your macros. Ira Brown: Now, when you record the macro, Microsoft Project doesn’t know to do that automatically. So what we’ll do is, as part of the macro that we create, we’ll edit it to just add that one extra command at the end, which is called SelectBeginning. Ira Brown: So let me just go back to Microsoft Project for a moment. And we’ll go to the VBA environment. This time I’ll choose View Macros, and this time I’ll just pick Visual Basic. That’s how you can get to the Visual Basic Editor. Okay. And then here you can see all the macros we created. They are the ones we did last time. Here’s the one we just created. Ira Brown: So what I’m going to add here is just this one line at the very end, and I’m just going to type in SelectBeginning. Now, here’s a little trick for you. I kind of like this. I actually want it to say SelectBeginning, but if I just type in “SelectBeg,” hold down my Ctrl key, and then hit the space bar, look what just happened. Ira Brown: So it basically… I think that’s called IntelliSense technology where if you just type in the few characters, it will finish it off for you. Now, if I just typed in the word Select like that and did the same thing, there’s a whole bunch of commands that start off with the word Select. Ira Brown: So it brings up this little menu here that you can then pick the command you want. Now I can pick SelectBeginning. Just double-click on it, and then it puts it in there for me. Okay. So that’s a good idea to put that at the end of your macro to return the user to the top row of your project. Okay. Ira Brown: And one more helpful hint, and that is, when you’re running a macro, oftentimes you are manipulating the information within your project. Maybe you are changing durations, changing dates, changing work, maybe adding tasks. Who knows? Right? Pretty much anything that you can do manually in Project, you can do programmatically through the macro language. Ira Brown: Now, let’s just say you’re in a 5,000-line project, so you have 5,000 tasks, and you want to go through and you’re applying some sort of logic to change the work, maybe applying a certain factor to the work on every task. Well, that’s going to take time to run. And part of the reason is that every single time you make a change to a task, it’s going to trigger the automatic recalculation of the project. Because by default, Microsoft Project will recalculate your project with every change. Ira Brown: Now, that’s normally not a big deal if you’re just making one change at a time through the normal use of Microsoft Project, but when you’re running a macro, it actually can be quite a big deal. Because again, if you have many, many tasks and you’re going through and manipulating a lot of those tasks, it could add many, many seconds, even minutes to the processing time. Ira Brown: But one of the ways that you can really improve the efficiency in most cases is by adding this extra line at the top of your macro called Calculation=pjManual. And then at the very end, you say Calculation=pjAutomatic. Ira Brown: So what’s going to happen is, before you manipulate any of your tasks let’s say, in the example I used, we’re going to turn calculation off, basically. So it’s not going to recalculate the project every time you make a change. And then at the very end of the processing, we’ll turn it back on by saying Calculation=pjAutomatic. All right. Ira Brown: So let’s just quickly take a look at how to do that. I’ll go back to the Visual Basic Editor. And again, at the top of your macro – now this particular macro here won’t make too much of a difference really, but conceptually, it still makes sense – I can just simply say Calculation, and I’ll use that little trick I told you. Ctrl, space bar, right? Then I can pick Calculation and I’ll just hit the tab key, I just picked it. And then I’ll type in an equals sign. Ira Brown: And notice when I type in the equals sign, it actually gives me a choice of either pjManual or pjAutomatic. These are referred to as constants in VBA. I’m going to pick pjManual. Just double-click it. All right. And then at the very end, we’ll do the same thing. Calculation=, and this time I’ll say pjAutomatic. Okay. Ira Brown: So that’s what you’ll want to do, particularly in any kind of macro that you create, where you’re changing a lot of information in your project, it will run a whole lot faster. Okay. Perfect. All right. Ira Brown: So now let’s move on to the next part of our session for today. Okay. So this is creating a user form. And the example that we’re going to take a look at is we’re going to create a user form to set the resource group field. Okay. So for that, we’re going to go through this little case study that I hope you find interesting. Ira Brown: Many of you that know me and have been to these seminars before know that I’m a very big Beatles fan. So a lot of people wonder how were the Beatles so prolific in the years they worked together to create all those songs in such a short period of time. Well, I found out that they actually used Microsoft Project to manage the creation of their albums. So, little-known fact. And I read it on the internet so it must be true. So we’re going to see how they might’ve done that here. Ira Brown: So I’m going to create a few tasks. So I’m going to create a task here called Write Song, Record Song, and Release Single. How about that? For those of you that know what that is. Okay. Ira Brown: So we’ll have a simple little project plan here. I’m going to make these auto-scheduled tasks, it’s defaulting to manual right now. So let me just switch that to auto-scheduled and then I’ll switch each of these to auto-scheduled. There we go. All right. Ira Brown: And we’ll say maybe we’ll take a couple of days to write the song, a couple of days to record the song, and then maybe we’ll make this a milestone, zero days. Right? And let’s go ahead and link up these tasks. All right. So we’ll click the little link button there. And now we have ourselves a little project that we can use for this example. Ira Brown: So I’m going to switch over to the resource sheet. All right. And we’re going to put our resources in. Well, our resources, of course, will be… And I hope you know what their names are. That would be a good thing. John Lennon, Paul McCartney, George Harrison, and of course, Ringo Starr. Okay. Ira Brown: So we put our four Beatles in the project. And notice there’s a field in Microsoft Project called Group. Right? So what I would like to be able to do is I’d like to be able to create some automation to automatically populate that Group field or do it in a more friendly way. Ira Brown: So the idea would be that I want to be able to prompt the user to enter the name of the group on a form that we’re going to create, and then we’re going to fill that Group field in automatically as part of this automation. Okay. So how do we do that? Ira Brown: Well, we have to go to VBA. So again, we click on View, Visual Basic Editor, this guy right here. Okay. And I’m going to create what is called a user form. That’s going to be the user interface to collect this information. So we do that by right-clicking where it says Modules, Insert, User Form. Okay. Ira Brown: So that is our form that just got created. And at this point, not much going on with that form. I’m going to just grab hold of that little anchor there and just drag it to make the form just a bit bigger. Okay. Ira Brown: So now we want to start putting what are called controls on the form. So remember, I want to be able to allow the user to enter in the name of the group that we want to apply to the Group field. So a good control to do that would be referred to as a text box. So I’m just going to find it. There it is right there. See? That says text box. Ira Brown: So this is referred to as the toolbox. As soon as you click on the form, the toolbox appears. And I’m going to just select that little control there called Text Box. And then I’m just basically going to go to the form, I’m going to draw it on the form. I just click and drag and get it to the approximate size that I want. That’s good enough for now. Okay. So that is our Text Box control, and that is where the user will type in the group name. Okay. Ira Brown: Now, one of the things that we want to do is we want to click on that control. And if you go to the View menu, there’s this window you can bring up called the Properties window. I’m going to select that, and I’m going to name that control. Ira Brown: So notice that right now, it’s just named very generically, TextBox1. But I want to give it a more descriptive name that tells me really what it’s for. And there is a good naming convention you can follow that when it’s a text box, you normally start off with the prefix “txt” like that. And then I’m just going to follow that up with the word “Group.” Okay. So the name of that control is now called txtGroup, and that’s how I can refer to it when I’m writing my code. Okay. Ira Brown: Well, I’m going to click on the form itself. So I’m no longer clicked on that control. I’m just clicked on the form. Notice the form itself is called UserForm1. Let’s give that a little bit of a friendlier name as well. Ira Brown: I’m going to call it “frm” for form, and then maybe I’ll call it frmGroup, because that’s what it’s going to be doing. All right. Well, what else do we want? Well, right now it’s just called UserForm1, that is referred to as the caption of the form. Ira Brown: Well, I don’t want it to say UserForm1. I want it to say something a little bit better than that. So I’m going to type in Project Widgets there. Okay. So now notice when I do that, you can see it appear at the top of the form. All right. So this is starting to come together. No pun intended. Ira Brown: And now I’m going to click on the form. And what else might we want to do? Well, we probably want to… We don’t just want to put that text box there. We want to provide some sort of instructions to the user. Ira Brown: So I’m going to put on what’s called a label. Right? So I’m going to click on Label, I’m going to draw a label right above that text box, just clicking and dragging and positioning it approximately where I want it, something like that. Hey, that looks pretty good. Okay. Ira Brown: And now with that label highlighted just like that, I’m going to… I won’t have to worry about giving it a name so much because that’s not all that important in this case, but you certainly can. But the caption is really what’s important. This is how we’re going to prompt the user. Ira Brown: So I’m going to say something like, “Please enter the resource group,” and then press Enter. And a colon actually as well, and then press Enter. And notice that now appears right above the text box. So that’s what the user will see. Okay. Ira Brown: So what else do we need to put on this form? Well, most forms like this, you would have probably an Okay button and a Cancel button. Right? Cancel if they don’t want it, if they decide they don’t want to do it. And Okay to basically say, “Okay, go ahead and process what I just did.” Ira Brown: So we’re going to put what are called command buttons on the form. So let’s do it. Let’s see. Let’s find the command button. Is it there? Yes, there it is. Command button. I’m going to click that one and I’m going to draw myself a button now, get it to the approximate shape that I want it. I think that looks pretty good. Ira Brown: And then I’m going to name this. So instead of CommandButton1, I’m going to go to the name here. I’m going to type in cmdOkay. Just like that. And then for the caption, remember, that’s the text that’s going to appear on the button, I’m going to type in OK, just like that. All right. Ira Brown: Well, I’m going to now need to put a Cancel button on the form. So let me show you a little trick. You can do exactly what I just did and just simply draw another button or you could select this button, I can right-click and say Copy, and then I can click on the form and say Paste. Right? Ira Brown: So now I have a second button that also says OK. And now I can just… But I know at least it’s the same size. Right? Now I can drag it to where I want it. I’ll give it a name. So let’s call this one cmdCancel. It’s a pretty good name for it. And then instead of displaying the text OK, I’m going to just change the caption to the word Cancel, like that. Ira Brown: Well, this is looking pretty good. We have ourselves a form. Now, what I’m going to do is just to make it a little bit nicer. Allow me to… I want to move this a little bit, right? So notice how I’m going to select those two elements together, if I just click and drag over both of them and then let go. Now I can move both of them relative to each other and just maybe position them where I want them on the form. Right? Ira Brown: And I’ll do the same thing with the OK and Cancel buttons. I’m going to select both of them and I’ll drag them so they’re right-aligned with that text box there. And now I can just make the form a bit smaller just by dragging the edge like that. And then I’ll make it a little less tall by dragging it like that. And that looks pretty good. Not bad, right? Ira Brown: So this will be our user interface for allowing the user to enter the group. Okay. But so far, all we’ve done is create the user interface. It doesn’t know what to do with the information that we’re going to type in. So we actually have to tell it now what to do. What happens when the user clicks OK, what do you want the form to do? Or what happens when the user clicks Cancel, what do you want the form to do? Ira Brown: Well, let’s talk about the Cancel one first. Okay. So what I’m going to do is I’m going to double-click on the Cancel button. Watch what happens when I do that. It takes me to what’s referred to as the code window associated with what happens when the user clicks on the Cancel button. Ira Brown: Notice that I’m in this little sub-routine now that’s called cmdCancel… That’s what I named the button, right? cmdCancel_Click. Right? So this is referred to as the click event. In other words, when the user runs the form, if they were to click the Cancel button, what would happen? Well, it would take them to this event right here. And any code we put in here is going to run when the user clicks the Cancel button. All right. Isn’t this kind of fun? Ira Brown: So what I’m going to do is I need to put in this… I’m going to hit the tab key. And the command that I need to type in here is something called Unload, and then a space. Notice how it’s prompting me for the object, it’s really the name of the object. So in this particular case, I can just say Me. Unload Me. Me being the form. Okay. It knows that Me refers to itself basically. Ira Brown: So this says when the user clicks the Cancel button, basically close the form. That’s what Unload Me is going to do. Ira Brown: Let me once again double-click on where it says frmGroup. And now what happens when they click the OK button? Well, this takes a little bit more thought. And we actually have to write a little bit more code to actually do what we want it to do. So I’m going to double-click on the OK button here, just like we did before on the Cancel button. Let’s double-click it. And it takes me to the click event associated with the OK button. So basically, when the user clicks OK, the code that I have here is what is going to run. Ira Brown: So let me go ahead and… First, let’s think about what we want it to do. We basically want it to say for every resource that I have in my project, let’s assign the group that the person entered on this form to that group field. Right? So I need some way of being able to do that for every resource. Ira Brown: So the way we’re going to do it is, first of all, I’m going to type in something here. This is going to be called declaring a variable. I’m going to say Dim r As Resource. Okay. So we did something very similar during the last session when we were working with tasks. Ira Brown: So r is going to be the name of the variable. And I can name it anything I want. But Microsoft Project needs to know what is r. Well, r is going to be a resource. Okay. Now what we want to do is we basically want to loop through every resource in our project and set the group. So here is the syntax to do it. Ira Brown: We do what is called a for-each loop. And the for-each is nice because it doesn’t matter how many resources you have. It’ll work if there’s one, it’ll work if there’s a hundred. It doesn’t matter. It’s going to process each single resource line. Ira Brown: So we’ll say For Each r in… So where is it going to look for the resources? Well, it’s going to look for them in the ActiveProject.Resources. Right? And then I’ll close that off by saying Next r. Ira Brown: So this is basically the syntax to say, loop through every resource in my project, in this active project. And then as you’re working with the first resource, do something with it. And when you do something with it, you can refer to that resource by r because that’s what we named our variable here. Right? Ira Brown: So what I want to say is now, r., and as soon as I press dot, notice it brings up, again, this little menu that I can select from all the different fields that are available for a resource. And I’m sure you’re familiar with many of those fields because they’re the same fields that are available if you were actually in the Microsoft Project user interface. Right? Ira Brown: So what field do we want? Well, we want the field called Group. So I’m just going to type in the letter G. Notice it takes me to the Gs. And there’s Group, and I’ll just double-click it. And now what do I want to do with that? Well, I want to say r.group=, okay? What do I want to set it equal to? Ira Brown: Well, I don’t want to hard-code “The Beatles.” I want to set it to what the user typed in the text box when they ran that form. Right? So how do I do that? How do I refer to that text box? Well, I refer to it by name. I will say txtGroup. I did that little trick again, Ctrl space bar. And I just say, set it equal to whatever’s in txtGroup. Then I hit a dot again. And the particular property, as it’s referred to, that I want to reference now is called the Text property. The Text property is the text that the user typed in the text box when they ran this form. Ira Brown: So what this says is, for every resource in my project, whatever they typed in that text box, set the Group field for that resource equal to that value. And then maybe we’ll do something at the end and we’ll put in a message box like we did last time. MsgBox, and then in quotes, “The resource group has been set successfully.” And we end it in a quote again. And we’re going to type in a comma. Ira Brown: And then this is the icon that I can display in a message box. And I’ll pick the icon that’s referred to as vbInformation. That’s the little icon that has an “i” on it. There we go. Ira Brown: So this is looking pretty good. Now, as a good general rule is before we run this routine here, we want to back it up first. Just in case something happens, we don’t want to lose all of our work. Ira Brown: So to back it up, I right-click on the form, I say Export File. And now I just pick a location, and I’ll just use my desktop for now. And I’ll just save it on my desktop, and I just click Save. Right? So now I’ve backed it up. All right. So we’re going to do one more thing here. Remember I said that we need some ability to assign this to the ribbon. Right? So that when the user clicks the button, it knows to display this form. Ira Brown: Well, what we need to do is we need to write this other little routine. So I’m going to go into one of the modules we created. Actually, I’ll create a new module. Let’s just say Insert Module. And I’ll name this module modPWMPUGPart2. There we go. Ira Brown: Name conflicts. Oh, I typed a one, I guess. I meant to type a two. Let me try that one more time. modPWMPUGPart2. Much better. Okay, good. So there’s our module. And now I can just create this one other routine. Let me just create it, and then I’ll explain what it’s doing. Ira Brown: I’m going to say Sub ShowGroupForm. Press Enter. Notice when I do that, it automatically puts in an End Sub. So it’s smart enough to do that on its own. I’ll hit my tab key. And now I’m going to reference the form by name. I’m going to say frmGroup.Show. There we go. Ira Brown: So this becomes the macro that I can assign to the ribbon. And then its job is to show this form that we just created here. All right. Looks like this is all falling into place very nicely. Let me export that module that I just created. Okay. Ira Brown: So now let me go back to the main Microsoft Project. I’m going to just minimize the VBA window. All right. So I’m going to go back to here and I am going to add that as a button to the ribbon. Okay. So remember how we do that? We right-click, customize the ribbon, we pick Macros. There’s our macro called ShowGroupForm that we just created. Ira Brown: I’ll add that to the Widgets tab by clicking the Add button. And then I’m going to click Rename and I’m going to maybe call this something like Set Resource Group. Okay. Ira Brown: And now we just have to pick a nice icon. Let’s see if we can find one here. Maybe that one. That’s not a bad one. And I’ll click Okay, and then click Okay. All right. Ira Brown: And notice, by the way, I’m not exactly sure why it made the decision to do that now, but it made these buttons here a little bit smaller. But that’s okay, we can still run it. So shall we give this a try? So let’s click the button. Let’s see what happens. Ira Brown: So it just brought up our form. You see that? Now, what if I change my mind? What if I don’t want to do it? I want to press the Cancel button. Let’s try it and see what happens. Form goes away, just like we want. Right? Ira Brown: We really do want to do it, right? So let’s click the button again. And it says, “Please enter the resource group.” So I am going to enter Beatles and then click Okay. And now we see what happens. “The resource group has been set successfully.” And notice that Beatles now displays in the group field. Pretty cool. Ira Brown: Now, I realize I forgot to do one thing. We don’t want the user to still see the form here, do we? We want to make the form go away once it’s done its job. So let me hit Cancel for now. We don’t want the user to have to do that as a second step. Ira Brown: So what I’m going to do is I’m going to go back to my Visual Basic Editor. And let me go back to the code associated with the OK button. I’m just going to double-click it. And you see right after that message box here? I’m going to put in one more line, just like we did for the Cancel. I’m going to type in Unload Me. Right? Ira Brown: So after it does its job, that will make the form go away. So I think we should be in good shape. Now, of course, we’d want to test it. Let me clear The Beatles from here. Let’s run it again. Set resource group, put in Beatles, click Okay. It gives me my message box. Click Okay. And now the form goes away. So this is looking pretty good. This is exactly what I wanted it to do. Okay. Ira Brown: So we have one more case study that we want to do around setting the user interface. But I think I’m going to save that for next time because we probably won’t be able to get through it adequately this time. But that’ll give us a little bit more time for questions at the end here. Ira Brown: So next time we get together, in addition to showing you ways that we can integrate Microsoft Project with other Microsoft Office applications… The next session is really an exciting one because we’re going to see how can we get Microsoft Project to interact with Microsoft Word, or maybe to get Microsoft Project to create a PowerPoint presentation, or maybe pull in information from Excel all through VBA. Ira Brown: So that’s what we’re going to be covering next time. But we’re also going to be covering one more example here where we’re going to see how we can build a user interface to properly set the resource initials. Ira Brown: Notice that when you put in someone’s name in Microsoft Project, it only puts in the first initial of their first name. It, for whatever reason, doesn’t put in the first initial of their last name. Well, we can overcome that limitation by creating our own functionality to set the initials appropriately. So we’re going to see how to do that next time, okay, which I hope you all decide to join us next time, it would be great. Ira Brown: As a reminder, we are going to be giving away three more copies. We gave away three last time and we’re going to do the same thing this time, three more free copies of the Driving Path Widget. So with that, I would like to thank you very much for attending today’s session. And I’m going to turn it back over to Melanie and see if we have any questions from anybody. Melanie: We do, Ira. So I’ll leave you control of the screen in case there’s anything you want to show. One of our questions is from Jeremy. What is the advantage/disadvantage of having multiple macros in the same module? Ira Brown: Jeremy, that’s an excellent question. I would say that when you’re using the macro recorder, by default, every time you create a new macro, every time you record a macro, it puts it in its own module. That’s just the default behavior of Microsoft Project. Ira Brown: But what I tend to do is I tend to take those routines, those macros, and I tend to put them into a module of other similar or related kinds of routines that I’ve written. So you don’t really just have to have one routine per module. You can have 10,. You can have 50. It doesn’t matter. But it’s a way of organizing the routines that you’ve created. Ira Brown: So you can put maybe macros that are designed to manipulate resources, maybe they can go into one module. And then macros that are designed to manipulate tasks can go into a different module, and whatever else you may want to come up with. So I think you want to organize them in a way that’s logical, that makes sense for whatever it is that you’re creating. Melanie: Thank you. All right. So this question is from Joan. This may have been covered in the first demo, but if you’re not familiar with VBA code, what do you recommend for learning? Ira Brown: Well, that’s a very good question. Well, there are a number of resources that I’ve run across from time to time that are available. There are a few people that have written books about VBA and a couple of people have even written books about VBA for Microsoft Project. Ira Brown: One of the things I didn’t mention yet, I kind of alluded to it a little bit today, but this macro language that we’re using, VBA (Visual Basic for Applications), that’s the same macro language that Word uses, that PowerPoint uses, that Excel uses, that Outlook uses. So it’s a common macro language. Ira Brown: So once you learn the macro language for Project let’s say, then you’ll understand how to create macros in Word. But instead of… You can imagine if you’re in Project, you’re working with tasks and resources and assignments and calendars, whereas when you’re working in the macro language with Word, you’re working with things like documents and paragraphs and words and fonts. When you’re in Excel, you’re working with things like rows and columns and cells. Right? And formulas. But it’s the same macro language just applied to that particular application. Ira Brown: So getting back to how do you learn about it, I recommend probably just going out and doing a Google search on Microsoft Project VBA. One of my colleagues that’s been in the business for a long time, a guy named Rod Gill, we’ll give a shout-out to Rod, I know he’s written a really good book about Microsoft Project VBA. You can try to see if you can get a copy of that one. And I know there’s numerous other resources that are available, that you can find if you do a search for them. Melanie: And Rod, by the way, has done some sessions for MPUG as well on VBA. So in that 600 hours of training we have out there, you will find some articles and sessions by Rod. Another question, is Microsoft moving away from VBA? Ira Brown: Well, that’s an interesting question also. Microsoft VBA has been around since, I believe, Version 4.0 of Microsoft Project, which is going back to the early 1990s, something like that. And it’s been with every version of Microsoft Project since, including when we started with standalone Project, then all these different versions of Project Server came along, then all these different versions of Project Online came along. Ira Brown: And the macro language has been a part of the client application. Remember, we’re working with inside of the main client Microsoft Project application now, so that’s where you would use VBA. Ira Brown: Now, Microsoft’s latest offering, which is called Project for the Web, is a web-based application to do some basic Project management work. Well, that doesn’t use VBA. That will use some other techniques for working with Project for the Web programmatically. Ira Brown: You can build things like Power Apps and Power Automate and other techniques for automating Project for the Web. But as long as we have a traditional Microsoft Project application – and I don’t see that going away anytime soon, I would imagine it’s going to be around for many, many more years to come – then we will have VBA. Melanie: Thank you. I think that is it for our questions today. A big thank you, Ira, for an excellent session again. A big thank-you to… Oh, go ahead. Sorry. Ira Brown: No, I was just saying you’re very welcome. Thanks, everybody, for attending. Melanie: Wonderful. So we would like to also thank our MPUG community here today as well. Thank you for coming today and thank you for choosing MPUG to grow your skills today. The PDU activity code, again, it’s up on the screen here for you. And I will chat that out once again. Melanie: We will send a link to this recording and a super, super short survey afterwards. Please always let us know what you think. We hope you join us next week for part three, using VBA to integrate Microsoft Projects with other applications. As Ira mentioned, that is a pain point we hear across our audience, so we’re excited for that. Melanie: And I also just want to say congrats to Lori in Wisconsin for winning an MPUG pug today. Again, thank you. And I will keep the PDU code up here and these ending slides for a little bit in case anyone needs to copy that down. Thank you again.

  • 1
  • 2
  • 5