Create Your Excel Invoice for Customer Service Easily and Efficiently

Excel invoice for Customer Service

hello this is Randy with Excel for Freelancers  and welcome to the dynamic invoice designer in   this week's training I'm going to show you how  to create one of the best invoices I have ever   designed in Excel fully customizable fully Dynamic  this incredible template includes one-click toggle   edit mode one click show and hide Fields one  click show and hide columns one click add and edit   customers one click date picker pop-up multi-page  support with invoice vagination Dynamic terms   with automated due date and a whole lot more  I cannot wait so let's get started thank you all right thanks so much Jeremy I've got a really  incredible training now we've done invoices before   but nothing like this this fully Dynamic invoice  we can use multiple pages so that means that our   invoices here can have multiple Pages page one  of three we can then Loop through the pages of a   single invoices and of course print all of those  pages we're going to show you that we'll be able   to add new customers create or edit customers  through this little pop-up we're going to be   able to edit these invoices so if we want to  edit some of the invoice details maybe we want   to hide some of the invoice details or show them  we can do that here so if we want to make changes   to anything we can do that here we want to make  a change to the label all we need to do is just   change it here and it's going to automatically  change up here so we've caught that to show you   if I want to hide or show columns if I don't want  to date call my main voice and maybe I don't want   to tax column I just unselect here and it's going  to automatically adjust the weapon you may just   have to move a few things over if you want but  it's very very Dynamic quickly and easily create   customizable invoices all automatically and very  easy with just a few clicks we can turn off this   edit mode and then all those features available  are gone through conditional formatting I'm going   to show you how to do that we have a dynamic  footer message now this footer message is going   to be at the bottom of here we notice we showed  you what page that we're on you've got the footer   message at the bottom total subtotal and tax and  invoice and everything is in there automatically   we will be able to navigate through additional  invoices if we have or we can also enter an   invoice number here and it's automatically going  to go to that invoices we can use previous and   next if we're at the last it's going to tell us  and if we're at the beginning it's also going   to tell us whether we're on the first one so we  can do that so there's so many features in this   invoice so I'm going to take you through every  single step of it so you're going to be able   to create your own invoices on completion of this  and you're going to know all about formulas we're   going to learn about functions we're going to  learn about shapes we're going to learn about   conditional formatting VBA behind it we're going  to use everything so not only will you be able to   create amazing invoices you're going to be able  to learn the foundation of VBA Beyond this so I'm   going to take you through every step also if we  want to add a new page all we need to do is Click   add page it's going to add a brand new page where  we'll be able to add different items to that brand   new page of the invoice so a lot of things let me  just save that now you see this is Page four or   four so we've got page three page two page one  so paginations is a great way great something   to add to those invoice something that we have  not had before we've always been limited to one   and we also did do a multi-page but it went all  the way down here so I think this is a much much   better so we have a very fixed very specific  field that we can set that range is going to   be set to just that and then we can just open it  so it's it's a really nice idea and I'm going to   show you how you can do that and you can use this  for any type whether it's proposals estimates bids   invoices you're going to be able to apply this to  any type of form so I'll show you all the ins and   outs of how that is done all right so let's get  into it right now the first thing that I want to   show you is this a customization field so within  this particular edit mode once it's turned on we   can edit the invoice details so we have all the  items of invoices that we may want to include or   exclude on an invoices if we don't want page  number let's say page numbers down here if I   don't want that I'm going to unselect it it's now  going to be gone notice it is gone so and also if   I want to add a label I could notice that invoice  total is and I've got to extend that a little bit   invoice it says invoice I want to make sure that  that's invoice total it is grouped so maybe I want   to extend that a little bit make sure that the  width is about 1.3 so we can do do that now I'll   just bring this over so we see that now we can add  this into this invoice total it's overlapping now   but as I bring this out you see we've got invoice  total here already set so this is the total tax so   we've got a lot of flexibility with shapes that we  can add into that very very handily so we can show   or hide the invoice totals maybe if we don't want  tax we can unselect the total tax and you see how   now that's going to be you have a subtotal and we  have an invoice total but we do not have a tax so   very quickly we can customize this type of an  invoice very very easily all right so showing   hi if we want to decide I want to change the label  maybe I just want to put tax on that I don't want   to put total tax all I need to do is just change  it and then it's going to say tax and then of   course I got to select it to display it and then  that tax is displayed so it's very very easy to   do that so we've got all of the information here  so we can show our high company logo all these are   the invoice details here changing the labels here  or whether we want to show or hide so notice we've   got status here is built if I do outside I don't  want to hide it I don't want to show it I can   just select here and it is hidden automatically on  single selection so we've got here this four tab   horizontal tab so we've got actually a vertical  tab here showing a four vertical tabs so this   information we're using the same cells notice the  columns are not changing the rows are not changes   so we're using the same cells so we also want to  show or hide company details notice we don't we   have cut company we have all of our company and  information we don't have labels here which is   fine we don't want a label but if I decide I do  not want to show a logo I can unclick this it's   going to hide that logo if I'm going to display  the logo it's going to show here of course and   then the company emails the same thing with every  field we have customer details now notice I've got   a label here that says customer maybe I want to  change that to client I can do so just like that   and it's going to automatically change to client  here if I decide I want to show the phone number   or I want to show the email or hide the email  email I can do that through here so showing and   hiding it simply about selection and then with the  same thing for columns so we may want to show or   hide individual columns if we want to show that  tax column we can also notice how the width of   this the width of the entire invoice here does not  change so it's automatically adjusted here based   on that that's really a nice feature because  we know that we don't have to change our print   settings when we're adding removing columns  so it's going to be very helpful to do that   so if maybe I do not want to have a quantity  I can just unclick here and the width of that   invoice stays the same it's really an email for  print I'm going to show you how we did that all   right so we have all this customer's invoice we  have it encompassed in this little toggle button   here pretty easy I'll show you how we do that  okay the rest of the workbook relatively simple   inside the admin we've got a few settings I've  got a company information so when we change this   company information it is automatically linked  to this invoice so here it says freder's service   if I were to changes to Frederick's construction  or something say hamburgers and then we can see   that it's automatically going to be changed on the  invoice so it's automatically linked which is nice   so simply changing that inside the admin company  information can do that so we'll change back to   Frederick's Service as our logo we got an address  so all these fields are linked to the invoice   including email logo including this logo we've got  a little browse for logo it's going to allow us to   browse for any logo we want just here so it'll put  the file path of that entire logo right here in   the cell so we know it knows where to look it up  we've got an invoice status so these are all the   statuses that are set we have invoices we can then  change the status based on that drop down list so   based on that we've got a named range called  status that's going to be covered there so we   also want to default open and what does that mean  that means when I select the brand new invoice it   is that status that's going to be set if I were  to change that status to unpaid wanted to do new   invoices it is that status that's going to be  changed there so automatically that default and   it's the same thing for terms notice the term says  net 30 on new invoices if I were to change it to   net 15 and click here we could see when we create  a brand new that's going to be changing that fee   net 15. that due date here 113 is automatically  based on the status which is excuse me the terms   which is net 15 and the due date so that means  if I were to change that to let's say the 31st   that due date would automatically update also  if I were to change that the terms here to Net   30 that due date would also automatically update  I'll show you how to do that so those terms going   to come directly from the admin so we have the  term name and then we have the number of days   that we're going to be adding so we add that to  the invoice date to get the due date we also want   to set the default on that so we could do that  all right we've got some invoice options and   details this we actually don't need I've I've  created this this is kind of just a summary of   all the invoice options the details it's more  information I'll show you I've spread that out   just to show you what invoices are options we've  got footer messages as you saw here inside the   invoice we can change the message that's going  to appear at the bottom there's no footer message   that's selected here however as soon as I select a  footer message here we can see that it's going to   appear here so that's automatically linked that  footer message so we can change that any way we   want so we've got a list of those coming directly  from there I've got invoicing tax whether we're   charging tax or not if that changes to no when we  go back to the invoice it's automatically there's   going to be no sales tax so if we let's go to the  previous here we can see that now there's no tax   calculations on here because it's automatically  calculated based on the tax if I were to change   the tax rate so with the calculations that's it  for the admin screen okay so all is we've got a   list of invoices every invoice is stored here  the invoice ID the date the customer the term   status due date footer message total and the total  number of pages very important for our pagination   the total number of pages invoice number one has  four pages invoice number five has two pages and   the rest have one I also want to know what items  are associated now we see we've got in this   invoice number five here we've got two pages on  page one we have these five items and on page two   we have uh also we have six items we see that this  invoice has two different pages so we also want to   keep track of all that and we want to know which  items on which pages so that's going to be stored   inside invoice items so this table is gonna handle  that first thing we have is the invoice ID we have   the page number I want to know what page is stored  on if there's a date the item name the description   the quantity the amount total whether it's taxed  or not invoice road now this is the associated   row on the invoice 10 12 11. what does that mean  that means this row here notice this is row 10 11   12 and solar and so forth so I want to know what  row is stored if I put a brand new item on here I   need to know that it's on row 15 and that way as  soon as I save that and I go back to the previous   page or let's say go to the next page and then  back I need to make sure that we come back on the   correct we need to make sure that this comes back  to the correct row that we originally saved it on   so that's very important we need to know what row  and therefore we're saving that row I also need   to know what database row this is the current  row it's on notices four five and six and this   is just populated with a formula row okay when I  do run an advanced filter when I need to know all   the items on a specific page I want to know all  the items on a very specific page for a invoice   ID right so we need to run an advanced filter to  do that for example on invoice ID number five on   page number one these are the items that are going  to appear so we're going to do an advanced filter   and this is our criteria both invoice ID and page  one so we need to have both of these because not   only do I need to know what invoice I need to know  what page they lay on and that's going to tell us   what page so we can then load these items in as  we Loop through different pages this will change   and I'll show you that right now I've also got  a list of customers here basically customer   information ID name address so on and so forth and  we have also some items so this is our items when   we pull those items up when I create brand new  invoices those items need to come from somewhere   and the information Associated along with the  quantity the default Mount that is going to   come from here so we have the default quantity  the price and whether it's taxable or not this   calendar pop-up is just simply for the calendar  that we're using will not be going into this this   is a backup shape in case the one gets deleted  this one will be automatically replaced in there   so that's pretty much a good summary of what  we've got going on here so let's go into some   of the VBA and see exactly how this is going to  work so that we can associate that walk you step   by step through everything so you're not going to  miss anything feel free to of course replay these   videos watch them again slow the speed down if I'm  moving too fast I do appreciate your feedback make   sure you leave a comment below like this video  give it a thumbs up that'll help me out pretty   appreciate that all right so here's what we're  going to do why don't we focus on some of the   basic selection changes in other words when I make  a selection change or worksheet change something   happens a selection change is this when I make  that change I want something to happen so let's   take a look inside the code we're going to go over  some of the changes that happen on this sheet when   I make changes to the sheet I want things to  happen so we're going to go item by item and   see how it works we're gonna go into the developer  Visual Basic alt f11 will get you there and what   we're going to do is we're going to click on this  invoice I'm going to go to this sheet invoice this   is where everything's happening and we're going to  start up at the top and work our way all the way   down in order so it makes it easier for you we're  going to Dimension the item type row and I have a   specific item type I need to know what row it's on  and I'll go over that item column item Rose long   and the term row as long term row I need to know  what row that terms on so we're going to need to   know obviously if we set a default I need to know  what the status row is what the term row is so   we've got those rows where the default is that's  why those two are important okay the first thing   we're going to want to do is this is going to  be on worksheet change when I make a change to   a worksheet I want something to happen what kind  of change are we going to be focused on here first   of all I wanted to mention the item database Rose  long the inventory item row or invoice item row   and also the selected row and that if the user  makes a change to more than one cell at one time   we're going to exit the sub we're only focused on  a single trip sometimes if we have a merge cell we   might need to increase this okay on invoice search  what does that mean if I on invoice search this   particular cell F2 here's what I want to happen  when I put in an invoice number here I want that   invoice to load up if they put in a incorrect  number I want to let them know please select the   correct invoice so that's the first thing that's  the change event when the user makes a change to   F2 and the change is not nothing means there's  not blank then I want something to happen so   here's what it's going to be if not intersection  Target the range is F2 when they make a change to   have to and I also want to make sure that F2  is not empty as long as those two conditions   are true I want something to happen the first of  all I want to make sure to see if B2 is true now   let's go ahead and go over some of the Hidden  columns so we can understand things it's only a   few things here I want to know when the invoice is  loading what does that mean by invoice loading as   soon as I load an invoice this is going to go to  true and it's going to go back to false and I'll   explain that a little bit later on but basically  when changes happen here changes happen here   notice that if I'm changing something here that's  one type of a change the user made a change and I   want to load the information up another type of  change is when a VBA loads if I hit enter 2 here   that's another type of a change I don't want to  automate load I want to load what's coming from   the database what's coming from invoice items  here what was saved not necessarily the item   information here so there's two different types  of changes I need to differentiate those two types   of changes by this and we'll go over that here  the search row now this is the row I've got some   named ranges so this is going to help us out here  if the user makes a change here I want to look up   what number it was found if that invoice was found  then I want to load it up so how do I do that well   I can use a named range so inside our invoice list  here I've got a named range for the invoice ID and   it's called invoice underscore ID if we look in  the formulas name manager we're going to see our   name manager I've got a few names we'll be going  over this this one's called invoice ID of course   it's offset formula and it's going to Encompass  all the invoice IDs so that will I've also have   a few others so that's the one I want to focus on  now so what I want to do is if the user puts in   invoice number one I want to return row number  three I want to know what row that is on so to   do that I need to run a match so we're going to  match whatever the user has put in F2 and we're   going to look it up in invoice ID and I'm going to  add two and the reason I'm adding two is because   it starts on Row three so so to do that it's very  important we're going to wrap it on if error if   it's not found I want to return blank I don't  want an error in this cell that way I can test   if it's blank that means the user has entered an  incorrect number and I could let the user know   so that's the first thing we're going to do if B2  equals empty this is an incorrect invoice number   let the user know with the message box please  select it correct invoice number we're going   to exit the sub so this is when they've entered an  incorrect invoice number so I'm going to type that   in here in a comment incorrect invoice number  okay but what if they have a correct invoice   number that means that B2 is not blank then what  do I want to do well then what I want to do is I   want to take whatever invoice number they have  entered here and I'm going to put it directly   inside here B4 B4 keeps track of whatever invoice  number has been selected notice this particular   is all it's going to be linked so I'll show you  that but it's this is our invoice number so as I   do previous you'll see that the number changes as  I do now next you'll see that the invoice numbers   change so I want to put that in there and then  what I want to do is I want to calculate the row   that's associated with the invoice number here  and as long as this isn't blank we're good to   go so B4 B4 is going to take on whatever the user  has put in F2 I want to clear whatever the user   has put in we're clearing that that's why this is  empty they've cleared the search we it's a correct   invoice number so we can clear that out and then  we're going to run a macro which will be going a   little bit later called invoice load this macro  is going to do all the heavy lifting and loading   the information inside so when I enter one it  is that macro that's going to load all of the   information details here all the items Associated  here that's on invoice search now we have another   one also as I explained to you on item change if  I make a change here in column K I want the item   information to load up so if we were to let's say  go to the next page Here Blake what do I want to   happen when I saw select an item from this list  I want to look up the information here inside our   items list I want it found and I wanted to return  the description the default quantity and the price   and I want to put them directly inside here the  description the quantity and the amount then the   total here is automated automatically added with  a formula so that's what we're going to do there   if the user makes a change between K10 and k44  and I also want to make sure B1 is false very   important that means the type of change that the  user has changed when B1 is true that means the   invoice is loading remember there's a change if  we take a look at here oh let's go to the next   one if we take a look here we see that the invoice  is loading here there's nothing on invoice here so   we can pull it up here this one so this one has  that information here so this type of change is   something different so I want to make sure that  we can understand that great so I want to focus   on that now that we have that so what do we want  to do first of all I want to make sure that if   B5 is empty I I want to let the user know it's  very important in this type of application when I   have a brand new invoice if I've tried to enter an  item I want to make sure that the user saves that   invoice first so if they've tried to make a change  or adding an item I want to let them know with a   box please make sure to save the invoice before  adding that up now how do we know it's not safe   and the reason is because we've got an invoice  number here six but that invoice number does not   exist on this list notice the last one is five  so what that's going to do is I've got a formula   that's going to let us know the row it's the  same formula that we used up here but just for a   different area so basically it's this match farmer  we're looking in B4 which is now six we're looking   through the invoice IDs it only goes up to five  and we're adding two if it's found if it's not   found it's going to create an error so therefore  we want blank 6 is not found yet so I know that   this invoice has not been saved yet so what I  want the user to do is save the invoice first   and I want them to put a customer in here and I  want them to then save as soon as it's saved I   bro is then secured here now we see in the invoice  list six is saved and it's on row eight so now I   know that we can add the item I want to prompt  them to first save it before we add any items   and the reason that we want to do that is because  as soon as I add an item I want it saved to the   database and that's very important here because I  want to make sure that if they're adding that it   gets saved automatically to the database so notice  this bathroom vanity on invoice number six on row   10 that automatically got saved and that's very  important if we look in the invoice items at the   last one we see here the item that I just added  invoice ID number six page number one bathroom   vanity here it got added in all the way if I were  to add a date it'd be added right here if we take   a look at this and I add I decide to add a date  that date's gonna automatically be added here so I   want to make sure it's updated as soon as there's  changes I want to make sure to updated so that's   how we're going to do it but I want to make sure  that we find that item first so when they search   for that item I want to make sure if they put in  something incorrect and let the user that this   is not an incorrect okay so that's important all  right so if they make a change to K I want to make   sure if B5 is empty meaning it has not been saved  let's put this invoice not spell right invoice not   saved okay in that case let the user know with the  message box please make sure to save the invoice   before adding the items I'm going to application  enabled events I want to clear the contents of the   target what does that mean that means I want  to clear whatever they've added here so if I   click new invoice and I want to add something  but the invoice has not been saved I want to   one give them the message box and two I want to  clear this out now keep in mind this is where a   lot of people especially me when I first started  out ran into problems I would clear the contents   of this and it would create a loop and it could  crash Why does that happen and it happens because   of this notice Target Clear contents this is the  change we're making a change to here so as soon   as this gets cleared out it's going to re-trigger  this and it's going to go back and forth back and   forth however if we wrap it in application enable  events false and then application enable events   true what happens is this will stop triggering  this change event won't trigger when this is   turned off so that means this type of change won't  trigger notice because clear contents this is the   trigger between here and here right they make a  change to k11 it's going to trigger this again   it's going to Loop out and possibly crash your  Excel so this is a great way we can prevent that   by turning off application enabled events clearing  the contents out and then turning them back on so   that's a very important distinction I want to make  sure to let that know and then I want to make sure   if the targets are empty right so what does that  mean if they have empty so now we know that then   save okay so first thing what I want to do is  if they've made a change what if that change is   empty meaning what if they've deleted it so if if  I go to previous let's see and I decide I'm going   to delete this item what do I want to happen well  basically I want to delete everything that's on   here all the way through and maybe the date too  that's a to you okay so if the target does not   equal empty I want to add the information and if  it's empty I want to clear it out so when we add   it in we want to I want to look up this I want to  look it up when I see bathroom vanity I'm going   to look it up inside the items I want to find out  what row it's on that's wrong row 4. then I want   to pull the description and column B I want to  pull the quantity in column C and I want to pull   in column D from the price and also that whether  it's taxable or not column e so we need to pull   all that up but the first thing I do is need to  figure out what row it's located on to do that   we are going to run so first of all we know it's  not empty okay and on our Zoom next and on air go   to zero now the reason is that is because we're  going to use the find and if it's not found it   could create an error so the first thing is I want  to extract that row that it's on it's in the items   database we're going to look in the item name I've  got a named range for the item name it's named   range here if we look in the formulas name manager  item name we see that it's here so it's again   using the offset it is the named range for the  item name so I'm going to look inside that named   range I'm going to look what am I looking for  the Target value meaning whatever the user just   entered Whatever item that is we're going to look  in the values on whole and I want to return the   row okay so now I want to check if it's not found  let the user know please select it correct item   from the drop down list exit this up so either  a this will trigger or B so are we also have a   validation on that one which will also trigger so  if they enter something incorrect this validation   will also trigger so that can happen too so  there's a few options that we have okay so we want   to make sure that they enter an item that's coming  directly from that we're going to exit the sub out   okay great so if it is correct then what we want  to do is inside that Target row I want to put the   description I want to put that in column L  and it's called coming from column B so it's   going that description isn't going to come into  column L and it's coming directly from column B   and then C and then D and then you're also going  to come in the corresponding lines going to come   into m n and p uh o already has a total o has  a formula here basically what we're going to   do is we're going to check if either one of these  are blank this one or this one is blank then this   is going to be blank otherwise we're going to  Simply multiply it and that's why if I make a   change to here or I make a change to here it is  automatically going to be changed in the formula   okay so we're simply going to bring in the default  quantity into column M coming from column C we're   going to bring in the price coming into column M  directly from column D inside our items database   that's it that's all we have to do along with the  taxable in column B once all those items are done   okay but however what if it's empty feel notice  this is here if we if we look all the way on the   app this is for non-empty let's just put non  and this is for empty so if it's empty we want   to clear the row it'll be empty when the user  actually presses delete in that case I want to   clear out everything in the item details and I  want to clear out B we could probably clear the   date too so we could we may want to do that all  right so we've done that and then all we have to   do so that just basically means if it's here we  can clear it out so that's all it's going to do   continuing on so that's all we did on item name  change so that's the events that we want to do   now what I also want to do is not only do I want  to populate this information what else do I want   to do I want to save whatever changes the user  have made here I want to make sure that they get   saved inside the invoice items database here I  want to make sure it gets saved directly in here   so how are we going to do that well what we can  do is we can look for any type of a change and   I also have a row that's associated here and that  Rose hidden but what I'm going to do is I'm going   to unhide it I'm going to take a look in here  inside home I'm going to go into notice it has   a custom format I'm going to go into more number  formats and you see it's got two semicolons here   when it's two semicolons it will hide any number  if I change that back to General and click OK what   that's going to do is it's going to display the  rows number now these rows correspond to the rows   in the inverse list notice they're on 78 and 79  and that way I know what row it has been saved to   okay so if we take a look in here so if I decide  I'm going to add an item in our update an item we   know that this bathroom vanity cabinet got saved  on row 79 so I need to make sure that any type of   a change automatically get now what if that change  what if there is no row there notice there's no   row then we need a new one assigned if I decide  to add an item here I need to assign a new row if   there's one that's already been assigned then we  can simply update the existing so the first thing   what we want to do on any change of anything in  here I want to check to see if there's a row if   there's no row we're going to add a new one which  will be the first available row here if there's an   existing row then we know to update it and that's  going to be on the change event which is what   we're focused on and anywhere from J10 all the way  through p and down so let's focus on that on any   invoice item detail change save to the database  so that range here is in a non-intersection J10   through p44 any change that happens then and I  also want to make sure b1's false not on invoice   load so that means when we run the macro invoice  load this goes to true so none of this will happen   when the invoice is loading so what do we want  to do the first thing what I want to do inside of   variable is I want to take that Target row the row  that we've made that change and I'm going to put   it into a variable called selected row or select  row okay now what we want to do is I'll again I   want to determine whether it's been saved or not  we're going to look in column queue and I can use   selected row here as a variable since we just  created it and we're going to look in that and   I want to see if it's empty if it's empty it is a  new item remember we're looking here in column Q   in that row and seeing if it's empty if it's empty  we are going to create a brand new item now for   the items what I want to do just one I want to add  for new items I want to add the invoice ID I want   to add the row here and I want to add the row here  so that's only for new items so that's exactly   what we're going to do if it's a new invoice item  the first thing I want to do is determine the row   so the first available row is the invoice item row  that's the variable that we're going to use and   it's giving that first available row I'm calling  a and x a lot plus one that's the first available   row inside that database okay next up what I want  to do is I'm going to do all the things that we do   only for new items in column A we're going to add  the invoice number that invoice number is located   directly inside B4 so we see if we remember  correctly that's where our invoice number   is always located in before so I want to add that  and put that directly inside column one I want to   put the page number inside column two or B column  and that page number the selected page number is   going to be right here inside b11 that is the page  number that's what we want to focus on so that's   going to go in the second column so that's exactly  what we're going to do right now column A is going   to take on whatever's in B4 column B is going to  take on that page number also called J I want to   set the invoice row and set the row formula  in J and K what does that mean inside columns   J I want to set the row of the invoice to 10 12  whatever the row of the invoice and then I want   to set the database row with a formula now we're  using a formula and this is important because if   a row gets deleted then we want to make sure that  all the other rows um beyond that are get updated   automatically and using a formula will allow us  to do that okay so continuing on so that's going   to set that row formula I also want to take  that brand new row that we just created that   new database row and I want to put it directly  inside here in column Q normally it's going to   be hidden so that's how we know it's in existing  okay great so what if it's an existing item if   it's an existing item all we need to do is extract  the row directly from what is in in Q10 everything   else here these two lines of code are going to be  automatically updated regardless of the type of   value it is so here we go invoice item sells the  invoice item row is going to be the target value   what does that mean right so basically with this  line of code so how do I know that well if I look   down here if I know that I've this is column let's  take a look we see that this is column J is calm   so if I know the date here was changed on column  10 and I want to place that date directly in   column C column three how do I know well I know  that the target the column that we've changed it   on is going to be actually column three so that  means any change to column 10 gets automatically   updated into column three because they're all in  order so all we need to do is just put it in the   same column minus seven so that's exactly what  we're going to do here the invoice item Row the   target column meaning the column that we made  the change minus seven is the correct column we   want to place it in and we're just going to put  the target value so whatever the user change and   that's really helpful and that's why in our  database date item name description quantity   amount total tax everything is in the same order  so if we take a look here inside our invoice date   item description quantity amount total tax it's  in the exact same order so having it in that order   is really beneficial because it cuts down on a  lot of code the only difference is this total   now this total is formulated right but I really  want to do I want to put that total inside here   however there's no change right there's no change  we're not making a change why are we not making   a change because it's the formula it takes care  of it so how do I make sure I want to make sure   in a separate line of code to add that formula to  update that total in a separate line because it's   changed by a formula not by the user so to do that  inside column H is going to take on whatever's in   column o in the selected row and that just  means that whatever's in O is going to be   placed directly inside column H and that's going  to update the total okay so that's all we need to   do to Simply update the items as we make a change  on any invoice there's a few other changes that I   want to go over again as you remember here when we  make a change in invoice if I make a change to an   invoice date or I make a change to a term I want  to make sure that that due date automatically gets   updated so that's going to be a change event on  either E4 or change event on B6 so excuse me E6 or   E4 changes to those cells I want to automatically  update the due date so let's focus on that any   term or invoice date change update the due date  not on invoice load and only if applicable meaning   that both E4 and E6 must contain values right if  one of them is blank it's not going to help and we   want to make sure it's not on invoice load we only  want that due date calculated when a user manually   makes those changes and not when they're making  a change or making an update so that's very very   important because we want them to whatever's been  saved and that's the reason is that is if they   manually make a change to that due date I want  that manual change to be automatically updated   when I load this invoice in so we only want to  calculate when a user makes a change to invoice   or terms but not on invoice load so on the change  of either one of those two cells and E4 does not   equal empty in other words there has to be an  invoice date and E6 there has to be a term so   both of those multiple and of course B1 equals  false meaning invoice is not load so when all   those things are true then we can check it out now  what I want to do is I want to find the term row   important thing is I need to know how many days if  net 30 I need to add 30 days to our invoice date   to create a due date now where are we going to get  the number of those days and that's going to come   directly in here it's going to be in this column  high so what I'm going to do is I'm going to look   up that term and I'm going to find out what row  it's on and I'm going to return whatever's in I   not row I'm going to add that number to the  invoice date and that's going to get us our   due date so we can do that here now when anytime  we use the find right we could create an error so   we always want to wrap it on our Zoom next and  on error go to zero I want to do the term Karma   what row is that term Associated so we're going to  look inside the Advent I'm going to look in that   named range called terms I'm going to look for  whatever's in E6 that's the term I'm going to look   into values and whole and I'm going to extract  the row now if it's been found it won't be zero   if it is zero we're simply going to exit the sub  it has not been found however if it has been found   what I'm going to do is I'm going to update that  due date that due date is located in E8 so what   I'm going to do is I'm going to add that do the  invoice date which is located in E4 and I'm going   to add in what I'm going to add whatever's in  column I the admin and the term row again adding   whatever's in column I if it's due on receipt  0 gets added so if I change this those are due   dates if I change this here to uh do on receipt  that due date is going to be exactly the same as   the invoice date because zero got added so that's  going to automatically update it so that's how we   do it very very simple just a few lines of code  on that so next up we have some changes when we   make changes to our customized invoice changes the  following changes are when I make a change to our   changes so notice we do have some changes these  are selection changes but when I make a change   to the labels I wanted for example if I want to  change this to uh let's say it's work date this   is for the column so if I change this to work date  then I want to make sure that that automatically   changes here now this is linked to this something  else that's actually linked to a separate cell and   why is that why can't we just link this to here  because everything gets cleared out this can't be   a formula right the reason is because notice that  if invoice details the same cell look at the same   cells located here so to do that what I need to do  is I need to save this information somewhere else   and load it in we don't want work date linked to  this cell because now it would be invoice number   so it's the same cell so we want to make sure that  when we make a change here it gets saved somewhere   else so if I want to change it back to date I can  do that and that's going to so how do we do that   well what we want to do is we always want to save  this information separately somewhere else that   it can be stored and then we can bring it back so  as soon as I select out and select in I want those   item details to be brought back in including the  item the show and label so all this information   gets brought back so where are we going to start  well we can start basically in columns we can just   store it off to the side so that's exactly what  I've done if we take a look here on the right   side I've stored all this information here if we  take a look we've got our information here we've   got whether we're showing it or hiding it this is  actually this new is actually based on a font so   if I were to change that font to wingdings you  would see it's just same thing so it's just the   check mark and the wingdings font so that's all  it is no difference just a different font but it's   the same character the font here doesn't matter  at all the font here it certainly does matter so   if we take a look in here and we see that it's  automatically wing dings font so when it's the   same character notice it's the same character just  the wingdings font so basically what I want to do   is store any changes that are made I wanted to  do here so the invoice the value so so we have   the item we have whether we're showing what is the  label so if I change a label I want to change here   because this is the information so if I just  let's say I want to add a colon to the invoice   number probably should have a colon so I want to  do that right here so that's part of the invoice   details and I want to add a call into that so I'm  just simply going to go up and I'm going to add   that column now that change I want that change to  be reflected inside here so notice that it's been   reflected here so how do I know that that happens  well we can use a little formula I need to also   know which particular tab has been selected is  it company details is it customer details or is   it item details we have four different sections  so if we take a look inside here we also have   invoice details company details customer details  and item details so I want to make sure now these   are simply The Columns that are going to help  guide us 27 28 29 30 these are the columns notice   we're using a formula okay so back into our code  so we want to make sure that we only make these   changes if B10 equals false then existence of exit  and less than edit mode notice that we do have an   edit mode in here so I only want to these changes  happen when edit mode so when this is turned off   notice take a look at B10 goes to false if this is  turned out we'll go over the macros on this this   goes to true you know if there's any changes on  here I don't want anything to happen when B10 is   false only when it's true this is going to tell  us our atomos truth so that's the first thing   if it is false then we're simply going to add to  the sub because we're not in edit mode okay we're   going to determine Dimension the item label shape  as a shape I want an item label shape because I   need to create individual shapes so if we take a  look inside here all these items this is the shape   this is uh this is the label shape called customer  label this is also shaped called customer so we're   grouping them so we can easily move them around  that way if we want to move up invoice I can move   it up here or if I want to create so notice that  our label and our field are mixed together into   two different shapes and we've grouped them called  invoice number group so we want to make sure that   we use these labels so we're going to do that okay  okay so first of all on if we make a change of   show or hide label notice that there's a change if  I make a selection now this is a selection but if   I make a change here I need to know whether we're  going to hide or show that label if I take a look   on if I unselect it you see that labels both are  gone if I show it if I just want to hide the label   I can delete it here and it's going to delete  let's delete here so notice now it just shows   six so I've deleted the label so we can show or  hide the label which can be kind of helpful so we   can show it again simply by typing it in so that  gives us the ability to both one show or hide the   entire group if we don't want to show anything or  maybe we only want to show the value for example   on these customers notice that the address doesn't  have a label I we can put it in if we wanted to   but it may not be helpful so notice so notice is  address and so but we may not want that right so   if we delete that I only want to show the client I  only want the client to have a label we can change   it back to customer now too as well okay so these  are types of changes so we're gonna focus on this   type if F and the row doesn't equal show right  it's blank then we need to hide it so how are we   going to do that okay so let's take a look inside  this if the user makes a change anywhere from f13   to G22 starting up here F 13 all the way down  to G22 because we don't necessarily know exactly   which one because invoice details has all of them  company details but what I want to do is I want   to make sure that there is a value on the left  in column e if the user makes a change to let's   say here and I delete this I don't want anything  to happen why is that important because there's   nothing here in column e so there's nothing to  do here so we're going to make sure if after G   if they make a change and I also want to make sure  B8 is false what does that mean what is B8 false   well that means menu load is false notice that  when I select something this is the change event   too right so when I make a selection change notice  ba goes to true so that again is a different types   of change so we're differentiating between this  type of change and the temperature change where   the user makes a change to the label or makes a  change to the show or hide those are two different   types of changes I need to differentiate that and  B8 is going to tell us when the menu is loading   this is going to go to true and then it's going  to go back to false so B8 must be false and also   again I want to make sure that even the target row  value doesn't equal that mean that means basically   we need to have something in column e here we want  nothing to happen so that's why when all those   conditions happen then what I want to do is I want  to set I want to know what item type row this is   the item type the item type is either invoice  details company details customer details or item   details so how do we know that well the macro  that's going to run I'll show you that macro is   going to set this type and we're going to use this  type B9 so that selected item type row is either   going to be 13 14 15 or 16. so we're going to  set that in B9 that's the type row because that's   going to let us know know which type has been  selected it's going to come directly from B9 right   here B9 so 13 through 16 any one of those values  okay so I want to set the item call now this item   call is important I want to know what column this  is on if I make a change to show I need to know   this is column 28. if how do I know this is column  for the invoice details let's take a look at the   invoice details if I make a change to here to this  column column six I need to know to make sure that   that should reflect on column 28 notice it's empty  there because that's where it gets saved if I   select it again here I need to make sure it gets  updated here so how do I know that it's column   28 well I can use a little bit of a formula to do  that and it's this formula first thing we're going   to do is add 22. why are we doing because that's  the starting point the starting point is going to   be 22. and also I want to add in something else  now notice that there is particular there's going   to be four columns per type four columns per type  you see here four columns here four columns here   so every type contains four column so it's going  to be a multiple of four that's why we have the   multiple of four here okay so we're going to set  the item type row minus 13. if we take a look   inside here the first one here this is 13. so 13  minus 13 is 0. 13 out of 13 is 0. if I multiplied   that times 4 it's also zero so if I take 22  and the target column what am I going to get   the target column is 2 is 6. so it's going to give  us 28 meaning if I make a change to here this is   column six if I if I take 13 I subtract 13 at 0 I  multiply times 4 is 0 so 6 plus 22 equals 28. now   if we use that same formula but it's 14. so here's  company details if I unselect here I make a change   to here this is now 14. so 14 minus 13 is 1. I  multiply that times 4 I get 4. so I'm going to add   6 plus 4 is 10 and I add that to 22 I'm going to  get 32 and 3 32 is the exact column that I want to   change so we're simply using math to get to that  column whether it's 28 32 and we're using this   formula here to do that so simply this formula  will allow us to get the column to save column   to save to so once we have the column and also I  kept the rows the same meaning look this starts on   row 13. so that's kind of a nice so it's always  the same row that makes it a lot easier so we   know the rows the row that we want to save it to  which is this row here is always going to be the   same row as the one we change so we know the row  we know the column and now we can make the change   so to do that we're going to do this sells the  target row we know the column is simply equal to   the Target value update call this data table okay  so off data table so we're going to update data   table with the information that we made change and  what's the update whatever we've made the change   and this is going to work also for the labels  too so if I want to add this company name here   this label here and although it's up here I want  to make sure that gets saved here to column 33.   so just like that it automatically gets saved if  I delete it it's going to be deleted there okay   so that's how we get change events that's how we  get whatever we make changes here to automatically   save here and it's very important because once  it's saved here then I can bring it back in   because I've got to clear out all the information  you want to make changes here I need to bring that   information back in so that's exactly where it  gets saved okay on change of labels show or hide   label but not on detail columns what does that  mean on change of item details is different these   are the columns right so there's no the label here  is Formula so we don't want to show our height   however so that means these are the labels right  if I unhide this we're going to unhide a column   it's kind of different than invoice details where  if I decide I'm going to unhide the invoice date   right if I want to unhide that I want to unhide  the whole thing so these three are very different   than these this is columns and these are all  Fields the first three are all Fields so how do we   differentiate them too so if we're making a change  to anywhere from G column G or focused on column G   and B9 is does not equal 16. what does that mean  if B9 is 16 then we know that's item details they   get handled very different only because these are  all columns in our invoice however everything else   so 13 14 and 15 are Fields these are going to get  handled all the same so we're going to treat them   so as long as it's not 16 we're going to do  something else I want to set the item label   shape I want to determine it does this label shape  exist what is the label shape it will be this this   shape right here it's called customer label it's  very unique to that just that field but what I   want to do is I want to see does it exist if I  make a change to here I need to find it right   I make that change I want to find it so where is  that when I make a change to G where is it located   does this shape exist customer label so how do we  know that well we're going to set the item label   shape this is the shape variable it's going to be  e what's in E is here is that information so this   doesn't change so this underscore label is the  name of that shape take a look at the name of   this shape it's called customer underscore lbl so  we always name our labels and our shapes based on   the item this doesn't change this is the text  this does change so this doesn't change so we   can give it a very fixed name so this would be  address underscore lbl city state and so on and   so forth so that's going to give us a name so what  I want to do is I want to know if it exists or not   so if the target value equals empty what does that  mean if the target value goes I mean it means if I   delete that the target value is empty what do  I want to do well in that case what I want to   do is I want to hide the label shape so we're  setting the label and we're hiding it so we're   simply going to hide it there however if it's not  empty what we want to do is I want to display it   item label say visible equals true so we're simply  hiding or showing the label based on whether it's   there or not so simply put as soon as I change  the value to customer I want that label shape to   appear and I want to appear right here great so we  notice how I'll show you of course how to create   them and position them but I just want to show  you we're just going in order so that's all we   need to do just we're going to show or hide those  label shapes based on the change directly inside   here continuing on now that we have that now we're  going to focus on the selection change code a lot   of things happen we make selection changes I want  to make sure that you know exactly how to do that   so what's the first thing we're going to focus  on we're going to Dimension the item name is a   string and the item shape is a shape denim label  so there's a lot of things going on on selection   change so we've got some shapes that we're going  to be dimensioning here now what I want to do is   if the target count large we're going to use 2 in  this case and the reason is is sometimes we have   larger fields that are named ranges and the reason  is sometimes we have merged cells such as this and   the merge cells notice that this one's a merged  cell so I want something to happen when we make a   selection change so that's why we have it on two  because this field is called enf so this is two   merge cells so if the user makes a selection on  anything larger we don't want it to happen but if   it's two it's okay so that's why anything larger  than 2 we're exiting on a sub so if you can use   that pop-up calendar and you use it on a merged  cell like I have here keep in mind that if you do   include this code make sure that this is larger of  course than the number of cells you have in your   merge cell okay first thing what I want to do is I  just want to make sure that we have that calendar   if it doesn't exist that little pop-up date picker  if it doesn't exist what I'm going to do is I'll   run a macro that replaces it and basically all  that that macro does is copies it from here and   it places it into the current sheet so that's  the macro that's all that does if the calendar   is visible we're going to hide it and that just  means if I've selected an account and I select   anything else I just want that calendar the date  bigger to be hidden so that&