Create Simple Invoice Excel for Operations Effortlessly

Simple invoice excel for Operations

have you ever wanted to create a very simple invoice for your business or company but didn't know where to start hi this is Randy with Excel for Freelancers and today we're going to do all of that from absolutely scratch we're going to create this incredible invoice template complete with invoice search custom taxes custom fields and a whole lot more we're going to write every line of code every function every feature from scratch it's going to be an incredible training I cannot wait so let's get started hello and thank you so much for joining me I really appreciate it I've got one that has been highly recommended the simple invoice and this one we're going to do absolutely from scratch so every feature every function every format every named range every line of code completely from scratch and by the end of this training you're going to know how to create this absolutely incredible yet simple invoice complete with default due dates we're going to have the ability to add new save search for invoices on just one box we're going to set custom fields and a custom message and we're going to do it absolutely from scratch including this custom background we did create an invoice many years ago but it's time to revamp it because I noticed in our Excel for Freelancers Facebook group there's a lot of questions surrounding this template the invoice the original one so it's time to to redo it to simplify it and to make sure that you will have all the tips tricks and techniques to be able to create your own invoice inside Excel and customize it however you need all right if you have not yet subscribed I just ask you do subscribe because I create these comprehensive applications each and every Tuesday and if you are new to VBA every single Saturday I also create VBA basic for beginner tutorials so you've got the best of both worlds right here on Excel for free Freelancers if you need help assistance or you have questions of course that's where our Excel for Freelancers Facebook group comes in we've got over 65,000 members in that group alone if you do like these trainings and want to support our Channel I create an additional training and an additional workbook download each and every week for our patreon and YouTube members and those are always based on your suggestions your ideas and your feedback so it's a great way to give back to the channel and get a lot in return because not only do you get those updated workbooks and updated training videos based on your suggestions but you also get PDF codebooks you get Advanced downloads you're able to get so much discounts and things that other people don't get so it's a great way to give back and also a great way to share and learn more so it's a great idea all right so let's continue on this training I'll give you just a very brief overview of what this is It's a standard invoice we're going to be able to quickly easily add items to an invoice we'll set the quantity and it's automatically calculated we can set a payment amount if we want to do that we can search for any invoice we can add new we can save changes we can delete an invoice we can print it out each individual invoice has a customer we can set the customer here just by selecting it or we can start typing the customer in that's another way to do it the address will automatically update based on that customer if we want to add a new customer we can do that so let's say Fred Frank if we don't have that customer we can add that we'll be creating also this brand new user form for our customers so that's kind of a nice way we can save it and automatically that's going to go in if we don't have an item this is how it difference than the original we can also add that so if we want bread and we want a new type of bread let's just say sandwich we can also quickly add that in here just by checking that if we want to add it it's going to create a user form where we can add the description and a price and it's automatically going to be added into our invoice just like that so it's very very easy way to do that so we have that and a whole lot more this is exactly what we're going to be doing we have a custom message so big sale on bread if we want that we can just add that it's automatically going to be saved in our custom message down here our total session will contain a subtotal a tax the tax is dynamic if we set the tax we can set that to any rate we want it's going to be affected if we don't want to charge sales tax we can just put a no in there no sales tax will be calculated also we've got a custom field this is our custom field right here we can change this to anything we want so we can change it to service member we could do that if we wanted to and we'll WR justify that and so there's a lot of things that we can do which is kind of a nice feature here we can put anything we want in there so it's fully customizable relatively simple and we're going to be doing everything completely from scratch so that's what we're going to be doing what I'm going to do is I'm going to take this sample and I'm going to move it over to another screen in fact I'm also going to show you how to create a background like I have so that's cool that's why that's showing here and so here this is a workbook inside this workbook I have a basic table invoice items we'll be going over customers and items we don't even have an invoice sheet yet so we're going to be creating that invoice sheet so that's all there's no admin screen there's an invoice this is a list of all of our invoices once we add them each invoice contains items so we have multiple items on a single invoice it's going to be stored inside this list this database here we've got a list of customers basic customers with customer ID and I've got a list of items which contain item id name description price so that's all we have inside this workbook nothing else if we take a look inside our formulas and name manager I've got zero named ranges we'll be creating those as we work through that and also we're going to be creating a brand new invoice sheet so what I'm going to do is I'm going to click on new sheet two and I'm going to rename this as invoice that's going to be our invoice sheet what I'm going to do is I'm going to drag this to the front cuz it's the most prominent it's the one we're going to be working on so here's what we're going to do why don't we start out with some named ranges so that we can work on that what I want is an invoice ID so an invoice ID name range so as we add some invoice IDs here I want a name range called invoice ID so here we're going to go into the formulas name manager and we're going to create a brand new invoice ID so I'm going to call it invoice and then ID then what I want to do is I want to create a dynamic named range because it's going to grow as we add data I'm going use equals offset formula now we're going to start out in the header row because that way if there's no data at all when I delete the sample data there's going to be no issues with our named range because I'm starting on the header row however I don't want that header row actually counted in the data so I need to offset it one row down and no columns over so I'm going to leave that blank now I need to determine how many rows so we're going to use the count a for that now if I'm moving fast you can slow down the video or of course you can also watch it as many times as you want so count a we need to count how many items are in this list once again I'm going to start out at the header row and I'm going to go down and instead of six I'm going to change this to a large number and then I'm parentheses and then of course I really don't want to count that head a row so I want to use -1 and then we just want a single column so I'm going to use one for that now when I use the Tab Key and I Tab out and then I use the shift Tab and I tab back in we want to make sure those dancing ants the dotted lines wrap around the actual data and it's working correctly so I know we're good so I'm going to click okay so I've got a named range for invoice ID once again if I tab in we're going to see the dance again so we know it's good okay I'm going to add a new one why don't we add our named ranges I want another one for our customer ID so I'm going to do customer ID and it's going to be relatively the same so equals offset and then I want the head a row on the customers so we're going to select on the customers we're going to go to the customer ID once again on the header Row offsetting one row down no columns over so count a we're going to count all the items again just select something and then change it to a large number 99 subtracting one for the header and then a single column okay we're going to tab over here we want to make sure that it encompasses the data very good so what I'm going to do is I'm just going to copy this because I want to create one for the customer name also so I'm going to copy it contrl C click okay and now I'm going to create a new one and this one's going to be called customer name and then I'm going to paste it down here now as far as the count a that's fine I can count column A but I really want to focus on the customer name where is that customer name it is located in column B so B so you don't see it we see we're on the invoice list now but if I Tab out and tab back in it's going to go directly to the customer list and now we see customer names we've got one for the customer ID and we got one for the customer name perfect okay I'm going to do something similar for the item id I'm just going to close this out and I'm going to go to the items and I'm going to open it up one more time name manager now what we want is Item ID and item so here it says item ID which is good now what I want to do is again use the offset equals offset then what we're going to do is I'm going to select on the item header just as we did one row down no columns over count a we're counting all the items ID and we're going to change this to a large number 999 you see the repetition here minus1 comma 1 so again double- checking I always double check to make sure that it's correct I'm going to copy this one once again because I want another one for the item name so new item _ name pasting it down here now again to count column A but I really want to focus on the name which is in column B so I'm going to change it to a b clicking okay and then back down here we see that we have the correct okay good that's a good start for our named ranges we'll be adding more but that's what we need customer ID customer name invoice ID Item ID and item name great so let's move over to the invoice and we're going to start working on the header row so the header row is going to be a little bit larger so I'm going to set that to about 38 on the height and those first two columns are going to be used for our admin we'll be able to hide those what I'm going to do is I'm going to drop this and I'm pin this so we'll go into the home we will hide those columns so I'm going to set those A and B and then I want to add a title on that we're going to call this simple invoice now I want a little bit of a special look so instead of just writing in the cell I will add a text box and we're simply going to call this simple invoice simple invoice we'll get that formatted up just the way we want to so I'm going to select on it I'm going to go into the shape format here I don't want any fill so no fill on that no outline on that going to set a font we'll use a Arial rounded I think we'll use aerial rounded bold that's a good one and I want it centered and we'll set it about 30 on the font size that looks good now what I want to do is I want to give it a little bit of a color and Shadow so I'm going to use control1 that's going to open up the formatting and we're going to give it a fill I'll use a gradient fill on that actually not that fill no fill on the fill but the text should have the fill so that's when we want the gradient fill on and we're going to adjust that a little bit so I'm going to delete these in here just going to have two colors so I'm pressing the delete key the top one I'm going to set a custom color on this so right about here and then we'll use a darker one on the bottom one so that it stands out something about here so I like that and now what we want to do is I want to give it a little bit of a shadow on that so that it stands out so a little bit of the Shadow like that and then I'll bring it down like that so that's a good idea that's nice start so we've got our simple invoice here and our header here we can close this out now inside our admin we're going to add some information here so I'm going to highlight some information here we're going to go all the way through these and I'm going to give these a distinct look because this is all the admin we're going to need and then we'll of course we'll hide it the first one is I want to know that selected invoice number so I want to know the invoice number that's been selected and then put that here next up what I want to do is I want to know that database row of that invoice and also I want to know what is the next invoice number next up I also want to know the search row now we're going to be adding a search field to search for that and I want to know what database row is when the user searches on I'll show you I also want to know when they select a customer I want to know what row that customer is on I also want to know what the next customer ID is and I also want to know the next item id because we'll be building brand new user forms for the customer in the item ID when we add a new one we need to know the next idea so now we can get some of the those filled in let's right justify those although it doesn't make too much of a difference so let's say we have got invoice number one I want to know the associated database row that's on that now if we take a look in our invoice list I just put some samples I want to know that this invoice ID is on row four since we already added our named ranges we can figure that out so we're going to use equals if a in case it's not found I'm going to use the match formula and I'm looking up something what am I looking up I'm looking for this invoice number one what is the named range that we're looking up invoice idid we want an exact match so I'm going to put zero on that now I want the Row the first one starts in row four so I need to add three on that if there's an eror I'm just going to show empty so what I want is I want to know that invoice number one is on row four so if I take a look at this we see it's correct it's on row four and if it doesn't exist it's going to return blank I also want to know the next invoice number equals if Air Max I want to use the max formula invoice ID what is the maximum number of all those it's currently three plus one is going to make it four if there's no date at all it will return an error so I want to default that to one so here's what I mean four is the next one however if I delete all of these numbers I wanted to default to one so that's exactly what it is now one great the search Row the customer row we'll save these for just a bit as we build out our custom invoice template so the first thing what I want to do is I want to save column c as a spacer so that allows us to move the invoice wherever we want it on the sheet so it's kind of a nice spacer so we're not going to put anything in there and what we're going to do inside here row two I'm going to expand this one a little bit because I need this one for the header now we're going to go all the way to H so this is what's going to be our title so I'm going to merge and send to that that's going to be the title of our invoice all in caps I'm going to put invoice Here and Now I want that on the top I want it italicized I want to bring the font up I want it bold and I want to give it a color ing to our theme we can use this blue here great so the First Column is going to be for our item name so we need that a bit expanded our second column is going to be for item description so we'll need that expanded a lot and our third column will be for quantity well in fact we'll just put in the names right now and then going to go in row seven so write in row seven I'm going to put in the item name next one's going to be the item description and next thing I want the quantity of the item and also the price and lastly the total so this is our invoice and then here what I want to do is I want to put in what customer and then our customer drop down list will be right here in fact we can do that now our customer dropdown list we know it we created a name branch called customer name we can put in a data validation now I do want them to be able to enter anything so the first thing what I want to do is put in list now if we can't remember it's C tore name but if you can't remember it just put in F3 it's going to show all of our named ranges we can select which one we want now because we want to be able to add new ones it's very important that we set this err to uncheck so that means the user can enter anything now when they do enter anything VBA code will tell us it's a new one so we're going to click okay on that so what we're going to do is now inside F I want to put the invoice number and then next up I want to put our invoice date lastly I want to put our due date and then next I'll use for a custom field very good so now what we want to do is let's put in a background for this a nice background and it we'll give us some color so what I want to do is page layout now I was asked the other day how do I create these backgrounds let me show you how I created the background for this so what we're going to do is I'm going to go into something called mid Journey mid journey.com this is an AI generator and it's very cool now this is called mid-journey Alpha meaning I don't think everybody gets this just yet but by the time you're watching this if you get signed up I think you're going to get a number of pictures for free and so basically what I do is I put in something like light blue abstract so I just write it in here light blue abstract background and then you can set some settings so I did set some settings here you can do a portrait you can do a square landscape so I set it to landscape 16.9 you want to use the most recent version that's the most updated version of mid journey and then you can mess around with the settings I usually don't mess and the speed of course and so we can do turbo or something like that so that way I just hit enter and it's automatically going to start adding those so you can see here if we scroll up here it's adding those here and we'll come back to that in just a moment so that's what I did so you can see here I even put the logo which I have to add so I got a logo I put in blue invoice logo transparent or white background but you can see here I got all kinds of images it's really amazing green ones for a previous training some logos some pictures that I had fun with bid journey is just amazing so I use it constantly and I pay like 10 bucks a month for it but I think you'll get a bunch for free so here's the ones that are getting created I just put those in and so you can see them as they're getting created and it's pretty fast so once I get one I like for example I use this one here I just click on it and then at this point you can make changes if you want to it upscale it down scale I don't do too much of that but oops I scroll too much okay so then you just download it so I downloaded it and then it wasn't quite the right size for Excel so then what I did is I used my snagg it editor so I just basically opened up this picture and I need to adjust the width of it so I put it about the width is 2750 and the height is about 1350 so I set it to about here and I also wanted to set the effects so I put a filter on there and I just put the opacity or transparency at about 68% so that's how I got the background and just make sure it's in a JPEG or PNG so I just could apply now that's one that I've created here now also you're going to notice when we create these forms you also going to see a background so for the form so I saved this as a background just like this then what I did is I kind of reduced it for something that's a form and then then I kind of brought it up here and then I brought it down here a little bit like that something about like that for our form background and then what I did is I'll run a save as so I'll do file and then save as and for the form backgrounds we want to make sure that it is in our format so we want to make sure that it's in a JPEG format as you can see here selecting here and then selecting the JPEG and that's exactly what I did for the form background so you can see the form background here we'll be using that a little later so both the background which can be in PNG background for the Excel and form background which must be in a JPEG so we'll be using those just so you know how we created both of those and that is for our background so somebody asked me on uh YouTube exactly how we created those so now I'll send them to this very good so we can stretch this out a little bit now what we want to do is I want to highlight those use contrl B to bold it I want to put those in the center I want to give those a border all the way around and we're going to give it a little bit of a color and I've choosing this color so for the background page layout background here and then what we're going to do is I'm going to work offline and we're going to select the background that I've used and it'll open that up eventually and I'll go to the folder that I'm working on which is our simple invoice from scratch and I've got our background image right here and I've got a logo to enter too so we've got our background here so that's it now as far as the view the grid lines we don't need to see the grid lines here and also I want to then color these I'm going to hold down the control so I can select multiple sections and I'm going to go all the way down so I want to make sure that we can cover all the rows we're going to go down to 39 so all the way down to here and we're going to give those the white color so I'm going to go back in and I'm going to make sure the background is going to be white for our invoice so now I also want to make sure that there's a consistent border around the entire invoice so we're going to go into the home here and then I'm going to go into outside borders you I just want to make sure that the entire invoice fits correctly so here what we're going to do is I'm going to hold down the control for these going to pin this one more time as we work on these and I'm going to WR justify those this one I'll put an underline here because I want the customer name to be evident that the users are going to enter it so we'll single line at the bottom border that's fine the address will appear here this will be a custom field and we'll have some information here now down here is where we're going to put in so here I want to put the balance and then above that I want to put the payment above that we're going to put our actual total we're going to put the taxes here I'm going to put this uh just temporarily we're going to change that text and the subtotal here so we are going to write justify these here and then I'm going to put a border all the way around all of these and we'll customize that just a little bit so for now we'll put in all borders and now on this one I'm just going to put a dotted line so contrl one border and I want to put that dotted line as a separator for those fields right here clicking okay this one is going to be our custom message so what I'm going to do is I'm going to merge merge and center that Center the text here and I want it bold and probably italicized and I want to increase the font to about 12 or 13 or something like that okay let's just put thank you for your business okay that will change I'm going to change that but I want borders around all of that so that's looking really good so for these the item name price total I want a separator in here we're going to use that dotted line so once again we're going to use control one and I'm going to use this dotted line I'm going to use it on the sides in the middle and and the n on the right side here that's going to be our little separator for our category cool so these are going to be format I'm going to use my control except for the tax the total the payment the balance just not the tax I'm going to format those as a currency so we got them formatted here the invoice number doesn't necessarily need to be merge and center but I'm going to do it with these with all of these I'm going to merge and center them so merge and center and then left justify them so we have our invoice number now our invoice number is going to be whatever's in B2 so equals and then we're going to put in B2 so our invoice number is going to show up right there okay our invoice date that's going to be formatted as a date and our due date is also going to be formatted as a date so I'm going to highlight those and those are going to be short dates so we can put in 115 here and then we're going to put in 215 just as a sample this one's going to be a custom field very good so we kind of have the premise of our invoice we want to add some more information I want to be able to search for a particular invoice so we can add that here not in this particular row we'll go the rot down so we're just going to put search invoice number and below that I want to put the payment amount okay I'm going to double click column J that's going to expand it automatically I'm going to right justify these this is where the user is going to be entering information so we're going to put this in white and put the borders around here in fact I want to put the borders around all of those here and I want to left justify these here so that when we search for an invoice we'll put in one and the payment amount whatever is450 so I want to make sure this is formatted as a currency as well as well so we're going to use currency very good now when we search for an invoice I want to know if the users said a correct invoice number or not so that's where this search row is going to come into so what I'm going to do is I'm going to use a formula equals if eror and I'm going to run a match just like we did before that match we're going to look for this invoice number we're going to look it up inside our invoice IDs and I want an exact match if there's an air I'm going to show empty notice it's empty there's no invoice number one but if I do add that invoice number one one I want it to return four and that's exactly what it did okay we need to add three to that so we can get the correct row although it's not critical in this case because all I need to know if it contains a row or not if it is a number it contains any number I know this is a correct ID any number at all then all I need to do is take that ID and put it directly inside B2 and I'll show you how that works we're going to do pretty much the same thing for the customer but I want to know the row that's associated for Betty White if I look inside our customer lists we realize Betty White is on row four so what I want is going to use that named range to determine what row Betty White or whatever customer has been selected here so we're GNA use equals if air once again we're going to run a match we're going to look for this customer right here we're going to look inside our customer names that's the name drain that we created we got an exact match I want to add three because I want to return the row that's associated if it's an err just show empty we see that Betty White is on row four perfect if we clear it out it's going to show empty I want to know the next customer ID so to do that we're going to use just like we did the invoice equals if air we're going to use the Max and this time we're going to Max it based on the customer ID that named range that we created plus one if it's an air meaning no data I'm just going to return to one so the next customer ID is 12 if we look in here we see the next customer ID is 12 that's perfect we're going to do exactly the same for the item equals if air we're going to use the max formula on the item id we're going to use plus one on that there's an eror we just set it to one that's going to tell us the next Item ID is 21 if we look inside here we see that the next one 21 so that's it saving our work so far we want to add some ability for the customer who's ever using this to be able to customize this so we're going to skip some columns around here that way we can hide them easy if we want to inside here I'm going to put here our invoice settings call it invoice settings and I'm going use three columns for that I'm going to use p q and R for that so I'm going to merge this across merge and center make it bold here so the first setting that I want I want to give the user the ability to set the due dates when I create a new invoice automatically I want whatever the current date is I want to add the number of days so we can automatically set the due date so inside here we're going to put default do after then the user is going to enter inside column Q they're going to enter whatever days right then we'll just put the word days here so now next up that's one custom setting I want to know if we're going to charge sales tax or not charge let's put in sales tax and that's going to be yes or no know so we're going to go into the data validation and we're going to add that dat of validation in here so here what we're going to do is this one is going to be required so that's important I'm going to use list and it's simply going to be yes or no are the two options and that's it so if they say yes it's just going to be yes we're going to call this tax option going to give it a named range I want to know the tax option either yes or no and I want to know the tax rate so sales tax rate what is that rate let's just say 88.5% or 085 but I need that in our percentage format so we can do that but I want to put in two decimals so that's about right so we're good to go on there I also want to know what our custom field name we're giving one custom field so custom field name now what is that now we could do anywhere let's just say we want a delivery address or anything we want to put in there and that's kind of a big field so what I'm going to do is I'm going to merge and center that and put on the left now all these user intered fields we want to make white so we're going to do just that setting them to White and the borders and then what we can do is we can set all these borders here I just need to change one thing we'll double click here right justify these and we'll left justify these just to make them clear and also what I want to do is on all of these options I'm going to format those sales using control one or bringing up that and I want the dotted line on the left side this is consistent with our field that's kind of nice for our invoice settings but I would like one more field I want that customer message so in case they want to put in customer message and it could be a large message so we're going to give it about three rows I'm going to merge and center that left and up white font and border and then we can merge and center this so we can do something thank you for your business so that kind of helps so we can put that automatically we're going to give this a name r although it's not necessary custom message and the only reason we can do that is kind of nice here now instead of this we can simply do equals custom message and it's going to be the same thing so as we change this it's going to change here perfect so that looks good now what we want to do is I've got this called tax option and I would like to have this called tax Ray I want to use name range it's nice in the formulas if I use name ranges instead of the actual ranges so tax rate we're going to call this and we're going to call this let's say due days so I'm going to give this one right here named range called due days that'll help us inside the code it makes the code clearer so you can understand called due days and we can have this one either we could use a custom field so basically whatever we have here I'm just going to put equals whatever this is right here our delivery address and we're going to WR justify that there we go we might want to expand it a little bit or we can make it a little bit smaller deliver to let's just do that so we have that that's nice so we can put whatever we want in here very good so things are looking good now we have item names now remember we created a name range for item names if we want to review we see we've got one for item name and this be all of our item names so that's exactly what I want to do for invoice so holding down the shift for all those item names we're going to go into the data validation right in here in data validation and I'm going to create a list and again we're going to call it equals item names and F3 will find that if we want to use that item name so I'm going to go back into it one more time because I want to make sure that the user can add in a new item name if it is new we'll prompt them for the user form and they can actually add in brand new so that means we don't want to show any error if they put in something incorrect because we want to give them the option saying hey this item is not inside your list would you like to add it and we're going to do exactly the same for the customer so we're going to click okay all right so now when we enter something let's just say chicken it's going to automatically come up if we enter test which is not it's not going to show an err and that's exactly what we want very good so we're going to save our work currently and we can increase this a little bit the item description item name we need a little bit more space on but things are looking really good the design is primarily set we just need a button set up here to be able to add new and save so what we're going to do is we're going to insert a shape use a square shape and I'm put in a button right about here now the color is pretty much good already but I don't want an outline on that so no outline and we're just going to call this add new we're going to format that ingly once we like the format of it we can copy and paste the rest so I'm going to go into the home I'm going to write justify and put it in the middle so that's looking pretty good here and we can zoom in a little bit if we want to see so I like the way that that looks we will put on our icons after I'm going to duplicate that we're going to use this for our save button so I'm just going to bring it over here we're going to use this one for save save and we don't need it as big so we can reduce the size of this one shape format so if we don't see the third button we can zoom in and we zoom in a little bit more when we select it here we can see that third one now so we can move it if we want to and we need another one for delete and one for print so crl dtrl d one more time and we're going to use these here and one's going to be for delete one's going to be for print so we'll just put in the word delete here and bring it over a little bit increase it because we need to have room for the icon and another one for print keep it very simple in this one so we like the way that looks I want to hold down the control just want to line them up make sure they're lined up but it doesn't have to be perfect just yet we still need to put in the icons so now it's time to insert the icons so inserting the icons pictures and we're going to place over the cells from this device and I've got a list of icons if you want all the icons or the background or anything I do make them available on our patreon platform so I'm going to insert all of them they're too big so we're going to set them to0 2 it's going to reduce the size now what we need to do is just bring our icons over here and we're going to size them up ingly and then we're going to put that here print here and the save we can increase the height a little bit so I'm going to hold down the shift on these going to go into the shape format and we're going to set the height make sure they're all the same to about 024 that looks pretty good 0. 25 is even better I like the way that our buttons look so now I'm ready to put them in the middle to make sure and I'm going to group them individually and then together so first holding down the control making sure they're in the middle then grouping them let's bring this one over just a little bit and the same thing for the delete we can expand a little bit we have enough space holding down the control in the middle and group and we're going to do the same thing for the print going make it a little bit bigger some room for the icon and then want to group them individually then together so now that they've been grouped individually I'm going to hold down the control going to make sure they're all lined led up and all disperse properly so lining up in the middle disperse them properly grouping them together once they are grouped we're going to use control one what that's going to do it's going to have our format up we want to set the properties to move but don't size now this is very important because if I decide I want to resize our columns the buttons do not change so very important saving our work so far we're almost ready our format and our function is everything there we got it all good so we have our item names so there's a few things that we want to do now we want to make sure that we can add in a customer name and when that happens I want the address to appear when item is entered I want the item description the quantity default to one and the price to put in and I want the total now if I have the quantity and I have the total I want to make sure that that total is automatically calculated and of course we're not going to clear that out through VBA I'm going to Center this one in the middle and I'm going to create a formula that's going to automatically calculate the quantity times the price but only if both values exist so how do we do that we're going to use equals if then we're going to use the and because I want to make sure that whatever is in F and the current row does not equal empty and we want to make sure that what's located inside G is also not empty so G8 also does not equal empty putting in the eight and then once those both are true what do we want to do so we're going to close that out if they're both true I'm then going to Simply multiply f * what's in G and then we're going to do that for each individual column times here however if it is empty we're going use empty or zero is fine too we can put in zero and that's fine too we want to be zero great so we see that now what I want to do is if one of them is empty we can put in zero now if you don't want zero to show up we can just simply use empty which is kind of probably better and that's fine that's exactly what I want to see okay good so we have it so now what we're going to do is we're simply going to copy that and we're going to paste this all the way down and we're going to paste in the formulas we don't want to upset the format so now we have that so let's add in another item just for fun too at uh 14 so what we want is the sub total so it's simply going to be equals the sum of whatever is located inside this column so we're going to sum it up very good now we have that now what I want to put is our tax now I would like the tax column to show not only the tax but the tax rate as well so not only our sales tax right but I want to show our tax rate so how do we do that I'm going to use a form so instead of the label equals if our tax option equals yes that means we are charging sales tax then what do I want to do then I want to put in the word tax I want to put in the parentheses here and then what I want to do is I want to put in that tax rate and but I want that tax rate to be formatted so I'm going to put in the text because I want to format that what is the tax rate is the tax rate that we put in what is the format that I'm going to assign I'm just going to put in quotation marks here 0. and then the percentage sign then the quotation marks then the end parentheses so we want to format at that what if it's false if it's false they just want to show anything meaning the they're not charging a tax option so then we got that but I want to add one more thing onto that that is the last parenthesis here so here we're going to add in and so one more thing here after the quotation marks and then put in the parentheses there we go that's what I want right there so that's looking good now we're going to double click that column so the tax rate is 88.5% however if we say no that we're not charging sales tax we want that to be empty and that's exactly what we want now we need to do the same for the actual amount of tax so we're going to again use a formula equals if our tax option equals yes then what are we going to do then we're simply going to multiply our subtotal times our tax rate and this is why we create named ranges it's much easier otherwise we're just going to show zero all right so I like that and if we say no or we can just use it blank again if you don't want to see the zero in there we can probably just use double quotes and that way it's just going to be blank when there's no tax option very good so we see if we're not charging tax now the total let's go ahead and reset that to yes now the total of that here is going to be equals the sum of both our subtotal and our tax amount so we're just going to sum these two perfect now our payment is going to be whatever is located here so if we put in 10 here that's our payment so our payment amount again we can use a named range if we want or we can just link it to the cell either one is fine so equals whatever the user has put inside the payment so equals whatever there is okay great so now if they change it to let's say 62.93 and then we want the balance is simply equal to Total minus whatever the payment is and it's going to be zero perfect so now we've got that set up it's looking really good we can zoom out a little bit to make sure and our invoice is looking good we want to set the print area to do that we're going just highlight everything we're going to go into the page layout and we're going to set the print area and we can preview it to make sure that it kind of looks good so let's get out of that cell and then save it and now we can preview it to you again once we do preview we want to make sure that it's looking good we can also set the margins and update it but it's looking fine if we want to change the margins we can increase it if we want it centered on the page we have some more availability so we see how much we can increase the columns we might want our total column to be increased a little bit all right it's looking really good I like the way that that looks so we are good to go as far as the design we're almost ready to start with the macro so what do we want to start on well what I would really like to do is when I enter a name I want that item description the quantity one and the price so it's going to come directly from here that item description and the price to come directly so how do we do that well that's of course going to happen on VBA right so when the user makes an actual change we want to do something so how's that going to happen well what we're going to do is we're going to go inside the V ba now if you've never written code before don't worry I'm going to walk you step by step if you don't have this developer tab available you can right click on anything click customize the ribbon and make sure you've selected the developer once it is open you can use Visual Basic clicking here or alt f11 to get you there that's the shortcut that'll get you into our VBA editor once you have that we got a list of our sheets we don't have any modules any user forms we'll be creating those we take a look we've got sheet two invoice first thing what I want to do that's the one we just created I want to change sheet two I want to change the code name so I'm going to click the properties here and we see it's sheet two I'm just going to change this to invoice so I can refer to it in a code AS invoice so I know which one is which it's always good to change not only your sheet names but your code names as well we can do that through properties very good so invoice now we want to focus on the invoice sheet when we use to make a change event right if we're going to make some kind of a change to this anywhere from d8 all the way through d34 I want something to happen happen so that's going to be a change EV event so we're going to go into our worksheet then what we're going to do is I want to look in a specific type not selection change which is the default that comes up but I want to focus on the change event and we can remove selection change we don't need that so the first thing what I want to do is if the user makes a change to more than one cell at a time I want to exit the sub out that's going to help us moving forward so if target. count large meaning the number of sales is greater than one meaning more than one sell at a time then exit sub on change of more than one cell at a time please exit it's nice if you say please so now what we want to do is I want to focus on a specific change event now I have Auto hotkey that's going to kind of help me automate our text so if I do something like that and you see it type out real quick auto hot key is an automation tool that helps me write code a little bit quicker or do anything a little quicker we're going to focus on a change event but very specific cells we're focus on d8 all the way through 34 so if they make any change at all I want to do something then so notice how end if appeared automatically that's also Auto hotkey Now what I want to do is I want to differentiate the type of change if range D and the target row meaning the value that they've changed target. row. value does not equal empty then I want to do something else if it's empty then that means they've cleared the cell cleared cell so what happens if it's we'll start with that if they decided to just click delete that means it's empty what do I want to do I pretty much want to clear the item description clear the quantity and clear the price obviously I never want to touch H because that's a formula so I want to clear all these e through G so we're going to write that now range e and the target. row all the way through G and the target. row. clear contents okay so that's pretty much what I want to do Let's test that out and see how it's working so let's just put in test description here and so the idea is this when I click delete right here I want all this cleared out so I'm going to hit delete and it's cleared out now I want to make sure that formula is still intact which it is perfect okay so that part is working good what if they make a change that does contain a value if it does contain a value if it is an item that's in our list I want to know what row it's on so we need a variable for that we're going to use Dimension our item row as long I want to know what item row it is assuming that it is we can do the item name too but the item name is the target value so we may not put that inside a variable it's not necessarily but what I want to do I want to test it I want to know if what they have entered here is it in this list or is it new how do I know that well we can run a search we have a named range for item names so what I want to do is I want to look in that item name range formulas name manager take a look this is the list I want to look inside this list is it found or not if it's not it's a new item I want to give them the choice if they want to add it so let's first run a test to see if it's in this list or not we're going to look for the row but if it's not found it could create an error so we're going to use on air resume next and on air go to zero that's going to trap any errors we're going to set the item rows equal to it's coming from our items list so our items list. range what is it it's inside their item name that's the named range I want to look for it so I'm going to use the find what am I looking for I'm looking for the Target value that's whatever they they've entered I'm going to look in Excel values and Excel whole and I want to extract the row so if it's found I want to know what row it's found on so let's just put in message box item row so that's going to tell us what item row has been changed on so we're going to go back to the invoice here and I'm going to enter a name here if I enter a name it's going to say that milk has been found on row five let's check that we're going to go into the items milk is found on item row perfect so if I put in something that's not it's just going to say zero so if it's zero that means it's going to be possibly a new item so let's check for that notice that there's no error come up so if item row equals z then let's do something if we're going to write if message box item is not within the item list would you like to add this item then in quotation marks VB yes or no right want yes or no from them and then just put in the title item not found equals VB let's put in no they don't want to add it then I'm just going to exit the sub then exit sub otherwise we'll just put in temporarily message box we're going to be adding something here which is going to give them the ability to add an item to be added just temporarily just so we know everything's working so they put in something it's not there right would you like it to be added if they say yes we're just going to put item to be added so we know that that's working perfectly if if they say no then nothing's going to happen no nothing will happen perfect so I like that so that's working good now this point we'll be adding some more information a little bit later on so we're going to put open user form that user form doesn't exist but we will add that but what if it does exist so if it does exist I want to take the item row and I want to fill in some information what is it that I want to fill in inside column E I want to put in whatever is located inside column C and then I want to put in the one and then put in D so basically what I want want to do inside I want to put the description the default will be one and the price will come from whatever so e f and g are the cells that we need to add information to so range starting with column e and our Target row that's the row the users working Target row do value is equal to what items. range where is our description located our description is located in column C so that's what we want to add C and what C and the item row so C and the item row. value item description next up we want the default as one for the quantity so we're going to copy this paste it down here this is going to be F which is our item quantity we can bring it down here we can see that our quantity is in column F so inside column F we're going to put equals to one default quantity to one last up we want G where's it going to come from G is going to be our item price and G is coming from where it's coming from column d as our item price very good so let's take a look at that so we're going to change this to milk and we see that reduced fat milk one at 349 if we now change the quantity we see that it's automatically updated if we take a look inside items milk we see here 2% milk at$ 3.49 is the price so we see how that worked just perfectly great so we can quickly add items if we just start typing in in it's going to automatically just add it in which is kind of nice so it's quick to invoice so this automation so we see that auto type here entry automatically Works in Excel 365 so we simply just need to start typing it in and it's going to automatically show up very good so we see that our invoice is working well now what I want to do is something similar for the customer when I enter a customer I want the address appear here then the city state and the zip to appear on the second line so how do we do that well we see we've got our address here then I want the city the state and the zip to appear on the next line we want to check to make sure that customer has beened so how do we do that it's a little bit easier here because we've got a customer row here here we had to add the it them but here we know our customer row so if this is blank we know that there's no customer or incorrect customer has been added however if it's a correct customer we see in B6 that it is correct and that's going to all happen on change of e3 so E3 we're going to take a look inside there so that's also going to be a worksheet change so let's title this we're going to call this on item change so we know that there's a comment there now we're going to focus on customer change so we're going to go clear out any unused spaces we're going to focus on customer change so on customer change if not intersection we're going to focus specifically on Cell E3 so E3 is nothing now what I want to do is then do something again just like want I want to know if you can use Target value or we can use E3 either one range e3. value does not equal empty then do something but if it's empty what do I want to do if it is empty is empty then I want to Simply clear out what's located in E4 and E5 so for example if they delete the customer whatever's here in these two cells I want clear it out as well so E4 through E5 should be cleared out just to make sure so range E4 we can use comma or through either one it's fine E5 do clear contents so clear address Fields however if it's not empty then what do I want to do I want to check to see if it's an accurate customer how do we know we're going to look inside B6 if B6 is empty we know it is not in a customer at least not yet so if range b6. value equals empty then let the user know so let's just say not existing customer just like we did before we're going to use a message box if message box this customer is not currently in the customer list would you like to add it and then we can put a question mark here and then VB yes no and then customer not found equals VB no meaning they decide they don't want to add it then exit sub and otherwise we're going to add the customer so uh launch user form to add customer very good so I like that so we're going to launch a customer and so that's going to be fine but what if it is a customer so if it's empty we're clearing it out if it's not empty we're going to check to make sure it's existing customer empty means not existing customer we're going to add an else in here going to put an existing customer found okay so we know it's been found so what I want to do is I want to Dimension we can put that up here Dimension customer row as long where's that customer row located it's located right here inside B6 so the customer row is equal to B6 customer great so once I have the customer row what do I want to do well I want to add the address I'm going to put that inside E4 so range E4 value is equal to what customers that's the database sheet range and where's our customer located our customer address is located on column C so inside we're going to just do that c and the customer row. value customer address so once we have that E5 is going to take on a little bit different so I'm simply going to copy this here paste it down here E5 is going to take on what's located in D which is the city then I also want the state and comma space and the state so I'm going to copy this which is located in column e and we're going to put in this is e and then what I want to do and I put the zip code and space and the zip code the ZIP code is located inside column F so we have d e and f perfect so I like that so we're putting those all in there d e and f so now we don't need this here we don't need and this we don't need just copy too much okay so we're putting in d e and f perfect I like that so saving our work so far let's go ahead and test it out here and that's going to all happen on the change of the customer so just double clicking on here that's going to run the change and we see that we have that if I click delete everything's going to be cleared oh so we need to make sure E4 is also cleared out let's double check that clearing out E4 this should not be a 5 four so again double clicking on there clears it out perfect looking good all right so that exactly what I want so that's working good the only thing that we need to add is the user form which we'll be getting to in just a bit so basically once we add Mary once we add the items we'll be able to add that in so let's do that now let's start on those user forms and create user forms so that if they decide to add something like this right I want to be able to add sugar would you want to add and then I want to launch the user form if we debug that we see that we have obviously not Target so what we would need to do is we want to exit the sub at this point right cuz we can't add it if if that Target row doesn't exist we don't have an item row so we need to exit because it's not found all right same thing for the customer we need to also make sure that if customer is not found we need to make sure to exit but here it's going to empty it we're clearing that out if it's not found we're going to exit the sub here we're going to launch that user form but let's create those user forms so what I'm going to do is I'm going to save our work we're going to add new user form so we're going to click insert user form here or we can also right click here and insert user form same thing okay so we're inserting the user form that's going to create a user form here we're going to use our first one which is going to be for our customer so the customer got a lot of fields so we're going to make sure it's down here and we're going to bring our properties up here so that we can see them as we'll be working with them on the user form first thing what we want to do is give it a name so this is going to be our internal name so we're going to call this customer form and then we want the visible caption for that we're going to call this customer add edit customers very good so we have that now what we want to do is I want to give it a title I'm going to click anywhere on here it's going to launch that tool box if you don't have the tool boox available you can toggle it right here that's our toolbox here so what I want to do is I want to enter a title for this it's also known as a label so we're going to enter a label up here and we're going to Simply call this add or edit customers now we can change it here or we can change it in the caption or we can single click and then another click so we're going to is ADD edit customers very good so now once we have that we do want to format it ingly I'm going to text Al line we're going to set it to Center here then what I want to do is I want to go into the font and I want to increase the font so I want to update that font here cuz I want to change the size to about 12 and I want to make it bold and click okay that looks good so add or enter customers if we w