Create and Manage an MS Excel Bill Format for Administration Effortlessly

Ms excel bill format for Administration

[Music] hello friends welcome back my name is pk in this video you will learn how to create a beautiful invoice management system in microsoft excel so as you are seeing my screen i have created this invoice management system to show the form you just need to click on this button as you click on this button it open this form wherein you can start capturing the invoice information let's say if you want to create the new invoice you can simply click on new as we click on the new invoice automatically it will create the invoice number and the invoice date by default it will take today's date however you can change this date using this calendar icon as you click on this calendar icon it will pop up this calendar form and from here you can choose your relevant invoice date now once invoice number is created you can start adding the items in this invoice if you remember the item number you can simply put here item code or item number and hit the enter as you will hit the enter it will fetch the item name default quantity it will take 1 however before entering you can change it let's say i want to add the product 2 as 10 quantity it will take the default price available in the product master however you can change it as per your requirement once you will change it you can just hit the enter or you can just click on this plus icon as you will click on the plus icon this product is added in this invoice at bottom you will see the count of items as well as total amount so here it is taking the total amount you can choose the item from here also let's say product five and you can just change the quantity if you want to change otherwise just hit the enter you can see your product 5 is available here all right friends if you will add the same product again let's say product 5 and this time i am taking three quantities we have already added product five where in quantity was one so if i will add the quantity three hit the enter it will not add new record however it has increased the quantity as 4 all right and you can start adding and if you want to delete any record from here you can simply select and click on this minus icon as you will click that will be deleted you can see the total amount and number of item count here once you are satisfied with this you can simply click on this save invoice as you will click invoice is saved now we are aware about the invoice number it was invoice number three if we want to search and edit this further you click on search button as you will click on the search button it will ask the invoice number let's put here three and just hit the enter so you can see these are two items which we have added are reflecting here now you can add more item here okay so we have four item now if i will say now this is it if you want to take the print out or if you want to create the pdf you can do that also let's search that in voice number three there are four item i want to take the print out of this let's click on print invoice as you will click on print invoice it will open this invoice number in print preview mode you can change the format as per your requirement but here i have taken this simple format from here you can just click on this print button and you can take the printout now i'll show you again if you want to create the pdf of this invoice so that you can send it on the email or if you want to share it on whatsapp or anywhere so you can click on pdf as you will click on pdf you can download the invoice as a pdf okay so here it is available as a pdf you can save this wherever you want using this save as option all right friends if you want to delete any invoice let's say i have invoice 3 i am saying i want to delete this entire invoice you can simply click on delete invoice button as you will click that invoice is deleted now if you will try to search invoice 3 it will say invoice number does not exist okay now i'll show you the data and the other seats so we have item master here you can put your code i have given here code 1 2 3 10 15 but you can add more you can put your actual item name and here you can put the price of that item so it will pick up the default price but you can change it while you are adding to invoice this is sales seat in the sale sheet it will create the auto invoice number and it will add the items like in the invoice number one we have these items okay then this is invoice format you can change this format if you want to add something here some name or some more details you can do that but i have taken this simple invoice format now quickly i will show you how i have created this and i will show you the code of this let me go to the developer tab and visual basic we have one man form here which is in voice form another form is the calendar form i have already explained how to create this calendar in excel bba i have created the separate videos on this topic you can see our calendar related videos in those videos i have explained how to integrate this calendar with any of the bba project okay now here we have form invoice okay this is when form we have taken here different text boxes you can see these are the different text boxes and few command buttons these are command buttons we have just changed the colors then we have these labels here and then we have these command buttons we have added few icons like save as print and pdf so you can take the images from google which is here then we have this list box here now i'll show you how you can design this form so let's add a form here just resize it as per your requirement and right click and give some background color and some formatting so let's take the back color let's take this color a special effect let's take this special effect okay now i will take an image here and i will make the title using this image so let's put this here right click go to the properties just take none in the border style and back color we will take from here let's take this color so these are few custom colors which i have added you can add your custom color here using the right click and you can define the color here whatever you want using this or you can put your rgb code and you can click on add color as you will click that color will be added here in your palette okay so let's select this one now i will add a label here i will take invoice management system and you can write it go to the properties we will format this and we can take some other font let's say area rounded empty pole let's keep this here and the back color that style let's take transparent and the font color for color we will take white okay so it will look like this you can keep it in the center of the form so go to the format and center in form horizontally all right so this is our title now to design the other text boxes you can simply take let's say label that label we will take here invoice number let's keep this here and then i will take a text box let's create a text box here right click go to the properties and here we will take single border and the border color we will take this color and this is text box one okay skip this here and select and copy this will be for it boy state let's make a copy of this and just paste this here this will be item number then we will take a command button so let's take here a label item name and go to the tool box and let's take a combo box here right click go to the properties here again style we will take single since this is combo box so we will take drop down as list not combo and we will make the watercolor the same as theme color this one now we will make a copy of few text boxes and labels so this will be our quantity smaller copy and this will be for price all right now we will start adding the buttons so let's take this button just click here this will be for plus or add item given here caption s plus right click go to the properties and go to the font make it slightly bigger okay and then you can change the color so for color let's take white this one and the back color we will take dark green color okay and we will keep this here make a copy of this and paste here and here you can type minus to delete minus and we can keep the red background here let's take this red or let's take this right all right let's keep this here okay now we can align this properly select these all right click go to the align as middle and similarly here you can first align them left these two and now align these all as middle and now we can add a list box here so just click on the toolbox and let's take this list box here and just drag this list box here like this and here we will display our data and go to the properties again we can take here border style as single and let's take the border color as the theme color you can take some background color also if you want let's say this grey color all right so this is our list box now we will add few buttons here like we have given here take few labels first let's take this level this will be item count right click go to the properties we will change the font size little bit bigger and bold okay and let's copy and paste here and we will keep this here right click and properties for this one we will change the name as lbl item count all right and default we can put here 0 or blank now let's make a copy of this copy and paste here this will be total amount place of 0 and go to the properties and we can keep the name as fbl total amount so basically we have added four levels this is just to show the caption and this is the actual level wherein we will show the values and this is the actual level wherein we will show the values and we have renamed this as to lvl total amount lvl item count okay and for these two we are keeping as it is now we will add few command buttons let's copy this one and paste here and let's keep this here and paste next one and skip this here and paste again all right so we have three different buttons now here you can add the images go to the properties and in the picture you can put actually image if you want to create the relevant images you can go to the powerpoint presentation and you can insert the icons from there you can download the icons from there or you can download the images from google also i'll show you one example i want the icon for save as so i'll type here save or save as and here you can see this can be the relevant icon so i will insert this and i will make it slightly smaller like this and then i can change the color as per my requirement from here or you can change the color from here also so let's say i'm taking this color now i will save this as let me save it as save as a picture and let's say picture one and i'm saving in the pictures let's say and i will save it as a jpeg file save as now i'll come to the bba project and i'll go to the command button and i'll go to the picture and i'll take that picture one you can see it is available here the back color see if you want to change the back color as white color you can do that also change it as white you can remove the caption from here you can put the control tooltip as save as so somebody will hover on this button it will show as i say similarly you can add for the pdf or for print out okay the same thing which we have done here all right now i'll quickly show you the coding of this project so let me double click on the user form so this code will be executed when we will activate this user form i have taken them i as integer and the such as worksheet so we have taken the worksheet for the item master so first what we are doing here in this combo box we are loading the different items on the activation of this user form so we are using a loop here for i equal to 2 2 and count of the rows on the item master okay and we are adding this b and i so i'll show you let me close this i'll show you item master so we are taking the count of column b and then we are running a loop from row number two till the end and it is adding all product in the combo box all right and before starting this it is making the data sheet clear so i'll show you the data sheet also just right click and unhide a seat we have a datasheet so here we show the data of selected invoice or new invoice okay so we are making it clear we are keeping only headers then we are calling this refresh listbox data i'll show you this one also so let's go to this refresh list box data this is refresh list box data refresh list box data sub procedure is to show the data in the list box so here we have taken ssh as worksheet for datasheet and lr as integer and we are taking it for the last row on datasheet and here you can see we are using the application.worksheetfunction. of a to a column of data so whatever number of records available in the data it will assign to lr if no record is available then value of the lr will be 2 because we have header here so always it will come one if no data is available so we will consider it as 2. now me dot listbox1 dot column count we are taking 4 so we are 4 we have the 4 columns then column heads we want to display the column heads and we are using the row source of the list box as a 2 d and the last row all right parallely we are changing the lbl item count and total amount so here item count we are taking the number of records minus one which means the header we will not include the header and we will take the count of or actually cost that will reflect on item count label then we are using here application.worksheet function and sum of column d this total we are taking the sum of this total okay so this is refresh listbox data now quickly i will show you some other things as we will run this first we will create the new invoice so let me show you this new invoice here so what it will do it will put the value in the text box one which is invoice number text box whatever the maximum invoice number available in the sale sheet here it will add class 1 okay it will add class 1 that will be new invoice number and default date it will show today's date and you can use the calendar to change it okay and from the data sheet it will make it clear because we are going to create a new invoice then it will show the data in the list box which will be header in this case okay then on the search part let me go to the search in the search we have used application.inputbox on the application.inputbox i have already created a separate video on this topic you can see that so input box be used to take input from the user okay and here we have used the type 1 which means we want to input as an integer or as a number now here we are saying please enter the invoice number user will give the input as an invoice number and we will consider as id okay now in the sales seat first we are checking whether this invoice number is available or not so we are using the countif function we will let's say if you have given the input as 3 now 3 is not available here if i will use the countif of 3 it will return 0 so if it is returning 0 then we will say invoice number does not exist and it will exit otherwise in the text box one we will put the id which is the invoice number this one okay id is the input this one now we are using a vlookup function in this lcd once we have the invoice number let's say it is 2 we will use the vlookup from here and it will return the date from the column 2 a to b column two we are taking the variable of the data sheet and removing the filter from the datasheet if it is there then we are deleting all data from the datasheet okay now what we are trying to do here for that particular invoice number we will copy the data from the sales c and we will put on data sheet i will show you here in the seats so let's say if you want to search the invoice number 2 then what we are doing we will put the filter of invoice number two then we will copy this data only and we will go to the datasheet and we will paste it here and then we will remove the filter from here so here in the bb code we are using the ss dot used and auto filter on the column one and using this id which is invoice number we are copying the data from c to f and we are pasting on the data sheet and then we are removing the filter and now we are showing the data in list box all right now this is for the deleting so here to delete an invoice we are putting some validations here you have put the invoice number in the text box one and then we are counting here that particular invoice number is available or not which you want to delete and then once it is available then we are using the auto filter in the sale sheet using this textbox one value which is the invoice number and we are deleting all the records from there then we are removing the filter and then we are showing this message this is for the delete invoice this is item number and here as you enter the item number and you hit the enter then we are putting the code here you can select it from here and what we are trying to do here first we are checking if key code equal to 13 key code 13 is used when you will hit the enter the key code will be 13 that means if you have hit the enter then this particular piece of code will be executed now here what we are trying to do first we are checking the textbox three dot value should be numeric which means you should enter a numeric item number okay if it is numeric then we will use a b lookup from item master so using this code two names we will get the name using the code using the lookup we have used here vlookup all right so it will return the item name in the item name combo box okay so this is item name combo box as you put any item number and hit the enter it will select the value of this combo box automatically as item number from the item master by default quantity it will take 1 and the price will come from the item master so as the value of this combo box will be changed so here you can see we have used if the value of this combo box will be changed it will take the combobox five dot value which is the price this quantity so quantity will come by default one and price will come from item master all right now as you hit the enter on the quantity then if key code is 13 then it will run the same command as command button one underscore click which is this button okay now here we have put some validation then in the data set so basically first we are adding the data here in the data sheet only not in the sales sheet in the sales seat data will be added once you will click on save button okay so first it is adding the data in the data set which will be temporarily available there because finally it will add the data into sales sheet as you enter so it will check if it is already there product is already there then it will increase the quantity okay otherwise it will add a new record there all right then it will put the total amount quantity multiply price and it will update the price and the product name here okay product name price quantity and the multiply of these two all right so it is adding this information then it will show to list box now using this minus button it is the similar code the only difference is it will delete that particular row first it is matching using the match function using the product name so let's say if you want to delete particular product it will get the row number using the match function so we have used here match function so this is row number and that particular row will be deleted from this seat okay using this button once we have added the data here then we can click on save buttons it will copy the data from data sheet and it will paste the data on sales sheet but if data is already there so let's say if you are updating existing invoice that invoice data will be available in the sale c so first it will delete that data so here you can see we are checking if countif on a to a of meter text box one dot value which is invoice number is greater than zero then it will put the filter of that particular invoice and will delete entire records of that particular invoice it will remove the filter then it will add that data so it is copying the data from a to d from datasheet a to d all the cards and it will add it in the last of sales sheet all right and once it is added on from these column c then we will fill the value of column a and b on the column a it will put the meter text box one dot value which is invoice number this one and b column invoice date okay and then it will delete the data from data seat once it is saved it will delete it okay then it will show in the list box all right now i'll quickly show you how to show the print preview to take the print here so double click on this button and here we are putting some validations first and we are making it sure some data should be available on the data c so that we will make sure we are analyzing a particular invoice and in that invoice we have some records or some items okay then we will show the print preview then we are calling this fill invoice detail sub procedure i will show you this sub procedure also this is fill invoice details a procedure is there it is quite simple we are taking here two worksheets invoice and sales so we have here sales worksheet and we have here invoice box this is simple template so first we will delete all data available here so we are making it clear from a7 this is even till a26 okay entire row we are making it clear then we are using the auto filter on sale seed for that particular invoice and we are copying from c2f from product name till total and we are pasting on the a7 here okay and then we are putting here that invoice number and date on b3 and b4 here okay once we have filled this information then let me show you once again then we are using as print preview we will hide the form and we will use this seat as print preview all right as you will click let's search invoice number one click on print preview it will hide and it will show this point all right okay now quickly show you how to convert this pdf so it's the same code only difference till here it is same code now only difference is in place of print preview we are taking ssh as worksheet for invoice ss dot export as fixed format excel type pdf we are putting here the location for the temp folder and file name as invoice number and whatever invoice number you have selected in the text box and we are opening it after publish true so it will open it let me show you let's search for invoice number one and click on pdf so it will convert to pdf all right i have created a module also in the small macro to show the form i have used fram invoice dot show false on this so model and then i have created a button here which is simple rectangle so you can just insert a rectangle from here shapes this rectangle and then you can design it like this you can take some special effect from the preset like this you can put the text also whatever you want some text right click assign macro and then you can select this workbook and that show form will reflect here as we will click it will show that form alright friend so friends that is how i have created this one i hope you have enjoyed this video friends if you like this video please hit the like button and please subscribe my channel to get the regular update of my videos thank you so much for watching [Music] you