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.
