Get the Best Invoice Format in Excel Download for R&D

Invoice format in excel download for R&D

hello this is randy with excel for freelancers and  welcome to the multi-page invoices we created many   invoices before but always a single page  what if we want to create an invoice with   hundreds or maybe even thousands of items  we don't want to create any ugly invoice   so how can we create an incredible beautiful  and professional multi-page invoices   very easily in excel i'm going to  show you all that and a whole lot more   in this week's training i hope you'll  stick with us so let's get started all right thank you so much for joining us today  i've got a really fantastic training and also one   of them that's been highly requested because we've  always in the past created a single page invoice   and of course in excel it's a little bit more  complicated because if we've got a lot of items   like this how are we going to put headers  and footers on how we're going to make it   look professional we're going to take care of all  that today i want to get you a really professional   invoices take a look at this this is when it's  been printed out we've got a page number at the   bottom we've got a consistent header at the top  and we've got a professional footer at the bottom   of the last page of the invoice right i want  to something that we can design fully dynamic   so a single click it's going to print no matter  what even if we have a single automated so we can   continually adding items to our invoice  and of course when we add more items we   want those reflected we of course want our summary  reflected and just one click will be able to print   or of course if we want to create a pdf saving it  as a pdf is going to work just fine as you'll see   here in the pdf we now have our new items we've  got three pages of pdfs here and we've got a very   very professional invoices with a consistent  header on every single page and a footer with   a summary on the last page so we're going to go  over all that how to do that and that's going to   be coming up right now so i cannot wait just ask  a few things from you of course i don't ask for   too much and i try not to always i want to make  these trainings available for you each and every   tuesday so if you could go ahead and hit that  subscribe button below and don't forget that   notification icon bell that'll ensure that you  get these trainings alerted each and every week   your comments really really help because i want to  hear from each and every one of you i respond to   each and every comment every single week so that's  really going to help us and don't forget to smash   that like button all right that'll help for the  youtube algorithms i am have a huge announcement   this week and that means our 250 workbook pack  is now available that's 250 of my best templates   now available for download and that means you  can get every single one of my workbooks and   along with an incredible library that's going to  allow you a single click to open the workbook and   a single click to view the corresponding youtube  training video and that's all inside the workbook   library available if you have already purchased  the previous one perhaps the 200 to 150 i'll   include a special upgrade go ahead and email us  down below right randy excel for freelancers.com   and i'll make sure to get you an upgraded price if  you've purchased before just let us know the email   that you've purchased on before all right great  so that's it we've got that this is going to be a   fantastic training as i mentioned before we have  created invoices in the past even from scratch   right if you are new to vba right and you want  to see i'll include the link down below for that   invoice from scratch to help you out also if you  are new to vba want to learn vba from the ground   up my mentor daniel strong has an incredible vba  course it's going to teach you every aspect of vba   and that'll get you all set up for my trainings  as well here because we don't necessarily go into   the basics all the time all right so that's  daniel strong's i'll include the link down   below for that as well let's get started on this  training because i've got so much to share with   you we'll go over the basics we'll cover every  conditional formatting every format every function   and of course every formula in this training i'll  go step by step sometimes i do like to create   trainings from scratch when they get a little bit  more complex then we go ahead and take over that   and we go over every line of code and everything  that i've already done and that ensures that these   trainings are anywhere between usually 90 minutes  and two hours right they are long and of course   i try to deliver as much value as possible so i  appreciate your shares likes comments and all of   that all right so what do we have here well we've  got a basic invoice here i want to be able to   search for an invoice if i've previously created  it i want to search for that invoice if i want to   enter that invoice number i want that previously  saved invoice number to load up i want to send a   footer message a footer message can be a thank you  and i'll show you what that's going to look like   in just a moment i want to have a subtotal up at  the top that way if we add items we always have a   subtotal a tax and a total of that invoice i  want to be able to save and update that invoice   and i want to have a fade out message when i save  that so they'll let the user know that it has been   saved i want to be able to create a brand a new  invoice that clears everything out and allows us   to start entering items very very easily okay  those items also want to be able to print the   invoice out it's going to print to that default  printer and you're going to notice something come   up here and we'll go over that in just a moment  and that's going to print whatever invoice you've   created on the screen so that'll print it to the  default printer mine is snagit so whatever printer   have it if it's mold if it's a single page which  is kind of nice let's go over that take a look at   that if it's a single page it's going to show the  footer at the bottom however if it is a multi-page   that footer is going to be on the last page so  if we were to print this out as you saw before   on my printer here you can see this one's been  printed to the see the last page on this one   our page three of three if we zoom in here page  three of three the footer is gonna be on the last   page it's always gonna be the last item regardless  of how many pages are on there so be able to save   it we'll be able to delete the invoice and then  of course we're also going to be able to add a   customer so we want to add a customer we just  click on a button here we can add a customer   in okay we can create new customers right or we  can select from existing customers if we want to   load when we select from an existing customer we  want the customer's address to auto populate okay   let's take a look inside here now when  invoices are saved we have a list of   invoices i just have two invoices saved here  i'll make sure to get that total populating   but i want to make sure that we are adding saving  those and i also need the individual items for   those invoices notice we just have invoice one we  have the individual items saved here so we have   invoice item one we've got the item name so all  of these are saved for invoice id number one   here is the item name here's the item description  here's the quantity what was used and then here is   whether the amount the amount of that whether it's  been taxed or not the row that's associated with   the invoice so this is on the invoice row number  9 10 11. and what does that mean that means it's   going to correspond to row number 9 10 or 11. so  when the invoice items get loaded back we know   exactly what row to place them on also on sending  the device items we want to know the row i want to   know the database row that's associated with that  and i'll go over that exactly why that's important   because if i make a change here i don't need to  know what database row saved and that database row   is going to be corresponding to right here in  columns so that means when i have a new invoice   this is going to get all cleared out if i decide  to add an item there's no database row associated   with that because it has not been saved yet right  as soon as i save and update this it's going to   assign a specific row we need to add a customer  first let's go ahead and do that that's a good   required field saving and updating that we're  going to put rows now 111 112 and 113 for these   three different items if we look in the uh down  here on the bottom of our table here we now have   on invoice number three the one we just created  we have those three items on rows of 111   112 and 113. now the corresponding invoice rows  is 9 10 and 11 corresponding here to the rows 9   10 and 11. great and we also have the totals here  so we're going to go over that and see exactly   how we do that so invoice items are kept for every  specific invoice if we want to be able to see the   top all we need to do is just freeze that right we  can go into there and then just freeze the panes   right here and just freeze the pants okay so now  we can see we can see that as we scroll down we   can see it we also have the tax item now this is  the tax item we're saying well why is this this   u with the dots that's actually the check box so  if we were going to change this font to wingdings   we would see that it is the checkbox and we  don't necessarily need it in the that specific   font in our database but we certainly need it  in our invoice right i want to be able to select   and unselect and add tax as i wish if the item is  going to be taxed or not right and notice that the   taxed amount's going to change here right so as  we unselect add taxes or subtract those are going   to be reflected here so i'm going to show you  exactly how to do that right so we need to save   that and we need to load it if an item is going  to be taxed or not i'm going to save and update   that when i load in let's go ahead and load number  two and then back to number three again the one   that we were just working on i want to make sure  that that tax status gets saved in the database   that's why it is saved here so if we go all the  way down the bottom here we see that we marked   this item as not taxed and therefore that you with  the two dollars roomlet is not going to show up   all right so we've got that there for that and  also what we want to do is we want to make sure   that we're going to have totals we also have a  list of customers here a list of customers simply   the customers we need to have a customer name  and we want those addresses to load in when we   change the customer so it's important to keep that  customer information and also we have some items   just a list of items we have a basically item name  a description the default quantity that we want to   show up on an invoice when that item is selected  the price of it and whether the item is taxable   or not and that way if we decide to put an hourly  labor which is not taxable and i want to put that   in here and i select from the hourly labor here  and i'm going to make sure that that default is   not taxed however if we change that tax status  here then we copy this and paste that paste it   down here i guess i can add a selection change on  that when i decide i'm going to add it in again   double clicking on that it's now going to be taxed  so it's going to take on the defaults of whatever   was set up whether it is taxable or not inside  from its original list we'll go over that as well   okay so back into the invoice so we see that  we're creating these items we're adding to the   invoice and we're saving them that is it for all  the databases relatively simple remember we're   keeping track of our total all the invoices the  individual items that go into the invoices our   customers and our items and in our admin screen  just some basic information here i want to know   the status of some invoices whether it's been paid  or open that's just something that we can mark   on inside i want to know what the default status  is for example if i select here and that means   when i want to have a new invoice i want those  new invoices to be automatically marked as   unpaid so when i go into here and i say new  invoice i want this status to change as unpaid   however if i were to switch it to open here  and i go back into the invoice i click new   invoice one more time that new invoice is going  to be set to open so that is the default status   for our new invoices likewise i want to have some  payment terms right if i want to set some payment   terms based on the invoice i want those payment  terms to help me determine the due date so if i   know that we have a net 30 and it's the invoice  date was 5 18 i know it's going to be due on 6   17. now if i change that to net 15 i want that due  date automatically to set to different likewise if   i were to change the date of the invoice date and  it is based on that 15 i also want the dueon to   change so we have that as well okay great let's go  ahead and load up an invoice with a lot of items   right so we also want to make sure we load up that  so also in the admin screen we want to set that   default new invoices i want to be able to  set a default so if we have a due on receipt   and then i check new invoice i want to make sure  that that do on receipt is set here in the terms   and i also want to make sure that in due on is the  same date as the invoice day right it's all based   on the number of days the number of days we set  right so if we were to add one and we said net 90   right and we said that's going to be 90 days from  the invoice date and we decided to set that as the   default when i click new invoice it's going to  take that current date may 18th we're going to   add on three months 90 days to that and it's going  to set up to august 16th so that's how we're going   to do it for new ones okay so it's very very  flexible very easy i want to know if we're going   to be charging sales tax or not right if we decide  that we're not going to start charge sales tax   and we want to invoice it we see that our total  update that let's go ahead and put in a brand   some new invices i want to make sure that that  gets reflected automatically and let's go ahead   and make sure and update i want to make sure  that that is reflected let's take a look at that   this is called tax option this is a named range  called tax option if that's equal to no i want to   make sure that we're not showing anything  so i'm going to add that in right now if   tax option equals like that let's put equals  in this case no right what do we want to show   i just want a 0. i will zero that out otherwise  i want to sum the total rate i'm going to end the   parenthesis here okay that's exactly what i want  to see now if we were to change that back to yes   then i want that whatever tax default rate it's  called tax rate this is the named ranch called tax   rate i'm going to use that tax rate and multiply  that so now we've changed it to yes and we see   that the tax is now reflected in here if we were  to change that percentage to 5 percent we would   then see that that new total is reflected here  and that's in a formula based on here we're simply   going to sum all based on we're going to use the  sum if i want to know all of the rows in which   this is not empty meaning i column i is not  empty if we know it's empty it's not going   to be taxed if it is not empty we are going to  make sure it's taxed right if it's got that check   mark we're going to be taxing it otherwise don't  tax it and if it is checked we're going to simply   sum whatever's in column h and we're going to  multiply that times the tax rate so let's pull up   a larger one with some more items so we see that  we are now taxing everything with a check mark   based on this particular rate so that's all we  need to do it and now the subtotal above is simply   the sum of whatever is in h9 so also if you notice  that this x here went also to nothing when i   changed that to no we're going to show blank  right that's what i want to happen i want this   field to show blank so if the tax option equals no  show nothing right empty space otherwise show tax   and then what i want to do is i want to show  the tax rate but i don't just want to show   that specific tax rate i want to actually show  a formatted version of that tax rate so i want   to show it formatted based on this format here  we can wrap that around text that's going to   format the text command is going to format it it's  going to format that value and it's going to be   formatted based on the percentage so that is how  as soon as we mark that to yes that is how we get   that tax percentage and we also get it formatted  the way that we want to so we know the tax is 5   great and we know the total the amount of the  tax in this case is 988 based on the tax rate   and we have a total of 21 054 now the total is  simply the subtotal plus the tax okay this is   really important we also have a footer message  we'll be going over that in just a bit later   great but the footer message inside the admin they  come from here right so if i have a footer message   here and i've got different footer messages  i can create a footer message now the footer   message the long text form is here and i've got  some named range to help us with that so if we go   into the formulas we go into the name manager  and we take a look at something called footer   name well this is a dynamic named range based  on the three items that we have here as our   message footer messages grow so will our named  range and i've got the same thing in the footer   message except this is for the footer message  in column l as you can see it's also based on   that and as we're going to count basically based  on j which is the footer and message names we're   going to count those and we're going to set it to  k that's going to count all the ones in column k   and that way it's going to help us out  because i want that footer message displayed   inside the footer where do i want that displayed  i want to display it inside a footer let's take   a look at what that would look like inside our  footer and it's going to be right here so if   we take a look and we zoom in here we see that  we have in our printed version save 30 so our   footer message is going to go in a box then  we have our subtotal our tax and our total   inside our footer which is exactly where we want  it but of course i want to get those displayed   inside the invoice those footer messages so if we  take a look inside our invoice here and i've got   some information down here our footer message  is actually going to be displayed right here   that is going to be linked to our footer we'll  get into how it's linked but basically what i   want to do is i want the user to be able to select  whatever footer message they want and then the   footer message name and then i want that entire  footer message to show up here if we expand that   we see that our entire footer message is saved  here we don't need it expanded in this but what   we want to do is we want to look it up so what  we're going to do is we're going to index that   footer message right i don't want to run a match i  want to know what row is located on so to do that   we're going to run a match based on the footer  name in k2 and we want an exact mass we're looking   up on that footer message column that column for  the footer message if there's an n error we're   going to return nothing so what that's going to  do is use index and match to determine exactly our   footer message so our footer message is going  to go here it of course will be linked to a   shape that will allow us to show that a few  other things inside this we understand the   item database row we went over that i want to know  the row associated with the customer if the user   enters a brand new name i want to make sure that  this customer row goes to blank and we get an   opportunity to add that customer if we say yes i  want to be able to add that customer inside here   so if not i just want to select from a customer  here but i want to know the row that's associated   i have a named range for the customers if  we go into the formulas in the name manager   we take a look at the customer name we tab over  here we see again we have another dynamic named   range using offset based on all the customer names  here so i've got one so that way we can know if   the user enters one of the names i'm going to know  what's row associated with that and our first one   starts on row three so that way inside the invoice  i want to add 2 because i know the first one found   is going to be on row 3 and i want the row that's  associated with that customer to do that we're   going to run that match based on what's in e4 and  we're going to match the customer name so we want   an exact match and we're adding 2. if there's an  error we're going to show blank that way we can   run a simple test we know if this is blank the  customer has not been entered yet we also want   to know the invoice row i want to know basically  is there a row that's associated with this invoice   number if we have a new invoice invoice number  four inside h1 has not been saved yet if we take a   look inside the invoice list i've got a name range  called invoice id if we go under the formulas   name manager and invoice id we see again we have  a name off using offset a dynamic named range and   you see 4 is not in there so if it's not going to  be found i'm going to show blank let's take a look   inside that formula that formula here again if air  we're going to match we're looking whatever's in   h1 for the invoice id i'm looking for that 4  is not there it's going to create an error and   it's going to return empty space and that's how  i can differentiate between a brand new invoice   or an existing invoice if we load an existing  invoice we know that h1 1 is in there and we also   know that it's associated with row 3. we're adding  2 is the first one found we're adding 2 and that's   going to associate with row 3. great but i also  want to be able to search a row right if i'm going   to be searching here for a specific invoice i need  to know again using the same formula that match   except this time we're going to be looking in  k1 i'm looking for whatever invoice the user   enters it happens very fast but i'm going to enter  here that's going to return a result here if it   returns a correct row here the macro is going  to simply take that particular invoice number   that the user entered and it's going to place it  directly inside h1 once that's done the macro is   going to then load all the items in here then  it's going to clear out it's been cleared out   that's why this is going to return 0 because this  has been cleared out great so i also want to know   the next invoice number we have a named range  for invoices as you saw and we're going to use   the max max formula it's going to be the invoice  id plus 1. so i want to know the maximum of all   the invoice ids which is 3 plus 1 which is 4. if  there's no data all it's going to create an error   therefore we're just going to set the default  to one all right i also want to differentiate   between loading an invoice and not loading and me  in other words when i load an invoice there's some   changes to here there's some changes to here  right everything gets changed when i load it   because it's the preview whatever's been saved  so i want to differentiate between that type of   change where the where we're loading values where  i'm loading the terms or i'm loading the due date   or i'm loading the invoice date as opposed to the  user actually making a change because if the user   makes a change then i want to make sure that we  change the due on date right so when we load that   i want to make sure that everything gets changed  based on that so i need to differentiate between   changes where user makes and changes that come  from the macro in which is going to be loading   the previously saved values and we'll update that  total there inside the macro once we get to it   so i want to make sure that g populates the total  okay great so i also want to so that's why we're   going to set this so that means when this invoice  loads we're going to set this to true then we're   going to set it back to false okay so that's  it that's all the formulas here that we have   now we have some conditional formatting now  traditionally let's put in a number let's say   two okay so traditionally i want to highlight  alternate rows but in this case i want to put   a little bit of a different color on the  next row down and that prompts the user to   enter that so that the user can quickly see  that they can enter items very very easily   because they have that next available space so  it's very easy and very intuitive for the user   to then enter the items there so how are we going  to do that well that's with conditional formatting   and basically all i want to do is i want  to check the row above if the row above is   not empty then i want to color the line below so  how are we going to do that well i'm going to go   into home then we're going to go into conditional  formatting and we're going to manage those rules   we're going to see we have basically two rules  one's for even rows and ones for odd rows so   for the even rows there's two conditions right  i want to make sure that d8 keep in mind d8 is   not empty and the mod row is equal to zero meaning  it's an even row then i want to do applies to d9   now normally when we use conditional formatting  for alternate rows we want to make sure that this   row d8 and the applies to would always be the  same but in this case in particular i want to   color the line below right so that way the line  above we're checking right if it applies to nine   we're checking the above row and that means when  i have a new invoice let's let's close out of that   when i have a new invoice notice how that first  line is colored right again d d8 contains a value   so we're going to make sure that the nine that  applies to gets colored right so that's a very   very easy way that we can do that right so here's  a let's go show you another example here okay so   i want to make sure that we do that so odd and  even rows get colored based on that so it's very   very important because we're applying it to the  row below the row below so the row below is d d9   and the row above is where the formulas are so  that's how we apply it to the row below the one   that doesn't include data okay so that's it for  the conditional formatting on this portion of it   okay great so we've been over that so now  what we want to do is i want to make sure   that when we save it or update it we're going to  go over some macros here now the key thing here   is we want to make sure that when we're saving it  we want to print it out ingly so that we can   do that and we're going to get into just that in  just a moment i want to go over some of the basics   for you before we get into the print just so we  understand exactly what's going on when we save it   and we click new okay so we're just going to  go over the save and new all right now the   adding the customer portion we're also going  to go over briefly so we can do that as well   but first i'd like to demonstrate some of the  actions that you've already seen you notice   that when we change the invoice date the due date  automatically changed and it changed based on the   terms selected so if i were to change this to  12 15 we see that this is automatically going   to change so let's figure out and see exactly how  that's going to happen well that happens of course   when we make a worksheet change but the only the  kind of change when this is false right we don't   when the invoice loads when i load an invoice i  don't want to make any changes to here i only want   to bring whatever's was saved in the database so  how are we going to do that well that's going to   happen on worksheet chain so we're going to do  that inside vba developer visual basic alt f11   will get you there and we're going to focus on our  invoice right our invoice this is where the macros   happen and we're going to focus all the way at  the top on worksheet change when i make a change   to a worksheet something is going to happen right  we're going to get into customer name in just a   moment but we're going to focus right now on the  invoice date or terms but not an invoice load that   means when we make a change to the invoice date or  when we make a change to the terms but not on the   invoice load what do i mean by that on invoice  date h3 on terms h5 but not but not when b only   when b5 equals b excuse me only when b6 equals  false so those are the conditions that we want   on intersection not intersect target range h3  and h5 and that means when we make a change to   h3 or h5 directly on this invoice sheet nothing  and of course b6 must equal false this means   not on invoice load you'll see when we run the  macro just briefly just very briefly when i run   a macro called invoice load what i'm going to do  is i'm going to set b6 to true right b6 to true   and then it's going to go back to false before  it ends so we know that that's going to happen   on invoice load okay we'll go over that in just  a moment so we want to make sure that b6 is false   so this doesn't nothing happens when the invoice  is loading the changes that are made to h3 and h5   won't happen here when it's loading only when it's  not loading okay first of all i want to make sure   that we have to have values if we are going to  calculate the due on date which is exactly what   i want to calculate i need to make sure that  we have two things we must have an invoice date   and we must have terms if either one of those  are empty then we cannot calculate the due on   date so we're going to check for both of those  required fields if h3 value equals empty or h5   value because empty then we're going to exit this  up both of those have to be filled in order for us   to properly calculate that due date okay so we're  going to mention the payment term as a string and   the payment row as long right i need to calculate  those that payment term is going to be whatever's   in h5 and then what i'm going to do is i'm going  to determine the payment row i need to know   what row that payment term is located on if  i take a look at all the payment terms here   all the way from f5 through through f23  or using our named range payment terms   i want to know what rows it on is that row 6 7 8  9 or 10 right i want to know what row when i know   what row is on i can determine the number of days  because that's in column g and whatever row it is   so we need to extract that so how are we going  to do that well with the payment row is going to   be equal to the admin right range terms we have  a named range called terms right just to double   check to make sure we understand formulas name  manager going into something called terms again   this is a basically another dynamic named range  based on the offset formula so i'm looking inside   that named range you see the dancing answer on  i'm looking for that term using the find command   what i'm going to do is i'm going to find that  payment term using find i want excel values and   excel hole and i want to extract the row now  if it's not found it would return the bug so i   certainly want to wrap that in on air resume next  and on error go to zero then i simply have to test   to see if it was found if it wasn't found payment  row is going to be zero so we can run a check here   if the payment row does not equal zero then i want  to calculate that invoice due date it's located in   h6 that invoice due date is right here inside h6  so that's going to be equal to what it's going   to be equal to whatever's in h4 plus whatever's  in the row and column g wherever it's found it's   going to be right here so again it's going to be  h3 our invoice date plus column g and the payment   row the payment has already been determined  that's going to set our due date automatically   based on that very good so we see how that we  can make a change to either one of these and   it's automatically going to set the due date based  on the number it's found okay cool we saw another   worksheet change where we make a change to the  customer and automatically the address the city   the zip automatically adds so how are we going  to do that right well it's going to come directly   from here it's going to come directly from here  we need to make a quick update on that oh if it's   available and what i want to do is i want to show  the address i want to show the city state and zip   and i want to bring it all into here so how are we  going to do that in fact i think it's missing the   state we're going to fix that up i don't think  it's showing all the values there it is okay so we   do have it there on this to make sure that we're  showing everyone all right good yeah that looks   correct so how do we do that well that's course  going to be on change of e4 now in this case in   this particular case regardless of whether we are  loading an invoice i want that address to load up   regardless of whether we're loading an invoice  or whether users actually making a change so it's   not going to be effective it's going to happen  either when we load the invoice or when they   make user makes a change so that's why we don't  necessarily need in this case when b6 is false   so what kind of change is that well that's  going to be a change to e4 when any change   to e4 happens that's when i want to check for it  okay so that's going to be all the way up here   on customer name change e4 right and i want  to make sure e4 is not empty right if it's   empty we're going to do something else right so if  we're first we're going to dimension the customer   row remember we need to have that customer row  located in b2 if b2 is empty we know it's a new   customer so if b2 does not equal empty that  means it's an existing customer we're going   to determine that customer role based on that b 2  value that is our customer row okay and that's the   row that's been saved inside the customer that is  the row that's associated directly right here so   if i know the row then i can extract the address  in column c and then d e and f are going to take   on both the city state and the zip code so once  i know that row i can bring it up here so once i   know the row e5 is going to take on the address  which is in column c we know that e6 is going   to take on again the city here in column d the  state here in column e and the zip code in column   f it's going to take all that on okay that's  all we need to do and we're going to combine   all of that into e6 except we're going to add  a spacer between the city and the zip and we're   going to add a comma and a space between the city  and the zip and of course the address here and the   city actually a city and the state takes on a  spacer while the state and the zip takes on a   comma and the space okay but what if the customer  is not found if the customer is not found we want   something else to happen right if i make a change  here and i want to put in a jackie right and i   want to put in the customer customer is not found  would you like to add them right so something   different is going to happen if i click yes i want  that customer name to be launched in our user form   so how do we do that well here else customer  not found we're going to clear out any addresses   that means i want to clear out both whatever is  located in e5 and e6 i want to clear that out   next up what i want to do is i want to create  a message box and then we're going to use it   in as an option box so if this customer  does not exist would you like to add them   if vb customer not found if it's yes then what  we're going to do is we're going to launch our   ad customer form this is our user form that we've  already created it's called add customer form and   i have a field here and that field is called  field one so i want to take whatever the value   is whatever the user has entered directly inside  e4 and i want to place that directly inside our   user userform as a value that gives us the default  name to start with so how are we going to do that   we're going to do that right here so add customer  form field 1 value equals whatever is in e4 this   is going to set that default customer name then  i want to display that form using this so this   is going to display that customer form display  customer user form great so we have that there   and that's all we need to do so great so what  about on search change right we've covered on   customer name change we've covered on invoice  data or term change but we haven't covered on   invoice number change and remember that's going to  be the one when the user actually makes a search   right when i search for the invoice that's  a change to k1 when something happens   what i want to do i want to check to see if  b4 contains a value if it does contain a row   then what i want to do is i want to take whatever  they've entered i want to put it inside h1 and   then i want to run a macro to load it so that's  just what we're going to do on any change of   k1 and i want to make sure k1 is not empty then  i'm going to check b4 if they enter an incorrect   invoice if i enter incorrect here i want to be  able to say please make sure to enter a correct   invoice number that would be for an incorrect  invoice number so that would mean b4 would be   empty on an incorrect invoice number i want to let  these you know please make sure to enter a correct   invoice number and then we're going to exit the  sub right otherwise right if it is correct then   what we're going to do is we're going to take  whatever invoice number in k1 the user entered   we're going to place it directly in h1 and then we  can clear the contents of k1 now keep this in mind   we must have this when i make a change to k1 right  and we're already inside a loop if i don't include   this this is going to create a loop and it's going  to crash your system that's what happens sometimes   you get crashes you don't know why i can create a  crash right now if i why is that going to happen   because one we've made a change here to k1 it's  going to say oh you made a change to k1 i'm going   to continue on it's going to continue keep looping  or looping and looping around but as long as we   say k1 does not equal empty here we're making k1  empty but it's only going to go into this loop   if k1 is not equal entry so we can do that right  so keep in mind that's a very easy way to create   system crashes usually when you have a crash right  for no reason is because of some type of loop like   on selection change or some type of loop on  here worksheet change because we've created   a loop so that can happen a loop that you can't  get out of so keep that in mind and then i want   to run the macro which we're going to go over in  just a moment called invoice load so once we know   we've got a correct invoice number we can then run  that mac or invoice load okay great so we've done   a little bit now let's continue all this on item  name change but not on voice load now there's the   last thing when i make a change let's pull up an  invoice that we can when i make a change here to d   all right anything called d9 all the way on down  and i want to make sure to load in those items or   to kind of change if i decide i'm going to delete  it i want to make sure to clear that i'm using the   delete key so there's going to be this type of  change so this type of change but i also again i   only want to make this kind of change i only  want to put it example if i put in two here i   want to make sure that that gets saved right the  type of change there's two types of changes one   when the user is entering items and another type  when we're actually loading that so we need to   differentiate between those two types of changes  so again i really only want to focus on the type   of change when the user is actually making a  physical entry of adding or removing an item   and we know that difference because we know that  b6 is going to be false then we can load the items   so that's what we're going to do if the user makes  a change anywhere from d9 all the way to d999   and b6 equals false meaning the invoice is not  being loaded then we want to continue i'm going to   turn off application screen updating faults just  temporarily to make things a little bit quicker   although it may not be necessary but  i'm going to show you a few things that   help speed up your application all right i also  want to make sure that d if d in the targe rope   meaning the target the value that the user just  entered if it's empty we're going to do something   else if it's not empty we're going to do something  so this is what we're going to do if it's not   empty we're going to dimension the item row is  long i need to know what row is associated if i   enter a specific item here i need to know what  row that silicone caulk is on i need to know   that it is row 6. i need to be founded because  then i need to i need to find it because i need   to make sure that we're going to enter that item  description i need to enter the default quantity   and i need to enter the price that's associated  with it as well as if it's taxable or not and i   need to make sure that all that gets entered here  into the description quantity amount i also need   to enter a formula right notice the formulas are  only in lines that contain values that's what i   want i want to speed the application i don't want  to put in formulas where they're not needed and   the taxable status or it's not taxable i don't  want to be able to make it untaxable or taxable   just by selecting on it great so how are we going  to do that well let's continue with the code   right so i need to determine the item row so  again we're going to use the find command i've got   named range called item name this is an offset  named range right inside our formulas here   we'll take a look at this this one even if  we didn't do it before named range item name   here it's just basically a dynamic name based  on our item name so let's take a look in here   and tab it over and we see that it's all of our  item names based on that item all right so what   we're going to do is i'm going to look inside  this named range and i want to find that item   name and i want to extract the row from it if it's  found right if it's not found it could create an   error so we need to run a test that's why we're  wrapping anytime we use find we really want to   make sure do we wrap it in on air resume next and  on air go zero and then we just need to run a test   if the item row does not equal zero that means  the item has been found been found okay if it's   been found then we can do the following right  all we need to do is take the item description   from column b in the items database here and  then place it in column e we're going to take   the default quantity from column c in the  items and place it in f likewise the the cost   is going to go in g then we're going to put in  that formula i want that formula here i don't   want to place that formula right i want to be able  to clear everything out including the formulas and   if i decide to load it right i want the formulas  to go back in here or if i decide to add an item   there's no formula in here if i add an item i want  only then do i want that formula to appear because   i don't like formulas that we're not using so what  i want to do is i want to place that formula using   vba so we can do that here h in the target or  dot formula could be dot value as well it's fine   equals and then we can see equals f and the target  row times g in the target row and that's simply   going to enter this formula here f12 times g12  that's it that's all we have to do to enter that   formula and that way we have live calculations if  you decide they're going to change the quantity   we know that that total is going to update  automatically and then last line of code we just   want to make sure column i takes on the taxable  status and that's going to come directly from   column e inside the code so i in the target row is  going to take on whatever's in e and the item row   taxable status okay all that is if it is not empty  but what if the user is empty when it is empty the   user will simply just be deleting an item what  do we want to have in that well in that case i   want to make sure that we're deleting everything  in that line item okay so to do that we can do   else here deleted item clear the row so e through  i we're going to clear the contents out i also   want to know if the database row exists right if i  decide to delete this i need to make sure that we   are going to delete it inside the database notice  it was 110 right i want to make sure to delete it   inside the database as well so we save and update  it and we decide to load invoice number two i want   to make sure that that item doesn't load anymore  right notice it didn't load so we need to make   sure to clear it also in the database as well  so to do that we're going to check i'm going   to dimension the invoice item database as long  database as long and that we're going to take   whatever's in b is going to take on that variable  that is our database row our invoice item database   row okay so now we got that so what i want to do  is i want to delete that inside the items invoice   items database we're going to delete that entire  row using invoice item database and the colon   and entire row delete so that's going to delete  the database row i also want to make sure that   we're clearing out whatever's was in b clearing  out there so we're clearing out that row what's   in it here then it's also deleted from the  database okay very good and we're going to turn   on application screen updating true to make sure  we've turned it off just to make sure things are   faster okay great so that's it for the worksheet  change worksheet change event so we've covered   also on customer name change on invoice search  change on an invoice date or terms change and on   item name change so all that happens however we  saw one more thing inside on the sheet and that   was when we make an item taxable or non-taxable we  want to make that selection so how are we going to   do that well that's based on selection change on  a selection change in fact i may want to do one   more thing notice if i want to make it taxable and  untaxable i've had to select something else so we   can do that inside the code as well i'm going to  add that in so when we make a selection change to   i 9 through i99 and i want to make sure that  d doesn't contain empty right that means   if i select something here nothing's going to  happen because d column d is empty right only on   those items where column d contains a value that's  what i want something to happen right if the   target value equals empty then i want to add in  that check mark that check mark is character 252   inside the wingdings font how do we know that if  i select here and i want to insert a symbol here   and i want to click symbol we see that the check  mark here this particular one is character code   252 whoops here it is character 252 right so we  know the character code inside vba that's going   to enter that character right there so then the  target value equals 252 else the target is clearly   contents and that means if we select on something  that has a value already we're going to clear it   if we select on nothing clear it now notice  that i had to select away and select back   again that's kind of annoying but what we can do  to get rid of that is select another cell using   vba and i'll select d in the target row so all  we have to do is just add in one line of code   range d and the target dot row dot select okay  so that's going to select something else now i   can select the same cell twice notice i can select  as many times we want because d gets automatically   selected okay so that's kind of an easy way to  quickly select and unselect so if i select here   select and select so i can keep doing the same  cell again and again okay great so that's all that   happens on the selection change event that's the  only code everything else is right okay great so   let's take a look we saw a few macros now i've got  two modules here one we're going to focus on the   invoice macros and that's going to be invoice  save invoice load invoice new and the other is   associated with the print macros okay so let's go  over the invoice macros where we're going to save   them now it's up here now i've dimensioned some  variables up here and we're going to go over them   as we go over inside the invoice the first thing i  have to do here i'm going to go over this in just   a little bit i'm going to skip this for now right  because i want to explain why we're doing that   it's going to go into a little detail as we create  them so basically we're removing some shapes here   and i'll go over why we're doing that because  we're going to do this this exact coat a few   times so i can go over it again okay so the first  thing is what the second thing is first thing is   removing these shapes and we'll go over why e5 is  empty right i need to know if we're going to save   or update this particular macro is the same exact  macro that's applied to this button this is the   macro that's going to run whether we're saving  a brand new invoice or whether we're updating   an existing invoice so we're going to run that  our fade out message is going to happen and it's   going to automatically save that invoice so it's  also going to update it right but what i need to   determine is is it a new invoice or is it an  existing invoice and we can look here inside   the invoice row of b3 that's going to let us know  whether it is new or existing so if it is a new   invoice we know that b3 is going to be empty right  if it's an existing invoice we know that b3 is   going to contain a value so b3 is going to let us  know if an e5 equals empty please make sure to add   a customer certainly we need to know we need to  make sure that we actually have a customer without   a customer in e5 we cannot save the invoice i want  to determine the last item row and i also want to   make sure that they've entered items if i try to  add a new invoice even if i've added a customer   if i haven't had any items and i try to save it  i want to let the user know to please make sure   to add at least one item before saving so all we  need to do is just check the last row the value if   it's less than 9 we know they have not added any  numbers so that's going to happen right directly   inside our code we know that e9 if the last item  row is less than 9 then we're going to check that   last item row here using range and excel up row  this will determine the last item row of column d   if it's less than nine we know that there are no  items okay so then we can just exit out of the sub   after we give them a message box next what i'm  going to do is i'm going to turn application   calculation to manual and i'm going to turn screen  updating to false this will make things a little   bit faster especially we're dealing hundreds or  maybe even thousands of items we want things to   happen fast so we're going to turn those off if  you haven't seen me use it before sometimes i   do that when we work with a lot of data great so  what i want to do now is what i was mentioning to   you before is i need to determine whether it is an  existing invoice or a new invoice b3 is going to   tell us that we know that if b3 is empty it is a  brand new invoice so there's a few things that we   need to do when it's new invoice i want to make  sure that the next invoice number that's going   to get placed directly inside here and i also  want to make sure that it's going to find the   first available role and place that next invoice  right here so we can do that through here invoice   row is equal to invoice list it's going to get the  first available row we're going to take our next   invoice number and place it directly in h1 it's  coming from b5 i also want to take that exact same   next invoice number and place it directly in  column a okay but what if it's an existing invoice   if it's an existing invoice i want to place it now  we just want to extract the row from invoice okay   everything else is relatively simple we'll move  a little bit quicker on this so we can get to the   print macros i want to make sure that the date  the customer the status the terms the due date   and the invoice total all six of these variables  simply make it into the appropriate place here the   date the customer status the term two and the  total notice the total didn't calculate we're   going to fix that right now we know that our total  is coming directly from here inside k5 so i want   to make sure that that happens right so k5 not  this right we're going to put that inverse total   k5 okay good now we've got the right one  now when we go ahead and save it let's do   let's just go ahead and update that so we're  going to save this one now i'm going to enter   number two we're going to save this one and now  we're going to enter invoice number three and then   we're going to save and update it we go into the  invoice list and we see that our totals are now   inside here properly okay now that we have the  right cell okay very very good so we make sure   that those are appropriate now all we need to  do is update the items right we've updated all   the main information now i need to make sure that  those items get saved i need to run check though   i need to know if the item was previously saved  inside this database or not how will we know that   well we're going to look at column b we're going  to determine the last row which we've already done   and i'm going to loop from 9 all the way to the  last row i'm going to check if b contains a value   we know it's been previously saved if it is not if  it is a new one and it's empty we know it has not   been previously saved we need to assign a brand  new database for that so we're going to do that   here here's our loop for the item row equals 9  to the last item row we'll get the first thing   we're going to do is going to check b in the item  row if it does not equal empty then the database   rows exist we're simply going to take that  database row and put it into a variable called   invoice item row however if it if b in the item  row is blank we know it's a new database item row   we're going to take that invoice out of row we're  going to extract that from the first available row   on the invoice item so our last row of the value  plus 1 is going to be our first available row   then what we're going to be doing is we're  going to grab that invoice id i need to put   that in column a why is that important because our  invoice items must have an invoice id and that's   got to go in column a so we're going to put that  directly inside column a that's only going to be   for new items also i want to know the row that's  associated with that i want to know inside what   is the invoice row right that's got to go remember  it starts at 9 so 9 10 or 11 right i want to put   that row that and i also want to put the database  row what is the row of this database that's going   to help us when i bring the values in so that's  going to put a simply put a formula we're using a   formula we're not using the exact row and that way  if i delete an item that form everything is going   to update ingly so we also want to take that  formula and put that in here and that's going to   be here add row formula i also want to take that  database row and i want to place it directly   inside b because this is the new item b is empty  right i need to place that database row inside b   then all i need to do is just bring over all the  information so then inside the invoice i need to   take whatever's information's all the way here  including the item the description the quantity   and the amount and i simply need to bring it  inside here item description quantity and amount   and then of course the taxable status as well  that's going to be for all the items regardless   if it is a new or an existing item we're going  to do the following i'm going to take b simply   going b through e of our invoice items can equal d  through g this is simply our information b through   d b through e is our item name description amount  it's going to come directly from our invoice here   d through g d through g so that's where we got it  from so now that we understand that we've gotten   that through there then all we need to do is  update that taxable that's going to come directly   from column i all right our taxable it's going  to go into column f so it's coming from column   i here and it's going directly inside our column  f right here it doesn't necessarily need to be   formatted in wingding's font that doesn't matter  that's it that's all we need to do to save and   update the information we're going to turn back  on application screen updating to true and we're   going to turn the calculations to automatic we're  running a fade out message now fade out message is   simply that shape that you saw in our invoice  when i save an update we see that there's a   shape that shape is has a specific name and that  name is called invoice saved message right what   i want to do is just basically make this fade  out you've seen this before all we're doing is   running a loop and we're changing the fade right  as we run the loop and until we decide to make it   invisible that's it that's all we need to let the  user know to make it okay great adding the new   customer there's a brand new button called adding  a new customer if i want to add a new customer i   can do just that all we're going to be doing is  simply adding in the name and then clicking save   and that's it now when we click save what happens  right that's going to be on here on the add   new customer if we view the code here we click  save button right then we're going to basically   saving that customer associated there so all we  need to know is the custome