Of customers and of course an admin screen where we're going to be able to customize colors such as for priority we'll be able to add icons for the scheduling we'll have individual folders to track pictures and files maintenance types skills list a schedule and a whole lot more excel is a powerful tool and i'm going to show you how to do that today so get ready for the training it's going to be incredible we're going to go over every function every type of formula we're going to go over these really cool dashboards where we got slicers and pivot charts i've got a bunch of orders we'll be able to save and add customers and a whole lot more for you so i hope.
You stick with us for the entire training because there's so much to cover in this incredible application if you like these trainings i create these for you each and every tuesday all right this application is absolutely free all you need to do is click down in the description there i've got some links for you for both email and facebook messenger and we're going to get this sent over to you if you'd like to support us there are so many great ways to support these free trainings.
One of them is obviously you can get a full 200 work pack of my best templates and it's just 77 that's a great way to do that that link will be below another way is of course to support us on our patreon platform patreon platform is a great way to do it because you get involved directly with me and what you want to see for example if you want to see me add something like drag and drop scheduling if you want to see me create automated work orders so there's a feature or maybe a fix on any one of these trainings you can have that done for you and everybody else.
All you need to do is see the posts in my patreon platform recommend what you want to see and i'm going to try to get that done for you that's our patreon that of course we also have downloadable pdfs we have early training so you can get in earlier you can get all these pictures resources and icons also in our resources package on patreon so that's a great way to support us all right let's get started on this i've got this obviously six seven part training we've got a dashboard orders right a schedule equipment technicians customers and an admin so we're going to be going.
Over each part of this right it's part these are individual sheets okay in this particular training i'm going to be using excel uh 2000 the latest excel 2019 2020 depending upon what version you have but this is the latest so we're going to be using that this time right it depends what do you like to see i've been using 2010 in the past right because i want to make sure we don't add features but if you want to see me use these newer versions let me know in the comments below i always appreciate your comments your likes your shares that really helps us so this is where we're going to be creating the work maintenance manager our admin screen let's just go over some of the features i've got a specific folder set for our technicians our technicians have.
Pictures those pictures are visible here inside our schedule right we've got an icon that shows the status we've got a different color and we've got our technician picture so if the gym is kind of small there but if we zoom in we can see our technician's picture there and also we've got a lot more than that so inside our admin let's move that over here i've got also right equipment pictures folder notice that our equipment also our equipment we have the ability to add and display equipment or add equipments to specific orders and those equipment can be repaired or whatever.
And the frequency at which we want them repair but we need those pictures stored in a folder so that is going to be on our admin and we'll be able to browse for that right here i've also got icons and attachments attachments to something i'd like to add for you in fact we can add that on on our patreon platform i may do that so this includes equipment but i may want to do attachments and icons icons are really cool why is that important if we take a look back in our admin screen and we see we have an order status we have a pending order status scheduled in process and completed and i've got a specific icon located in a folder in this particular folder.
And this icon these white icons will be displayed on the schedule based on the specific status of an order pending so if we take a look inside the schedule we can see that we have different icons so if we zoom in we've got these different icons we've got job in progress we've got completed we've got pending here this one so those icons can be dynamic and we can display them and they're automatically going to get refreshed as we move and navigate through them and the jobs that are scheduled on that so a lot of really really cool things that we get to show you on that and also in the admin screen we've got uh scheduling do we want our schedule to start on what day maybe we want to schedule on monday and then of course that schedule is going to automatically.
Once we refresh it it's automatically going to be refreshed and we see it starts on monday now so our schedule can start on any day of the week if we decide we want to start it on sunday and we go back to schedule refreshing the schedule it's now going to start on sunday so we've got a fully dynamic schedule we can select the year we can refresh it here and we can navigate of course so that's going to come from the admin screen what days do we want to show right i'm going to develop this this one i'd like to add some conditional formatting on this so we can hide or show just our.
Certain days we want we can select those workdays on a single click we can have a selection there obviously whatever the order status we've got maintenance types what type of maintenance type is it for a particular job right if i have a specific order here and i'm going to be installing a brand new particular or maybe i want to do a new install or maybe we're going to fix it what type of maintenance are we going to do is it a repair is it a replacement inspection so i want to be able to know what type it is and i want to know what maybe a specific location if it's in the kitchen.
I want to know about that and we may want to save it and so i want to be able to display that right
And i also want to be able to save the order i want to maybe make some updates to some customers and we're going to save that customer too okay so we also have frequencies how often should we complete this job right if we take a look inside the orders we've got a list of frequencies right i need to know if it's every day how often we do it maybe we want to go back every six months right so this particular job we want this recurring every six months so we can save that and then we can have that order saved and it'll reoccur actually every six months so we're going to save that information in there all right so that's what really important we've got those.Frequencies because we have to know how often that we're going to reset we've got times a list of times and durations that is going to also help us out on orders because we want to know when to schedule this right if i've got a specific time of disappointment i want to change that time and i want to maybe change that duration i want to be able to do that with the drop down list and then saving that update okay i also want to be able to uh filter those orders i want to know orders are we going to include completed orders or we're not going to include completed dirt right if we decide we're not going to include completed orders only those that are open right we may want to search a specific customer we may want to search by a specific order id or maybe we want to.
Search by a specific customer right we can do that here too if we want to search all mary's customers we all marries jobs we can do that right or maybe we want to search by specific equipment that was installed i want to know where this equipment was sold so let's take a look inside here if i got air conditioning i want to know when air i know that that that air conditioning was installed or worked on in these three particular jobs if we include to complete it of course it's going to be a lot more than that so we can do that so there's a lot more we can do on that clear the filter and.
Show that all the jobs that we want to do all right so how are we going to do this so there's a really really great search and filter that i'm going to be showing you here so we're going to do all of that all right let's just start out with this really cool menu if we take a look at this we've got a little shape based menu here if and each one of these are individual sheets so we've got our admin sheet our dashboard orders we have a scheduling sheet an equipment sheet right so if we want to get to those sheets we can use these quick locks right so all i need to do is just click here or here or here and so these individual shapes are based on the individual sheet we then have a few database sheets that's just for the data so i've got a orders database this is going to track all of our orders right or we're just going to store all that information i've got our order.
Equipment database these are the equipment that are stored on the specific orders so if we were to take a look in order number one we could see that as well so back into the orders right if we want to look in a specific order we can do that as well so there's a lot we can do so we want to know what equipment like these two equipment were on this order number six right so we want to make sure that we store that notice order number six has two different equipments so we want to store.
That in the database as well that's for every specific order number and i've also got some pivot tables that we're going to be going over all right so let's get into the menu and see exactly how we created this really cool menu well basically what these are it's just a few lines of code in fact it's just one line of code along with two supporting lines of code on this so the most important thing is to create these shapes so that's what i've done here i've created these shapes and also what i've done is i've created two different icons for each one so each shape each screen has both a white and a blue icon notice that it when it is off color that dashboard has.
That white icon whereas it has the dark blue and keep in mind that when it is off right those off colors are going to be in white so all i did was pretty much create some shapes here and i just it's very very easy because i created this shape and then i place this icon right on top of here now the key the important thing is when you're developing a menu like this and you don't want to use a lot of code to do it is critical with the naming of the shapes right so let's take a look at some of the naming that we have and we see inside the shapes here we see that we have this called uh.
Let's take a look at this one on here this particular shape here we see the selected shape is called dashboard dashboard right that is the shape name and when i select a single shape we can see that name up here dashboard right so we have to make sure that the name of the shape is exactly the same as the name of the sheet very very important name of sheep and that's also the name of course of our code namesheet so we want to make sure that we're consistency and that means.
And that includes both for the icon and the shape itself has to have the same name okay make sure you can use this right you can you can edit those names directly in here inside the selection panel and that's an easier way to do that but keep in mind that each shape right is exactly the same name as the sheet shape when we have that kind of consistency it makes it very easy inside the code to then switch on that now of course you can use hyperlinks as well to switch sheets but also i.
Prefer vba too you know i like vba but hyperlinks would work just as well we could hyperlink these
As well so to do that of course we would just add that hyperlink all right but the best way to do in vba we're going to assign a macro to that and these shapes we get and assign a macro so we're going to call this menu select and the good thing about that is regardless of the icon regardless of the button all of them are tied to a single macro a single macro so when we edit that macro we're going to go into the vba editor we take a look at this that vba editor we can also.Use alt f11 to reach there alt f11 you can also get there through the developers tab and the developers tab is right here and using the visual basic you can also get to that developers tab inside the options right and then also you have the customize the ribbon and you want to make sure that that developer tab is selected so we're going to go inside the developers tab we can close this for now inside that individual basic what we have is i've got a few applications and a few different modules right i've got four different modules each module has a set number of macros so we're.
Going to focus on the application macros and the macro that we were just talking about is called menu select and actually all the work is done by a single line of code and i've wrapped that around application screen updating false and then we're going to turn it on to true and that makes a lot less flashing so remember the names are critical right the names are are critical so the name of the shape that called it is called application color that's the name of the shape so if i take that name of the shape and i use the same name the sheet name notice that these sheet names are.
Dashboard equipment right customers right so all them have the same sheet name so if i do that this is going to activate it and i simply wrap that around screen updating false and screen updating true that makes the flashing a lot less and that's all we have to do so one single macro to control all those menus consistency the only important thing that you need to remember is when you focus on is those names as long as the name of the shape is the same as the name of the sheet then you're good to go that single line of code will help you also keep in mind that if you do try to run this particular macro from here you're going to get a bug and that's simply because there's no shape that called it right it's looking for a shape you didn't call it from a shape because we called it.
From this little run continue or f5 right and that's why so you'll need to run this particular macro or anything with application color in it must be run directly from a shape on the screen okay that is it all right we've got a few different macros on the admin screen while we're going over just a few and that's have to do with browsing with folders so if we scroll down here to the admin screen here we've got different right i want to be able to browse for a particular folder if i click here i want to browse for the specific folder that we have right so how are we going to do that that's going to put that file path of that folder directly in this cell for technicians in e5.
Or e6 or e7 so that's relatively a simple macro we're just going to browse for that and that's the we have basically three different browse for technician equipment and attachment folder right and also equipment pictures so that's a little bit different but this particular one right all we need to do is we're going to focus on the job folder as a file dialog what we're going to do is we're going to set that file dialog right going to dimension that that job folder is a folder picker right we're looking for a folder picker under the application file dialog then we're going to focus on that with the job folder we're going to set a title as browse for the technician folder.
Right we're going to allow multi-select and that's false right we only want them to select one single folder therefore the allow multi-select must be false if dot show doesn't equal negative one then go to no selection and that means when they do not make any kind of selection right we want them to skip this step right so that step is basically going to take that entire folder path and place that directly in e5 on our admin screen that's it the others are exactly the same except we're placing it on e6 and e7 that is it okay we also have a refreshment equipment pictures right i wanna this is a simple macro that what i want to do is i want to be able to take the equipment pictures and here's our equipment and i want to refresh those i want to.
List all those equipment pictures now remember we need a few things to get those equipment pictures i want those stored in specific folder and i've got them right here inside a folder called equipment picks this is the folder that i have this is that folder that we browse for so here's all of our equipment pictures right now what we want to do is we want to make sure that the names of those pictures are exactly the same as the names in our picture file so when we combine this picture file name along with this particular folder here this folder along with a backslash one additional backslash then we are going to get the full file path of that picture so what i want to.
Do is i want to be able to create a link and that link i want to be able to refresh those pictures run this macro and have all those pictures refresh we also want to give those pictures a specific name this is called equipment 4 basically on row 4 equipment 5 equipment 6 and so on and so forth so when i refresh those pictures the first thing that i want to do is i want to delete every single picture in here i want to make sure that they're deleted using the macro the refresh picture will then clear that out and then add the new ones so how are we going to do that well we can do that through this little macro right here the first thing what we want to do is we want to dimension the last row i need to know the last row of the picture right if we're going to loop through all.
The rows our last rows row 44. so we're going to loop from 4 through 44. we're going to look for this if this we're going to combine that with the folder if we do find the correct file path we're going to place that picture so we're going to focus also on the equipment folder as a string and the full file path meaning the folder along with the name of the picture is going to create our equipment path and we're going to also we also want to remove all the existing shapes so we're going to dimension the equipment shape as a shape so the first thing we want to do is mention is remove all the existing shapes but we want i don't want to remove all the shapes on the sheet right.
Because i've got other shapes here a lot of other shapes i only want to remove the shapes that are right here in this list in column j so how do we do that well if you see each one of those pictures contains the word equipment item and then there's a number so if they each start with equipment item or equip item then what i need to do is check to see if the shape name contains equip item if it does then delete it and that's just what we've done here using the in string command if it.
Doesn't it could result in error therefore we've draw wrap that around on error resume next and on air go to zero so what we're going to do is we're going to check for each shape on the sheet we're going to loop through every single shape every single shape in the entire sheet the equipment dot shapes we're going to loop and what we're going to do is we're going to check for the name if the equipment shape name contains equipment item and that's greater than zero meaning it is found at least one instance of that has been found in the shape then we are going to delete that shape and that's all we have to do we just loop through that and that's going to loop through.
Every shape on the sheet and it is going to allow them to delete us so once we've deleted it we then want to add the new ones so the first thing we need to do is determine the last row based on column c our equipment id is now in column c we've left two columns for our menu so inside column c we're going to get the last row if the last row is less than four that means there are no items and we can exit out of the sub okay so the first thing we want to do is i want to take that path that equipment path located in the admin screen in e6 and i want to put that in a variable called equipment folder and then what i want to do is i want to loop through all of the equip all the items in that list starting in row 4. to do that we just want to do i also want to make sure that.
Our equipment path contains the correct value so what we do is we're going to build that equipment path it's going to be that equipment folder and then remember we're going to put in that backslash as i remember then what we're going to want to do is we want to take what is the contents of i column i and the equipment row now column i of course contains our picture file name so the combination of that file name plus the file folder is going to be our full file path however we don't know if it is a correct path yet so we do need to check for that we can use the directory command to do just that if the directory equipment path vb director equals.
Empty that means it is an incorrect path what we want to do is we want to skip it because it is something's wrong so we're going to go to the next item and what that's going to do is going to skip all this and go right here to the next item in the loop however if it is correct we want to insert that we're going to use the pictures command pictures dot insert we're going to insert that full file path what i'm going to do right then is give it a specific name we know we want to give it the equipment item right that's very important because we need to be able to remove those and then we're going to do is we're going to assign it a very unique number based on the row which is fine as long as they're unique we're going to do that once it's entered then i want to work with it i want to give it a specific size and a location so to do that we can then focus on that.
With the shapes equipment name and the equipment row with that new shape that we just created we can then work with it i'm going to give it a left position the position is going to be based on the equipment remember i need to call out the sheet once again because i'm inside another width so that's very important equipment and i'm going to focus on column j and the equipment row i want to place it on the left but i'm going to move it over i don't want it directly on that left border i want to move it a little bit to the right so we're going to move it 10 pixels to the right then also i'm going to give it a top position also based on column j and equipment row in a top position but i don't want it right on the top i want to move it down a little bit so that it's a little bit off the top and then i want to do is i want to lock that aspect ratio if i'm going to be.
Resizing that height or that width i want to make sure that we lock the aspect ratio that's very important so we can do that with dot lock aspect ratio equals mso true then i'm going to set the height i'm going to set that height as to the maximum of the rows and this is helpful because it is going to be based on the row height so that it doesn't go beyond the height of this row so whatever the row of the height is is going to be the picture and that's kind of nice because if i change the row of the height here if i change the height of those rows and then we refresh the picture it's automatically going to be based on that so i can refresh the picture here and you see the pictures are going to be bigger and we can also return it back to what it was we know that it was a 32 so we just have to go back down and then change those rows to 32 so it's very very.
Nice and handy if we have that based on that so we can resize the pictures accordingly and all we need to do is just refresh and they're going to return back to their original size so it's going to be based on the height of that row to do that we can set that height is going to be equal to the equipment the equipment bro and the equipment row means the entire row the height of that row minus four means i don't want it the exact height i want a little bit less than the height that's it that's all we have to do to create those equipment items and it's a nice look right so it's always going to be based on that specific real height so if you want a less row height you can do that a very easy.
Way to display those pictures very good so we've got that covered and next up what i'd like to do that's it that's all we need to do for application macros and you notice we have some orders here so we've got a lot of orders and some macros in the order so let's go back into the orders and we're going to focus on this in this we've got some filtering mechanisms here that we went over we've got the ability to save an order we can add a new order we can load an order by selecting something we can print the order we can delete an order we can also save and update the customer if we want to add a new customer we can do just that right if i want to add fred's brother fred freders roger.
And then i can do just that one two three four five main street we can add is just simply save or update the customer it's gonna automatically save that new customer so if we take a look inside the customers and we see that this has now been added our brand new customer has been added so it's a really cool feature to be able to add customers very quickly and very easily so how are we going to do that well the best way to do that is to be able to simply know what customer rows so we've got to know a few things we've got to know what customer row is on if it's a new customer we need to know that we also need to know if it's a new order so we've got a few order and few and also.
Want to know some equipment or we want to be able to add some equipment into the specific order as we went over so we're going to go over the macros that do all of that for us so let's take a look inside the vba code and inside this module and see just how we did it it's called the order macros module and of course i need to dimension a few variables to help us along with these macros so the order rows the row that that order is on and i also need to know the order column we're going to use data mapping and i'll be going over that order column we're going to loop through the columns i also want to know the database of that equipment row remember we have individual equipment we need.
To know the we need to know the row where this equipment is stored that's important so i need to know that row also i need to know the information of the last row for looping through and the equipment row what rows when i need to know what row the equipment is on right is it on 21 20 where is that row because we're going to loop through the equipment and make sure we add it so we need to be adding it's going to go from that and determine how we add those equipment because we can add multiple equipments on that all right so continuing on so we're going to dimension that and i also need to know the equipment folder right if i'm going to be displaying that i need to know what folder our equipment's started the file path the selected row the last results row and the results we're going to be running an advanced filter so i need to know that okay the first thing what i want to do is i want to be able to load this list right if we saw this list we can load.
This list based on a customer we can use a filter here so we need to reload this list based on the search value if we search for a specific id i need to know that i need to reload this list if i clear it i need to do that so how are we going to do that how are we going to be able to clear this list automatically and load the list well the first thing what we want to do is we want to make a change when the user makes a change here to f5 or when they make the change here i want to load it back right if i select this here we're going to run a macro or we select this clear button we are.
Going to run a macro so there's some macros that are associated with that so that's called load list now this is going to be interesting because there's two different databases we know that our order id and our customer are stored in one database if we take a look at the order id we've got order in our order database and we've got customer so if i'm going to be filtering those that's great but however what if i want to filter equipment now we notice that our equipment here is stored on here all the equipment here and the orders here right so if i load order number one we see we're going to get these four different equipments right if i take a look in orders and i decide i'm going to search order number one and i want to select that order number one we see that we've got these four different equipments right so we need to know if i want to search for a specific oven or if i want to search for i've got a different database right.
We're either searching for customers and orders in one database or equipment in another one so keep that in mind that's going to be different so order equipment database we're going to create a brand new different advanced filter based on what was entered here so back into that first macro so we're going to focus the first thing we need to do on this load list macro is clear out the current list with the orders b6 now this is our selected row b6 and i'll go over that i should go over that now to help you out so b6 is the selected row if i'm going to if i select a row i need to know what row selected so let's take a look in columns a and b.
And see what we have here b6 here is that selected row i'll go over these things as we go in the code so we're going to use conditional formatting for that so if we go in to our home here and we see conditional formatting and we manage those rules we see that i've created a few different rules one is going to be based on our mod row look at the mod row here mod meaning even rows mod meaning auto row so i want to color those audio rows based on two conditions one e7 our first row doesn't equal empty or e7 and of course our even rows here and our odd rows here our odd rows are going to be colored light blue our even rows are going to be colored white however our selected row here in b6 i want to know i want to give that our theme color which is that.
Fade from dark blue to a little lighter blue i want to bold the font and i want to make it white so you can see that clearly here and it supplies to all of it so i want to make sure that when we make a selection on the selection change that's when we want it to run so b6 we certainly need to clear out right because once we clear it we want to clear that and i also want to make sure that we clear all the data so from e7 all the way down to f in the last row or whatever we want to make sure we clear that out so we do that here clearing the contents of the orders then what i need to do is i need to determine what sheet are we going to if this says equipment and then i want to search for a specific equipment such as air conditioning or part of it perhaps i want to know those orders that contain air conditioning so that's going to be a filter so i.
Need to differentiate between those two databases based on what is in f4 if f4 is equipment we are going to be searching this here this database here however if it is either order id or customer we are going to be searching our orders database here so we need to differentiate that inside the code here so we can do that here so if orders f4 just added this equipment and i want to make sure that enter search we have not added any particular search term right we want to make sure that it's not the default search term the user actually entered some type of a search term inside directly.
Inside here it's called f5 right i want to make sure f5 doesn't contain when we clear that out we see it contains enter search so enter search would not help us and also i want to make sure that they've actually entered something other than enter search in there so to do that right i want to make sure that both of those are contained then in that case what i'm going to do is i'm going to focus on that order equipment database that database first thing i want to do is i want to clear any previous results we're going to have results we're going to run this through an advanced filter this order equivalent order database and that advanced filter is going to.