Create an Excel Invoice for Technical Support Easily and Efficiently

Excel invoice for Technical Support

in this series of videos i want to show you how to create your own invoicing system i'll start off by describing how to create the invoice itself in terms of formatting and structure also how to keep a customer database and how to keep a record of invoices and you'll see here that wherever we have an invoice that's overdue it appears in red but if we say it's paid then the red background disappears so it's a good way of tracking your invoices you can also see over here when the invoice was emailed and you've got a link to the invoices as well so you can view them now in other videos i will also go through these macros that i've created you've got a macro that will save the invoice as an excel file a macro will save it as a pdf file a macro that will automatically email the invoice to the customer a macro that will add the invoice to the record of invoices and the last macro here will clear the current invoice so that you can start a brand new invoice and it will also automatically generate the next invoice number for you if you want to learn how to create this invoice from beginning to end including all the macros run by these buttons then follow the link in the description of this video to the playlist that contains all the videos in this series okay in this video we're going to look at this start new invoice button i've got an invoice completed over here if i click on this start new invoice button it clears the invoice of its items and also of its address so a few things that stay in here like the date which might be relevant to the next invoice but it's also clear the po number having pressed the button i get this little message box that tells me what my next invoice number will be when i click on ok it updates the invoice number and also it saves the invoice template okay let's see how we can create this button so this is as far as we got in our last video now we're gonna have to write a little macro for this button in order to do that it's a good idea to have the developer tab on your ribbon if you can't see it right click on another tab that you can see customize the ribbon just make sure developer is ticked there click on ok then on the developer tab click on your visual basic button so that will open up the visual basic editor and if you've been following along this series of videos you'll already have this macro for the record of invoices if you haven't got that macro and you've not been following along we've already created a module so you need to go to insert module to do that and we're going to start a new macro so i'll give myself a little bit of space at the top and i'll write sub crate new invoice open bracket close bracket press enter you'll get an end sub line and i'll just create some space for myself i'll close down this project explorer move this over a little bit so we can also see our invoice there's three things i'm really doing here i need to increment the invoice number i need to clear certain parts of the template and i also need to save the template first so let's concentrate on changing the invoice number i'm going to declare a variable for that invoice number as long so i'm going to store the value in c3 in this variable just to save confusion it looks as though the invoice number is in d3 but these cells are merged so the value is actually in c3 you can see that in the name box so invoice number equals range c3 so now i've stored that invoice value in this variable the next thing i actually want to do on the sheet is clear certain parts of it for example the po number the invoice address and any values in this table down here so i can say range start with po number now the po number as i've just said is in a merge field so i have to reference both of those cells so that's c4 colon d4 comma i'm also going to clear b10 where we have the customer name i'm not going to clear the address you'll see why in a moment and then i want to clear the invoice items table so that's b19 colon g35 close my quotation marks close the bracket and i'm going to use the clear contents method once i've cleared the contents i need that little message box that tells me what my next invoice number will be so message box your next invoice number is space quotation marks ampersand i'm going to concatenate that text with invoice number plus one which will increment the invoice number so now i need to update the invoice number in cell c3 so i'll say range c3 equals invoice number plus one now i've changed that invoice number i'm going to select range b10 and that means that once the macro is finished the user will be in cell b10 so they can easily choose a new customer for the next invoice and then i'm going to save this workbook okay let's see if the macro does in fact work i play it you can see i get your next invoice number is 1001. if i click on ok it changes the invoice now we've got a bit of a problem here because although we've cleared the customer name we've not cleared these formulas below we wouldn't want to clear them or to keep them in the invoice so to solve this i'm going to put this vlookup formula within the ifna function and specify that i want to return an empty text string instead of an n a error if i copy this down the only thing i do need to do is change the call index number so it picks up the right column so that'll be three four five six and seven and then if i pick a customer it gives all the address details okay let's create a button for this macro so i'll go to the developer tab go to the insert menu under form controls go to the button button draw your button choose the correct macro create new invoice click on ok change the text in the button so if you click into the button delete what's currently there create new invoice to format the button click outside it right click on it format control alignment will say is left margins have a 0.5 margin on the left side and then we'll put a little icon in the button so i'm going to go to insert illustrations icons i'll type document i want to do i'll change the height of that to 0.85 drag it into position now i want to group the icon and the button icon is already selected i'm going to hold down shift and right click on the button that'll also select the button then on the shape format tab i'm going to go to group and group and that groups the button and the icon so if i right clicked on the button and then moved the button the icon moves with it so let's see if this works if i put po number in already got an invoice address some stuff quantity 100 price 8.99 that's my invoice and probably add it to the record first and then i'd want to create a new invoice so i'd want to clear the existing data your next invoice is 1002 it's cleared the invoice and i'm ready to go for my next invoice and a new customer okay that's all i wanted to cover in this particular video hopefully that's useful if it is give me a thumbs up subscribe and i'll see you next video [Music]