How To Create A 1 Click Estimate, Work Order & Invoice Application In Excel Today [+ FREE DOWNLOAD]

hello this is randy with excel for freelancers and welcome to the one click estimate work order and invoice that's right with just one click we're going to be able to create an estimate and then transfer that to a work order and copy that automatically to an invoice we're also going to have a drop down dynamic add new list print email and a whole lot more we're doing it all from scratch i cannot wait let's get started all right thanks so much for joining me today i've got a really terrific training today in business we often have to create estimates for our customers and then when we complete that and get that estimate approved by a customer we need to turn it into a work order for our staff then once it gets complete we need to turn that into an invoice today i'm going to show you that and a whole lot more we're going to use the same screen for that and we're going to just be able to take certain items and bring them along through the order process until it gets completed very simply with a single click we're going to use a lot of conditional formatting we've got great formulas in here some good vba code great work with shapes so it's going to cover a lot we're going to do all of it from scratch at least on this screen we've got some database set up and some information we're going to do all the coding for you i hope you do like these trainings i bring these to you each and every tuesday this workbook is absolutely free all you need to do is click the link down below using your either email or messenger and we're going to get that sent over to you if you like these trainings i'm going way above and beyond these trainings in our brand new patreon account that's right with this workbook you can also get a complete beautiful pdf code book and that's completely organized so that you have this code each and every week in a pdf format you can read it study it perfectly beautifully organized along with that i'm going to be offering additional workbooks and additional training beyond that based on your suggestion so if you want a certain feature or a function or you want me to fix something or you want me to focus on an issue over training i'm going to do that inside our patreon account i'll create an additional training video a shorter one plus an updated workbook each and every week plus we've got a whole lot of benefits of that i can't wait to share that with you that's with our patreon it starts at just three dollars a month i'll include the links down below brand new patreon that'll help support us all right let's get started this is a sample so obviously we're not going to be using this but it gives you an idea of what we're going to be trying to create we've got some add new buttons save update delete email print and order estimates and a whole lot more all right without further ado i'm going to close this sample one so we're just going to save it and we're going to close it out i hope we do get subscribed on this channel don't forget to click the notification icon bell that'll make sure you get these strings this is the workbook we're going to be working off of so you can see it's a blank screen so we're going to get started on this but before i do i want to kind of give you an overview of the workbook in itself in our admin screen we've got some things that are set up already it's going to help us move things along and the cool thing about this one is it is dynamic meaning you may not want to call them estimates you may want to call them bids you may want to call them proposals whatever you want to call it you can put it in here in this order type same thing with work orders they might be called work request work order or whatever else you want to call it you can put that name here and then also invoices some people call them receipts or bills or orders so whatever you want to call you just put it down here so we've got that we've got some dynamic vocabulary that's going to help us in our application i've also got a list of job sites we can assign a job site to a work order an estimate or an invoice that's going to help us dynamic taxing so that we've got a name set up here and it's called tax notice the tax name up here that is the name so if you want to call it gst or whatever we want this dynamically to appear on our invoice and estimates so we can do that also we want to have a tax rate notice this tax rate is also a named range up here so currently eight and a half percent you can set it to whatever you want i've got a list of service technicians here that's going to be a drop down list on a work order possibly on our invoices we want to assign a certain invoice work order to a service technician so we've got a dynamic list here and also i've got some footer messages we can go ahead and center this footer messages is going to help us with this application so we can set different footer messages whether it's for work orders whether it's for specific invoices or even estimates we can have dynamic footer messages i also want to know which one is going to be set as default when i create a brand new invoice which which one am i going to use i do have some code set up here am i going to use this for our default estimated footer message or am i going to use this for our default invoice this would be for our invoice right thank you for your business so we've got footer messages that are dynamic based on the form that we're going to be using okay that's going to be really helpful i've got an orders database this is the database that's going to store our orders we've got an order id an order date the type of order is it a estimate a work order an invoice we've got the customer assigned the service tech assign if we've assigned the service tech the job site a footer message if we've added a footer message we've got the total amount and also we've got the estimated id the work order i'll go over these things a little bit later on so that they're more clear and then also what we've got is the order ids it is the individual items that go on in order right so for example these three items went on order number one this was an estimate these three items were brought over to a work order this is the type so these three items are the same that are brought over to the work order these three items were brought over to the invoice so each one comes with a description it comes with an item number a quantity an amount a total an order row this order row signifies what order we're going to be using here on the order we're going to build that up shortly and then also we want to know the let's go back here the row that it's on and then also the items database i've got a database of items that includes an item name the name of the item the sales or estimate description this is the description that the customer will see we've got a work order or service description this is the description that will go on your work order so you print out your work order and it goes to your employees it's a little bit different so for example if we've got a 50-inch bathroom vanity cabinet we might have a work description that says and assemble and install the bathroom vanity cabinet so this is the work order description we have a default quantity what is the quantity for example certain things we like if we're putting installing a bathroom we might have two stainless steel supply lines we rarely install just one alone so we might be putting in two so we want to set up a default quantity of two okay we've got a sales price we've also got the labor material cost i want to know that cost so when we do the work we understand the total cost of the job very important and i also have a customer database just a basic database customer id name address to phone email and status okay so let's get started building this one out i do have some information here that's going to help us we'll be going over that shortly but let's get this update the first thing what i want to do is just give this a background color and this color is going to change eventually but what we'll do is we're going to start it off all the way let's go all the way to y just a big one over here and we'll bring it over here and then what i'm going to do is give it the the the invoice we're going to format cells so this conditional formatting is going to be our base formatting and that's going to be for our invoice so i'm going to give it a specific color all right so we'll go into fill effects and then we'll bring it over here and we'll just bring this blue so i'm going to go with this blue here and then a medium blue so it's going to just fade down a little bit and then click ok then on the row below we're going to do it just a little bit below that right a little bit lighter than that so i'm gonna again format those cells and i'm going to fill this and then this fill effects also fade effect then what we'll do is we will go the lighter blue and then we'll just set the background color and click okay and then okay okay next up i'm just going to color all the backgrounds go down to a large row and just cover this color this that blue and then what we want to do is we don't want to color that invoice but we'll work on that okay so now we've got our basic color here and what i want to do now is go into our invoice our invoice is going to start probably right here on e3 so e3 is where we're going to start and we'll bring it on over all the way to let's say i right i so i want to keep because i want to have one for the item name here let's see item name here i want to have the description here i want to have the quantity here the amount the total here so that's what i'm going to want for that so we'll bring it over here starting on e3 all the way to i and then down we'll go down two so let's say row 38 38 bring it all the way down that'll give us enough for both the totals and there okay so i've got that this one we're going to color white so i'm going to use this as a white color the white background okay so i like that that's going to that will be sufficient for us what i want to do is i want to put the title here what is the title going to be so whether it's estimate invoice so i'm just going to put invoice okay it's already formatted here a little bit for us to help us move things along okay so that's going to be our invoice and i also want to know here what is this but i don't just want to know the invoice i'm going to change that text in just a moment but basically that's the idea but it's going to be dynamic right it's going to be based on whatever the order type is now that order type is going to go right here so for example i'm going to this is going to be dynamic whether it's work order estimator invoice is going to be changed here so what i'm going to do is i'm going to put something like let's just say invoice here and then what i want to do is i want to base this title on whatever is here but i want it an uppercase so how do i do that so i'm going to do so this i've already named a range i've called a name range called order type notice i've set this cell just to help us to do that all you would need to do is just type in order type here and then it automatically sets the name range so that's relatively easy now what i want to do is i want to set this dynamic and i want to do it equals to 2 but i don't want to set it in lowercase i want to set it in uppercase so to do that i'm going to add a formula upper all i'm going to do is just going to send the uppercase of that so now it's an uppercase that's exactly what i want okay perfect next up what i want is i want the number here so again i want something like invoice number here but again it's going to be based on whether it's a work order an estimate or whatever so we need to again make this dynamic based on whatever it is so to do that again we'll just use the same thing so in this case it's going to be equal to order type and right and then i just want to put in let's say the space and then let's say the number and then a colon in there there we go so now as we change this to work order it's going to automatically change here both of them and that's exactly what i want i want it dynamically placed okay so then again i want to do the same thing i want the date here but i want to know is it an invoice date an estimated date so again we're going to do something very similar to this so i'm just going to copy this i'm going to paste it down here and this time what we're going to call this order type and let's call it date so instead of this i'm going to use date so now what i want to know is the invoice date and next up i want the service tech this won't be dynamic service tech who's the service tech assigned i'm going to put that in five there and then in six what i also want to know is the job site are we putting in a job site job site what is the job site that's been assigned so now we'll put on colon so that we make it consistent for each one of them and then also we'll put the date in here so let's just say we're going to put in let's say this is invoice number one the date of let's say five eight and let's say the service deck what i want is the drop down list of service text and i want to drop down list of job sites now i have those set up to make things a little bit easier so back into the admin let's go into the formulas name manager and we take a look we've got some names set up so let's take a look and run through these all of them so we can see the customer email based on an offset meaning it is dynamic as it grows so if we tab over here we see that we have a dynamic list called customer email based on this email list as our list grows so does this we're using the offset if we zoom in here to this offset formula right here we see that's customer database f2 one row down because we're starting on the header row count a two columns count a we're counting i want to know all the ones in b2 we can use the customer name because that would be required so we're counting all one's b2 that's it that's all we need to do for the dynamics so we've got a customer and i've got a customer name here tabbing over customer name same thing i've also got a extracts those have to do with our advanced filters so we'll get into that little message i've got a footer message notice the footer message are based on the footer message names right here also in offset pharma we've got an item cost this is based on the item cost the cost of the atom monitoring cost we've got an item name based on our item name here so we've got a named range called item name i've also got one called job sites okay these are based on the job sites again dynamic named range i've got an order id based on the order remember these are the order ids orders can be estimates work orders or invoices based on the id i also have an order type notice the order type we did go over that's exactly the order type that we're using in these formulas here so we have that print area we're going to be defining service text of course we don't have i guess that's not we don't have that actually i can get rid of that but service text because we already have our let's take a look at that oh we've got here i'll update that f8 so they're going to update that the reason that is a merged cell so let's update that to make sure that we have that count a should be not f we're counting a single cell so tab out of that now we have because it's a merged cell notice the service text f a through f g right but we only want to count a single column so that's it so check that and now it's correct so service texts are dynamic okay and then we have the tax name which we went over and then we have the tax rate which we went over okay so that's it for the named ranges so i want to add both job sites and service technicians to our data validation so back in the orders here's our service text so i'm going to go into data data validation here and i'm going to choose a list and what is that list well i'm going to choose f3 so we can easily select from our service text signing down here service text that is going to be and the same thing for job sites again i'm going to go into data data validation lists here and we're going to choose a list and then i'm going to say what is that source again f3 we're looking for job sites down here and job sites is the one clicking ok all right so now we can select the service tag here and we can select the job site here perfect okay so we can save our work so far here and also i'm going to drop this down because we're going to be working on the formatting a little bit all right so we've got that so what do we want up here i want to put a customer i want to make sure that the customer in name is automated there and that's going to go into e5 so i'm going to put in the customer that doesn't need to be dynamic because that's the same thing so the customer here and i want a drop down list of customer names again data validation here list and then again f3 to make sure we have the right one and then the customer name clicking okay click all right okay so now we've got the customer name perfect i want when i select a customer name i want the address 1 and address 2 to appear here so we're going to get to that in just a moment what i also want to do is i also want to have a header row here i'm going to put that header row directly in row 9 so right here what i want this to be called items could make this dynamic to the items description and then next up i want the quantity that's just a small column next i want the price and then i want the total i also want to give it this blue background and i want that condition i'm going to be based on conditional formatting as well so i'm just going to give it that blue background and i'll show you what i mean by that because only invoices are going to be blue so i'm going to give it that invoice blue and then i'm going to give it just some borders all the way around here so black border will be sufficient i also want to border around our entire invoice so i'm going to put a border around here all the way around just around just all the way around here and we'll go with the outside borders here okay so next up i want the items here and i want some basic just in here now we want the totals here i want to about three rows for the total one two three so in this row here i would like to put in the subtotal that's giving 37 sub total and then i want the tax but that's going to be dynamic based on the tax name and then i want the total okay so i want that here then i want those so we're going to do is you're going to right justify these here and then i'm going to left just well we'll keep those on the right because they're going to be amount so i'm going to make sure that those are accounting they already are and then i'm going to put borders around these all okay so we're just going to go the borders around this one here i want the footer message i want that footer message to go right in here actually let's bring that up one more i'm going to bring it to 34 let's bring it up a little bit more because that's the way i had it before that's going to be more consistent okay i like that better let's color this in blue and change the borders around i'm going to format those cells sorry it's off the screen and then what we'll do is we'll put the border on the top here and then we can get rid of these borders here okay so that looks a little bit better i like that better here's already merged in center that's going to be our footer message okay for the message and go thank you for your business okay so i like that and we'll put a border top here format those cells sorry it's off the screen and then i'm gonna all right good so i like that again saving our work so we have now let's put in some borders just a small very light border on here vertical here and then i'm going to format those cells and then we'll just use the dotted line here and on all the sides we can probably go with a lighter gray i would say not a dark just so it's light let's go with the lighter a lighter one here that's better here a little bit lighter okay and click okay all right we're going to use this border for black okay so i like that now our invoice is getting built out it looks really good we've got everything here but i want to put that dynamic tax name well what's that tax name going to be it's going to be equal to tax name and what else i want to put in the parentheses here and the parentheses and then i want to put in the tax rate and what is the tax rate right but the tax rate itself is not going to be formatted right and i'll show you that and parentheses so we got that but i don't want 0.85 right i want that really that's showing the rate so what i'm going to format that's we're going to use the text for that and how we're going to format that well all i need to do is put a comma here i'm going to put in 0.00 and then the percent sign and then close quotes so there we go i like that and then we'll fix that up all right tax name bracket tax rate we want to tax so we're gonna put the text so text i want that text there then what is that text we're gonna put in a percentage on that so how do we do that well we do comma and then the quotes here then what i want is 0.002 decimals then the percentage then closing it and then close okay then the close parenthesis all right that's it so that's it tax 8.5 you can actually add a probably one more space between that so we can do that just a little bit of an additional space here between the tax name okay that looks pretty good tax 8.5 so we want the formulas what is that subtotal going to be it's going to be the total of all of these so it's simply the sum we can use the autosum on this no problem with that and then i just want to add in some all the way here so everything okay great so now we have the total so if i put in 50 here or whatever then we're going to show 50 here if that tax rate now i want the tax rate it's going to be equal to that subtotal times the tax rate perfect and then we have our total it's going to be equal to the sum of our subtotal and the tax rate okay so that's going to be regardless of whether it's invoice estimate work orders we haven't have okay so i like that that's looking pretty good and now all we want to do is our item names so we've got dynamic excuse me we have data validation or items so i'm going to highlight all the cells and we want some lists so what do we want to put i want to put in those item names list data validation here i'm going to put a list and what are we looking for we're looking for item names if we're not sure the name f3 go to item name click ok and click ok perfect so now when i have the item name that's what i want okay so when i select an item name what i want is i want to have that description appear here okay we're going to get to that in just a moment that'll be vba same thing here with the address but what i also want to do is i want to add some shapes i want to add some things i also want to add in additional features here so what i want to do i want to have a search order here search order number here so we can search for the order and here i want to have a footer message footer message so that we can select which footer message we we want and it'll appear automatically okay so what is that footer message going to be based on a data validation again we have that here listing here going to be based on that footer message so f3 we can find that footer message right here and click ok click ok ok so these are going to be in white so we're going to change that background to white here and then we're going to just surround this by some borders so that we can get that cleaned up formatting those cells border all the way around okay and then i'll put a dotted line in the middle and then what we'll do is we'll do a solid between them so i like that that looks good we can search for an order we can search for for a message and we can bring this down we don't need to let's use the longest one which is this one okay that looks pretty good bring it out a little bit more now i'm going to put some button sets here i want button sets six different button sets here and then i want also some guides here i want to know between the estimate the invoice and the worker so we need to add some shapes for that so let's save our work we're going to insert some shapes on that and what shapes do we want well what i'm going to use i'm going to use this particular shape right here this one's one i want to use this chevron here that's going to help us out so to do that i'm just going to bring it over here like this and that's going to be our first one so i'm going to have that let's say call this estimate but i don't want to call it just estimate there because remember that's dynamic right so how are we going to get that dynamic well again it's going to be equal to whatever is in what we have set up in our admin screen so equals going to our admin and then we're going to scroll over here and select estimate so that way the text is automatically so that way if i can change this to bids or proposal if i do this proposal it's going to change automatic proposal okay it's about to write this time so now we go back to orders we see its proposal okay so but we'll stick with estimates otherwise i'm going to confuse myself here and then what we can do is we'll just do estimate not plural okay so that's a little more clear so it's estimate so it's dynamics going to change and i want this basically to give it that yellow color so i'm going to use choose this yellow here so next up i want work order so what i'm going to do actually why don't we let's uh bold this out i want to do a little bit i said that's good right there i'm just going to increase the font a little bit on that one we'll increase this to let's say 14 so it's bigger and then i'm going to center it across make sure it's centered give it a specific shape let's try something like about .25 to 1.15 that should be 0.25 that's enough for the height and then 1.15 that should cover it 1.15 okay so we're good to that i like the way that looks there i'm also going to create ones for work order and invoice i'm going to duplicate that i'm going to bring it over here to the next one and then i'm going to call this work order and i'm going to duplicate that again and we're going to call this invoice but to do that right this one's c6 right the one below in our admin screen that's going to be our work order okay all right so to do that we want to need to create this one a little bit longer here so we'll do this one point let's say 2 5 making sure that's a little bit longer also remember when it go when we keep when we add that formula again it's going to revert back to its original size so we do need to change it each time we change the formula right so okay so we understand that let's make this one a little bit longer and then what we can do is also want to let's right click these and we'll set the properties in the text so we don't need that much spacing on the left and right and so the text box notice it's got point zero one we can go to half that give a little more space okay that'll be easier so now we've got and then the last one is we have our invoice so that's going to be c7 again back to 14 here and i think we're good to go on that okay i like the way that looks let's move it over okay and also what we want to do is we may want to put it behind or in front but i think that looks okay i want to color these specifically so the work order i want to give this one here the invoice i want to make this blue so i want to keep some consistent colors probably this blue for the invoice okay so i like the way that that looks all right we can play with the colors a little bit you know i think i in my sample i had i was probably using this this one here for for the estimate a little bit darker but let's go with the lighter one for now and then what i want to do is basically when we select these it moves along across and we're going to do that with vba we're going to get into that let's just make sure they're lined up right now holding down the control button making sure that they're lined up perfectly okay very good saving now let's continue with our button sets over here then we're going to add some icons so first so again i'm going to insert some shapes here we're going to insert just this rounded rectangle here and then we'll just give it a gray i want to give it a new relatively neutral color on here okay that's going to be all right and then what we'll do is we'll just do a little bit bigger and then i want to give it this gray color so again i want to give it relatively neutral just go with this in this case this is going to be add new so we're going to use this for add new and i'm going to center that because i want to put icons on both sides most of them are going to be centered so this one will center that looks pretty good so what i'm going to do now is i'm just going to duplicate this and we're going to create another one called cancel new this is where we need a little bit bigger of a column here so we can accommodate the buttons here that should be sufficient here cancel new okay so cancel new in this case we wanted let's add that cancel new and this one in this particular one we can format that a little bit to the right here bringing that and then also what i want to do is i want to save update so i'm going to duplicate that bring it over here this one's going to be called it's going to be using the same button whether we're saving or updating orders it's going to be save update and then again the next thing what i wanna do is i wanna be able to print the order so i'm gonna duplicate that ctrl d to do that and then we're gonna call this print order so print order next up after print order what i wanna do is i want to add a delete order so we've got to be able to delete it so delete order and then i'm going to bring that down here i've got some icons next up we're going to use and then after that what i want to do lastly is i want to be able to email that order our customer has an email address so one want to be able to email it turn it into a pdf and email it okay good i like the way that looks let's line everything up looks relatively good here and then we're ready to add our icons okay good here and then good here now let's make sure they're lined up not only horizontally but vertically as well okay good let's uh line that up here and then the same thing here now we're ready to add our icons i've got them saved up already so what we're going to do is we're going to insert here and then pictures here then we're going to locate the pictures it's going to use all of these pictures so i'm going to select all and click insert i'm going to set the size down to about 0.2 on that except one of them will make bigger because that's going to be our icon bring them down here we can see them this one here is going to be our icon so i'm going to bring make this up it's our logo for the sheet now what i want to do is add new we're going to put that right here our cancel new is going to go right here our print email it's going to go here our print is going to go here and we have our save and update and then our delete okay so we're good on those i like those the way it looks and then i think they're a little bit small don't you i'm gonna hold down the control here so we can select all and i'll just go point two two make them a little bit bigger i like that point two two is good and now all we need to do is just center and group them so i'm gonna hold down the control i'm gonna center those and then i'm just going to group them and do the same thing for each one of those centering them to make sure they're vertically centered and then grouping them and then we don't we're not going to be hiding or showing these they're always going to be displayed so we don't necessarily need to name those it's an extra step i suggest you do it in your applications but for this one we've got already a lot to cover so we're not going to be doing it now and then what we'll do is i'm just going to group the last one okay so now we're ready to go now we're ready to add some functionality this week okay so we've created everything i really like the way that that looks and you can of course you can add a company logo here so we've got some vba to get to here a lot of it but let's go over a little bit here the order type is going to go number one or number two so what i want to have is i want to have an order type here basically it's going to be based on this based on this list here if it's an estimate i want one if it's a work order i want two and if it's invoice i want three and i want that type number to go here that's going to really really help us once we're inside vba so how do we get that well we can just use the simple match formula that'll get us there that's all we need is a match so let's do that so it's going to be equals match and what are we matching i'm looking up this here and what is the range that i'm array that i'm looking up here it's going to be based on this right here and i want an exact match so i'm going to put zero and that's going to do it's just going to assign us a one so estimate it's going to change to one and that's just what i want so i also want to know when we're going to be loading an order i need to differentiate between when we're actually making changes the user makes changes here like this or when we're actually loading the order from when we enter an order and it loads all up i need to differentiate between those two types of changes so i want to true or false here so we'll just put false for now that's going to change on i also want to know the order id so the order id whatever order is being displayed so if i've got let's say i've got order number one i want to make sure that we also need to order row here so let's say i have order order number one what is the order row well we can use again match for that too so we can use equals if air just in case it doesn't exist we're gonna use match what am i looking up i'm looking up this order id and i'm basing it on the order ids here and we want an exact match but also i want the row number i don't want it to return one i want it to return in this case four so we need to add three to that because our first one starts on row four if there's an error i just want to show empty so our return is four and i also want the next order id notice i've got i want to know that it's going to be 4 as our next one we can use max for that we've used that before in the past so equals if air max what is a max based on the order ids order id but i don't want the order id i want to know the one after the max of that if there's an error why would there be an error there'd be there if we have no data so if there's an error we have no data i want to start it off at 1. i want to set that default to 1. so our next order is id all right i also want to know the row here i want to calculate the row based on the id here i want to calculate the work order row based on that so it's all going to be based on this so what i'm going to do is i'm just going to copy this formula down i'm going to paste it here here and here i'm going to paste those formulas okay and what i'm going to do so basically when i put an estimate i estimated 4 here i want the road to show up right here so how do we do that well it's based on that there is no 4 but what if i put in 3 3 is contained perfect and if i want to put in 3 here i want to know the row of that work order same thing here so it's really the same formula it's based on that okay so that's what i want there because i each one of these orders like for example if i have a an estimate i want to know if there's a work order associated with that estimate if there's an uh invoice i might want to know that there's a work order associated with that invoice so this keeps track of all of the associated documents for an order an estimate a work order an invoice they all go together so we need to keep track of all those ids together okay we're going to clear out these rows right now just so we don't have anything i also want to know what message the footer which is i want to know what row this is on so how do i know that so that i can extract that and put it right inside here so how are we going to do that well if you notice on our footer message our first one starts with row five so if we return the first one i need row five so how we're going to do that we can do that right here so i want to know the row so it's going to be equals and if air matching what are we matching i'm matching this and three based on our footer messages which is the no and then i want an exact match okay but i again i don't want just to return one i want to return the row and row starts at five so we need to put in four if there's an error just put in nothing that way we know that our first footer message is going to appear on row five also want to know what is the default estimated footer message what is the default work order message and what is the default invoice what do i mean by that the default would be located right here where is this check mark what is that is it here right is the default estimated i want to know when i create a brand new estimate what default footer message should i put in here where is this check mark located based on k so all we need to do is use a match and look in column k so to do that we can just use a match so default estimated footer message equals and then if air we're using a match again one of my favorite formulas what am i looking up i'm looking up that check mark what is that check mark it's actually a character what character is it's character 252. i'll show you how i know that in just a moment and where am i going to be looking i'm going to be looking inside here right here i want to know what row it's on right but again it starts on row 5 right so we know that so we're looking it up there and we're looking for an exact match and i want to know and i want to know plus 4 right because i want to know that if there's an error i'm going to show empty okay that way we're showing 5 it is the same one that i know that air so all i need to do actually we just need to actually change this to absolute here and then we can drag it down now i want looking for this check box right here how do i know that this check box is there so when i go into insert symbol and i'm looking for the check box here i'm looking for this one right here you see this wingdings 252 that's how i know what character number it is and all i need to do is make sure that this font is set to wingding so we look at that we see the font's been set to wingdings okay all right so i'm going to do the same thing for this one so all i need to do is just drag that down here and then we just need to make sure that we actually add in matching character 252. but this time it's not going to be k it's going to be this one's going to be column h i l this one's going to be l l is of course for our work order and then we have m and that's going to be for our invoices so m is for invoices then that's k l and m so we know that our default invoice is located here thank you that's the one our thank you message so it is that one that we want to use and we're locating it on the row but what if we don't want the row now we can use the row or we can use the actual message if i wanted the actual message i can use index but the row is sufficient for now we could just put the row if i know the row we can take that and put that in here if we know that but what if we want to put in the actual message there we could do that with an index so let's say i want to do that so i can do that index the footer message here we can index the footer message and if we're indexing it then we don't need to add the 4 on there we're just going to be using the column 1. that's it so that's gonna actually return that footer message i think that's a little bit better so why don't we do that again indexing we're indexing the footer message what is the what is the array that raise the footer message okay we're using the row we're locating it here we don't need to add the 4 in here comma 1 that's going to get us our actual footer message so we know all we need to do is place that right in here and then it's going to automatically fill once we have the vba working we're going to do the same thing for here again index this repetition helps you learn it for sure no doubt about that footer message comma we've got the row now all we need to do is get the column which is column one and that's it okay thank you so we've got our footer message we're going to save our work and now we're just about ready to start some of the vba that's going to help us navigate this application all right the first thing that i'd like to do is i'm going to color these buttons differently based on the order type that's here so if it's one i want to put a border around this and make it bold and i want to make sure the rest of the ones or borders are not bold and no borders around there so i want to highlight our selected button here so to do that we're going to get into the vba i'm going to go into the developers visual basic alt f11 will get you there and i've got some module called order sheet macros and that's the one we're going to focus on right here and we're going to focus on the order set buttons that's the one i want to focus right now on and then we'll get to add new so this order set buttons basically i want to set those buttons based on what is selection so we're going to start off with with the order sheet this is the order sheet that we're going to primarily do most of the work on i want to set a type number i've got some defaults already set up here i've got some dimension variables already set up here order and i'll go through those as we need them so the first thing what i want to do is we're going to do a type number now the type number is long here type number is going to be equal to basically what is located in dot b range b1 b1 here dot range b1 that is the number it's gonna be one two or three we'll call that the order type number order type number okay so that's gonna be it now with that what i want to do is i want to run a loop so basically i want to run a loop through all of the buttons now to do that the best way to do that is to name our buttons something very specific so we can do that right now so for estimate i'm going to call this order type one order type one okay for the second one i'm gonna call this order type two and then obviously order type three order type two and then for the invoice we're gonna call that order type three that's gonna make it a lot easier to work with them order type three okay so now that we know this so if this is going to be one we want this if it's two and three okay now that we have those if you can see that they're all been named okay so we continue back on the mattress so we got the type number so now all we need to do is run a loop we have another long variable called for order number four order number is going to be equal to one two three basically we're going to loop through those three buttons close our loop next order number also a long variable so inside that what i want to do is i want to determine what the current type is what the actual type and that's based on our variable type number if the order number is equal to the type number then that's the one we want to highlight then do something else do something else okay so this would be the current button current button and what does that mean basically what i want to do and i'll show you how we do that so let's say we're currently on estimates we know this is one we know the button's one so it's currently on one so that means the type is one and when the button is one so what i want to do is i want then color this button one so that's all we have to do there is highlight that button so how do we do that well we're going to start out with the shapes dot shapes and then we know it's order type right that's the name we assigned to it order type and then also and the order number because we're looping through so this is how we use each one order in this case number dot line i want to only increase that line line dot weight the weight of that line i want to make it three so it's a little bit bold right and then what i want to do is i also want to make the text bold so how do we do that dot shapes in this case the same one so actually what we can do is just copy and paste that we don't have to retype it but this time we're going to focus on the text in the text frame so we do that with dot text dot text here text frame to in this case we're gonna so dot text range dot font dot bold equals mso true we're making it bold bold font and then this case increase order width perfect so that's what we're going to do for our selected but what about for the others for the others i simply want to make them return those so in this case what we can do is simply copy this here paste this down here and then reset it not three in this case it's going to be zero so reset in this case it's going to be reset the border width for those that are not reset the border with okay and then this one's going to be mso false we don't want it bold all right so there we go we've got it like that that's all we really need to a relatively simple macro now of course when we select buttons we're simply going to run this macro that's this macro down here okay let's go ahead and test this out we'll bring this down here okay and then we can bring this up here going back in the macro all i need to do is just run this macro that we just created and we're going to see that it's now got this border so basically what we want to do is we've got a good border it's working right it's bold but i want to actually make the borders probably white probably let's go ahead and shape outline and we'll make them white okay so the custard you could really do any color but we'll make it white okay i like that that's looking really good and then what i want to do is i'm going to probably move this one to the top bring to the front that looks good i like and then we're going to bring this to the back then this one send to the back okay so i like gives that gives that that nice effect feel all right good so if i were to change this to 2 right if i were to do that we're going to change this to let's say work order and the macro of course is going to take care of that we run the code one more time i'm going to make sure that that's that work order that gets highlighted okay perfect so that's exactly what i want now theoretically we could bring that shape to the top which would kind of be nice but i'm not sure if we want to do that in other words bring it we can also just move them a little bit over there so they're perfectly separated and then that we won't have to worry about bringing them to the top or something so just giving that that effect okay i like that there that's working great what i want to do now is create the macro that we're going to use when we actually select it so let's call that's called the macro select so that's one order select here that's the one i want to work on now since our macro is working just fine for this to do that again we're going to focus on with orders in this case what i want to do is i want to know the application color that's the name of the button that called this shape so to do that right if i want to know the number if i want to know if i want to extract one or i want to extract two all i need to do is know the name which is this one and i remove the text order type that's going to leave me with one or two and then all i need to do is determine what is here then i need to determine if it's one then all i need to do is put whatever is here if it's two i put here and three then i'm gonna take whatever that text is i'm gonna place it directly in b2 so we're gonna start off with just that so with orders we're gonna do that we're going to set the order shape which is already defined as a shape up here order shape as a shape that's exactly what i want set the order shape it's going to be equal to dot shapes we're already in dot orders application dot collar okay so all that's going to do is set the shape that called it i also want the order type the order type is going to be equal to dot shapes again application color in this case what i want to do is i want to have that text frame i want to extract the text so the text this is another way text frame dot text range dot text that is the text of the button it's estimate work order invoice so that's going to extract the name easily enough right so our order type is extract name so we've got estimate work order inside our variable here which is the order type which is a string variable okay so what i want to do is i want to know if for example if they select this if we're currently on work order if it's equal to this already there's nothing to do we're not changing in other words if we're currently on work order and i select work order there's nothing to do right we're not we don't need to go to estimate we don't need to do anything so we need to exit the sub so what i want to check is if the text the text here which is work order is it the same as what's in v2 if it is then do nothing and exit the sub so we can do that in defense statement if the order type is equal to dot range b2 dot value then exit sub exit on same order type on same order type okay nothing we need to do we're not changing the order type so we don't need to worry about that but if it's not if it's different then we need to do we want to set b2 to whatever the order type is so dot range b2 becomes whatever the order type it equals the order type set order type relatively explanatory okay so now what i want to do is i want to run this macro that's automatically going to highlight these buttons that's the macro we first created so i'm just going to copy this can drag this down here create a little more space for us so we can see what we're doing then i'm going to paste it directly in here okay so now we're going to color buttons accordingly okay because we've already added the oh as soon as we add this this is going to change right as soon as we take this as soon as we put this to estimate it's going to change this number and once that number has changed then we can then go ahead and color the buttons accordingly all right so once that done we're going to color the buttons now next up what i want to do is i want to clear some information on here we need to add a little bit more information what i want to do is i want to track some information about this particular invoice or work order or estimate i want to track that it's very very important so i want to know what's been estimated in other words if we're on the invoice if we're looking at the invoice here i want to know what's been i want that all the estimated information to be here it's off the screen so when we print it nobody will see it but i want that so i'm going to put that right in here so i'm going to put called this estimated i want to know what's been estimated inside here i want the estimate total in this case estimate totals so i'm going to put down here probably the total price and then in here i want to know what the quantity was estimated quantity the estimated i also want to know the price of what was estimated and also again the cost what is the cost of what's estimated i want the total price total price is simply the quantity times the price of estimated what that's going to do let's say the reason we're doing this is let's say we're on the invoice i'll change this to invoice so it's a little bit invoice we don't need to add some conditional formatting onto this tube and that's going to help us okay so let's say we're on the invoice we're looking at the invoice here's all the invoice information i want to compare what was estimated and i want to have all that estimated information here when i can compare the two i can create a pie chart that's going to do just that okay so we need the total price right and then i want to know the total cost and then i want to know the estimated profit what is the estimated profit that we had we know the total price we know the total cost what was estimated so we can determine with the estimated profit i want to compare that with the actual profit so profit so we got the estimated profit so this is all for estimated so all i'm going to do is just put some borders around here and bring that back up here put some borders around here like that so we have that and now what i want and of course we're going to sum up all the prices sum up all the costs and some of them and next up what i want to do is i want to have the invoice so invoiced i want to know everything that's been invoiced right now we have the invoice information here but i also want to know the costs associated notice the invoice just has what we're selling it for but what about the cost we have to keep track of the cost the actual cost that's not on the invoice it's what whatever we're sending to the customer doesn't have our cost associated here but we do need to keep track of those costs and we need to know what the cost was so i'm going to put the cost down here okay so the first thing what i want is the invoice totals just like we have the estimate totals okay and then in this case i want to know the item cost the item what is the individual item cost and i want to know the total cost so let's say we have the item cost here we know the quantities here so we multiply the quantity times the atom cost and we get the total cost and again once we know what's the total cost and we know our total that we're taking in we can determine the total profit so that's just what i want i want another profit how much profit did we make so this is going to be all for invoicing so we can just put some borders around here just like we did so now we've got that so while putting i'll sum the total price we'll use a sum formula here lastly what i want is the invoice data i want to know the item database so i'm going to put that in here i'll show you that it's item database and then row down here and they'll just put database row all right so this column column s is going to handle that what is that exactly well that's the row that's associated every time we save it it's got to be stored somewhere it's going to be stored in here so i got to know the row is it 9 10 or 11. so when we bring this information into it we're going to take this row and we're going to bring it directly in and we're going to have it row that way i know if i need to make an update i know what row to update if there's no database row here i know to add a new one so we have all that information okay that's going to help us out and what i'm going to do is i'm going to put some borders around here so it's going to be basically going to come all the way down here so i'm going to put some format the cells and put some borders around here i'll put a solid border on the left right and bottom and i'm going to do the same thing here so we can keep track of it and also here okay so we have some solid borders here we're going to add some formulas and we're going to be adding so a little bit of the formatting but what i want to do now is also doing some conditional formatting good so we can distinctly uh see the difference between all these columns and we understand what they're going to be for of course i'm going to go over it again even if you don't quite get it don't worry about it we always go over it again so saving our work as we do frequently we're going to put we want those pie charts up here but what i want to do now is i want to put some conditional formatting based on it if it's an estimate i want to change the look of the screen as you if you caught the beginning of this you saw that it looks very different so let's do that i'm going to zoom out and i'm going to add some conditional formatting based on this so for example if it's an estimate i want this screen almost to go all yellow so how do we if it's a work order i want it to go green and of course if it's an invoice i want it to stay this blue so to do that what we're going to do is we're going to add some conditional formatting so we're going to go in here conditional formatting and we're going to create a new rule let's get based on a formula and we're going to start off with the estimate so when it's an estimate how do we know this here this cell b2 is going to be equal to what whatever we've associated inside the admin with estimates which is going to be here located in c5 okay when that happens what i want to do is i want to format i want to give it a fill and i'm going to give it a fill effects just as we do but in this case we're not going to use blue we're going to use this yellow or orange mixed here and then i'm going to give it this fade that's exactly what i want all right good and now what i want to do is i want to do the line below okay i'm going to do the same exact formula so we're going to do then again home and then going to do conditional formatting a new rule and use the formula okay again it's going to be this b2 is going to be equal again i guess i could have copied the formula that would have been easier this here okay so i'm going to copy it this time and then in this case what i'm going to do is i'm going to format them and give it that little bit of a fade effect here this fill effect here using this and it's going to blend into our main color clicking ok and okay and okay last one lastly what i want to do is i want to color everything else so in this case we're going to hold down the control we're going to color all the way up here and up to row 3 holding down the control all the way the cells that we want affected all the way up here again and also what i want to make sure is this header row 2. so this is everything and i guess it's not going to be this but we'll take those these out these search order and footer message we're going to clear those out very easily so that's the cells that i want affected so again conditional formatting new rule going to the formula pasting in that formula we just created formatting those cells and giving it that yellow color clicking okay clicking okay all right good so now as soon as this changes to estimate then we know it's going to change as soon as i got the word it's going to change that's the look i want notice it and all we need to do is just clear the formulas clear the conditional formatting from these two but i'll do that at the end okay so next up i want to do the same exact thing for work orders so this time again i'm going to go into the home conditional formatting again a new rule in this case it's going to be a formula just as it was b2 also but in this case it's going to be work order again let's do that again equals b2 and then equals again admin going to the work orders okay there we go i'm going to copy this here because we're going to use it two other times formatting those in this case i want to use the fill effects of that green color so we're going to use this medium to this light green and clicking okay now for row two we're going to do something similar except we're just simply going to use the lighter color again inside the home conditional formatting new rule here using a formula pasting that formula in formatting it here going in to the fill effects here using the lighter green here and then all we need to do is blend it down okay clicking okay and okay and okay all right now lastly all we're going to do again going to hold down the control here holding down here all the way to control here holding it down and then doing the same thing for here there we go okay again now lastly all we need to do is conditional formatting new rule and using a formula here pasting in that formula we've copied formatting and giving that that green base color here clicking okay and okay all right good so now when we change this to work order we are good to go okay the last thing what i want to do that didn't work let me just see if i work not word order work order if we spell it wrong of course that's not going to work all right good all right that looks very very good okay we can zoom in and again what i want to do is these i want to remove the conditional formatting so all we need to do is clear the rules from the selected cells because these we want to remain wide regardless okay so we've got that let's save our work now it's going to change now all we need to do is set the macro for these as we highlight these so let's do that and but let's finish out the macro and continue with that so we've got the select buttons that's going to change it we now want to do is we want to set it up so continuing on with our macro all we need to do is set b2 to the order type we did that here already we're going to set the buttons here now what i want to do is i want to clear out certain cells if we're changing it i want to make sure that we're clearing out certain cells first thing what i want to do is clear out the old ivf i'm switching that selected whatever that order id that we're visible that we're showing i want to clear it out i want to make sure that we're we're changing the order so i want to make sure that b5 is cleared out so we're going to do just that okay so the clear contents b5 is the one we're going to be using and what else do we want to do i want to make sure that we're clearing out also any type of rows that we've associated with the database so s10 all the way through s34 so i'm going to clear those out inside there so comma s10 through sas 10 through s34 clearing the contents out okay clearing contents of order okay so we clear out basic but i don't want to clear all the order the reason i don't want to clear everything is because if i'm moving from an estimate to work order i want to make sure that the items remain so whatever items there are i want those to remain because that's going to help us figure out what we need to do okay so now that we've cleared up and we set all the buttons what i want to do is i want to determine i want to do some things different based if it's a 1 if it's a 2 we've already set the order type here inside b2 we know the order type so now what we need to do is set the type number so what is that type number that type number is going to be equal to whatever is located here in b1 so we can set the type number so type number is equal to dot range b1 i like that now we're going to do a few different things if it's estimate work order or invoice we need to do some different things so we need to determine what is the type numbers one two or three if the type number equals one then do something then it's an estimate estimate what do we want to do if it's an estimate basically what i want to do if it's an estimate is i want to basically bring over all the estimate when you determine is it has the estimate been saved before is it an existing estimate or is it a new estimate how would we know that that would be if there's an id here or there's a row here if there's a row here we know that that estimate exists already so the best way to do that is to just check for that so let's going to do that right now inside the vba so if dollar range b8 we can use b8 that's fine dot value equals empty then no existing estimate else and if okay so this is going to be existing estimate because we want to do some different things if it's not an existing estimate what we want to do is we want to clear the fields out so we can do that here so if if b8 equals empty then no existing estimate okay so what i want to do then is just simply create a brand new order right there's a bridge there's no estimate so what i want to do is i want to run the macro and this is the macro that we're going to run called order new we haven't created that macro yet but that's the macro that i want to run i'm going to put that macro right here order new create new order right there's no existing order so we can create a new one else what i want to do is i want to load the existing if it exists i want to load it right i know it exists i know what row it's on that means because this is already filled out so we want to make sure remember we've got an estimate associated a work order and an invoice they're all tied together right they're all tied together that's why in the orders database i've got an estimate id a work order id and invoice id they're all tied together so for example this one this estimate here has an estimated idea of one a work order id of two and an invoice id of three so i know how they're all related they're all related together so when i bring them in here i've got an estimated id of one a work order id of two and an invoice id of three so for the estimate if i know these things i can bring in that information and i can load that right if i've got ordered database items i wanna bring in that estimate all i need to do is extract these three items from the estimate run them through an advanced filter if i have order id bring in that information here into our results our results will come here and then bring all this information back inside our orders and fill it out okay notice oh i think we forgot to do conditional formatting let's add that up right now notice we forgot the call we did forget that on the green one so let's do that home the conditional formatting manage those rules right we're looking for the green there's nothing there right we want to do that so all we need to do is just click anywhere where we already have it manage rules and what i want to do is i want to add them onto it so i'm going to go all the way over here i'm going to put a comma in here because we forgot that i was thinking about something else and then one means i'm going to do f2 so i can use that i'm going to do comma and then i'm just going to simply highlight that row i want to make sure this should be green with everybody else and then we're just going to click apply there we go i like that better make sure that we have on estimates let's double check estimates to see if we forgot i think we remembered that there oh we did remember that okay so since we're working on estimates now let's keep it on that screen saving our work all right so continuing on so if it's an existing estimate what i want to do is i want to load the existing existing estimate load estimate details so the first thing we want to do is we want to take that id whatever id that estimate id it's going to be located right here i want to do two things with it i want to take that estimate i want to put it in our main order id i also want to take that estimate id and i want to place it directly inside our criteria which is going to be right here inside k3 and then we're going to run our advanced filters so to do that we can do just a few lines of code to do that so the first thing what i want to do is dot range b5 b5 is that that's our order id whatever our current order is id so b5 is simply going to equal our dot range b8 b8 of course is where our estimate id is coming from equals make sure put in equals there yeah i'll call this our estimate order id okay that's one step the next step is i want to add that criteria or running the order load so we can do that if we run the order load it'll load the existing one so we can do that and then what i'm going to do is i'm going to run a macro simply once that we have that id located in here once i have let's say the idea is 2 or in this case 1 i know the row i can run a macro to load all the information because i know the row is located i know it's located on row 4. i can load that in okay so that's what we're going to do right here and our load macro will take whatever is in here an order id it'll take whatever's in here and it will put it directly inside here so that's going to be so all i need to do is just simply run the macro and what macro is that that's this order macro it's called order load and this could be called right here we haven't created it yet but just the name so order load that's the macro we're going to run so we're going to call this load estimate order so that's all we have to be relatively simple for the estimate okay and if and if so that's everything that we have to do if it's nest but what if it's a work order a little bit different if type number equals two then it's an estimate and we need to do something a bit different with that okay and if okay so what are we going to do if it's type 2 if it's a work order again first i need to do is it previously saved or not do we have a work order id for this or not is b10 empty or not so we can do that by just checking here so if dot range b10.value equals empty then no existing work order else and if okay and this way we're going to put existing work order so if there's no existing work order what i want to do is i want to take all of the items from the estimate and i want to bring them inside the work order okay but to do that i want to bring all the information in but why don't we do that let's do this let's let's just write a little bit of macro before we do that so basically if i've got an estimate and i've got a description of what i want to i want to create the macro that's going to run that then what i want to do is i want to update the work order so for here's for example let me show you here in the items database we have a sales or estimate description right but the work order is going to have a little bit different description for example this is going to be white marble vanity countertop this one's going to say install vanity marble countertop okay so that's just a little bit different so what i want to do is i want to pull from the estimate i want to determine the item name and i want to pull in the work description not the estimate description but before we do that let's automate the sales description let's do that right now let's automate as soon as i enter something here i want to automate so that it out of the description the quantity and the price automatically do that so let's do that we're saving our work we're going to take a pause on this macro right here we're going to go in to the order screen right here and we're going to do is something called a change event right if we make a change that we want something to happen so it's change event in where are we going to be making that change we'll be making that change anywhere from e 10 all the way through e34 but only when our order load is false not when it's true because the order load is something different so we're going to write that macro right now if not intersection again it gets based on e10 e10 through e34 is nothing and range b3 must be false b3 dot value equals false okay then do something or what are we going to do well let's write up what we're making call this change on item name but not on right order load okay so we're going to make sure that we're not on order load so when that's the case then we want to do something so what do we want to do well first of all i want to determine what that item is i want to know i want to find that item and then once i found it i want to make sure that we can find the row and i'm going to look it up and i'm going to place either if it's a work order i'm going to place order items here i'm going to place this text but if it's an estimate or invoice we're going to place this text so it's going to be based on what type it is we're going to place either what's in column b or what's in column c based on whether it is an estimate a work order or an invoice so we're going to do just that right now inside the code so to do that we can continue on so we have to make sure we're going to set the order type i need to know what's in b2 order type is going to be equal to not range b2 so b2 is going to set our dot value okay so once we have our order tab we need to know i want to know is e and empty in other words i need to differentiate between the change if the user has deleted it like this if they delete it what i want to do is i want to clear out everything here if they have not deleted it i want to add it so basically i need to know whether we're clearing out the row or we're adding the details inside that so we can do that with just a little bit of code here so if range e and the target row dot value does not equal empty then do something okay else empty do something else okay so what are we going to do if it's not empty well if it's not empty i need to determine what we're going to be typing i want to find that item so we need to look for that item so what is that item name and where are we going to find it so we're going to do this we've got already as a range found item we've already dimensioned as a range so we're going to set the set found item is equal to itemsdatabase.range what is the name it's item name that is the range that we're looking at and what are we going to do i'm going to find it what are we looking for we're looking for the target dot value and we need to look in the excel values we need to look at excel whole we need to look for that okay to see if it's found so once we know it's found we need to determine we need to see check to see if it's found so how do we know that so if not found item is nothing then it's found and if then we know it's found so not and nothing cancel each other out so these are positive so item found so as soon as it's found then we need to just add in whatever's in now basically if it's an invoice again remember if it's an invoice or an estimate what i'm going to do is i'm going to add in this the sales estimate description if it's a work order i'm going to add in what's in c so it's going to be dependent on what is located okay so how do we know that well we can just do that inside here we can just check for the number we can either check here in b1 so all i want to know is if b1 is 2 then i know it's a work order so we can just check on that right now if dot range b2 dot value equals 2 then work order else invoice or estimate so we've got that because we're going to place different and if so if it's a work order and assume it's been found what are we going to do i'm going to place the information here so why don't place inside the description f so in this case range f and the target dot row dot value is going to be equal to what it's a work order so it's going to be items database dot range and what is the column we want that work order description i want to place it directly from column c the rest is in column b so column c is going to get the description so that's what we're going to add in here c and what is the row it's the found item dot row that's the rows found dot value that's the work order description and then all we need to do is just copy this and we're just going to change the column here this is for the invoice estimate so for this one we're simply going to change it from to b and this one's going to be the invoice and the estimate description invoice estimate descriptions they're the same so now we've got that we've placed that but i also want to place inside here the work order doesn't necessarily need a quantity of the work order needs the quantity the price doesn't matter the total doesn't matter as far as the work order is concerned but we do need that on the estimated invoice but the quantity is important for both so all i'm going to do is i'm going to place the quantity for both so how do we know what that quantity is that default quantity that's going to come inside column g so g is going to take on our quantity equals and where is it located and i'm going to paste this right in here and then what we'll do is we'll just determine what that default quantity which i believe is in column d from our items databases and column d so that's where we're going to place it inside column d is going to be our default quantity default quantity okay so once we have that we know it's been found also what i want to do is i want to place the prices inside i want to know what that price is only on invoice and estimate we don't need it on work orders so that price is going to be eight and i want to know the sales price the sales price and that's going to come up right in from here that sales price is located directly inside column e from there so we can place that in there inside column e okay so we're going to place that here going to come we can do that just copy that here and then what we're going to do is just place it right in here and that's going to be h h is going to take on that it's going to go directly coming from e so h is going to take on our price and it's going to come directly from inside our database on column e okay so let's just do that update that coming from b now we're done that is the sales price so we've updated everything on that and now what would we want to do well if it's empty if the user has cleared out i want to clear everything out else it's empty so in that case what we want to do is we want to clear some items out so we're going to do i want to do f through h so range f and the target dot row and colon through h and the target dot row dot clear contents so that's the first thing i want to do let's go back to our order sheet here i want to clear out all the way from e they just cleared it out we know that f through h they want to clear that out and i also want to make sure that we are clearing out everything here inside j i want to clear out any estimated totals here that might be here we want to clear those out just in case because they're going to change okay so let's do that and all i need to do is just copy this and just change that to j and k here okay so changing this to j changing this to k that's going to clear everything out just in case we want to add that in okay very good all right let's run a quick check and see how we're doing saving our work before running any macros double clicking on this it's going to add that silicone caulking double clicking on this adding that and then double clicking on this all right what if we've got a work order type let's change this to a work order we're going to get those buttons working so we can automate this it's going to be a little bit easier we don't need to type it in okay if we've got a work order it's going to should say install silicone type kick cocking and then we make sure this is bathing vanity kit let's update that that's not working all right take a look at right in here we want to make sure that it is involved b2 b1 b1 not b2 my you guys all saw that but i didn't see that here there we go apply silicone so now what we do is we've got the work order we've got the work description here okay so that's going to be much easier all right good i like that and now what we're going to do is we're going to add conditional formatting i don't want to see these prices i don't see any of these prices on a work order so what i'm going to do is i'm going to add conditional formatting here i'm going to highlight that i'm going to go into the home conditional formatting create a new rule use a formula and if this we could just see it we could do two things we could say this is equal to two or we could say work order is equal to there's two ways we can do it same thing equal so we could do admin equal to this uh we could do it backwards here which is fine equals it then what i'm going to do is i want to format i want to do the font and i want to change the font to white i don't want to see the prices here click ok and click ok all right so that's going to hide the prices on the work order but as soon as i change it to an estimate it's going to change it back okay perfect that's what i wanted to see now what we want to do is let's get some formulas in here so we have the total so the total is going to be equal to and of course i want to make sure that there's if there's a quantity and the price i want to make sure that there's let's just go with quantity quantity equals empty then empty otherwise quantity times the price here times the price and the price is located in h10 h10 i like that that looks good just enter that 29.99 then i'm going to copy this i'm going to paste this formula all the way down here pasting that formula down there okay so now we've got our totals i like that very good okay good so we've got our estimate looking good and now what we do is we now that we have our conditional formatting we have our uh items information we have that all automating and let's delete that and make sure okay notice how it deleted everything just the way i like it our formula still here that's the way i like it perfect so it cleared out so our information is working just perfectly i really like that and we can continue on making sure that our default quantity is correct so let's see i think we've got this one's a two default quantity perfect our default quantity is coming there let's continue with the macro that we started on so that's the macro that we're going to order sheet macros we started this one if it's item sheet 2. so we already have if it's a work order but this in case it's no existing estimate let's change that to estimate so if it's no existing estimate then what do we want to do what i want to do is i want to update the work order so i want to take go through all these rows determine the randoms and i want to add in so let's say we're on an estimate here let's just double click that i want to put in the proper names so so what i want to do is if we move to work orders i want to update these descriptions with the work order descriptions i want to update it with not the estimate descriptions which is for the customer but we want to update it for the the technician the only one who want to put those work order descriptions so we're going to do just that right now if it's empty so that's so there's no existing estimates so we don't need to pull that information we need to create it we need to get that information i want to get those descriptions directly from here our workload information so that's what we're going to do inside the vba okay so the first step what we want to do is we want to determine the last item row so we can do that here it's already variable last item row is going to be equal to dot range e well you can call me 9999 actually i'm not going to go that many really have like 34 dot and xlr dot end xlr dot row so i want to do the last item row so once we have that first of all i want to make sure that if it's less than 10 go to no items if the last item row is less than 10 then go to no items so we just want to go out of that and we're going to put that down here at the last in this one right here no items so now that we got that assuming that there are items we can continue on so we can start with a loop for item row is equal to 10 to the last item row next item row so basically what i want to do is i want to loop through all these in this case the last item row is 12. i want to loop through all these items starting with tango and 12. i want to find this item inside our items database i want to look for the description in what's in column b i want to place that description or work order or whatever it is in this case we're on a work order right so i want to replace that sales estimate item with the work order description right because what we're doing in this case we're changing from an estimate to a work order so we want to look up the word description and place it here so we're going to loop through that and we're going to find it out so the first first thing we're going to do is we're going to set the found item set the found item we need to look for that item just so we previously equals inside the items database dot range again item underscore name that's our named range okay we're going to do find we're looking for it again and what are we going to find we're finding what is located dot range e and the item row that is right here this is the item name we need to look for it right where are we going to look for it excel values excel whole just as we did before now if it's found if not found item is nothing then it's found okay then we know it's found found then what do we want to do well then all we really need to do is put down i want the default quantity actually probably don't need to put the default we don't need to put the default quantity i want to keep the quantity the same all i really need to do is just take whatever is the single line we don't need the end if all i need to do here is take whatever is located in c in the found row and place it directly inside here f so that's just what we're going to do here so we don't need that right we can clear that then in this case dot range f and the item row dot value is equal to items database dot range located where is it located is located inside c c and the found item dot row where it's found dot value so that is the work order description that's replacing that and replacing that directly in f so again we're replacing the sales description with the work order description quantity and price doesn't matter on a work order so everything else stays the same so that's all we need to do is update that we just need to do that for each item so we can clear that out all we can do is run that little loop and clear that all the way out so we're going to do that for else okay but what if it's an existing work order if it's an existing work order i need to load all of the items in front how do we know if it's an existing work order we know it's an existing order because b10 will have a value if it is an existing work order i'm going to take whatever's in b10 i'm going to place it directly inside b5 then what we're going to do is we're going to run the macro that macro it's automatically going to load all the details based on whatever's here and i'll we'll walk you through that but there's just two lines of code that we need to do with that so they are this dot range b5 is getting equals dot range b10 that is move order id okay and then all we're going to do is run the macro order load we haven't created that macro yet but we will create it very very soon okay so that's all we need to do because it's an existing order right so we know that it's existing okay great so that's it no items that's all we need to do if it's an estimate right remember we're let's change this to work order right one is the estimate two is the work order and the last thing is three is the invoice so three is the invoice here so now what if the type number is three so if let's call this invoice load so if the type number equals three then it's an invoice okay make sure we do the end if here close our loop close our if okay so then invoice again just like we need to do we need to know if it's an exhibit there's an existing invoice or if there's none and we would know that by here if there's b12 contains a value we already know that the invoice is created or ready if it's empty of course then we know it's not so we're going to use b12 this time to let us know just that so we can start out with that if b12 equals empty so if star range b12 dot value equals empty then we know it's an else existing invoice make sure we comment clearly so we know otherwise it is a new invoice so what do we want to do if it's a new invoice if it's a new invoice i want to bring those work order details into it so how are we going to do that or in this case we'll bring over what was estimated we basically want to copy what was estimated and then base it adjust make adjustments based on whatever the work that was performed so we can do that with the following lines of code so load estimated items if available and we know what was estimated okay so how we going to do that so the first thing we want to do is i want to make sure that ba if there's an estimate we'll know because b able to contain a value if there's an estimate so how do we know that so if b8 does not equal empty then the order contains an estimate so if dot range b8 dot value does not equal empty then order contains estimate then we need to load it and if then we need to load the estimate details i want to load that because when i bring that estimate in so how are we going to do that well first of all what i want to do is determine the last order item row but i want to make sure that we know that i want to load all those estimated items into that invoice loading all the and then we'll convert it to an invoice so the best thing to do is determine the last i want to run that automatically okay so we'll use the advanced filter for that so the best way to do that the last order item row is going to be equal to the order items database order items database dot range a 99999 right lassa dot end dot excel up dot row last item row so what i want to do is i want to run an advanced filter right now i want to know the last row here in this case it's 11 i want to take care and put that order id inside k3 and i want to run an advanced filter on that so i know all of those estimated items then bring those estimated items into the invoice okay because i know that's how we're going to do that so we can do that with just a little bit of code right here so once we know the last item row i'm going to determine it if just to check last order item row is less than three then go to no database items and put down just in case there's no database items we're going to put it down here no database so we've got that there all right so let's here sometimes we want to make sure we put it in lowercase and make sure if last order items row that's not spelled or last order item row make sure i have a variable up here let's take a look at the variable up here last order item row that looks just copy that because that's the one i've got the variable on and make sure i spell it right because i didn't here okay good last order item row and let's paste that here make sure we got the variables correct then go to no database items okay so continuing on and we have it what i want to do is i want to set that criteria right now so inside k3 order items database dot range k3 dot value is going to be equal to what i'm going to set whatever's in ka and b8 equals dot range b8 i want to set that estimated id set estimate order id criteria that's very important so we can load all the items from that estimate assuming that we do have that we can then move on we get ready to run our advanced filter so or how we going to do that so we can do that with this okay so advanced filter so we're going to base that advanced filter on that so the order items database let's update that order items database dot range and we're going to look here's our here's our original data located in a2 all the way through i so we're going to update that a2 through i and this is going to be in this case the last item we're going to copy that variable right here the last order item row that's the one we want to use right here last row advanced filter we're going to run our criteria is going to be through k2 through k3 so let's update that k2 through k3 that's auto hotkey that automated that that's a free software you can search on google and our criteria is going to be located here from m2 m2 it's going to go all the way over to youtube so let's take a look at that me too youtube okay youtube there we go so youtube that's where criteria now what we want to do is we want to get the results so how we're going to do that so i want those results and i want to know the last row of those results so to do that we can just get that information inside a variable so the last results row so that last results row is going to be equal to in this case it's going to be m column m is what we're focused on and remember we need to call out that order database here because that's the database we're working on make sure that the sheet is correct so the order database m that's the last results row okay once we have that i want to make sure that if last results rose less than three then we know that we'll just go to just no items go to no database items all right assuming that we do have data we want to run a loop so for the result row it's equal to three to the last result row next result row so inside this is where we want to run our information so what i want to do is i want to determine the first thing i need to know is what row are we going to put in the order that row located here is located in column t so i want to put that inside a variable so we're going to call that the order item row so the order item row is going to be equal to the orders items order items database dot range t and the result row dot value so that is the order item a row i know a lot of order okay so once i have that i want to take the information i want to bring that information in so what do i want to bring in well first thing i want to bring in is of course that item name located in column o so that's the first thing so we can do that dot range inside e that's going to go in column e and right order item row dot value that's our item name is going to equal what it's going to be equal based on not of course t it's going to be based on located what's in o so we're going to change that to o o is our item name here item name and then next up what do we want to do i want to put that description right that's going to be located column from p so i'm going to copy this here and we're going to place it in that's the item description okay that item description is going to go inside f in our column there and then what we want to do is make sure that that's going to come from directly from p so we want that p however if we look here if we take a look inside here we see that we have order items of quantity and price we've got it all in the same row so we could do e through h simply equals o through r so why don't we do that in a single line so e and h and order item row order item row.value equals all the way from o and then we can do and colon and then we have all the way through r and the result row so instead of item name we have description quantity and amount item name description quantity and amount okay so we also want to bring over the row number i want to know what database row so that's what we're going to do where do we want to place that row i want to place that directly inside column s so that's going to come directly in here inside s here okay but i also want to set up our quantity our estimated information inside our quantity and our price i want to put it in here too so j and k must also take on here our quantity and amount here must also take on here q through r so let's do that right now in the next line of code here so in this case j here order item row and k and the order item row dot value equals right it's going to come directly from to q and the result row and then we have all the way through our q and r g q and r okay that's it just the two those two values that's a q and r and the or result row and the result row so now we're going to branch so we're bringing it so again i want to bring q and r i want to bring those directly in aside here this right because then what we can do is we're going to determine the cost we can do all this through formulas but this information has got to come from the estimate right we're going to change the invoice the estimate information stays the same quantity in the price even though we can update that then we know exactly if we decide to change the quantity or change the price on the invoice we know that the quantity of the estimate stays the same right we run our estimated maybe we need to charge more on the invoice for something or less we want our estimated information to stay the same that's why we bring it in separately there so once we've brought in that information i also want to bring over the information actually we don't need to bring in this only the reason we don't need to bring is we're only bringing in the item information as soon as we save the invoice we're going to generate brand new rows okay so we don't necessarily need to bring that in now so we've got that bringing all that in to copy the estimate let's put in this here estimated this is the estimated quantity estimated quantity and the price and the price bringing that information in okay next it that's all we need to do for the result row so we're done with that but remember when we're bringing something into call me what we don't want to do is i don't want to trigger the macro that's going to run so to do that what we're going to be doing is we're going to set order load to true then we're going to set it back to false when we make changes to e so we can do that right here right at the top of here i'm going to set dot range b three equals true b three dot value equals true set order load to true and then we just have to set it back to false to true and then we're gonna set it right back to false right after we get done with changing anything to either so right here then to false so i'm going to change this to false that order load to false that way it won't trigger any changes every time we make a change to e it might trigger the worksheet change so we don't want to trigger that we know if b 3 is set to true it won't trigger that okay so now that we've got that we can then now continue on with our macro so the next thing what we want to do inside that macro is i want to check to see if it's an existing invoice right else existing so what do we do if it's an existing in that case what i want to do is i want to take whatever that invoice id here in b12 and i want to place it directly inside b5 so dot range b5 equals range b12 b12 okay set in this case set invoice order okay so then again all we're going to do is just run the macro order load which we're going to create load okay so that's it that's load the existing invoice okay and then if there's no database items we can go down here and if there and then end with okay so we're good to go on that macro and let's save our changes and then we'll just go ahead and run it now is this mac or this selection order select it is this one that i want to run automatically when i select either one of these three buttons so i'm going to hold down the control assign the macro to this will check from fix any bugs that might be coming up okay so is this one so if we're on an estimate and i want to move to the work order okay let's that's kind of a collar didn't see that that's not going to work okay keep in mind that when you try to run this macro remember when you run this macro let's check this variable order item bro okay it's ord item remember the variable is no er so that variable is ord i don't know okay i didn't remember my variable names that's kind of important so what i'm going to do is i'm going to find this order item row and i'm going to replace it with every and everything that uh just with the correct variable replace in this case we're going to replace with ord item row and then replace all in case i missed any of the macros so five of them okay good now we're going to continue on we will get a bug on there as far as no go to no item right so in this case we want to go to no items and then continue running it this wasn't found so again this is the normal bug that we're going to get when we try to run this macro this is a good bug this is what it means we can the application caller i try to run using our player f5 that's not going to work which is fine i want to run this macro from this here this here or this here okay let's debug that okay last order item row let's check i didn't see that bug here cannot change part of merge cell okay well we shouldn't be changing any merged cells so let's double check that means we have the range incorrect so no problem with that way m2 through u2 and then we have our we we need to do is we need to make sure that this sheet here is for everything including our criteria including our results very important there okay because we have to specify that sheet okay there we go all right that looks good so now i've got everything working right let's we don't have this macros not working order load we're going to do with orders that's going to be fine and then we don't need that okay that macro is not running yet no problem but it's looking very good i've got we're switching now between this the work orders going there i really like it it's looking really good everything's changing okay great so let's just get our last few work orders that are going to be able to add and save these and then we'll go into the print and email and then we're done all right good thanks let's get into the macros here let's go ahead and double check that everything's done on this module here we're good to go on that and now we're going to use we're going to focus on the order macros the first thing i want to do is update save and update an order okay before we get into the add new what i'd like to be able to do is if i'm going to click add new or save update i want to know what we're going to be adding here are we adding a new estimate are we adding a new invoice or we're adding a new work order so what are we adding so the best way to do that is to give the user the options so what i'd like to do is i'd like to add a little bit of a drop down list so i'm going to ungroup this and i'm going to insert a triangle here a triangle like this and i'm just going to give it a little bit of a size let's just do point one it doesn't have to be very big and then by point one and then we'll just give it a color i'll drop this down here while we're formatting that and then i'm going to here and give it the black color that's all i want to do here you can see the triangle here but i'm just going to flip it right because we don't want that locate rotate i'm going to do vertical that's the triangle i want and what i want to zoom in a little bit so we can see it i'm just going to bring it right over here so this add new is going to be there i want a drop down list there so we're good with that so i'm just going to again click on that and group everything i'll line it up group and everything so when i click here anywhere on this button what i'd like to do is have a drop down list button but i'd like to have a button so what i'm going to do is i'm going to copy this i'm going to paste it here but this one i'm just going to make it square so do that there so there we go so what i want to do now is i want to create one button for estimates one for work order and one for invoice so to do that but we don't know we want a dynamic right so we're going to call this just estimate button and then what i'm going to do is give that a formula equals right because we don't know what the text is going to be whatever the dynamic name for estimates is here there we go estimate so i'm going to duplicate that and then we're going to call this work order button and then what i'm going to do is change this to c6 which is our work orders and then i'm going to do the same thing with the invoice duplicating that this time of course it's going to be named invoice button and then of course i'm going to assign that c7 for the admin so that way whenever we change it it's a dynamic okay so what i want to do is i want to place these right on top of each other and line them up accordingly and then we're going to group them so once they're lined up here i'm just going to make sure that they're lined up and then i'm going to format and then i'm going to align them and distribute them vertically to make sure they are i'm going to group them together i'm going to give that a specific name that i'd like and we'll call this the add new group add new group doing that once they're grouped together then what i want to do is i want to place it obviously you can place it right about here right and i only want this group to show when the user clicks this if they click it and it's did visible i want to hide it or show it so how do we get that to show we can do that with just a tiny little macro very very simple we can do that we'll call this the order add new menu and that was one last one that we had to do here called order add new menu that was the last one so let's do that if it's very very very simple so if orders dot shapes add new group that's the group we just created dot visible equals true that means it's displayed then what do we want to do then i want to hide it orders dot shapes add new group dot visible equals mso false right if it's visible i want to hide it if it's invisible else i want to show it right so all you need to do is just copy this then else hide it there visible equals mso true okay so show or hide it basically on that it is this macro that i want to then go ahead and assign that to this okay so there we go so then i just click on this button assign the macro and then paste that in there click ok now it's simply going to act as a toggle so when i select there it's going to on off on off one more thing i want to do is if the user selects something else i want to hide this automatically so to do that all we need to do is just change on selection what does that mean that means if they select anything any other cell and this thing is open then i want to hide it do that we can do that on selection change so let's do that inside the orders right here and that's based on selection change worksheet selection change it's very easy if shapes then paste that in here that name divisible equals and we're so true then hide it then shapes that means anything selected dot visible equals and we sell false that way anytime they select anything it's just going to hide it automatically because we don't want it we don't want that displayed no matter what so they select on there change this to true that should be true not there we go okay so now all we need to do is down hides it okay so if it's true not mso true then we go there we go that's what i like okay so now we can just assign a macro to add new based on this and it's going to be based on the name so it's very very simple all we need to do is that okay good so i'm glad i got that so now we've got a really cool toggle drop down very easily made and we can continue on with the rest of our macro okay so let's get into the macro the first macro that i want to run on these order macros is the save and update we need to be able to save and update an order so how are we going to do that so inside that what we're going to focus on we want to make sure if we're going to determine is it a new or an existing order that's very very important and we can do that b6 is going to tell us b6 is going to be that row if for any reason this is blank b6 is going to be empty so we can do that simply by allowing us to do that let's write that if dot range b6 first of all what we want to do is actually before that actually let's write that in a b6 that value equals empty then new order and if and then else it's going to be an existing order else existing order but before that as i mentioned before i do want to determine what is the type the type number which is a long variable is going to be equal what's in b1 i want to know what we're on b1 type number equals b1 that is the order type number so now that we have that we can continue on because that's good a lot of dependencies are going to be based on what number that is if it's an estimate a work order or an inverse so continuing on now that we know that it's going to be new order if it is what i want to determine is the first available order row so the order row is going to be equal to the first available row on our orders database orders database that is going to be that first row orders database that first row okay plus one so it's the last row plus one the first available row so once we have that i also want to know what the next order id is so dial range b5 we're going to set that is going to be equal to where's that next located it's located in b7 if we take a look at that remember we've created the next order id located right here using the max forma the next one why is that next one because we have three already the next one is going to be four so we know need to know that and i want to take that next door today i want to place it directly inside b5 so we can do that here equals dial range b7 b7 is going to be that next order id we'll put that in there next and that's the next order id okay so once we have the next order id in b5 i also want to take that same order id and i want to place it directly in column a of our order row so we can do that with following lines of code would be orders database dot range a and our order row the one we just created dot value is going to be equal to whatever's in b5 that we just placed there b5 let's give you again the order id all right so once we have the order id we can then uh that's it that's all we do for the new right that's all through but what if it's an existing and if it's an existing simply the order row is located whatever is located already in b6 so b6 is going to be our order row if it's an existing so our order row order row is equal to what's in b6 existing order row so now that we have that we know that we've determined whether it's new or an existing order we can continue on so the order id i also want to know what the order id is i want to put that into a named range so let's do that here order id is going to be equal to whatever is in b5 so changing that to b5 now it's going to set our order id into a variable order id so once we have that order id into a variable we can then i want to determine what row what where should we place that order remember what i want to do is i want to place that order id i want to be placing it here is it an estimate is it a work order or is it an invoice i want to place the order id in one of these cells so how do i know which one well if this is one right if we know that we're going to be replacing it inside here it's an estimate if it's 2 right that would be a work order i know to place it in 10 or 12 if it's an invoice so we just need a little formula to know whether we're placing it in b8 b10 or b12 we can do that with just a little bit of a formula what is that formula dot range b and we're gonna start out at eight plus we're gonna be two times the type number remember the type numbers one two or three type we can do this the type number minus one and i'll explain that type number minus 1. all right so i like that there so that's it so the value is going to be equal to the order id so for example if the type number is 1 right if we're on estimates 1 minus 1 is 0 0 times this should be two times two times zero times two of course is zero so eight plus zero is eight right now what if the ordered number is two two minus one is one one times two is two two plus eight is ten so that's that we're going to be placing an eight ten or twelve with that little formula so that's gonna set that order id set order id in either one of those okay so once we have that because i need to be able to save it that's very very important we need to save it now once we have that we're going to run something called data mapping if you haven't seen this before i'll show it to you so i'll show i'll type out the code for order column equals 2 to 11. next order column and i'll explain why it's 2 11. so basically what i need to do is i need to take this information let's say the estimate id i need to take the date the service check the job site i need to take the customer here and i need to place it all inside the order database i need to place the order date i need to place the order type here and i need to know where it's coming from so for example the order date's going to come from i4 the order type is going to come from b2 the customer is coming from f5 and this f5 okay so also it's all mapped out so we're gonna go all the way from two we've already placed the id so we don't need to start out at one from two all the way to equals column eleven so okay so remember this b8 estimate id b10 work order id b12 invoice id so it's all been mapped out so all we need to do is update that and that's just we're going to do with this little line of code that we're going to continue on with so what is that line of code well we're going to add to that orders database so the orders database dot cells the order row we've already set the order row above order columns dynamic it's going to be column dot value is going to be equal dot range and what is that range located it's located in the orders database dot cells it's going to be located in row one and the order column and that's all we need we can get rid of that dot value so what is this so all we're going to do is we're looking for this range right here row one i'm looking for that range right here whatever is in i5 i'm going to place it here whatever's in i6 i'm going to place it here so that's all we're going to be doing with data mapping and it's simply three lines of code to do all of that okay save data to database that's all we do is just data mapping so now that we have that what i want to do is i want to update the order ids and what do i mean by that well basically what i'm going to do is i'm going to be looking here on row 4 and i'm going to be replacing this with one i want to update any of the of them that exists i want to make sure that we're updating basically all of this i want to make sure that we're all updating because i want to know every single work order id associated with this entire order one two or three just placing them in here so we can do that with just a little bit of loop like this we're gonna call this update order ids and we do it with a little bit for id row is gonna be equal to to 12 step 2 and we're stepping 2 because we only need them you know every other row so next id row so basically we're looping let's close that up we don't need the space there so basically we're looping through our order number here i'm starting here in eight i'm going to 10 and i'm going to 12 and i'm going to look if there's a row i'm going to replace them all so how do we do that for each one of them so we can do it very easily so first of all the order database row i want to set that row i need to know that it's going to be equal to dot range b and the id row plus one so that's the and that's the order davis row order database row right if we're skipping from eight our database rows here 10 our database rows here right so for example if we have one two and three here our database row is five our database row six our database was four okay so we can do that so we want to get that ordered database run and put that into a variable once we have that we can then make the updates i want to make the updates to three different cells so the order database orders database dot range actually we can use dot cells in this case cells order database row column we can do column you can do range or column either one order database row dot what's the column let's call them nine dot value equals dot range b8 b8 that's the estimate id i want that estimate order id b8 in this case we're going to call it estimate order id basically this is column 9 column 10 and column 11. so basically i just want to go through and i'm going to update these all so here's what we're doing for nine and all we're doing is going to go now all we need to do is just change the column number on those so this would be for the uh work order id estimate order let's do order id work order id would be in column 10 and then also we have the invoicing column 11. so that's it invoice id so we're just updating that because we want to associate that when we load it in the reason is when i load this order in i want to load all the associated orders here i want to load them in load them in here here and here bringing that so i always know the row of the work order the associated estimate the associated work order so they're always tied together that keeps everything in this job together very very nicely when we track that okay so we're just updating that all the way through and we just need to update this for b10 and then b12 okay so we can get those all right so that's it for that so we've updated that now all we need to do is update the order items right we've updated we've brought all the information the customer estimate brought but what about the items i need to save those inside the database row and basically what i need to do is i need to look if are these in existing or is new how would i know if it's already saved there well i would know because s is either blank or it's not blank if s is blank it has not been saved yet so we can do that to determine the row so we can continue on like this but first we need to loop through all of the items so let's put a note in here add or update order items okay inside the order last item row is going to be equal to gonna be based on e we can do this column e and then that's going to give us the last row so last item our last item row if the last item row is less than 10 then go to no items all right there's no items we can't continue on no items bringing that down here okay assuming that there are we can continue on with our loop so we can do this for the item row is going to be equal to 10 to the last item row and then close our next four next item row so going up so what do we want to do first i want to determine does it is it exists not if dot range s and the item row.value does not equal empty then it's an existing row else so in this case we say existing database or existing database row or else new database row okay so if it's an existing database row we can just put that into a variable we can copy this and what is that database row so the order item row is equal to again s right that's order item row thanks for sticking with me on these extra long trainings i really appreciate it so much value i try to deliver for you guys every week if you like these trainings don't forget to pick up our 77 200 workbook pack that's just 77 for 200 of my best templates i'd really appreciate that that helps to keep these trainings free all right so what if it's new one i need to create that so what is that new database row in that case we need to set it up the order item row is going to be equal to in this case the first order available from the database so in this case it's the order items database okay and that's the first available row so plus one that's the first available row inside that okay so now that we have that first available row what i want to do is i want to put the order number inside column a so right here this one here copy this order a and the order item row dot value it's going to be equal to the order id right we already have that updated here we've already we've already got that in a variable right here so we don't need that let's just double check that order id located in b5 so we have that in a variable order id so once we have the order id what else do i want to do well i want to put a few things in the order this one is going to be we're going to need to put this one in i also want to put the row associated that located in column i and probably h as well so putting all of that in so and also what i want to do is i want to take whatever row this is let's say 11 or 12 and i want to place it directly inside s so we need to do those few things for that brand new item so we can do that here so the next thing we want to do located in b i want to put the order type why don't we put the order type here copy this because that's only going to be for new that won't change b is going to take on the order type equals order type okay so inside now that we have it in b what i want to do is in i i want to put the row number i equals what is that row number so equals going to put in a formula on that in this case quotation equals rho okay so that's going to take on a formula what do i mean by that it's going to be an i not a i so looking back inside our data items database here inside i we're going to put that formula that's the way it's going to be consistent as we bring that down always the rows are going to be there so and we can also put the order row as well so i want to put that inside h so i might as well do that right now so just copy this here and i'll put it directly inside i'll put it next here h is going to take on this specific item row equals the item row okay so now that that's the order item order item row this is the database row database row okay and the order type obviously order i think that's spelled wrong order type order type all right well instead of the order type we didn't define it up there it's a dot range b2 b2 is the order type invoice estimate whatever it is that's sufficient enough b2 will take on the order type okay so now that we have that the last thing i want to do is take inside s dot range s and the item row and the item row dot value that's going to of course take on our order item row this is a row right here i want to know that database row i want to put that directly in s so we can do that equals okay that's our database row okay perfect so we've got that there set it all up that's only for this is for new database items this is for existing now what about if it's regardless of whether it's new or existing in that case it's just one line of code basically c through g is equal to whatever is in e through i so we just need to write that in hook in a single line of code so it's going to start out with order items database dot range c and the order item row and through again all the way through g let's take a look at that and the order item row dot value is going to equal to dot range our e and what is the item row our item row item row and colon all the way through i we want that total in there and the item row so bring in all the item details all item details bringing that in there all right let's clean that up we'll save our work and we'll check for any issues we'll fix them and then what i'll do is i'll clear out the data here i'm going to save it i would like to clear out the existing data just so we can get a nice fresh start so i'm going to do that right now so let's clear this all out deleting all of that and then the same thing in the orders i want to delete all of that so we can have a fresh start inside the orders we're starting out with an estimate okay good now what we're going to do is we're going to take this right we want to make sure that we can clear this out here and we can clear this out here and then we can clear this one out here so again and this one out right we got a brand new our next order row we got a brand new order nothing here one mother thing i wanted to update this let's we didn't do this yet we should do this right now when i make a change here i want that address to appear here that's important big application lots to do assign that macro order save and update click ok all right let's do that before we save it let's get up that i'd like to not have a nice looking complete invoice so that's checking double clicking or entering anything into f5 will automate the address so basically i want to look for that customer if it's found right how do we know if it's found well we do already have a customer row here all we need to do is check to see if there's a customer row so we can do that here equals if error we can look for that how do we do that we're going to simply use a match and we're looking up the customer name and we're going to look it up based on a customer name here and then we want an exact match okay all we need is the row so the row is going to be plus 2 we're going to add 2 and on that and then if there's an error we're going to parent so our first customer is on row three double check inside our customer database our first row perfect so all i want to do is take on c and d and i want to directly place it directly inside here f6 and after that's only if we have b19 contains a value so we can do that on change event so let's do that right now inside the worksheet change so inside our orders here the sheet we're looking at a change event right let's go up here change event right but we're going down here and that's going to be a brand new change event and it's going to be based directly on um if there's a change to f5 so we can write that up here and that can work either way whether it's on estimate or invoice load is okay in this case so if not intersection based on f5 nothing nothing and i want to make sure that f5 doesn't equal blank range f5 dot value does not equal empty then what do we want to do then do something then i want to check to make sure that we can check it up here b19 does not equal empty we can do that inside the f and range b 19 dot value does not equal empty then we know we've got an accurate customer then what we can do is load up so the first thing i want to do is arrange f6 dot value that's going to be the very address line one it's going to be equal to what customers database dot range and it's located in i believe b let's double check that to make sure we got the right one customer database we want the address c actually c and d so let's put that in c and what is that row the row is located of course in b19 we could put that into a variable if we wanted to but this is relatively easy and c19 dot value and that's address one and then all we need to do is in f7 do address two and then we are good to go okay so pasting that in here f7 is going to take on address two so seven that's coming from column d all right and that's going to be address two okay good we're good at that saving that let's make a quick change double clicking that it's going to trigger the change event and 4205 avenue customer database make sure that that's correct star wars okay good i like that all right that's done now let's go ahead and save again saving our work and now we'll attempt to save it and fix any issues there order database row okay let's take a look at that variable same issue as before it's order database right looking at again same thing we've did before order database i got to remember my thing order database rule right here we could change it but let's just update that and in case i in case we didn't do again find and replace since i don't remember my variables all the time okay find that and then replace it again with order database order database row here it's a little bit shorter okay that's a good way to make the correction and then replacing all okay click ok just one okay no need to do that okay order item row in this case we have order probably order item row ord probably in this case let's take a look order item row same thing order item row fixing that here doing the same thing find that because i don't remember that and then replace that with ord ord item row i have to remember my variables replace all four replacements okay i think we got a space and now let's undo that order item row there's a space there we don't want that okay replacing all click ok all right that looks better saving our work and then we'll continue on fixing any missing okay save update okay that looks good let's take a look inside our orders database here all right checking on clicking save and update let's take a look at this this line we don't probably need this is an empty value id 10 this is b11 it's an empty value but that's okay we just don't need the variable in that case we're going to play that we don't we don't in fact we don't probably get rid of that whole line okay let's take a look at that i like that and take a look inside the order database row we now have the order date order type customer job site footer right total we've not created a work order invoice yet so that's correct let's take a look at the order items database everything looks good this doesn't look good looks like we've got an issue with the rows here so let's fix that i believe i know the problem here in our orders i want to make sure the last row we want to go only the last row we want to make sure that it doesn't go beyond 34 so that last row and column mean needs to go no greater than 34. so we need to fix that up when we loop through those orders right when we loop through those the last item should be no more than 34. okay so let's take a look at that that's the last row so there we go so let's take a look let's uh delete that here clear that out that looks good and then what i'm going to do is go back in there just click the save and update make sure that doesn't appear again here inside the ordering database okay it's gone so again order id estimate we've got our estimate information quantity the amount the total here this uh looks okay we got to double check to make sure if we put in a multiple quantity let's update that update the quantity two of these here and then we'll save and update that make sure that that update gets reflected here and here and here which it did eleven four okay so everything looks good as far as the saving now all we need to do is update and load that now what i want to do is i want to be able to load that order in there so that's the next macro that we're going to write and also we need to write the new one so how do we create a brand new one let's do that right now create a brand new one we got a few ways to do that but add new i want to click on here and create that new estimate new work order or new invoice let's write that macro right now okay and that's going to be the order new macro so that's the next one that we need to write called order new so to do that first of all i want to determine the order type right i want to know what is the type are we creating a new estimate or we're creating a new work order or we're creating a new invoice so we can do that the order type is going to be equal to dot shapes what about what is the name of you how do we know if we're going to be clicking on one of these buttons how do i know which one we're creating because it is the text that's based on the button so if i want to know the text estimate how do i extract that text and i want to take that extract that text and i want to put it directly inside b2 so to do that we can say the order type of shapes and what shapes it's the application caller make sure i get caller this time dot text in this case text frame two dot text range dot text that is the name all right that's gonna matter now what i want to do i want to place that inside b2 dot range b2 is going to take on that variable value okay so it's equal to the order type and now once we have that obviously it's order type kind of self-explanatory once we have that what i want to do is i want to run this mac remember we have the macro up actually it's right here it's going to set those buttons so for example if i click new estimate or new work order i want these buttons to update accordingly so how do we do that well we do that simply by running the macro that does it because we have a single macro that's going to update that and that was here it's called this one right here order set buttons remember this one here this this will do that for us automatically because it's based on this type here in b1 so we can go back in there and then run that macro here let's go that macro here so update buttons okay so those are selection buttons i guess you could call it order selection buttons update order selection buttons because we want another order type okay so once we've updated that then what i want to do is i want to know if it's an invoice type because i'm going to be creating some charts and so those charts are going to be based on that so i'm just going to write a little heavy i haven't created those charts yet so if dot range b1 dot value equals three or order type equals three in that case then we want to show the types then let's put in this invoice type show chart so we're going to come back to this and add those charts in and if else we're going to hide the charts else hide charts we haven't created them yet so we'll just focus on it once we create them we'll show and hide them accordingly here so that's that we'll come back to that first of all then what i want to do is i want to set the order load to choose your dot range b3 dot value is equal to true equals true set order load to true okay and then of course i want to before we're finished i want to make sure it goes back to false so just going to copy that equals false set order load to false good now once we've got that all right we're ready to continue on so the first thing i want to do is clear out some of the fields right if we're new we need to clear out a lot of the fields i've got them here i'm just going to copy and paste it from some notes it's going to save us a lot of time because we're already well into this okay so basically here it is right here here's all the fields that's kind of easier all i'm doing is clearing out i'm going to clear out basically these fields right here all these fields all these fields everything here gets cleared out of there it makes it a little bit quicker so we have to type everything in okay the next thing what i want to do is i want to default the current date for our new order i want to make sure that i4 takes on our current date so dot range i for is equal to the date equal to date set current date once we have that i want to put in the b3 i want to set that default oh we already got that here in false good good good and also what i want to make sure is the add new group remember that group once we select on something here i want to make sure that this group is hidden so we can just hide that group once we've selected a new one so we can do that here dot shapes add new group dot visible equals false mso false okay hide add new group okay so that's pretty much it then we can just select maybe we'll select uh let's say f5 so that they can enter the customer so dot range f5 and then dot select so we've selected that that's it that's all we need to do so i'm going to save it i'm going to then take this macro here order new and i'm going to assign it to every single one of those buttons here assign that macro to the group once i send it to the group it'll assign to all the individual buttons automatically add new estimate add new work order add new invoice so that's looking really nice okay good so now we can add individually each one perfect okay let's continue on now that we know we've added now what i want to do is i want to be able to search enter that one right we've added it and i want that estimate to automatically load up we've already created it here we have estimate we have estimate number one i want to make sure that we load that when we change d8 i want to run a macro this can actually load that macro into it so how do we do that well with another macro called order load so let's write that macro right up so first of all i want to make sure that b6 doesn't equal empty if we don't have a row that's associated we won't be able to load it so i want to make sure that b6 contains a value so if dot range b6 dot value equals empty then let the user know message box please enter a correct order number exit sub nothing we can do unless we have that okay so assuming that b6 does contain a value we can assign that a variable and that's going to be order row order row is going to be equal to whatever's in b6 so we can just copy and paste that here okay see my voice is holding up hopefully order row it's getting kind of raspy isn't it all right let's continue on um now what i want to do is again i want to set b3 to true just as we did here set order load to true so i can just copy that set the order load and then of course before we finish it out we're going to set it back to false again okay so we're going to go down here and then set it to false and we're going to write everything in between here just going to again we're going to use data mapping to load in those very very easily so the first thing what i want to do is just make it a little bit faster application dot screen updating equals false then we need to make sure that we set that back to true before completing out the macro so down here guess towards the end here equals true setting it back to true that'll make things a little bit faster when we load them okay so again what we're going to be doing is we're going to be running a loop four just as we did when we're saving i'm going to do the same thing but in reverse for order column equals 2 to 11. next order column again we're going to be looping here same thing and this time we're going to be we know that we're all need to be looping from 2 we've already got the order id there 2 all the way to 11 and bringing all this information into b12 into b10 into b8 that's it that's all we're going to be doing so we can do that with just a single line of code so dot range where's that range located it's located in the orders database dot cells row one and then it's gonna be the order column dot value that's the value that's the range and where's it coming from it's coming from the orders equals in this case orders database dot cells order row comma order column dot value okay so that's it that's all we need to bring in the ordered data good so now that we've got that now all we need to do is bring down the items the items that are located in that order but before we do i want to make sure look at this total right i don't want to bring this into i-37 right let's take a look at i-37 that's calculated right we don't want to upset i don't want to bring that value in so i don't want to bring it in what column is this let's column eight right so i don't want to bring it in so i only want to do it if order column does not equal 8 then right so we don't want to bring we don't upset the formula we only want it one way i only want the totals going into that i don't want the total coming from here and going in to a hair so we don't want to replace that that's unnecessary and we need that formula okay so we've got that now what we can do is we can load in those order items so let's write a little memo here called load order items and of course we want to get the last order row we already have that here we've already written it up up here so we can we don't need to do that again so let's update that let's say right here inside here we did that right here here's what we have all these here we've got the last order our last item row we've run our advanced filter we've gotten this so that's pretty much it let me just copy that and we can paste that in because we just make some updates accordingly because we're gonna need to write it all again okay so inside the order load right here okay let's go over it we'll go over that code because we've just got the last order item row is going to be ordered database that's correct if it's less than three then go to node database items we need to set that up here we don't need this we've already got it here so i'm going to replace this with no database items okay so b3 is going to take on true set the order load to true we do need that unless we've put it up here b3 already true up here we don't need to add it again so we can get rid of that we've already done that there but one thing we do want to do okay i want to make sure that be it in this case right what i want to make sure in this case i want to make sure it's b5 b5 is going to take on set order id right b5 right whatever's in b5 we're going to put that what does that mean that means that whatever's directly in here that order id once we've loaded it up we know that i want to put it inside here k3 that's going to get us our criteria so we can get our results here so that's going to take on b5 right we're going to take that on inside k3 then we're ready to run our advanced filters same thing getting the last row okay and of course our order item row is going to come from t we know that bringing in all the information just we did e through h equals o through r there's no changes there so e a g through h is going to equal all o through r here's our results o through r here and then what i want to do is i want to take that item row and i want to bring that inside s so i want to make sure that s when we load that up s contains that database row so we're going to add that in right now we're going instead of this s instead of this an order item row dot value is going to equal what is going to basically equal you whatever's inside you is going to take on that take a look at this here inside orders database inside order item database here whatever is in you that is the road the database road that is associated on that is the road that i want to bring it on so it's going to come directly from you you here and the result row we can get rid of that just a single column there and the result row okay and then we're going to change this this is going to be our items database row okay so now that we have that we've brought it inside s and everything looks good we are ready that's going to go all the way through that okay we can change this to let's see no database that looks good and now what we want to do is i want to add the estimated details on invoice orders only what do i mean by that if it's an invoice that would we just create an interface i do want to add in that estimated information here so how we're going to do that with a few lines of code so i'm going to write a note here says add estimated details on invoice order on invoice orders only when estimate exists right if we create an invoice but there's no estimate that was created then it wouldn't matter but it's only when there's an estimate that exists from that when estimate exists so how do we do that well first of all i want to see that if dot range b2 i want to make sure that b2 or b1 right i want to make sure that we're on an invoice b2 is going to be equal to either anything like bc7 right then we know it's an invoice or we also know that in this case uh b1 is three so that's probably easier b one is three booster if dot range b one that's gonna dot value equals three then we know it's and right that means invoice and also dot range b8 b8 remember b8 contains our estimate information here b8 is our estimate id i want to make sure this is not empty then we know that there's an estimate that exists we've already loaded it so ba is going to take that on so and ba.value does not equal empty then we know an estimate exists we'll say existing loading invoice we'll call loading invoice and then we'll say estimate exist okay because i want to load those details from the estimate so how do we do that well it's just a little bit here so what first thing what i want to do is run an advanced filter again same thing what we did before so all i'm going to do is again i'm going to copy this here right down here and then we'll make some adjustments accordingly so first thing what i'm going to do inside k3 i want to know that order but k3 is not going to take on what's in b5 it's going to take on what's in b8 right b8 that order id you're going to set that criteria we're going to run our advanced filter just like we did before we're going to get the last results just like we did before we this time we're going to bring in the information i only want to bring in a little bit of information and that's going to come in j and k so what i want to do is change this so the order item row is going to be in t so basically what i want to do is i want to bring the quantity and the price from that estimate only we've just loaded that estimate we just loaded the invoice now what i want to do is i want to get so here we've taken the estimate all i want is the quantity and amount q and r i want that to come inside j and k i want to come in right here i want to bring i want to know this is the invoice i want to know what's estimated i want to bring in right here in jnk okay so do that just like one line of code so j through k is going to equal the orders database row let's take a look at that so we make sure q and r q and r bring in that information and bring that information q through r okay so that's it that is the quantity and the amount not the name or the description we don't need that just the quantity and the amount that is sufficient that's all we need and then also that's it so that's we don't need anything else i don't need this here and that's it so that'll bring in the estimate it'll put it directly inside there only on the invoice and only when there's an estimate exist okay closing that up so that's end of next result row end if and then b3 we're going to set b3 to value order load to false that's good and then screen update into okay good we like that that looks good we've just about got that written up i'm going to save that and now when do we want that to run well i want that to run basically when we make a change to this order load in a few instances but order load i want to certainly run when we make a change on this when we put that search order in there i want to load it up d8 when d8 is changed so let's write that up right now inside our orders right here and it's going to be a change to d8 worksheet change we're focused on d8 so we can write if not d8 making that change nothing then and i also want to make sure d8 doesn't contain empty right and range d8 dot value does not equal empty then do something right what do you want to do first i want to order load the order but i want to do make sure i want to take what's in d8 and i want to place it directly inside our order id b5 so we're going to do just that range b5 dot value equals range d8 dot value set order id good let's take a look at that now saving our work we only have one order we'll fix any issues that have come i'll double click on this and then we didn't look like we had any issues let's see this loaded up it's an estimate so of course we don't have any information we don't need that and we've got our database rows this looks good this doesn't look great whose idea was that i'll fix that up we don't need that format those cells that was not not not gonna so we don't need any fill on that none on that okay good all right that comes up again we'll fix that i think that was from an incorrect sheet on a probably a i know what it is it's an incorrect when we run our advanced filter we need to make sure that we have we meaning me i'm one that made the mistake probably it wasn't you guys so here orders then we need to make sure that we're always calling out that she remember we had an incorrect sheet before we probably did that probably probably incorrect that remember we had the inch it looks good now but when we didn't put that sheet in it added those results up here that was for the incorrect sheet when we didn't call out our item database that's good now we're good on that so i like that it looks up good so now what we can do is add new let's see estimate clears it out entering on okay good that's looking good everything's looking good now all we need to do is just a few more macros on this i want to cancel new print and email on that and then we'll get those graphs and then we'll be done we've got a few formulas but we'll get to those in just a minute cancel new models will finish out these while we're at it all right when cancel new basically all i want to do is just simply load any order so what we're going to do is i'm going to check in the other database if a4 doesn't equal empty i'm going to take whatever's in a4 and i'm going to place it directly inside d8 and that'll automatically load it up so we can also place it in b5 and load it too so either one would work just fine so let's do that if orders database dot range a4 dot value does not equal empty then in this case orders dot range d8 dot value equals again in this case here orders database a4 equals this right here that's all we need to do and that's cancel new so that's all we're going to do let's see how that works and make sure that that triggers that and copy that and then what we're going to do is we're going to paste that on this cancel button inside this macro assign the macro pasting it right here clicking okay okay so we're going to add a new estimate cancel the new estimate it's going to load that first one that's exactly what i want load a new work order cancel in there it's going to load that aspect notice that when we're in the invoice screen if we cancel new it's going to load an estimate i like that it loads that estimate but what i want to do is i also want to run the macro notice it's the invoice is selected we don't really want that so when we load that i also want to run the macro what macro do i want to run i want to run this macro right here this macro right here set button so when i run this macro notice it changes to estimate so it is this macro that i want to run an order load okay so as soon as we set that we can do that so going into the order macros and going back up into the order load as soon as we set um whatever is located in b3 we can set that type here we could probably just run it at the end that should be fine right about here is fine so that way let's say we're in the work order let's say we're in the work order right and then what we're going to do is we're going to cancel and we're going to go to the estimate and that way it highlights the estimate all right i always wanted to go back from the invoice notice invoice selected cancel news you can go back to the estimate perfect just the way i like it all right so now we got the cancel new going now all we need is print which is one line of code email and then we're going to get to this summary here and these graphs up here then we are done on this amazing training all right so let's get into the order print that's going to be really easy so it's going to be simply we're going to set the print range manually because it's only a single we don't need a dynamic so what i'm going to do is i'm going to this is going to be our print range right here page layout here set the print area and set that print area that is i'm going to preview it to make sure we're already fitting in it looks fine it looks good i need a border around the bottom here but otherwise we are good to go on that so i'm going to format those cells sorry it's off the screen here and just making sure that border set there on there okay and then we'll preview it again and make sure that that border is looking okay now it's looking good i like it it's looking good we can also of course set the margins if we want to increase the margins if we want but i think we're good to go on that we'll increase this a little bit here we've got some more space to go so no problem on that okay looking good so let's set that print area all we need to do is inside the macro is print that page now that we have the print area set manually because it's done all we need to do is orders dot print out and then what are we going to print out what we just print out from to copies preview we don't need to print active printer we'll just put true on the active printer and then we'll print to ignore the printers and false so i don't want to i don't want to ignore those print areas so printing out is good i've got snagit as my default printer so let's copy this i'm going to assign that to our print button that we've already created up here and then the last thing we want to email so we're going to sign that macro paste that in there click ok print that order and it's going to print out automatically on my snagit default printer and let's take a look at that okay that's looking pretty good i like the way that that looks and we can close that out now now all we need to do is email and then we'll delete the order and then we are good to go okay so emailing order whatever you want to do of course i've already set some objects up here to help us all the way up here i'll look as an object and i like mail is an object so that's going to help us if we don't no good i also want to file path i've got a file path of that we're going to create export as a pdf and i'm going to attach this as a pdf so to do that we need a file name right that file name is already defined as a string here so we can use that so orders with the orders what do we need to do the first thing what i want to do is set that file name what is going to be that file path file name is going to be equal to they will use the current workbook path this workbook dot path and i want to add to that and backslash and what i want to do let's add this create a name so i want to know let's say estimate and then the id let's say the type type is located in b2 dot range let's say orders or we already have a dot range b2 that's the type and let's do an underscore here and i also want to know the id and the id is located in b5 so and dot range b5 so we've got the type and the id i like that like estimate number one or something like that that should be sufficient and i also want to make sure it's going to be a pdf so we're going to go to the end and dot pdf dot pdf okay good so we've got the pop path set we need to add let's say right here there we go okay so now that we have that file name set and now what i want to do is i just want to check to make sure that this doesn't already exist so if directory file name vb directory does not equal empty right it means if the files currently exist then we need to kill it i need to delete it if the same name so in this case kill file name we just want to delete file if exist so now let's kill this two l's and kills all right kill me on that then it is killing me all right continuing on now that we have that what we want to do is i want to do an export as a fixed format what type of format is it i want to do a pdf type of format and the file name we've already defined that here file name and what we want the quality can be anything we want in this case include doc primary false we don't need to include those doc properties ignore printers let's do this one ignore i don't want to ignore the print areas and then from 2 we don't need open is published this is okay and then fixed format all right this is fine we don't need anything after that that should be sufficient okay so once we've created this file this perfect file that we have i want to set the alex set the outlook app is going to be equal to create object and what kind of object do we want to create i want to create the outlook application outlook dot application it's going to launch the application so once we have the application i want to set the outlook mail set the outlook mail is going to be equal to the outlook app.create item 0. it's going to create that email that single email and now we can focus on with that so with that outlook mail what are we going to be doing well i want to do a few things i want to set the 2 is going to be equal to where's our orders i need to call out the sheet again because we're inside the alarm mail dot range where's that client email located well that client email is located i want to locate it right here we need to add a formula for that let's do that right now equals if error what is the value where we can simply use an index and i've created a named range if we remember i'm going to call customer email and what is that row it's going to be based on here the customer row here but it's not based on that actually let's just minus two right minus two dot one in that case indexing that we have everything if there's an error go empty okay good fred there we go i like that now we've got the email as long as we have a name okay so we're going to save that customer email is located in b20 so range b 20 dot value customer email so we've got the two there and after that what i want to do is i want to set the attachments right i want to attach that pdf so inside that we're going to do dot attachments dot add and then file name and then file name is going to be there that's it so we're adding the file name that's the pdf done that and then what do we want to do i want to do the subject we can do a subject can put any subject in there we want subject we can just do this as a subject that should be sufficient which is this right here make it easy subject is going to be equals ordered so let's do orders in this case and then orders again right we need to call out the sheet because we're inside the email here okay so we're going to add that and that's going to add that and then also what we want to do is i want just let's leave the body blank for now equals we don't we didn't have a template for this one as far as the body so that's fine then dot display i want to display that email if you want to send it to send without displaying use dot send okay that's it okay and then let's see we can do that's it for that the email there let's do on air go to zero in case there's any errors okay and then also what i want to do is i want to set the outlet down they set the outlook mail equal to nothing so that's that we got that covered there and then that's pretty much it so now all we need to do is just assign that to that button there so we're going to copy this outlook email right here i'm going to take that and we're going to copy it right here assign that macro paste it in there click ok and now click sign email order it's going to create alex let's fix that attachments nice spelling there randy okay moving along and it's going to create that email and let's take a look it's got that let's double click that estimate number one and we've got our nice estimate here inside a pdf format okay let's take a look yeah that looks pretty good i like that i'm pretty happy with that and we got our total good good good all right okay that looks nice all right so we've got our email done that's sufficient enough we don't need to worry about that anymore saving our work okay let's add in that's pretty much it for the macros let's add in and then we need to do a few things and update that delete the order we may save that on actually i got a good idea tell you what work on that delete if you can get it if not i'm going to put that in my patreon account patreon is where it's all about so patreon i'm going to add this into that if you don't get it you can always find us on the patreon of course it's very very cheap just three dollars a month or 10 or 20 based on which one you want to look at and i'll create these updates take your ideas whatever you want me to add to this all added create a brand new training with this if i miss something or you want me to focus on something or fix something i'll do that i'll put the training and the updated workbook inside patreon for you so go ahead and get signed up i'll include the link down below all right let's get this completed the first thing what i want to do is let's continue on and we're going to create a work order for this so i'm going to create a work order i'm going to click save and update i want to save that work order okay making sure that we do have the work order now we've got a work order id going in the order database we now have the work order id going in the order items making sure that we have the items associated with that work order perfect let's create an invoice for that okay i'm going to go next to invoice i'm going to save and update this notice we've got our estimated information here save and update okay good now we've got all the orders here order database we now have an invoice just the way you like it we need an invoice id here i'll double check on that should be three there and then also what i want is making sure that we've got this that looks very good we've got our information here all right we're looking very very good now we've created the whole scenario estimate work order and invoice everything's here notice we have no prices or anything on the work order and the invoice we can flip between the three of them and all we need to do is simply have nice conditional formatting here okay let's update that and then go into the home conditional formatting i want to make sure that we have added that manage those rules and then what we'll do is we'll update that accordingly we're missing conditional formatting there i'm going to add that blue one there so i'm going to copy that and then all right i just pasted the format so we're good to go on that formatting estimate work order invoice looking really nice okay let's zoom this to about 95 or 90 so we can get a better look at the screen here okay and then we'll save it now what i want to do is i want to put the cost associated with this item and i want to bring that down here i want to know the cost associated with this that cost is locating the item database and it's located right here inside f so i want to bring that in i've got that in a name range here called item cost here if we take a look in there so we can use that to bring it down based on the item name so let's add in these formulas right now if we're going to start with equals if error okay if there's all i want to do is just simply use an index i'm going to index that item cost item cost and i want to use a match and i want to look for that item name i want to look it up here if it's found inside the item name and i want an exact match okay i want to index that one column if there's an error show blank okay so that's going to pull out that cost right i'm going to copy this down and bring it all the way down here pasting in those formulas down here making sure that nothing else shows unless we have a cost okay it's the same item cost that i want to use right here so i'm going to copy that over bring it down here pasting data in here checking that is the same invoicing right i want to copy that but this is going to be based for the invoice one is going to be the estimated so i'm going to again i'm going to paste those formulas down here pasting those formulas down here okay good i like that what is the total item price from the estimate right i want to know that so how are we going to know that well basically what i want to do is just simply look up j10 j10 times k10 so that's very very simple so just multiplying this the quantity times the price so equals in this case if air we can use if air just in case i want to look if j10 in this case j10 equals empty then show empty otherwise i simply want j can j 10 times k10 that's all we have to do just simple multiply if there's an error just show empty okay so it's 299 it's going to multiply that i want the total price okay copy this down again all the way down here paste in those formulas all right good so i like that 229 multiplying that simply by now we've got the total price but what about the total cost well the total cost is relatively the same it's going to be almost the same except we're going to use a different cost variable this case in this case we're focused on our cost right a quantity times the cost right i want to know the estimated cost right so it's just that 149. copying this down right pasting down these formulas and we get the total cost i'll go all the way down here pasting those formulas here so now we have the total cost 1 times 170 right 2 times 15 31 and now what we want is the estimated profit well the estimated profits just simply again equals if air just in case there's an error is simply our total price minus our total cost that is going to be our profit if there's an error we can just use blank okay so we've got our profit is now 150 that's our estimated profit copying that down and bringing that all the way down here bringing those formulas down here okay so we've now got our estimated profit on our items right if our total cost for our price is 59 our total cost is 31.98 our profit is 28. okay great so that's all associated for our estimated but what about for our invoice totals we know the item cost i want that we know what our totals are right but we don't know the total cost total cost is simply based on the quantity times the cost right so all we need to do again is just simply add this in and just update that because we've already got the formula here so but this case the quantity is based on the invoice quantity here so we're just using that quantity you want to make sure it's the invoice quantity and our cost is based on our invoice cost our item cost here it's really missed the same thing but we just want to bring it over here so that's our item cost our total cost is located here total cost and then we bring this down our item copy this and then bring this down here paste special and then paste the formulas down here so now we know our total cost now what about our profit again it's simply just the same thing copying we can just copy this and then paste in the formulas here but in this case our profit is basically the item cost in this case our profit is simply we can just add this in our total here minus our total cost here that's going to be our total profit right so we know if we're selling it for 299 and our total cost is one for then our profit is 150. so copying this i'm bringing it down here pasting that formula down here so now we've associated now what i want to know is i want to know the total now one does the total so equals sum i want to know our totals here so we're just going to bring this down here and our sum is here and then extrapolating that we know the total price we know the total cost so i'm just going to bring it over here copying these pasting them here and holding down the control pasting those formulas here now we've got our invoice totals we know our invoice total is 350 right our total cost our profit was 303. we've estimated it at total cost and our estimated profits here great so now that we've got all that i want a little bit of a pie chart to show to reflect those and i want to put them up here and the pie chart is basically going to reflect these numbers here i want to know the price the cost and the estimated profit so what i'm going to do is i'm going to insert that here i'm going to start a pie chart it's going to be a basic pie chart we're not going to be doing anything crazy here because we've already three hours into this thing so and then of course we're going to need some data here right so i want to select the data and i need to know what series is on so we edit that i want to know we don't get a series name but i want to know actually the edit here so one the access label range we want to know what that is so i'm going to update that so what is that so let's go ahead and select the data in here select the data and i want to edit the series what's that series going to be that's going to be based on our information here let's bring this cancel this bring this down here a little bit we'll move this out of the way for a bit actually we can bring it smaller i won't need it that big at all i'm going to bring it about like that and good i like that the positioning there will update that because i'm going to put two one for the estimated one for that so we don't want it too big inside this so let's select the data here we're going to edit that i'm just going to bring in those label ranges here and i want them pretty much this is going to be sufficient here total price total cost in the estimated profit okay good i like that let's make it a little bit bigger here okay i want some series labels on here so we're going to add the data labels here i don't need a background on this so we can update that format there i don't need a shape fill we'll put no fill i don't need an outline here we'll pin this down here for now shape outline no outline on that i do need this a little bit bigger so we'll increase that and make it bold here so we can see it and also going to make these bolts so we want to know our total price estimated coffin and just let's change the layout a little bit on this one make it look a little bit better on the design side of it and probably go with something like this so i like that i thought we'd get rid of the background we did but we had to change the design again didn't we okay so no outline again okay that looks pretty good okay so i like that but i want to add a header on that so what is that that's our estimated right so if we go in here into the layout here and we want to add a chart title and we'll call it content over this particular chart title is going to be our estimated so we're going to change that to estimated and then what i'm going to do is i'm going to duplicate this and based on the invoice i'm going to control d just like that duplicate it we need to give these things names of course this one i don't think we needed this because we'd be the same so i'm going to delete that because we don't need it's going to be the same here this is going to be invoiced we'll use the same legend for both of those invoiced okay and then i can bring them a bit over here and of course we need to give these things names don't we and then i'll bring the name over here a little bit over here like that okay but the data is going to be different here so we're going to select the data right editing this this data is not going to be here it's going to be actually what is going to be here so it's actually going to be three items here we need to know the total which is here so actually let's clear that all out i need to delete all of that data here it's going to be here holding down the control that's one of them and the other two are here totals right here so those three are going to come from there clicking ok and then we have there and click ok all right so we have dow our totals here just the way we want them our profits 303 just we like it our costs are here and our total 709.57 that's coming from the invoice total very good okay so we've got our estimated and our invoice here and our little chart little graphs here now what we want to do is i only want to show these remember there was one little thing we needed to do we only want to show these when we have a specific we're showing the invoice i don't want to show these on work order estimate so i'm going to give these a name we're going to call this one estimated chart keep it simple call this let's do it right here actually inside the labels here calling them in and we're going to call this estimated chart es chart and i want to call this one invoice chart so this one's going to be invoice or actual let's call it actual actual chart invoice or actual is fine invoiced i'll call this actual it's both the same invoice or actual is fine but actual but both are good so we'll call it actual chart okay so we've given them names and now i only want to display these two when we actually have our selected on invoice right when i select something else we can close this now we can bring this up here when i select anything else right a work or an estimate or work order i don't want these to be displayed so how do we hide that well we can do that just in that little part of the macro that we have on our order load remember in our order macros we left something open and i told you we'd come back to it and we're coming back to it right now it's right here so if b1 equals 3 it's an invoice type then i want to show those charts now that we have those charts we can do just that so dot shapes in this case estimated chart dot visible equals ms true okay we're going to do the same thing for the invoice or the actual in this case pasting that in here and this one's called actual act and then otherwise we're simply going to hide them in here and in this case we're going to do ms oc false and then false so we only want those displayed saving our work and then what we'll do is we'll just going to alternate between them and then we're going to load them up okay but we also want to do that on load there's one more i want to add that and loading them it'll work just fine but actually selecting them let's take a look in this one two three four okay let's update that not quite working yet if b3 equals three right that's what i want to do let's set that up order new that's fine now we don't want that on new it should be on load not on new copy that nah not the right space order load is where i want to put those in load load load load exactly bring it down right about here or load that's where i want to put it in not new new wouldn't be do wouldn't be there all right let's take a look at that now let's see put in three here invoice good that's showing up okay not showing up on this and not showing up on this okay estimate work order invoice they're showing up that's what i like all right excellent all right we did everything the delete order that's your challenge if you want to know how that works make sure you join us on patreon that's where i'll have that updated as well as any other ideas you have thank you so much for joining us on this incredible one-click estimate work order and invoice we will see you next week don't forget to comment below and subscribe thanks again [Music]

Channel: Excel For Freelancers

Views: 25,433

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Excel Estimate, Excel Invoice, Excel Work Order, Excel Estimating, Excel Estimate & Invoice, Creating A Work Order In Excel, Excel Invoice & Estimate, Estimating in Excel, Invoicing in Excel, Excel Estimate To Invoice, Excel Work Order To Invoice, Work Order Excel, Invoice Excel

Id: CkbEQ_S91Y0

Length: 161min 30sec (9690 seconds)

Published: Tue Sep 14 2021