Create a Proforma Invoice in Excel for Entertainment Effortlessly

Proforma invoice in excel for Entertainment

hello this is Randy with Excel for freelancers  and welcome to the invoice from scotts that's   right we're gonna be creating a brand new invoice  all from scratch you're gonna watch me write every   single line of code format every cell and create  every single named range we have a whole lot to   cover in this week's training so let's get started  all right thanks so much for joining me as you see   we're starting off with an absolute blank sheet  I do just have a couple lists I've got a customer   list and I've got an item list that is all I have  I've got sheet 1 sheet 2 sheet 3 all blanks so we   are gonna start from the beginning because I want  to show you exactly how I create these all the   mistakes I make how to fix bugs when I run into  them how to format how to set things up every part   I want to share with you I know you love these  when I start from scratch so I want to try and get   to do a lot more of course before we get started  I do create these videos each and every week for   you absolutely free through YouTube I just hope  you can subscribe if you want to get these videos   make sure you click the subscribe down below and  don't forget to click the notifications icon I   do create these free and I'm so happy to do so if  you would like I do have 100 of these applications   for you available in a zip files along with the  library so if you want to pick that up right now   it's just $37 so be sure I'll include the links  down below if you like these workbooks you want to   get a hundred of them I've got 37 dollars special  going on right now also if you do like creating   invoices I've got a brand new mentorship program  where we're gonna creating an entire accounting   application one single component of that  accounting application is an invoice that looks   just like this this is a much more complex invoice  than we're going to be creating today but I want   to show you the potential and power of Excel  and as I take you through I'm going to show you   every part of an invoice like this along with a  complete accounting and that means purchase orders   inventory items we're gonna cover dashboards  sherry and sink and a of other features that's   going on in the mentorship where I take you week  by week and we create an amazing application and   I want to teach you how to define design develop  and deploy your very own applications for passive   and reoccurring income so if the mentorship sound  like something for you I'll include the links down   below and I would love to have you alright let's  close this out and get started we've got a blank   sheet sheet one that is what I want our invoice  so we're gonna name that sheet we're gonna call   that invoice sheet I want that to be our main  invoice sheet on sheet 1 sheet 2 what I'd like   to have that I want to have that our invoice list  that's going to be a list of invoice so we'll call   that invoice list sheet 3 what I want to do is I  want to track the individual items of each invoice   we need to track those in a separate table and  database so we're gonna call that invoice item   list or invoice items should be fine for now  invoice items keep it small only thing we have   is customer lists and items that's all we have so  we've got three sheets and invoices basically what   I'd like to do is design this in front of you now  how do we go about designing this what always as   you know if you're familiar with my videos I'm  gonna keep the first two columns available for   our code so we can hide those so let's drop this  down we're gonna be using a lot of formatting on   this and we'll just call it that gray that's gonna  signify that we want to use those and save them   for code so I think that's quite important and  next up column C I want to give this just a blue   color I want to make sure we're gonna need that  column but I want it visible and blue up here I   want to create the name give it a name and we'll  call this invoice that's gonna be larger field   perhaps we're going to go to about 30 and I'm  going to create a larger because I want something   nice and large so we'll up that to about let's say  22 and then we're going to make it italicized and   I also want to merge that maybe up to F so we'll  merge the center this is gonna grow here I'd like   to put the invoice number invoice number at least  a name and then the label for the invoice and I   want the invoice number to go right here perhaps  in h1 these are going to be blue let's keep put   those in blue for now and I'm gonna go all the way  down give that just the light blue so we can start   to form out our invoice so basically what I would  like to have is our customer I want our customer   information probably in e let's say we'll put the  e to customer right about here I want to put the   customer customer and then what we'll do is we'll  have a drop-down list of customer in III something   like that then maybe in row 7 what I'd like to  do is I want to put the items in here so we're   gonna have a drop-down list of items so let's put  the items description of the invoice so each item   is gonna have its own description I want to  put the quantity how many the cost what does   the cost we'll put that actually the price or cost  either way and then the total is we're gonna total   it that should be sufficient and I don't want the  database to row this is important and I'll explain   that a little bit later on and this is gonna be  the item rule and I'll explain that later on - I   want to keep track of what row and we select the  item I need to know what row it's on so let's   give those a board just give it a basic I'm not  gonna create an amazing beautiful thing because   what we're gonna be focused a little bit more  on functionality than we are beauty descriptions   gonna need to be a longer field the quantity is  gonna be a smaller field and this will be fine   this is fine okay and let's see what I want to do  is I want to have a drop-down list of customers   and then I want to put the address line 1 and  address line to the customers so when we select   a customer and then want to have a drop-down list  and then down here I want to have the totals so   let's say in row 28 let's put in a subtotal here  subtotal here and then we'll put the subtotal and   then in case there's tax let's do tax and we'll  just set 10% or something like that and then of   course you can adjust it and then I want to have  the total here the actual total here so we can   drop all this down a little bit and the same thing  was here and then what I want to do is I'll have   a merged field so then here I'll do a message so  let's merge the center that left and top and then   make this will say thank you for you can do custom  messages down here thank you for your business and   we want to make that a little bit larger so we  can go up and then italicize something like that   all right good so now we're getting to form our  invoice I'm gonna put an outline on our form on   our invoice so we're gonna do format the cells and  put a border around that I'll choose that we're   using a blue theme so I'll choose like a blue  like this just an outline like that that should be   sufficient for now and I want to get rid of these  gridlines because I really don't like the way they   look so we can get rid of those so we have a much  cleaner looking invoice but I like to put some   dotted lines here so I'll go down here to about  right here then I'm gonna format those cells sorry   it's off the screen there but right-click format  the cells I go with a lighter blue something not   too and then I'm gonna use a dotted line vertical  dotted line that's gonna give us a nice look there   so now we just have to add the borders here so  I'm sorry I right-click I'm off the screen right   click and format the cells that's all I did but  it's off the screen a little bit so you know what   I did and so let's give it a border here so I'm  giving it a top order and I also want borders here   and here format the cells again sorry right-click  right-click format the cells also the border right   click format cells and then I'm gonna bring it up  here so I'm gonna do also a left border and then   a solid vertical border and then maybe a dotted  let's do a dotted centered border there okay so   you get to see now we can right justify that so  you can see the invoice kind of come to life here   get an idea of what's gonna go customer and I also  want an invoice date let's put that here invoice   okay that's gonna go in here and I'm gonna write  justify these two so that they're on the right   and I'm gonna left justify these two that way  the invoice date so put an invoice date on one   and then I'll format the way I want with a short  date all right so it's starting to come together   and we'll put an invoice now but I as you know or  as you will know I need some more information we   need hidden informations here that that's going  to help us create this invoice so the first thing   I want to do is I want to know a customer Roah  custom let's just call it customer row so when   we select a customer I need to know what row  they are in this database here so that's going   to be helpful I also need to know the invoice row  invoice row when we selected the invoice I need to   know the original row of the database coming from  here it's going to come from here so that we need   to know we also need to know if it's a invoice a  new invoice or not so click new invoice and that's   going to be true or false we also want to know if  it's loading invoice load true or false also on   that we can put false that's going to help us I'll  explain that later and then all the next invoice   number I want the next available invoice number  we're gonna color all of these just give it a nice   color so we can know we understand that those are  important for us and those are for the developers   you will hide these columns and nobody will see  them so that's okay but we can keep them all right   so now let's focus on our other sheets I need an  invoice list a list of all the invoice this is a   very very I'm gonna keep this very very basic for  our purposes here so let's call this invoice list   and then invoice numbers gonna be our first call  date you know we have a date for that and customer   I want put customer and then total this is maybe  the total may not be so important but it may be   for you when you do accounting purposes but for  our all we're gonna do is just save it not too   important so let's just increase this give it  a little bit of formatting nothing too formal   nothing too fancy for our purposes today just  trying to keep it simple and I want to do also I   want to keep track of invoice items so that's very  important I need to keep track of the individual   item so let's type in invoice items we need that  and that's gonna be a little bit bigger the table   invoice number we need to know what invoice number  each one is I need to know the item name and the   description now the description may be different  than our save description meaning we need to save   the invoice description so that's very important  also that the quantity and the cost or the price   we can do price and then what we want is invoice  bro I need to know the row of the invoice that   this was placed on and I'll explain that a little  bit later and then the what I mean is I mean when   we save an invoice item here I need to know is it  wrote 11 10 9 8 so I need to know the role it is   so I can place it back when I load the invoice  I can put it back in the right place so that's   important all right so we've got everything on  that let's merge the center it and then grow that   a little bit bold it and make it look a little bit  better and then also Center these add some borders   around it and then we'll just give it a color any  color will be fine all right let's alright so now   we have invoice items and we have everything that  we need on the center that and let's just increase   the columns but also on invoice on this particular  one what I'm gonna be doing I'm going to need to   be running an advanced filter and what that does  is so let's say there's items from tons and tons   of invoices I want to get a result of only one  specific invoice I want all the items for one   specific invoice so how do I do that well I use  an advanced filter based on an invoice number so   let me copy this I'm gonna run an advanced filter  I'll put it right here and I'll call this criteria   and so what I want is I want a criteria based on  the invoice number whatever invoice numbers here   so how do we do that I'm just going to create a  formula it's gonna say equals what is it going   to equal whatever the invoice number is right  here so when we do place an invoice here it's   automatically set as the criteria here okay so  we can Center those color those just so we can   let's give it a different color so now we see  the criteria and also so I want to run advanced   filters so I'm gonna copy this and I'm gonna  paste it here and then I'm gonna call this filter   results so these are gonna be results of our  filter so only one specific invoice out of all the   invoices let's say two let's say we have tons of  invoice - I'll need the items with invoice - are   gonna display here so I'm gonna merge the center  that and then make it a little larger so you can   see it and then give it it just a color let's give  it this color so we can differentiate between the   results and every other car all right so we have  our basics now down so that's important and let's   say our invoice list let's say we have one two  three our invoice numbers are going to go down   like here so we need to know those invoice numbers  that's very important now we need to do some named   ranges so let's create some named ranges I need  primarily three named ranges I need an item list   name I need a customer list name and then I  need an invoice number named range so let's   create those three we're gonna create dynamic  named ranges using offset so all we need to do   is just highlight the selection I'm gonna include  the header row because I don't want there an air   if there's no date in here so into the formulas  name manager and there's no name managers I'm   gonna create a new one and I'm gonna give it the  name of invoice item name which is fine and so   what I'm gonna do is I'm gonna create offset which  is gonna be dynamic offset and we're gonna start   out at a two which is what I want but of course  a two is the header so I wouldn't want to include   that so we're gonna move it one column down then  comma comma count a and what are we going to count   we're gonna count basically everything from here  let's say two nine nine nine nine all right so   just a large number of rows then but of course I  don't want include a header so have to do minus   one then comma one for a single column tab it  over double check and we're tapping it over look   double check the dancing outs are correct good so  we're good on the item name we've created and I   had a name tab it over once again it looks proper  let's create a brand new one I want four customers   I want to do the same exact thing so name manager  new let's do customer name tap it over do the same   thing offset formula offset starting out at 8 2  comma 1 comma call account a we're counting all   the ones where we count any one account all the  ones and then again we're gonna do nine nine nine   nine and parentheses and then minus one because  I want to subtract one because we're including   the header and I don't want to include the header  comma one and then tab out tab back into it make   sure that we've selected all the ones does that  look right make sure it goes to the end yep that   is correct okay so we have customer name and we  have now item name and so let's continue on one   more to create goats go into the invoice list and  I want to create invoice numbers so name you're   again new you get the idea here invoice number  just create that and again same thing offset so   we'll start out with offset and what are we gonna  offset a two again so we're gonna do again comma 1   comma comma count a same thing pretty much also  gonna highlight these go to say nine nine nine   one more nine and then minus one comma one okay  so tab over it and make sure it includes all the   data that we want good okay great so now we've  got our three named ranges so now we're ready   to create some a few formulas that are gonna help  us so we can close that out let's say this always   save our work so we created our work okay back  into the invoice so first thing I want the next   invoice number we noticed we have three invoices  I'm going to erase those in a moment but I've got   three invoices so the next available would be  four so let's create a formula for that equals   if air while if air because if there's no invoices  we need to make sure max what is the max the max   of the invoice numbers which is the name brain  injury's just created plus one right our maximum   is three our next one would be four plus one comma  what if there's no invoices if there's none I want   to start it off in one if there's an error right  there because there's no data I want to start it   off on one right now it's four right because our  next one's for we've did the max plus one but   what if there's none if there's none we want it to  revert to one why would it be none why would there   be in air because as soon as I delete that data  it's gonna revert back to one that's exactly what   I want what about the customer rub well I need  to create a role for that and I want to create a   drop-down list if the customers here so we can do  that with this just data validation pretty simply   here so into the data data validation list equals  customer name that's the name drink view just   created click OK and make sure that includes all  of our customers great so basically what I want   to do is when I select a customer I want to put  the address 1 and the address 2 lines down here   but I need to know the customer row customers keep  in mind they start off in Row three so keep that   in mind so let's get that customer roof through a  formula and we're gonna place that formula right   in b2 equals what is it you go if air in case  there's an air match we're gonna use them match   what are we matching we're looking up what are we  gonna look up I'm looking up III I can't click it   III and then comma customer name that's the range  I'm looking it up zero on an exact match then I   want to add two why are we adding two Manning to  because our customers start off in Row 3 so the if   we get a return of one that's gonna be the first  customer I want it to actually return 3 so comma   while I come if there's an error just click empty  alright great so let's double check that our our   first one should be Row 3 which is exactly right  that's exactly the way I want it first one is in   Row 3 our first customer here's in Row 3 perfect  ok great and now we need to invoice row I want to   do that pretty much the same thing but the invoice  row if we look at let's put some invoice numbers   back in here 1 2 3 so invoice number 1 is going to  be Row 3 let's make sure we create a formula for   that so refer to this can be based on h1 equals  if air what is the what would the value we need   to match again mask what are we matching this  time we're gonna look up the invoice number and   we're going to match it with the invoice number  and then we want an exact match so it's going to   be 0 again we want to add 2 because our first  one starts in Row 3 plus 2 comma double quotes   for if there's an air and perfect now invoice one  is wrote 1 we change this to 2 it changed it to 4   perfect that's exactly the way I want it now we've  got all of this setup what about our item names I   want a drop-down list of item names here so I'm  gonna highlight this and I'm gonna go into data   validation data validation list and I'm going  to create item equals item underscore name that   is the name that we gave the item name so good  now we have a list of item rates great but what   I want to do is want to know what row this is on  we can increase this a little bit sure what I want   to know is what row is this again we can do that  with a formula so let's do that equals again if   there as always in case there's an error I want  to match use a match and what does this match   based on is based on d8 so d8 and what's the  lookup array it's going to be item name exact   match again also adding to 0 actually adding to  again cuz I need to know the row comma and then   in case there's an error so great the first one  is on Row 3 and Row 4 perfect now I'm going to   copy that formula down copy and I'm gonna copy  it all day this can be hidden if you like paste   formulas okay so now we have it if there's now as  soon as we select and any row in item we have the   road we're gonna need that because when I want  to load the description the quantity and the   price in a macro alright great so our invoice is  starting to form let's create some buttons we want   some buttons here and that's gonna help us and we  can save the invoice so let's do that right now   let's save our work and insert I'm gonna insert a  shape here let's just write here and let's format   I wanted let's say point two seven and one point  about one point one point two five okay so that's   about the right size here and then I'm gonna give  it this color maybe this color now I got some icon   save and I want to create basically four buttons  save the in saver update the invoice I want to   cancel new I want to add new and I want to print  so I like it before different buttons and I want   to add some high cons so let's insert some icons I  had something saved so I can just copy these into   them and I want to size them all the same so I'm  gonna point to that's the size I want to give a   point to I'm gonna give them all squares and this  one actually this one I want a little bit bigger   this is our invoice background actually that's  fine I'm gonna resize the others a little bit   smaller someone hold down the ctrl click on all  three of these those are gonna help us and let's   go to 0.18 point one six for those cuz I want  them smaller okay so our first one is going to   be save update save save our exes update and then  now I'm gonna write justify that okay and then I'm   gonna put this icon right up here and then I'm  gonna bring that I come to the forward I won't   actually I'm gonna bring them all so click on all  three of them and then bring them to the front so   we're going to bring them to the front now they're  all in the front alright that looks good I'm gonna   group it but let's duplicate this one one two  holding down the ctrl D 3 ok so that way we have   three buttons three more buttons four in total  and I'm going to put this one I'm going to are   going to overlap so I'm going to size these and  gonna line those to the left and then I'm going   to distribute them vertically here so that they're  all distributed and this one is going to be add   new add new and this one's going to be cancelled  new so add new and cancel you're not going to show   up at the same time cancel new so those two are  gonna all should be same so let me copy this I   want this icon for each of them add new we can use  that plus for this and what dot printer icon go   here that one needs to be brought to the front  format bring to the front okay and so this one   I'm going to duplicate again and I'm gonna bring  it here and then the cancel new I want to bring   here because that's gonna be I'm going to bring  that to the front too so I want to cancel to be   able to use this to be able to cancel to get out  of that new and so we can do that let's zoom in   kind of a that'll help us work with them and we'll  create them these buttons right here add new and   weird that plus sign go there just bring that  to the front format align bring bring forward   okay so we'll have to realign those again now we  have everything let's just bring it up and we'll   start to group our buttons and that way we have  everything set so we can start assigning creating   macros and assigning them to that let's just align  these once again on now that I moved him aligned   to the left and and distribute vertically alright  great so we have add new and they're a little bit   big aren't they let's move them let's shrink them  a little bit okay so let's shrink them a little   bit hold down the control and then shrink them all  a little bit okay that should be good all right so   we have add new invoice on the green duty invoice  add new that's fine add new save update and this   should be print print invoice and we can also  duplicate that and put that and bring the printer   up in front all the way to the front bring it  to the front okay so we're getting our butts now   we're going to name our buttons ingly and  so we have specific different buttons for that I   don't know if we need that okay that's good that's  okay I think we've got everything just right okay   so I'm gonna hold down the control select all  the icons within this button and then I'm gonna   group them and I'm gonna give it a specific name  save the invoice button it's really save update   but that's fine save this one I'm going to give  another also holding down the control I can also   grip gonna group them and then we're gonna call  this add new button and then cancel new button   holding down the control grouping them cancel new  button and then the last one print invoice button   holding down the control on all of these and then  grouping them print invoice button okay so we have   them all named and basically add new and cancel  aren't going to show up at the same time so I want   to make these pretty much overlap because they're  not gonna show up at the same time all right good   now we've got our buttons and we've got them  all named that's gonna help us zoom in too far   okay so we've got everything it looks really good  now we have so we're really ready to create some   macros now why don't we create why don't we get  the address here and here on change of III when   we make a change we're ready to have that address  automatically and if we look at our customers we   see that address is located and B and C so let's  start out and let's get into our code developers   if you don't have the developers available you  can find that into the options just go into the   customize ribbon make sure you select developer  here alt F eleven is a shortcut that'll give   you the individual basically we have no macros  nothing else we're gonna create a module also so   we're gonna insert a module we'll just give it a  name you can click on the properties here and you   can give it any kind of a name whatever you want  just called invoice macros the only thing you want   to make sure of is that the name of your module  is never the same as any of the macro so we're   gonna create seller macros but first we want to  create some actually macros on the sheet itself   right and it's gonna be based on a change when we  make a change to the customer I want the customer   address to show up so worksheet and not selection  change that's when you make a specific selection   we want to focus on change actual change what  are we changing we are going to be changing its   III and he changed to e3 so that's what we're  focused on so let's do that if not intersection   that's called Auto type it automatically typed  for me I've used autohotkey you can look it up   on Google autohotkey that helps me e3 on any  change we want to do something so what is we   gonna do well we want to make sure there's a few  things we want to put in then of course and then   what we want to do is want to make sure it's not  empty and we want to make sure b2 is not empty   why do we want to make sure I want to make sure  that this is not empty right make sure and I want   to make sure that there's actually a customer row  and B to those two things are important so we can   do those in another one in another yes if target  target value is not equal empty and range B to dot   value is not equal empty then we can proceed right  empty then then we can move forward with adding it   in so first we're going to get in the customer row  as long I want to know the customer row and we're   going to set the customer row customer row equals  b2 right b2 is right here adding that in control   c equals v2 and put a note customer row now that  we have the customer oh we can put in the address   line 1 and line 2 where are we going to put it  we're going to put it right in be 4 + e5 those   are the cells right below it so range e4 dot value  equals where's our customers our customers are   located on sheet 4 right here so sheet 4 . range  b write our address first address our customer   name is located in a our dresses are located in  B so our first B and what B and the customer row   dot value that would be the address 1 and next up  and of course II five the cell below it range so   we can just copy that and make changes very very  easily copy that go down and that should be a 5 a   5 is addressed to address 2 is located in column C  so we can do that change C and make that address 2   let's double check into excel here and look at our  customers addresses b1 address to see so now when   we change it when we change this double-click it  the address shows up automatically make a change   again it's gonna show up great ok so we have that  so now that's purgin but what about one way since   we're making changes what about when we make  a change to items we want the description the   quantity and price in the total now i want this  to appear but I only wanted appear in certain   conditions when we load an invoice when I bring  an invoice that we've saved a long long time ago   the description the quantity and price is the  one that we saved not necessarily the one that's   located in this item list so keep that in mind so  when we load an invoice load it means previously   been saved we want to differentiate between this  kind of change because there's one change where   the user is actually entering it and then there's  another change where the invoice is loading so we   can use that through invoice load so basically  I only want the description the quantity in the   price when we change this but I only want it to  load when the invoice is not loading so we need   to put in a caveat the condition into our code so  this is going to be based on basic changes d8 all   the way through D 27 so let's write some code  in that and go ahead and back that now this is   gonna be on let's let's note it's always good to  notate on customer name change and this is going   to be on invoice item name change on invoice item  name change but not on invoice load very important   there's if not okay what are we again our range  we're focused on is of course D 8 through D 27   so that's what focus on and making sure that B 5  is false so D a through D 27 D 8 through D 27 and   range B 5 equals false we want to make sure that  is B 5 is false dot value equals false so on those   two conditions let's add then alright then what  do we want to do that I want to load the invoice   again I want to make sure that we actually  have a value right the only way we can load   the invoice details the only reason I can I need  to get the item description the default quantity   in the sales price I need to know the row that's  very important we need to know what rows and that   row is stored right here so we have to make sure  that C is not blank so we can do that with the   following lines of code if range c and the target  dot rho dot value is not equal empty then we can   continue then we can continue then then we can do  whatever we want so then we can add an information   then ok so then we're gonna dim the item row  as long now the item road is gonna of course   be whatever is in c in the target row so we can  just copy that and put equals below it before it   equals item row that's the item bro the original  data road so now that we have the item row all   we need to do is in e f and g place whatever is  located from here in BC and d so we just make   one line of code we can do that so it's basically  range e and of course the target row which have a   row are on target row and through what is it G  and the target row dot value equals remember II   through G let's go over that again e through G  equals the items this would be sheet B through   D so that's what we're gonna add it on equals  and then of course we're focused on sheet five   which is our items equals sheet v dot range b and  the item row and the item row and parenthesis : g   and again the item roast gonna copy all the  item row dot value copy over description quantity and column price so i'm going to copy  all that over and that's of course on change of   d8 so let's take a look at that and see how that  works and go back in the invoice and double click   on here in Turkey all right and double click  here enter it and then enter one here alright   great so it works nice now we have the totes but  we need the total ok we need to make sure we had   enough formula so let's do that equals what is  the total I want to make sure if the price does   not equal empty does not equal empty then what I  want to do I want to multiply the quantity times   the price quantity times the price otherwise it's  just empty although it means the others and we're   gonna format that of course copy that and we're  gonna paste the formula all the way down paste and   then paste the formula alright great so let's  format all of these and I want to format all   of these two currency so we got our numbers so  into the home we're gonna format everything as   a currency alright so now we've got it as a firm  see you okay good so we've got a nice little let's   save it and we also want to add the subtotals  equals the sum what does the sum it would be   all these so we've got the subtotal and I want  the tax equals what is the tax that let's just   say it's 0.1 10% times whatever the total is  here so we've got the tax and we of course of   equal the total equals the sum of both of those  above now we've got the total now we're starting   to build out our invoice all right nice it's  looking much better now I like it it's getting   better it's a little bit too big drop that down  okay don't like it both all right so we've got   everything and let's add in another item here  so really what I want to do now is we've got   the item we've got the address we can start  building out our macros now we also want to   start to save it I want to be able to save this  invoice to the database so that we can bring it   up basically when I enter an invoice here I want  to load it up so let's start out with that because   we've got a good-looking invoice here and we can  increase this a little bit make it look a little   bit bigger that's a bit too big okay so we got  invoice number and it looks good increases com   a little bit and this is a bit too big too okay  so we've got a nice economist let's get into the   macros and start building out the first macro  motors I want to be able to save this so what   I'm going to do is really essentially I want to  save the invoice number the customer the date   and of course I want to save that all to here  invoice number date customer and then the total   not sure the total but we're just saving it for  the fun of it okay and then of course I want to   save all those items into our items list here I  want all those items to be saved here so let's   write a macro that we can do just that so into  our VBA here let's clean this up a little bit   so it looks better we don't need all the space  we add a little bit more we do need on change of   invoice number so we have let's put a little bit  of reminder in here for us on change of invoice remember load the invoice that's something we  have to write but we need to write the macro set   all right so the first matter is that I want to do  is I want to save that invoice so invoice save sub   invoice save actually save update so let's just  write it's gonna be the same we're gonna use the   same Mac or regardless of whether we want to save  it or update it all we need to do is differentiate   between the two and I'll show you how to do that  we're gonna dimension some variables so dimension   the invoice row I'll need to know the inverse rows  long I'll also need to know the invoice item row   as long and I also want the last item row last  item row as long and the item row we're gonna do   item I need to know the item row for adding new  items that need to know the last row so we can   add more that's important because we're adding all  the items invoice them and we're gonna primarily   of course focus on which she once was nice cheap  one okay so with sheet 1 and we want to do the   first thing we want to do is we need to determine  if it's a new or existing in order to determine   new or existing invoice and we can do that with  based on a cell d4 invoice so we can do that if   dot range b4 remember before is what we set as  a new true or false before it equals true then   it's a new invoice and if else it's an existing  else existing okay so we're gonna do some things   if it's new and some things if it's an existing  if it's new what I want to do is I want to get   an invoice row we need to assign the row if it's  brand new we need to get a brand new row and the   row would be the first available row we can clear  these out so in this case our first invoice first   available row 0 3 so we want to get them we can  use the line of code and Excel up on sheet 2   which is our invoice list let's bring this out  a little bit invoice list so our invoice row is   equal to sheet 2 dot range a 999 dot and Excel  up dot Rho plus 1 actually plus 1 that's going   to be the first available row number xlab just  gonna give us the last row the value so we add   row it's the first available row after that so  we have our invoice row that's the first Villa   row also what I want to do is I want to set h1  is our invoice number remember H 1 is where we   put our invoice numbers so that's very important  right here into h1 that's our invoice number so I   want to put that where am I gonna find it I'm  gonna find it right here in b6 gonna take the   next invoice number put it right here in h1 so  we can do that right here dot range h1 not value   equals B 6 equals dot range B 6 and that's gonna  place our next available invoice number right in   the right place that's gonna add invoice all right  so the next thing I want to do is I want to add it   to sheet 2 I want to put that same invoice number  in sheet 2 and call me so XI two dot range a and   what row a in the invoice row and the invoice  row dot value equals what does it also equals B   six about range B 6 so we know we've still got  B 6 right there B 6 is our next invoice number   add we only need to add invoice numbers for new  invoices therefore saving an existing invoice   if we're updating it the invoice numbers already  there so we don't need to again okay number so we   need to do that that's for the first and also  one more and actually that's it I think we're   good to go else so else but what if it's but what  if it's an existing invoice if it's an existing   invoice our invoice row is already known where  is it known our invoice rows already based on a   formula right here based on b3 it's going to be  there so we just have to add it there if it's an   existing right so invoice row equals dot range  b3 value that's gonna be our existing invoice   row all right great so we're set up next up all we  need to do is add the remaining details to sheet 2   sheet 2 calls b2 dot range in B what does B that's  a customer name and where's our customer that date   actually be to I think is our date let's take a  look dot value these are B and the invoice row   dot value equals our date and our date is located  let's just double check that to make sure we have   everything right invoice so this dates going to  go and be customer in C and total in D and date   of course is going to come right from h2 so let's  add that in the code equals dot range h2 that is   the day okay so we have the date of whatever the  invoice is and next up we want to add the customer   sheet and we can just copy that over and add in an  update it's a little bit faster than typing it out   and that would be column C is where the customers  gonna go and call is III that's what the customer   name is located and that's a customer name the  last thing is the total so I want to add the   total and that's going to go in D and our total  is located in H 30 I believe let's take a look at   that H 30s where our total is we're going to add  that into all right H 30 that is our invoice total   so we've saved all the invoice basis but now we  have to save the invoice items that's right boys   so now let's add invoice add or update perhaps  add or update invoice items so so what do I want   to do with that basically what I want to take  is whatever items are here not the total just   whatever items here the item name description  quantity price and I want to place them right   in here in this table but they might already be  there right if we're saving or up getting this   is for saving your updates so how do we know  well what I want to do is I want to basically   check this database row here this is the row  the database so it's been if it's been saved   the rows going to be here it's it's going to be  here if it's not that means it hasn't been saved   before that's how we can easily add new items  to an existing invoices and we don't would just   update the database or we add a new row and I'll  explain that to you later but basically this is   our check this column column is gonna let us know  whether it's been saved before or not if this is   blank which it is now it has not been saved before  so that's gonna help us so we just need to run a   check to see if I is blank but I also want to know  the last item row that's gonna be important so we   need the last item row because what I want to do  is I want to run a loop from the first all the   way to the last so we need to get the last row  and we can do that with this line of code here   so the last item row equals dot range D the last  possible row would be 27 27 dot and Excel up dot   row last invoice I think row so now we can run a  loop and we want to make sure that the if though   oh we should put a Cabot if the last item of row  is less than 8 in case there's no item 8 then go   to no items no items just in case there's no just  in case there's no last item row what if there's   no room here what if what if there's no rows the  last would be less than 8 so we need to put that   in to check for that otherwise we're gonna run a  loop from 8 to the last row in this case it's 11   we can start that loop through our code for item  row for item row equals eight to the last item row   and then next item row so that completes our loops  and what do we want in that loop again I want to   check if I is empty I need to know whether to  save it to an existing row or a new row this   will tell us if it's blank I need to save it to  a new row so we're gonna first check in I we do   that if dot range I and the item row and item row  dot value equals empty then then it's a new row   right then it's a new row so if it's empty down or  let's put let's put not empty it's and it's just   not we can do that in a web not empty an existing  row okay Dan alright else new row and then end if   okay so we can write some code ingly  based on whether it's new or not so all we   need to do is get the invoice rhythm if it's an  existing row invoice item row we've updated up   here we've dimension it up your invoice item row  equals I write if it's existing it's not empty it   has a row and we can say equals this right here  but what if it's new well if it's new I need to   add it so in that case if it's new I want to find  the first available row equals what cheetah we on   we're focused on the invoice item sheet three so  would be sheet 3 dot range a nine nine nine nine   dot and Excel up dot Rho plus one they'll be the  first available row first available row so that's   going to give us our first so we know where to  put that item so then it's a new item so now we   have our invoice in the room now I want to put the  invoice item in there the invoice numbered there   so if it's new right let's say let's say it's new  then I at least put in the invoice number here if   it's existing the invoice numbers already there  so let's put that in only if it's new so that's   in column a so let's do that only number only if  it's new so sheet 3 dot range a and the invoice   Edinboro item row dot value equals what is the  invoice number well the invoice number is located   here in Voight's equals in the item would be H 1  dot range H 1 is our invoice h 1 value and that's   the invoice number invoice number i also want  to add this item row inside i right remembered   now we're adding it in so i also want to put it  write it here in i so on a picture cuz it's blank   if it's new so we want to put it in so let's do  that with this line of code dot range i and what   row we on were on the item row item row that's  for the invoice the invoice equals what does it   equal it equals the invoice item row i know it's  kind of confusing invoice item row so basically   let's here I'll walk you through it I don't I want  to put this I just placed I'm gonna be placing it   right here all the details right here but I need  to know it's on Row three or I need to know it's   on row four or I need to know what's on row five  so I want to put that three four or five right   in here and you'll see that once it's done I'll  walk you through it but you'll see it so I want   to put that row right in here and we only need  to do that on new items otherwise its existing   already all right so we've got if it's a new item  or an existing item so now we're ready to Wratten   add in the remaining details we can do that with  this line of code sheet 3 dot range B and what   is it the invoice item item row and colon through  e because that's last one equals what is it equal   value dot value equals whatever's on sheet1 right  dot range in this case D D and in this case it   would be the item row item row and what is that  it's also through gee right all the way through   G : through G naught F G and the item row we  need to end there and the item row so what that's   going to do is going to copy over the description  description quantity and cost so here we're gonna   add description quantity and price actually so  we can add all this so basically all we're gonna   all we're doing again is sort of saying B in this  case B actually not here invoice items B through   e equals whatever's here D through G so basically  we're just taking the information here and we're   placing it on the row right here now we just need  to add the invoice row and the row here so we can   do that with the next lines of code sheet 3 dot  range F and the invoice inventory item invoice   row dot value equals what does it equal well it's  going to equal the item row I want to know the   item row I want to know for example if this is  put on item row 8 or row 9 in a row 10 I need to   put those rows and I need to put them right here  8 9 or 10 I need to put them rail next up I want   to put in G's the row actually we can do that up  here because it's only for new items so let's do   that here dot range G because we only need to do  that once and the item row dot value equals and   then it's going to be open quotes equals row it's  a formula that's going to get us the row add row   okay so we've added the row number we only need  so on down work now we're done we've added FG   all the way through G F everything we've added in  all the way up to G great so we can run that code   let's run the code and see if we have any issues  or any any missed uh let's debug it and see if   we missed it invites item row he wiII he need a  coroner and invoice item row okay that looks good   let's breathe on it reset it okay good all right  let's take a look at it alright now we have our   invoice number that looks good and center that  and we shrink that bring this out a little bit   so we can see the details quantity price that's  good invoice wrote we didn't get the row let's   get the row number in there we need to get that so  that didn't work out G and the item row we should   be invoice item row and it should be sheep three  sheep three sheep 3G and they should of course be   the invoice item row eagles were out there we go  now we got it now we got a man we messed up our   invoice so let's take a look in G because that  doesn't belong there okay that's fine it doesn't   honey blue pair C we better out here we don't  need this just add this in ten fifteen twenty   that's kind of expensive okay so we got that we  don't need them there so now we have the prices   there that looks good we have that we have that  there and we can add in manually now because the   codes gonna do that for us items we're gonna have  the row here so equals row just add those in and   formula take care of it from now on but we have  the rows so you see eight is going to be this item   nine these are based on the invoice rows eight  nine ten and eleven which is what I want eight   nine ten eleven great so now we've saved this  invoice with it with all the details perfect   what do we do now we need to load the invoice  I need to take when I enter an invoice number   I need to load it up and I need to also clear the  invoice I'm gonna clear the invoice so let's write   some more macro so that we can do just that all  right so invoice and save and update let's make   sure I want to add a few more things on that this  is not quite complete yet a few more things I want   to update the button set and I want to make sure  that that the invoice load is no longer false so   want to make sure that the invoice new invoice  is no longer true we just saved it so we want to   make sure that before it goes to false so let's  got they debt dot range b4 value equals false   invoice existing invoice so let's just do set set  new invoice to false and also the the button sets   dot shapes what is that what are the button sets  I want to make sure that cancel new this is only   for new cancel a new button that's only for new  so I want to make sure that's hidden and I want   to make sure that the one under it in this case  it's add new is also displayed so we want to do   that with those two two shapes cancel new button  dot visible equals and the so false and dot shapes   add new button needles add new I think that's  what it is not visible equals MSR true and make   sure that's true so let's run that again it's not  going to duplicate okay let's invoice item let's   make sure that's spelled right run it again okay  good now let's take a look at our button sets add   new is there it's ready save and update now we can  assign a macro to the save update a sign Mac or   the only macro that we've created save and update  great so we click this it works just fine so let's   say we want to add a row look there's there's four  and four of them but I want to add an item to this   invoice all I need to do is just select an item  here from the drop-down list and click Save an   update now you see it's been assigned to row now  we look in there it's now it's here see how it is   now we just added one row so now we can load the  invoice and we can also clear the invoice so let's   write both of those macros because I'm when I add  the new all I want to clear all the data here I   want to make sure not to clear these formulas but  I want to clear all this data the customer data   the invoice number and everything so let's do that  now let's write that macro and we'll scroll down   here and we'll write it new so sub invoice new and  with chic one what I want to do well I pretty much   want to just clear the fields out so how do we  do that we could just say would be for the first   thing I want to do with d4 I want to set that to  true set invoice that your dot range b4 that's our   field that lets us know it's equals true set  invoice to true check new invoice to true new   invoice to true all right what I want to do next  I want to clear the contents of some field dot   range I want to clear the invoice number that's  h1 I want to clear the customer that's III and I   also want to clear the customer address efore and  u v e4 and e5 that's a customer address down below   so I want to clear that I also want to clear the  data D I think it starts in d8 and goes all the   way to gd8 through G I think 27 and also want to  clear the roads I ate through I 27 let's double   check that I ate through I 27 let's take a look  at that range so we have everything right we want   to do the inverse probably the date to h2 should  also be cleared out h1 right we want to clear out   these C actually no these are formulas D through  G correct and all the way from 8 all the way to   27 and also I ate all the way through I 27 I think  that's right and just see a date probably know I   think the date we're gonna set to the current date  so let's do that H 2 would equal now so let's do   that in the next line I could so dot clear one  clear that clear contents clear invoice field and then set the date range h2 is our date h2  will set that to now equals now that's gonna   set the current date it depends on the format  date and time or date depending upon the format   you said all right now I also want to update the  shapes alright dot shapes basically it's gonna be   these two shapes except we're gonna reverse it  these two shapes right so we can copy and paste   them paste them right in here cancel new we want  that visible so MSC true and we want the add new   we just click the add new so we want that false  okay so that's good that looks just about right   so let's run that and see if we run into any  issues no it looks good let's take a look make   sure our formulas are still here our formulas are  still here we need those formulas to current date   customers blank invoice blank cancel new saver  update good those two buttons print invoice that   doesn't look good I don't think I like that  print sign and bring it over here okay so the   print invoice everything's there actually it's  kind of ugly get rid of that let's get rid of   that print behind it cuz that doesn't hard to see  right it's better if we just have the print okay   and we'll increase that to let's say point two  point two not two okay that's better that kind   of better all right so we've clear out the invoice  everything looks pretty good let's color this give   it a nice color so I don't really like the color  and make it bold alright so now we have that let's   save all of our work we've got the clear so the  next thing up is I want to load the invoice what   I want to do is I want to put the one in here and  I want to have that invoice load automatically so   let's write a macro to load the invoice so  not only do I have to load the details but   I have to load all the items so let's do that sub  invoice load can we need to define some items for   sure so what do we want to dimension here so I'm  let's scroll down here we're gonna need lots of   variables in this case of course the invoice row  we're going to need as long the invoice item row   as long and then now we're also going to need the  last invoice item row we're gonna run an advanced   filter best invoice items row as long probably  a few more - such as dim I want the results row   results result as long and then the last item  result row as long because we're gonna run an   advanced filter I need to run through those  results and so that's very important with sheet   1 we're focused on primarily sheet 1 so we need to  keep that focused they're working on that and what   are we going to do well the first thing I want to  do is I want to make sure that b3 is not empty if   it's empty then we can't do anything why b3 b3  is our customer row if I enter an invoice we're   gonna have uh excuse me B 3 is our invoice row if  we're getting into the right invoice we need to   make sure that we have an invoice gross we enter  something that's incorrect it's going to be blank   so we want to make sure that first thing one do  is make sure there's actually a row tied to the   data right we need to if we're gonna load this we  need to make sure it's there ok so we have that   now let's continue on with the code invoice b3  check for that let's check for b3 if dot range   B 3 dot value equals empty then message box boss  please select a correct invoice number ok exit so   let the user know that we need to make sure we  get a correct invoice alright now that we we've   gone past that we can define our invoice row it's  equal what is it equal to b3 so we can set that   up just copy that and then go down and set it to  equals about this long training equals b3 invoice   row ok so now that we've set the invoice row  we're ready to go that we want to make sure we   also set the invoice low to true so let's do that  because we're loading all the information we need   to differentiate between the dot range b5 equals  true set invoice load to true okay so we've got   that we've got the invoice row and now what I want  to do is I want to set want to add in the date and   the customer information we know the invoice row  so we all we need to do is take the date and put   that inside here and take the and take what else  take the customer and place it right in e3 so   we can do that with following lines of code dot  range h 2 h 2 is going to be the date we already   have the invoice number because the user entered  it equals sheet 2 that is our invoice list dot   range and that would be B and the invoice row dot  value that's the da