To call we're calling them resources here we can simply just select it and with the drop-down list quickly add that person you'll see that automatically this schedule gets updated I'm gonna show you how to do all that how to create new projects how to put them on the schedule how to color code them so that automatically the selected project shows up in orange we've got a lot to cover so let's get started before we do I want to make sure you are aware of my amazing dashboard master class if you do like these trainings please check out the dashboard and master class that has.
A 15 hour class for single click dashboards can let you do four different financial reports and it's an amazing template and blueprint that I've used to create amazing applications as well as customer jobs many of those in the past so I want to make sure that you get a look at that I'll include the links down below so please check that out alright moving on we've got a lot to show you the basic functions of this project manager where we have the project information project name a project type.
Start date assigned we have the ability to give a put a completed on date the ability to assign seven different resources also known as employees or staff however you'd like to call them because you can create different types of resources of course we have the ability to add a duration now whether that duration is days hours or weeks you can quickly add it so if we were to change this to say one week this job and we just automatically update it it.
Automatically updates on the schedule so if we select this job we want to add a day onto that we just put chains at 6:00 and automatically add days added on anywhere you select in that job it's going to load the job is automatically going or I should say the project it's automatically gonna change the color to orange so we know always the one that's been selected so there's a lot of great features on listen it's not very difficult to program it's just a few formulas some lines of code as we're gonna go over and of course an advanced filter and it's pretty much made up of.
Just four different sheets so we have our project manager here and as we always want to do in case we have multi-user for many reasons separate our front-end what the user sees to our backend which is our database where our data is stored in fact this one's got three different data files we've got a project list and this is kind of a basic list I really should update the look and feel of this it's just a basic list and I'll go ahead and do that while I'm working with you on that so we'll update the look but this is basically a list of.
All of our projects here we have our resources now our resources is just a list of resources now we're gonna probably add more information like scheduling like availability address we've got so much more debt but I just put in the names for right now just a list of names so I kept that pretty simple that's you could call them employees or staff or whatever whatever you want really so automatically and as we add items here like as we add them they're gonna automatically appear in.
The project manager down here so they get automatically added so it's a really nice feature so that when you add them we also have project resources now this is basically the resources that are assigned to individual projects for example on project number one we have both Fred and Sally assigned to it project number five we have Leslie we have Fred you know and so so this is how we keep track of who goes on what project because we can have up to five so we want to create a databases separate lists for that and of course we're gonna be filtering those.
And then of course like if we have a specific job that we're gonna be filtering those results so if we want to know everybody that's on job number seven we're gonna use an advanced filter I'm gonna walk you through that so for example the job that currently up here is job number excuse me project number seven here as you can see so there are two people on it so we did run an advanced filter so we know how to get that alright so we just have those three database in fact let me update this so it gets a nice look and feel well and also you can see how we did that I'm gonna pin this temporarily we'll go.
Ahead and merge and center this and increase the font to maybe thirty just as we do and then change it I'll change the font to maybe Cambria give it a just so we can give it a little look and feel something that matches this but most of you know how we do this so I don't do it too often because I want to get into the nuts and bolts of it but you can see how I did that we'll give it a let's say a green color and then we'll use a fade so I'm using two colors on the green from medium to medium light and then I'll use the headers here and we'll go ahead and.
Give it one green down just so it looks a little bit nice so we'll you know how we do the fades and I'll do a fill effect I'm gonna use two different greens we'll use a medium to light and then of course the light gray and give it a nice fade effect and you can do a lot more with this we're gonna be focused primarily not on the look and feel of this one but mostly how it gets done because if you follow any of my trainings before and there's a we're getting near a hundred of those you'll see that on some of them I do cover the look and feel and other times we just focus on the internals of how things.
Work so sometimes the idea before all right so it gives you a little bit of an idea so we've got our project list in
Our project list our database of course that contains our project number which is vital and has to be unique for every project so we've got a project number here we've got a project name a project type and a start date we have whether there's a status we'll work on that a little bit in the future in fact maybe in the future we will color code based on status that would be kind of nice we.Can also color code maybe on project types of we can color right now I have I kept them pretty simple the projects are green color but if we select them they're orange but I think in the future parts maybe we're gonna color based on either status or project type so we have different colors for different project type so I think that would be something nice we also have a description and then we have the duration and duration types of two weeks two days or you know a few hours or something like that and then we have duration days and end dates and.
Both of those are based on a formula but these are based on formulas and of course they're based on the number of duration and the duration type so we get we want the total days and what I do is I extract the days from whether weeks or days or hours and then we also get an end date and basically is the number of days plus the plus the start on right so if the start on 20 we're gonna finish on the end date on the third and that's basically just um we're just adding d4.
And I'll show you how we do that adding d4 to the number of days and then I've used an if statement here in the duration date let's zoom that in a little bit so you can see it and so here we have the formula here if I 4 equals days if it's days then h4 then we're good to go with the number but if it's weeks we're gonna multiply the number by 7 obviously there's seven days in a week and I want to get days and then of course if it's number of hours I want to do dividing I want to divide because one day if it's one day it's one one.
Twenty-fourth of a day and if it's of course if it's twelve hours it's gonna be half a day so we use that formula to pull the number of days and that way we can easily allow the user to select between weeks days or hours so this project this database for this project list is very simple and we have data mapping here and we're gonna go over that and we've got data mapping from columns a to i-4 and I'm going to show you exactly how that works through the code and of course we've got our main screen of project manager now we have the ability to add a project simply by.
Clicking add a project and if we want to assign we can call this new website if we're having a website company and maybe we can call this new site as a type and then we could give it a start date let's say on the 2nd of January I want I'll pipe in a pop-up calendar here I think it would make sense but right now I have not just focus on that status and it's just called pending and we don't want to give it a completed date and we see that David and Mark both have times on their schedules so we can.
Assign it to David and let's it's gonna be a three day job so we'll put in three days and that's it I also want to add in attachments in the future this is just a space it's not done yet we're gonna cover that in so I want the project to have multiple attachments in a preview so I've added this space down here but it's I've got a lot to show you so I haven't added the attachments feature and yet we'll do that in part two or part three or based on your suggestions okay so we've got everything filled out that we need to fill out now all we need to do is click the save project and.
You'll see right away it appears on the schedule now of course if we want to increase it now they would say if we want to change that to five days all we need to do is just change it automatically and it's increased automatically on the schedule so that's a really great feature I'm going to show you exactly how we did we can also add a new person to it and say it's a big job and we want to add when you see mark has time in his schedule so we can click on mark and automatically mark its add it to it we don't have to save it we don't have to click anything so it's automatically saved new jobs if it's a new project we do have to save it for.
Those changes because users make they may cancel right they may decide not to add it but when it's an existing job all we need to do is simply make the change in fact if we want to remove mark we just hit delete and then automatically mark is removed from the schedule so it's these kinds of types of applications once they get made they're really easy to work with really easy to use so I can't wait to show you exactly how we did it of course we have the ability to go previous month I don't have any particular jobs on December and of course when we go previous month we.
Also we need to change the month and we need to change the year so we also have the ability to change the year here by
Clicking it but I actually I haven't added in the ability I could probably do that here on training so the schedule doesn't update I just probably let's do that right now on change of our two or x2 we want the schedule to update on the change of either one of these we to run a Mac or let's do that now because it's not working I haven't added that in yet so we will do that right so.We're gonna go into the developers tab if you don't have the developer stuff available make sure you click on the file and of course we'll click on options and then in the customizer ribbon make sure that you have selected developer to get your alt f11 will also get you into the VBA and you can click here Visual Basic to get you in there we're gonna focus on project manager now we've got a lot of code here I'm gonna go that step by step with you but let's add in just the code we talked about we have a macro called project schedule.
Load I'm gonna show you how that works and Google step-by-step through that macro but right now all I want to do is add a change event change event and what kind of change well that was our - if we make a change if the user makes a change to either our - on sheet 1 or X to R 2 or X 2 then I want to load the schedule so let's just do that right now so we can do if not and that's autohotkey that automatic type that r2 comma X - if the.
User makes changes any one of those then what then cause then we're gonna reschedule load then we're gonna run this particular macro and that's gonna refresh the schedule now when we head back into the schedule and all we have to do is double click inside here and it'll automatically update with the new information based on the date so we can move ahead as we move to February or then back to January so we can use previous and next month to update the schedule as well alright let's get into the nuts and bolts and see just how we did that we've got some more information.
To show you I've got a few lists one is which months and I've had the months of January to December and we've titled that of course month so I've given that a named range as a month and of course also years here we've got years from 2019 through 2030 and I've called that years and then of course here in r2 I've given it a data validation here and the data validation here of course is months here and then of course here in the year I've given.
That a data validation as years so when you see the data validation here that's gonna be years all right so you've seen how we get the drop-down list and now you've seen how we update the schedule and so we have those named ranges and of course I've got the project load is false now I want to differentiate when the project is loading the loading means one out when I click here when I click here that's gonna go to true real quick and back and that's because I want to differentiate between the types of changes in these cells for example some change this type.
Of change I want saved a test so when I click this as a test I want that type of change to save in the database so when we look in number 10 we'll see that this is a test it's been updated here in the database but the type of change of course when we load a site when we select a specific project to load that of course it's not that type of change is not so we use project load and I'll show you it in the code but we use this true/false to differentiate between those changes I also want to know if.
It's a new project or not because if it's a new project for example and I click add project this goes to true so that means I know that if we make a change here we're not gonna save those changes to this database until the user click save until the user click Save and of course if they click cancel new I want everything to go back to false and we're gonna just load a random or probably the last specific project that was say we're gonna load that so we have those two things so a new project I also want to know the project row and where.
Does the project row the project row we're gonna use match and it's going to be based on the project number here project number is located in i-4 so when we look at the project list we're gonna see projects here and now I've given those named range let's take a look in the formulas name manager take a look at some of the named ranges that we have we've got project name and we've got project number for this particular sheet and when we tab over we're gonna see that this dynamic named range and I'll.
Zoom that in so you can see it okay so that this dynamic branch is equal to a3 starting at a through using off why are we using a 3 a 3 is the header because when there's no data in here at all it's not gonna return an error when I include the header but of course the header is not a part of our data so we want to offset that one row down one row down if I were not to offset that if I were just to keep that and we were to tab it out we would see that it would now include the header so we want to make sure that we.
Offset it down when we include the header we want to offset it down in row and I like to include the header because when we do that no matter even if there's no data it's not gonna return an error for this dynamic named range and then we're gonna count then we need to know how many rows in this offset how many rows we're gonna use count counting a three we're gonna use making sure that we count a column in this case a that always has a value and of course we're never gonna enter a project without.
Actually having a project number so called a it's always gonna have a value that's why we use column a in fact when we use the name it's just in case somebody is not a name I still I am still going to count column a we're just gonna use column B instead and of course because I've included the header row here as well starting at a3 I also have to subtract one from that count so we subtract one if we didn't it would give us one too many rows so then of course one is the offset there and that is how we create the dynamic region we've done the same thing for project name the.
Project name here we've done the same thing here starting with b3 this time and then if counting we're still counting a3 through a nine now we're still going to count days because column a always has a project number so we want to use but except this time we are going to start our offset our named range in BB three so that it sounds so we have project name and project number so when I want to find a row right remember keep in mind these start on row the first one is row four so if we find if we find project one.
It's gonna return one so we need to add three because we're looking for the row I don't want I don't want the first one I want the Frey don't want the first row I want the first value the value is in Row 4 so when we use that match to locate the project row we want to add three so for example we're going to match what are we matching we're matching I for that is the project number in I 4 we are going to match it within the project number that's the named range I just went over with you and we want an exact match so of course.
That is going to be zero we're going to add 3 because our roast our first value our first project row starts on Row 4 just as I showed you and of course if there is any kind of an error if it's not found or if it's in case I 4 is blank such as on a new project we want to return if err we want a blank space so we don't want any errors in that so that is how we get we always have the select among VBA is going to change our month for us and our next project number.
Is going to use max we're gonna use max formula to see that now we know in our project list we have our project numbers 1 through 11 what if I want to find the next one so when we add a new one it became numerical and integral right after one or the other so if I want my next one to be 12 I'm going to look for the max product which in this case is 11 and then I'm going to add one that is gonna get us 12 so I want to know so when we add a project I already know what project number to save because that's going to be 12 right here in fact.
Again it's going to be the max of the project numbers the maximum in which in this case is 11 plus 1 which is 12 and of course if there's an error meaning there might be no data if there's no data at all I want to return instead of an error I want to return one as one being our first project number so in case there's no air in case there are no project numbers we're gonna return one instead alright so that is how we get to the formulas here on this so that's pretty much all the hidden things that for the project manager sheet now we're.
Gonna move into the functionality of how we get this to work we'll focus first on the project manager section and then we'll move to the schedule now if we right click here we've got some button sets if you can see there's a save project and a cancel new button these two buttons are part of a group that I've named called new project group and if we click on the selection pane here we can see that this group is part of a cancel button and part of a Save button so part of that group is a new project and I want this group only to.
Show up for new projects and if we cancel that then I only want another group to show up called existing project group existing project group this button is essentially just a button with some icons I only want it to display when we're displaying an existing project right so that way the users can differentiate between the two modes it's either an add new mode or it's an existing mode and for the existing we want to show the add project button for the add new we want to show two buttons saved giving the user ability to save.
And a cancel new button so basically it's just those two button sets we're also gonna have something called filter projects and we're gonna be able to filter by status and by project type that's in the future right now I don't have that available just kind of put a spacer in there and I will also have something I've got a hidden column here for unassigned projects I would like to show projects that have not been assigned so we're gonna add that in a little bit later on right now just put it kind of as a spacer and I'm hiding it.
Right now so you can see that's kind of what I've got planned so we have the ability to hide and show filters but this is not active yet so we'll keep them hidden for now we'll just focus on the main part of it cuz we already we still have a lot to cover in this training so I want to make sure to get you the most out of it without overwhelming you and of course we've got you know probably two three are probably a four part series because you know there's a lot of features that we can add into a project manager and of course I have a my own set of ideas but I believe that you're gonna add a lot more.
With your suggestions with your comments and ideas so I want to make sure that I hope I can get your ideas in as well without entirely redesigning this application but I know there's a lot of things that can go into a project manager so I want to make sure to add in as much as possible okay so there's a macro that's run when we add a new project does a macro that's run and if we want to look to see what kind of we can right-click and we don't want to right-click the group insensitive but we want to right-click at any individual component of that button so.
When we right-click that we can see what macro has been assigned clicking the assign macaron right-click and we can see that there is a macro called project new project new let's go into that macro and take a look and see just what that involves so when we click on the VBA we go inside the VBA and we have our module called project macros and we double click on that we see we have a macro called project new now primarily we're gonna focus on sheet1 so that's the most important sheet one so we're going to just work within that and of course we.
Want to set the project load to true because when we do that it allows us to do certain things without saving without trying to save and I'll show you why that later but b2 must be true we also want to set b3 or to set project new to true as well okay we want to make sure that the new project we want to differentiate between the new and existing so b3 is going to do that for us and basically what I want to do is I want to clear out a whole set of fields and make sure that when you clear out.
Your fields make sure that if you have a merged cell like this project description when we clear that out we must include all of the cells in that range so that must mean d15 through I 17 if I just try to clear out d 15 it's going to give us a bug if you've had this bug before let me let me create that bug just so you can see what kind of bug would appear just so you know so if I take out D 50 if I try to Clara just D 15 and I don't and I exclude I 17 you're gonna run into this kind of bug.
Right here cannot change part of a merge cell so if you see that bug if you see that bug it's because probably you have not included the last part of that merge cell so make sure you have that full range now when you're adding a value if you're adding a value you just need D 15 but when you're actually clearing it out you need all of the cells in that merge cell so you need to include all those and of course I want to clear out all of those fields so we can get ready and.
Of course I wanna then change those when I create a new job I want to do two things I want to hide this button set right here called existing project group I'm gonna hide that and I also want to show this group here new project group this group I want to show that so don't want those two things to happen within the code and I do that with just these two lines of code shapes existing project or physical equals false right it's no longer in existing so we're gonna hide that group of shapes and we're going to show the new project.
Group we're gonna show this particular button set or a new project group we're going to show that with MSO C true that's gonna display it and next up we're just gonna reset the project load to false now that we've made all the changes and so we're gonna set this to false now why do we set b2 to true and then to false because in certain changes when we for example in the project manager code when we make certain changes I want to save it for an existing job I want to take those.
Changes and save it for example when we lo project or when we change resources when we change something we only do the following when b2 is false like we're only going to load a project when b2 is false so we want to make sure we're only gonna save changes when b2 is false only change is saved so that's why we set it to true and then back to false and I'll go over this code with you in a moment but I just wanted to show you what how that works all right we have another one it's very.