Create and Manage Editable Invoice PDFs for NPOs Effortlessly

Editable invoice pdf for NPOs

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 creating this save as pdf button it's going to save the invoice as a pdf obviously but it'll also put the record of the invoice on the record of invoices sheet and leave a link to the pdf as you can see here let's see how we can create this button so we've already created our invoice and we've created three other buttons need to sort out the size of these buttons eventually if you want to know how to develop what we've created so far then you need to watch the other videos in this series now this button that we're going to create is going to require a vba macro and for that reason you must save your file as a macro enabled workbook rather than a normal excel workbook now the button we're going to create is going to require a vpa macro and it's a good idea to have the developer tab on your ribbon if that's the case it doesn't appear by default so right click on another tab customize the ribbon make sure developer is ticked there if you click on the visual basic button now if you haven't been following the other videos in this series you might not have a module in your project so if you go to insert module that'll create a module and the module i have here contains the other macros that we've created for the other buttons for this invoice i want to know how to write these macros watch the other videos so at the top here i'll create a new sub procedure sub save as pdf now i'm going to have to declare a fair few variables in this sub procedure and they are actually exactly the same variables as the last button i created for saving the invoice as an excel file so i'll just talk you through these if you haven't seen the other videos so the first one here is for the invoice number i need to capture that because i need to transfer it to the record of invoices and you'll see that's the same for the company name the amount and the date of issue so that accounts for these four variables term i need term to calculate when the invoice is due the path is going to be the path of the folder that i want to save this invoice to the file name is going to be the file name that i'm going to create for this pdf and i'm going to generate that file name as a concatenation of the invoice number and the customer name you'll see how to do that in a moment next wreck i'll talk about later on you'll see where that is relevant now i now need to store a value in each of these variables so the invoice number is stored in cell c3 in my invoice it may look like it's in d3 but these two cells are merged sales and you can see in the name box that the invoice number is stored in c3 the customer name is stored in range b10 that's down here in the invoice the amount that's right down here in i-41 date of issue well that's up here and that's in cell c five term that's going to be 30 days 60 days or 90 days and that's in c6 path that's the path to the folder that i'm going to save the invoice to and i have that on my clipboard the only thing to note there is you must have that backslash at the end of your path and enclosed in quotation marks now file name is going to be the concatenation of the invoice number and i'll put a space dash space between it and the customer name okay there's the variables we need to define at the moment now what we want to do is save the invoice sheet we don't want to include these sheets in the pdf and also we don't want to include these buttons in the pdf so i'm going to set a print area and select the cells that contain the invoice information then i'm going to go to the page layout tab print area set print area and i can check that's worked if i go to file and print you can see it's only printing one page and i haven't got any buttons on the printout so the code for creating a pdf from the active sheet activesheet.export as fixed format now the first parameter i'm going to use is type and i'm going to say excel type pdf comma the next parameter i'm going to use is ignore print areas and i'm going to say false for that because i don't want to ignore print areas and then i need to give a file name so i'll use the file name parameter that's going to be path concatenated with file name okay let's see if this macro actually works if i create an invoice number 1001 put today's date in i'll change the customer there i'll put another item here down here and now i'll create that invoice so now i've gone into the folder that i've specified for the invoice you can see it's created that pdf if i open it up there's the pdf no buttons but all other information contained okay so that bit of the macro obviously works but what i do need to do is to record the details of the invoice here together with the link to the pdf now the problem i'm going to have is finding the next available row in this record of invoices and the method i'm going to use is to go to the very bottom column a down to row one million forty eight thousand five hundred seventy six and then use control up to select the first value it finds and then i need to move down a row and that will be the next available row now this is where i'm going to use this variable next rec need to basically find the next available row in my sheet now because nextrec is defined as a range i need to set it because a range is an object so i'm going to set it to the next available cell in column a now the record of invoices is sheet 3 that's the code name for that sheet and sheet 3 is the most reliable way of referring to that sheet equals sheet3 dot range the range i'm going to refer to is that last cell in column a now the equivalent of control up arrow key in vba is dot end open bracket excel up and then i need to come down one row so that's offset one row no columns so having found that cell i need to put the invoice number in it then the next column along i need to put the customer name so i can use offset no rows one column equals customer name then i can copy this place down here so the next column along i need to store the amount see that in column c over in my sheet next column along i need to store the date of issue and then the next column along is date due and that's going to be a calculation that'll be the date of issue plus return so that'll fill in these columns here but i still need to put a hyperlink to the invoice now to do that it will be sheet 3 dot hyperlinks dot add and the first parameter i'm going to use is anchor that is basically the cell that you want to store the hyperlink in so that's next rec dot offset no rows six columns along and then i need to give an address for the hyperlink so where i'm going to link to so i'm using the address parameter for that and that's path concatenated with file name concatenated with the file extension select dot pdf okay let's see if this macro works so i've got to go back to the invoice template sheet and i'll run it record of invoices there we are and now i have a link to my invoice which opens up the invoice for me so the last step is to create a button for the macro developer tab insert under full controls click on the button button let's create a button choose the macro you want to run with this button save as pdf to edit the text click into the button and then you can delete existing text i'll put save as dot pdf to format the button click outside of it right click format control alignment i'll say left for the text margins i have 0.5 on the left margin now we'll have a little icon of a pdf on the button i've got that on my clipboard so i want to group the icon and the button so the icon is already selected i'm going to hold down shift on my keyboard and i'm going to right click on the button group group and to resize the button i haven't got very uniform sizes at the moment if i go to picture format this is where you can set the size so i'll say 1.4 for height and 4 for width right let's create another invoice change the customer name i'll leave the other details as they are if i click on the button you can see it's added the invoice to my record of invoices table and it's created the invoice for me okay that's all i wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up subscribe and i'll see you next video [Music]