Streamline Your Invoice Management with Excel for Management

Invoice management excel for Management

the next one is invoice hierarchy if you're clicking it you are able to see popup window at the moment we have three customers if you click the plus sign here all avaluable invoice ID is a sitting here if you double clicking ID number you are able to see invoice detail double click the seven number seven invoice information as well click here number three you are able to see another invoice information very quickly let me show you how it works let's create new invoice at the moment we have up to invoice number eight therefore we have number nine here let's create a new invoice let's add in supplier info selecting double clicking it automatically in the form let's adding customer info just select one of them I will go with the King Next Step let's add product description clicking it you can select the information quantity add one more box to as well scroll down a bit you are able to add shipping cost also discount the rest is automatically calculating it in addition you can change your payment method and information in here go back click new invoice go to save one you can see ID number nine has been addited customer name is King in save to you are able to to see two new product description here let's go back now you see invoice import changing to 10 the next function is we can bring back existing invoice in for let's go with number nine click call existing invoice the number nine is here let's addit one more product description here I will go with notebook put one as quantity after that click invoice correction if you go to save to you are able to see third one is added it here as well let's go back as next function let's delete invoice come here put nine call existing invoice I want to delete number nine ID invoice clicking it disappearing go to save one the nine is gone in save two the nine is gone here the next part is a saving method it allow you to save Excel file JP file and PDF file let's go with Excel file I'm here number four calling existing one I want to save this one as Excel file in order to define the location please go to input sheet you're allowed to select JPG file location Excel file location go back click save as XF file button here we saved it here as id4 go to folder click id4 zoom out now you can see Excel file of invoice let's go back now I want to save as jpg put here five click existing invoice first all informations available then click jpg click okay let's go and check this is a save folder id5 is here double clicking it you are able to see JPG file as well the next one is printing as paper or PDF file I click here number seven call existing invoice print PDF file I will save it in photo folder file name going to be test 4 click save button let's go and check you are able to see test 4 Double clicking it it looks very nice let's go back the next one is invoice hierarchy if you're clicking it you are able to see pop-up window at the moment we have three customers if you click the plus sign here all avalable invoice ID is sitting here if you double clicking ID number you are able to see invoice detail double click the seven number seven invoice information as well click here number three you are able to see another invoice information very quickly here called checkbox clicking it you are able to see checkboxes in front of ID number click ID five as an example then if you click print button this one will print it automatically from popup window that's all for demonstration are you ready for creating this file let's go then let's get started once you open practice file it looks like this this file consists of seven different sheet the first one is called invoice which is the main sheet this far is form of invoice and there is a various functional buttons for invoice management system tool in case of the contents of invoice here in the middle the number of items are very different depending on invoices but the rest of the item is a single information for each invoices therefore I split into two pieces save one which is a single information like issue a customer name and so on in case of save to it consist of only the item information here you can see in case of ID 1 has two items in case of id2 has three and so on and so on here for current status you can see the statistic of invoicing which you stored the next three sheets are inputs for input sheet here you can choose the storage folder for photos and also storage folder for Excel file of invoices in J column also you can Define different input parameters as well for customer list you can add your customer information and also here you can store the list of your suppliers as well the last item is called item Management in here you can store your listed item which you are selling let's go back to invoice sheet just click the button the next one is a name boxes if you go here you can see name boxes click the arrow here there's a lot of names for specific cells or ranges in the sheet for example if you click here ID it's automatically indic the location where defined as ID in the same manner you can find all different defined in names if you want to change the name boxes names please go to formula Tabs go to name manager you can find the predefined it names here I will close it here up to now is the explanation on precondition of uh practice file as a first step let's start with the receive import which means if you click this button then something popped up that information going into customer information in this sheet here that's going to be here build to in this area in order to go to vva editor please press alt f11 it looks like this the most important two thing is module and form please press plus here you can see main module press plus here you can see custom user form from here in case of the main sheet double clicking it in here we are going to create our functions in case of above for example supplier I already creating supplier list in case of custom list I already preparing here the last one is hierarchy and we are creating the hierarchy form here let's go back to main module first things first let's create popup window if we press the button in case receiver let's go to custom list that window will pop up the name of this user form is called custom uncore list copy the name contrl c go back to main in case a receiver is here contrl V put show sh press tap key it's done next part is let this part is empty in order to save save our memory put set then control V and equal to nothing then enter which means after showing then this custom list going to be set empty let's go back to sheet come down to here I will make it zoom in before starting I want to mention one thing all these buttons are already link with the corresponding subs for example our receive inut cases click right hand Mouse button you can see aign macro clicking it you can see receive Part B or selected it if you click add button here it's directly heading to the sub for receiv Part B let's go back again in order to run just click the button I'm going to click it then automatically the window is popped up now I would like to close this window by clicking this button here nothing happen at the moment let's implement the closing part press alt tab you can go back double click the close button automatically creating new coding area so here we can put unload me that's all Let's test in again alt tab click receive info click close button it works let's go back again if you click the button here inside you can see the name is a change this part called called list box and I put list boxcore clients let's have a little deeper look on this part the first thing is if you see the column count the total number of count is by eight but if you change nine and then it will increase one more columns in case of column hat means it's adding head or not in case of column width you can control width of the columns here for inside if you go font click here and click here you can change detail font size phont style and so on and so on scroll down a bit that's important part called row sources in order to read the information there is a required range the given ranges are following by here so in order to change your input sources you have to change this part okay that's good enough for explanation of list box go back to sheet what we are going to do is following first click the receiver info button then popup window select the customer information which we want to put into invoice form double clicking it then these informations are printing into this area let's implement it that part close form here back to vva double click the the custom list here it automatically showed this but what we need is actually double clicking it so go here procedure select the double click then we have double click function now we can delete the above one the first thing is we have to define the worksheet so I already preparing them custom worksheet as worksheet set this custom worksheet is equal to this workbook Point custom list in case custom list is go back to sheet this is the name of our custom list right go back again simply speaking custom list is our custom worksheet in case of PBA is always read from right to left this become that one next part is with custom worksheet and with under this worksheet we will Implement something let's go back to sheet if you click here you can see customer name company name Street number City up to email these informations are what we needed go back to here let's start with custom name select the name crol c go back in here make the big bracket contrl V this is how we call specific cell with the name box defined it name point value equal to copy this part crl Ctrl V Point list top Open Bracket again list box name Point list again in this case list index top comma zero close the bracket enter the meaning of this part is following let's go back to custom list form double clicking it this is called list box client then we have list here from this list we have list Index this is called list index Zer this is called list index 1 2 3 4 Etc go back double clicking it what it means that the index boox clients list at 01 is defined as custom name so the First Column put them into custom name Let's test it it whether it's working or not go back to sheet click here I will double clicking it then the name Stefan is printing into our invoice sheet however the rest is not printed it so we have to addit index number one 2 3 Etc close the popup window go back to vva double clicking it what we are going to do is copy entire this one crl V go back to sheet the next one is company name crol c contr v then change the index to one go back let's testing it press button double clicking King and refund company correctly printing it close here again go back double clicking in the same manner we can extend it to other inputs therefore I prepare Street number up to email in order to looks nice what we can do is Select the range like this go to indent clicking it then everything is moved into inside after inserting information it'll be great automatically closing it so copy unload me again here go here control V then it will close it okay let's test it erase it this part click receive import double clicking automatically all informations are plugged in so we are done for this function and let's apply for supplier information as well right hand Mouse button go to assign macro click add here we are automatically into supplier sub what we need is a user form name so click here copy the name here contrl C go back here control V copy here control v contrl c here crol v crl c control V before move on next part let's go to custom list and double click here let's copy all of them control C go back to supplier double clicking it delete here control V double click here in case of the close button the name is exact same as before so double click here close button uncore close the same in case of the supplier information we have to click here this is a list box for supplier right so copy here the name and double clicking it then we create this one but we need to have then we copy this part again control r x contrl v here deleting this part let's changing this client part into supplier easy way to do it is following first copy this part contrl c contrl f click replace for replace with part click here select this part crl C put it here contr V after that now let's select this area we are going to change from list box client into supplier under selected area click replace all click okay now everything is changed it close it here after changing list box supplier we still need to change name boxes here as well let's go back to sheet if you click here we can see supplier company name contrl c go back here crl V next one is this one crl c crl v then we extended this [Music] part back click button double click it all expected inputs are correctly printing into sheet so we complete this part as well now let's create reset function go back to vva double click the main scroll down a bit you can see clean up part A and Part B we will start with reset for supplier info part first let's go to supplier user form double clicking it copy entire this area contrl C go back here come to here contrl V the next part is let's delete this part put it this quotation mark the meaning of this quotation mark is empty the cell so we apply this into all other places Let's test it it whether it's working or not grab here and move a bit put CLE inside of this sub after that press f8 button then it will read line by line first company name is erase it it up to website everything is cleaned up press one more time means completely ended it the yellow part is gone now move a bit here scroll up bit for same manner we can apply for reset function for receiver info as well I will do it [Music] quickly now customer reset function has been done as well zoom out up to now we impl this supplier part and customer part still we have to creating for for this part and this part as well then let's continue to implement then click reset button here asign macro add Button as a starting point let's call clean up part and Part B call clean up part A call Clean Up Part B move a bit we finish these two part then we have to clean up these two part if you click here it's called issue day crl C go back control V due date if you click in here it has a formula it means that we don't have to code anything for this part now if you click sub total here it also has formula text as well however shopping cost and other cost they don't have any formula therefore we need code for these two total sound has formula as well we do it quickly go back we edit this two the next remaining part is this item description section when you see description and quantity these are the input for item it has formula unit has formula unit price also has a formula total also has a formula therefore only these two area need to re set if you select this area I already give a given name in name box simply control C go back to sheet let's print it here after that point clear contents which means this area also going to be cleaned as well the last remaining one is this ID number I will move a bit here a bit once we click this set number here then new ID number will popped up for next invoice that will be nice so let's implementing that function for this reset function in order to do that we have to know one name bar just go here search all rows which means basically in save one sheet column A without header that will be our all rows we need this information let's go back to vva I will move a bit here let's start here in same manner ID equal to worksheet function which means allow to use function in sheet Point Max to Open Bracket close the bracket in Max function we need the range we apply the all rows then plus one because of the header we complete reset function as well let's test it go back to sheet let's move to invoice for testing purpose I give a dummy values here I will click the reset button then everything is cleaned up ID number also update into number eight if you go back to save one the last value is seven so eight is correct value we complete P reset function as well before starting new invoice registration function let's adding small error Handler for supplier info and receiver in for let's go to VBA Al f11 click customer list double clicking it in here before inserting something let's clean up that area therefore we a disc code call Clean Up Part B in same manner supplier double clicking it double clicking it by call cleaner part A in addition I believe it is good idea to having issue date in here as well issue date equal to forat now comma I will follow this format and enter when you see the small small letter Chang capital letter which means code are correctly implemented it now let's move on to new invoice registration go to main module scroll down a bit before to go further I create extra sub which is for eror Handler if you look at inside if ID is missing then message box is popped up in same manner issue date description start and so on and so on here there's a second sub which is also for eror Handler if they find the same ID number then it will complain it is already existing in the system scroll down a bit now you can see store and next this is our new registration for invoices for speed up I already create as variables also set into variable for in vva when you see here sheet3 which is a sheet three here and invoice so that's the name invoice this workbook sheet invoice called order worksheet store worksheet one going to be save one store worksheet to going to be save to now let's create new ID number because we are dealing with a new registration of invoice as a starting ID is equal to same same as what we implemented in reset function worksheet function Point Max all rows class number one after defining ID number I am checking whether it's the same ID or not the next I have to create last row and I will show you two different method to Define last row of certain range let's go back to sheet go to quantity select the last one you can see quantity n crl c first what we are going to do is when we adding item description here then we will find the last row of the quantity that will be our last row for item description section let's go back so we say our last low is equal to bracket control V which we just copyed from name Box Point and and Open Bracket and close bracket inside Excel up Point row enter which means that from quantity n and it goes up and then you find the last value that values row information going to be last row let's go back to sheet from here there's certain numbers let's say we are here then the row information going to be 29 there will be our value however from 1 to 20 is are already occupied by other values therefore we need to give Min - 20 then 29 - 20 then we got 9 as last row which means our total item for item will be nine right let's go back so here Min - 20 this is one of the method to calculate last row the other method is I already prepared just select the area go to here uncommented block means already activating it for this one I applied find function and this is the range I'm looking for store worksheet one which is a save one looking at entire column A then find the last value after that that value defined as row num now we have two last rows let's move on in case of registration which means store into save one sheet and save two sheet in case of save one there's no looping in case save two we have looping let's start with no looping case go back to sheet save one no looping information will sitting here when we have new invoice from in row nine in each cell has to be filled in by relevant information which going to be up to column T here go back our relevant sheet is store worksheet one and with right inside of this worksheet we will Implement something we are going to use CES function Open Bracket and close the bracket so here inside we can put row information and column information in store worksheet one in case of our raw information going to be raw num contrl C come here contrl V now we need column index let's go back to sheet basically we will start from column A which going to be one so we put one equal to bracket ID which means that our ID information will store in this worksheet at set location of here that going to be here let's adding issue date as well go back copy entire this contrl V here going to be two we don't need ID inform but issue date right go back our issue date here in the same manner we can apply up to column T it will speed up so I prepare up to 20 which is total amount we complete no looping part let's move on with the looping part store worksheet two first as Next Step let's define a row for saving new invoice information simply go up copy here contrl C come down contrl V change this part into two here we added row number two let's go to save two sheet when you see here it's starting from a to G which means seven columns now we are going to have a looping inside I will use last low for if I equal to last low next I in this looping we will save invoice in information in save two sheet our defined sheet going to be store worksheet 2 and with in this time I will use range then we start with column a right we give row information and row two in our case is looping therefore plus I however we consider header minus one close the bracket if if you go back to sheet that means here we are going to put something the necessary information going to be ID right then equal to ID here the next one will be item number go back crl c contrl v here B clear however in this part is incorrect because go back even though we have a same ID number but multiple item number will be existing therefore we have to apply different method let's go to invoice we will take the value from here right that's what we need let's go back to vva we will get info from invoice sheet using range again and column B and I for looping + 20 close the bracket comma value top enter which means that invoice sheet column B information will store into save to column B relevant cell in same manner we can apply for descriptions the only differen is is only changing column information I will speed up I create up to total amount scroll down a bit now we store the save to with looping part after finishing it go back to invoice sheet after that as a Adder Handler I will check whether there is a missing input or not finally let's reset invoice sheet as well now we complete the new registeration for a new invoice Let's test it it go back to sheet we are going to create number eight invoice supplier import double clicking it receiver import double clicking it for description you just select the item quantity I already prepare all functions automatically relevant parts will be inserting it here I put here I put three scroll down a bit in case you have a shipping cost you can add it here or discount you can add it here as well before clicking new invoice button let's check the current status in save one we are up to id7 in save two we are up to row 17 ID number 7 let's go back click new invoice I can see reset function is working and also invoice number is nine at the moment let's go to save one we have a new ID and all relevant information let's move on a bit up to column T in case of save two we have two items with ID number eight go invoice sheet now we complete a new invoice registration function as well as next part let's create call existing invoice function first we insert invoice number here based on this invoice number we will bring detailed information into invoice sheet from save one and save two sheets let's create it go to here click rightand Mouse button assign macro then click add button it will automatically move to calling existing order in order to speed up I already defined variables and also set as like this in addition in case of calling existing order ID number is important therefore if ID is empty then it will bring the message box if the looking for ID number is bigger than maximum ID number in this case also will give error message like that in addition we will check whether the ID numbers are existing or or not in separate sub lastly I create this one as well scroll up in case of a clean up all sub we give all clean up but also give a new ID for next invoice in case of a calling existing invoice we don't need this part therefore we need to create new reset function without providing new ID number just grab here contrl C go here contrl V we complete this part let's go back so we are here again calling existing order we complete clean up all two the next part is we have to bring the ID into vva code I will use J variable here then we can say J equal to ID this ID number become J the next part is is we have to find a row which matching with J for speed up I prepare the code already the idea is following our new find row variable which is range this will be in save one sheet in column A then we apply find the function find what means J once we find J ID number in column A that's going to be our new find row variable scroll down a bit now we need the last row information from save two sheet I just give dummy ranges which has enough length the next one we need a new last row which we using for looping later on the meaning of last R year is counting the same ID number in the list of column A then return as last row in in addition I prepare the eror Handler as well go down now we prepare the F row variable and last row let's start with without looping first in case of calling existing invoice import is a similar as new registration so let's using existing code I will move to New registration here we have no looping information I will select from here to here contrl C going down again come down here contrl V in case of calling ID we don't need to bring back ID number which already existing in case of due date it has formula no need in case of total amount also it has formula therefore no need in case of calling invoice information will be the opposite case of new registration therefore let's change the order here contrl x issue date remove equal sign put CER here control V put it back equal sign in same manner we apply the other as well let's try one more time with the custom number crl X delete equal sign put CLE in front contrl V e equal sign now speed it up for the remaining part we can change like this way let's move on to with the looping part as a starting point we have to find a new F row I already preparing it and error Handler as well for this part also we can use it from new registration code let's go back to new registration code in here we are in looping part what we need is this part is requir contrl C going down again control V here similar concept as without looping part we don't need ID number and also we have to exclude the input which has formula therefore we delete item and also below part as well as the next step now let's reverse it it in new registration we stay with save to worksheet but it's supposed to be invoice sheet now contrl C control V here copy this part crl C put it here contrl V here as well contrl V our sheet going to be order worksheet therefore I copy this part contrl c contrl v actually we don't need this part here delete delete here now let's change the order select the range crl X delete the equal sign put CER in front crl V equal back here one more time crl x delete the equal put C here equal sign like this I think we complete the calling ID part as well let's test it go back to sheet so now we have a total eight invoices let's check it I would like call number three George product damag company name go back here put three following existing invoice I can see George and product damage company is correctly bring let's check item description for ID number three it has three ID a2222 a2222 so calling existing invoice is work well as well let's move on as the next step let's start a printing function click right hand Mouse button go to assign macro click add button we are automatically in print report up before printing we will check whether there is missing value or not if there's no missing value we start our printing processes we Define the range here in case of a range address it's coming from worksheet input range j22 let's have a look what it is we are in input sheet in j22 which is representing our print range if you go to invoice sheet from A1 to here and H column 54 this is our print range if you want to change the range just go to input sheet change the range here in our code the range of address is automatically reflecting it once our range address is ready we have to put it as print range as you can see order worksheet which is our invoice sheet range address which we just defined it in this part is just a setting looks nice I think actually we don't need this part let's deleted it in here we have to define the range our range be this one right range point print out here top we complete our print function let's check whether it's working or not let's go back for the demonstration purpose I will print it as PDF file in order to do that please go to file in my case I already set as Microsoft print to PDF if you press this button automatically is saving it in our case it will already done by bva code let's go back to sheet I will click button here I will save it in photo folder give a file name which you prepare file format will be PDF file then click save button here in worksheet you can see our ID number changed to new invoice number let's check inside in photo folder I can find find this PDF form double clicking it click this apps here go to menu view zoom zoom to page level then you can see pull page it looks nice as expected let's go back to Excel file we complete print function now let's move on save as Excel file click right hand Mouse button assign macro click add it's automatically in here in case of save as file I already provide full code for you what you have to do is just using it however it'll be good idea to check how it works go back to sheet we need a new information right I will put invoice one call existing invoice go back to vva let's start it maybe it is good idea just rerun it next to it line by line in our case is ID number one at the moment put C here press f8 checking input values all values are correct we read range address now we are going to create new worksheet you you can see new worksheet now I will remove grid line grid line is gone press f8 again now we Define the save path here keep moving Arrow Handler halfy existing invoice information eras it here let's change as value it will take some time so I will speed up put CA here then you can see black dot then press F5 it will skip all the middle part the next part we are going to arrange the format of invoice for saving file press f8 you can see in each column is readjusting it for speed up I will put new black dot and previous one just clicking again you can remove it press F5 we complete the format of invoice providing new ID as you can see new file name is in this location now if you put cussa here you can see the location of file and the file name will be like this ID number onecore invoice. excl file next steps save as new file and close the file we came back to our original Excel file then reset function is activating now lastly message box like this click okay and press f8 again we complete the code let's remove this black dot click here now we just finish save as Excel file code let's check it how it look like go back to photo folder click here you can see ID number one double clicking it look like this zoom out you can see we are perfectly creating nice Excel invoice file as well closing it let's move on save as JPG file now click right hand Mouse button go to aign macro click add button in this case also I provideed full code what you have to do is just simply using it the concept is a similar checking missing values Define variables then we Define the range address which is as similar as print function and Excel file saving we save the store folder now let's go back to sheet click here go to here store folder that will be here so inserting your storage link you are able to get jpg form as well go back scroll down a bit the main idea is a similar as before so I will skip up to this part now we store as jpg there is extra sub below but I will skip that part as well clean up and finishing it let's check whether it's working or not go back to sheet calling existing ID so I will put two here call existing invoice click save jpg as you can see jpz order folder ID number 2or invoice. jpz file is stored click okay let's go to photo folder come here jpg order double clicking it you can see id2 here double clicking it make it bigger it looks very nice let's go back to file now we complete the three storage method from printing PDF file jpg and Excel file let's Implement next function which going to be delete invoice click right hand Mouse button go to assign macro click add we are in the deleting sub for speed up I already defined variables and also defined the worksheet here the next part our ID value in worksheet has to be in the vva code therefore I will use J parameter here the J value become our ID number let's start with delete without looping part first we have to Define r first value one which means it will be in save one worksheet column one means in column A then we apply find method what means J which is ID number it will be the first matched J value in the column finally we will return the row information of that area that will be our Row first value one in case of without looping let's go back to sheet in save one in each row representing one ID number so we are going to delete just only one row in the end let's go back in here we are applying deleting code it has to be save one worksheet Point row tab Open Bracket using Row first one value close the bracket Point entire row Point delete enter we complete without looping part let's move on with looping cases stored ROM we'll use it for worksheet save two the meaning of stored ROM is following we are applying count if function in save to worksheet under this range with a J value counting them that will be our store runam we need a total number of looping for new data I will use new ROM crl C here crl V equal to in invoice sheet and Excel up which we applied before row minus 20 in the same manner as roll first value one we create roll first value two in this case it's going to be save to worksheet the rest is the same now we prepare all necessary parameters let's start looping part if Row first value two which is the the first matched J value in column A in save 2 worksheet else and if in this case we are going to have a looping in L case we need only message box and act this sub then close and if in inside we are creating another if statement if stored num which is from save the data in save two worksheet compared with invoice item description number you can see as another eror Handler checking point we can consider else cases and if in case of Al cases which means the are not equal let's give message box now here we are going to create real looping or I equal to new ROM next I in save to worksheet Point rows top Open Bracket rows first value two close the bracket Point entire row delete clean up here we complete the delete part as well let's test it let's go back to sheet I will choose number six this time call existing invoice I would like delete this part click invoice delete button let's go to save one number six ID is disappeared in save two number six is gone here as well our invoice deleting function also complete let's move on invoice correction part the precondition will be already we complete invoice form here or calling existing invoice then we apply invoice correction for our example I will use invoice one call existing invoice now let's correct this invoice click right hand Mouse button asign Max click add this is our correction sub for speed up I already prepare for parameter set three worksheet in this case I also include K parameter as well same as before let's bring ID number into vva code J equal to ID then I will use the same formula Row first one value in delete Sub in same manner let's start with no looping part first in this case we just updating existing information into new information which means the idea is actually same as new registration right let's use new registration code then this is our new registration scroll down a bit this is no looping part select from here to here contrl C go back in Corrections up in no ring part control V we complete this part let's move on for with looping part scroll up a bit in case of a store R num same as before count if functions I us it it in this case let's use the list sub code of it here scroll down from from here to here contrl C from here control V let we prepare store ROM new ROM Row first value to R ready in case of a correction there are three possible cases the first one is new corrected information will be smaller than previous cases which creating m rows then it is required to remove some of the rows case b new correction information will be larger than previous one due to shortage of rows it require inserting some rows case three new corrected information will be same as previous one then nothing to do right for case a new information will be smaller than existing one for for case b as if new information is bigger than existing one last one is else and if for Case C there's nothing to do go back here case a let's use new parameter k k equal to store ROM minus new ROM for case spe K is equal to new ROM minus store ronom now we prepare new K parameter for three different cases let's start looping part in case of case a for looping we apply I equal to K next I I will copy here control V here as well in case of deleting some rows let's use theet sub code I will copy here contrl c um here contrl V now let's move on to case b contrl c this part crl V here we have to insert some rows this part change to insert by using K parameter and if statements we are able to control proper rows as next step we have to to store corrected information into save one and save two worksheets which is similar as new registration right let's use new registration code then here is new registration here is with looping I will copy from here to here crl C scroll down again control V in this case there is no row number two so let's replace it this into Row first value to select this area crl F click replace copy this one crl c crl v here let's select the range from here to here replace all click okay let's test it it whether it's working or not go back to sheet in case of invoice one let's adding two more items I will click invoice correction let's go to save tool something is wrong here let's go back to code is actually this part we just copy paste it this one has to be actually new R num let's try again I will fix it these two part as same as before select number one again calling invoice we have two items in this time let's change custom information as well for checking receiver input change to King here is King now adding and cck invoice correction for save one now it's King for save to in terms of information we got correct information here however we need to some update for format here contrl c r s format okay here also control C Al es format okay now let's testing delete correction cases we have three IDs let's make it one go to invoice put here three call existing invoice make them empty here delete now we have one item invoice correction go back to save tool in case of ID3 we have only one item go back here we complete invoice correction as well let's Implement next function it's going to be invoice hierarchy click right hand Mouse button go to assign macro click add button here we are in show train tribute Sub in here we are going to show our new user form which is hierarchy click here hierarchy we will try to appear this user form first copy user form name hierarchy go back to our sub here control V Point show top Let's test it whether it's working or not go back to sheet clicking it now let's create close button close it here go back let's go to supplier user form contrl C close button go to P Mary click inside here control V put somewhere here if you click this is the name of the button button uncore close click the button let's add close function which is unload me and I would like to go to invoices up let's test it again click here click here it's working click one more time the next part let's create here hierarchy we call it triw let's go back at the moment we are inside of hierarchy use form double clicking it then we are outside of user form in order to create triw we need a new setting click toolbox here click right hand Mouse button additional controls scroll down in here you can see Microsoft tribute control version six selecting click okay now you can see treeview icon clicking it then you can see small plus sign from here you can drag it make it tribute size as you wish if you click tribute we are in tribute you can see font here there's just three dots clicking it you can change your font font style and and size I will put 11 for font size for the name of tribute you can change it it as you wish I will just stay with the tri here double click the tribbie you are inside of the tribute what we are going to do is after user form is activate we will call new sub which is in main module the name is generate trew control C here go back double click scroll up call tribute Next Step let's create generate tribute double clicking it here for the speed up I already create the parameter set here and also some ranges go back to sheet what we are going to do is we are going to select only unique name of customer names which means we have two kings two George two Richard so expected unique name going to be one king one George one Richard in this example under these unique customer names we will give corresponding ID numbers as well let's go back to BBA let say our order sheet going to be save one worksheet under this worksheet scroll down here and with under save one worksheet we will Implement something let's define last row also adding add Handler here our last row is belong to column a means this is our last row eight if our last row is smaller than two which is two is here smaller in that case we stop our sub that's what it mean for this one the next part we are going to reset our hierarchy tree view it means that in hierarchy user form the tribute which we Define and the node will be clear which means empty then Arrow Handler after that we are going to call new sub which called add unique values if you scroll down you can see add unique value here let's go back to sheet in this column what we are going to do is we looking at this column D after that in column AB we find unique names only and printing it here for testing I will run this part quickly app 8 here put black dot here let's move a bit here let's see whether it generating the expected unique values press F5 because of here we went to invoice sheet but we can check it I will reset this one remove black dot let's go back to save one sheet now you can see only three unique usernames are printing it this is our input values for tribute under these names we will prin in the corresponding ID number numbers let's go back we complete up to here for this part it's a bit complicated I already made it it so just using it in here we need a new last row which going to be like this go back to sheet the last value going to be that one number four so last value of column AB error Handler as well let's start our first looping part the looping will base on on these three numbers for cost row equal to two to last result row next c row right under this looping we are going to deal with tri with hierarchy which is our user form then tribute then note and withd before to go further let's revisit node part double click here each line going to be node this is called parents under this is called child then we can create multiple child as well in real life terminology you have a shopping mall it has four story building let's say we have a second floor then shopping mall going to be our parents child going to be second floor on second floor there are many shops one of the shop called shoe store then on second floor going to be parents the shoe shop going to be child let's say there are multiple shoe stores on second floor in order to distinguish each of them we normally called address but in computer science that's called key which means that each node has its own key so the key is equal to address let's go back in here we are going to add our key which going to be save one worksheet range going to be column AB row information going to be c row value after that comma we are adding text information text is equal to save one worksheet range going to be same as that one so the value of text going to be this one the key is going to be this one this sent allow us to choose three customer names Let's test it it whether it's working or not go back to sheet I will press invoice hierarchy button now we can see three customer information as expected let's create a child under these names close it which trigger us to start second looping as usual we are using four statement for order row is equal to last row the last row is coming from this column A the last values complete four statement in here we need to Define two parameters one called custom name the other one called order id id from column a actual name from column D now we are dealing with the tribute so under tribute and withd in case of a child node we can start with ADD relatives which is equal to customer name comma relationship going to be tww child comma we need a key which is order ID we Define here column a ID info comma text which is order the idid name again let's check whether it's working or not go back to sheet click here and double click the name then corresponding ID numbers are showing here there are three there are two tributes are working very well let's go back to vva I would like to make some checkbox in front of each note let's do this part if you go to toolbox there's called checkbox clicking it plus sign you can create something like this in case of a caption let's change it like this checkbox activation in case of a phone color selecting it here I choose yellow in case of back Style clicking it transparent font selecting it bold 11 okay selecting it make it bigger in case of a name let's change to checkboxes double clicking it and please add this code me means Hy in tribute checkboxes equal to checkboxes value we complete the checkboxes part as well in addition you already notice that I already created a triw click part this sub allow us to see corresponding invoice JPG file under our store folder Let's test it both check boxes and this part go back to sheet click we have checkboxes then we have checkboxes disappearing double clicking it check boxes as well if double click here the corresponding invoice JPG file is popping up id7 as well another button below here if click the button the JPG file will printing it as well close it let's go back to vva click hierarchy Auto toolbox click here drag it then let's give a name I will put this print one invoice go to font make it bigger in case of a button name I already create the sub just to choose this one command button underscore print double clicking it automatically it will go to the same sub which I created before the important part is the name of a button has to be in line with this name here in case of a command button for print is already done therefore there's nothing to do from your side let's go back to user form click Trev viw here please scroll down here you can see line style clicking it change into first one then we have a some line is appearing actually that looks better Let's test it again go back to sheet click invoice hierarchy you can see plus sign here just clicking it it looks more intuitive we complete this part as well close it as the last function please go to current status in this sheet you can see status of our existing invoices if you click this button it's automatically updating it click right hand Mouse button go to asign macro click add button for speed up I already complete the entire code for you if you scroll down a bit in this area you can change the format the name of a printing values finally we complete our invoice management tool thank you for watching tutorial I wish you have a nice day [Music]