Effortless Invoice Management Excel for Procurement

Invoice management excel for Procurement

hello everyone welcome to my Channel today we will see the supermarket invoicing and the stock management system in Excel VBA here we are going to see the employee database custom database Supply database also the expenses we are going to update in a database especially we are going to see a vehicle management system all together in this invoice management system the procurement data entity will be separately so we can see everything in one Excel project so uh this is the Excel project I am going to open now we will enable the content after enable this content the Excel file will be disappear it means application will be disabled so then login menu will be appear so we can type the username password username is my name and password is one two three four you can trust in the key or you can click the login button I will press the enter key because the login command button in the properties the default is true I said that this to rule so we can enter that here you can see in the menu here I put the supermarket name so we can send this as per our requirement so here you can see these are the command buttons for the sales invoice procurement in the procurement we can enter the procurement also the procurement history will be served in separate seat also here we can add new products and new categories here in this command button can enter the expenses apart from the procurement here we can open the Excel file other than the open Excel command button we can see the Excel file only through this command button we can open the Excel file then customer database Supply database employee database and the Vehicle Management Systems here when you click the this exit button only we can exit from this Excel file now we will open this sales command there's a user lock into sales invoice as well for each commands we have login users so different persons can candle each kind of database here I use the same username password for each login we can change that ingly foreign you can see this is the sales invoice here you can see the command button print invoice and next customer after finishing their first invoice you can print the invoice then click the next customer when you click the next customer the invoice detail will be served in the database then the invoice will be served as PDF and it will be saved in a particular folder then it will be sent to the customer as email then the new invoice template will be prepared for the next custom so you can view the invoice you can type the invoice number here in this text box then you can click the view invoice the old invoice you can view then you can reset then you can cancel the any invoice if you want to cancel any old invoice you can type the English number here then you can cancel here cancel that invoice ingly the quantity will be deduct or at into the procurement main storage then through this you can go to the menu then you can come back to sales when you come back you have to enter the username password okay here's the number one when you initialize the use of home you can select the category from this category drop down few categories I added here we can add the categories in the new product command so we can select one of the category or else we can type the first letter the name will be up here then press Tab Key it will go to the next text box here we don't want to type the product ID and unit based on the selection of the product from this combo box the product ID and unit will be update here automatically I use the vlooker function here so when we add the new product in the add new product command in the database the product ID and unit will be updated here so when we select the product after selecting the category the product ID and the unit will be updated automatically so you don't need to type the product ID in the unique then here quantity we can type for the unit price 50 then discount you can't give any discounts then the use of initialized the discount will be zero here then you can type any discount you can see after deducting the discount the total price will be here then press the tab key that command button will be selected you can enter before that you can't keep the quantity unit price and discount empty based on these three selection the total price will be updated automatically so you can't keep these three empty or you can't type any known integers here see this is empty I am pressing the tab key so there's a message quantity must be integer this is MD same and you plus when you leave the discount MD that will be zero then the message will be here also you can't type any known integers here okay right so you have to type integers in the quantity unit price and the discount then the total price will be here I will press the enter key now see we didn't start any procurement date for this product so we can add this select this product so we will select a fancy item after selecting a fancy product the product ID and unit will be changed okay okay now we will add this okay there we have few products okay it is added now we will select one more product ring item okay Coca-Cola to okay there are fluffy product okay now we will add few fancy item okay fancy item okay again built when we type the B the product will be appeared the product name will be here okay we will type 40 products okay when we add this oh lowest talk available please confirm to add available quantity initially there was 32 quantity I know um so here we added let's click OK here we add a whole quantity so they are both remaining 28 so now here we request 40 but there was only 28 so there was a message low quantity I want to add the available quantity so available quantity is 28 and it is added here now we will write this again in the same product belt then we will write 10 okay there's no stock so there's message no stop so all type of message will be there if there is no stock the message will be there if they are slow stock then we can add the available quantity or we can ignore if there is no procurement made yet then there are real message as well if we type any non product which is in the database then we can't do the there is no stock there say we will write something else Apple that is not in the a procurement storage view light there's no storage which are the products in the program and storage Source will we can make the sales okay now you can see this thing we have to fill these text boxes before making this entry so we can type the PO number here the date will be automatically the today's date will be here we can select the terms here from the customer database we can select any customers their details will be here when we select one of the customer the customer address phone number and email will be there if you are not going to select any customers then this will be anonymous and the the owner's email address will be here we can change this to your owner's email address now if you want to make any changes from this one from the second line say purchase two Coca-Cola link items so you want to purchase only one so you can select that see this line is selected after selecting that you have to type how many quantities you want actually I need only one if you want one then you have to fill these three quantity unit price and discount this is one in the unit price if there is no changes this one also you have to update 300 and let's say if you're going to change the discount one it will be test so actually what we are going to change from the quantity or unit price you have to change all tradings quantity unit price and discount if you change all three things only then then the total price will be correct actually you are going to change the quantity only but you have to retype the unit price and the discount as well to get the correct total price you don't need to add this now we will remove this you don't need to add this if you add this there will a message WhatsApp product so in this case what we are going to do we are going to edit so we will click the edit button there will be message as you so you want to add update if no it will not be updated if yes it will be updated see the quantity is changed and the discount also changed total price also changed updated right right you can do see when we update each line the total discounts also updated here automatically also that grand total also here updated one by one so now we can print this invoice here not here here it prints this invoice the previous data will be appear you can change the page setup what what you want to print or if you want to fix FH setup connecting with the printer you can fix that now I will print this as PDF it is not connected to printer so I will print this as PDF I will save this in the desktop invoice number three it is saved we can check this it is in the desktop you can see invoice 3 see the invoices here inverse number I didn't type the pure number that the terms and this is the customer details because I didn't select the customer care before selecting the first product we have to type these details from into this text box then only this will be appear after selecting one of the product if we select this there will not be updated [Music] it is printed these old products are detected from the Master Storage now we can click the next customer this inverse data is updated in the invoice record also you can invoices prepare to send to mail to the customer I didn't select the custom if I select customer the customer name will be here okay you can see invest number is three the subject will be automatically or the email body please find the investor attached you can see the invoice is attached here the same in base is here right now this is in the display mode if you want to send directly we can update the maybe a coding ingly thank you now I will cancel this okay now it is ready to the next customer now the English number next invoice number is updated here number four and the number is one here updated discount is updated to zero this is updated to current date this is to zero this one is anonymous we can select the customer as well for the next one for the next sales we have to fill this first you have to click the menu button when you click the menu button you will get into the menu here this is the procurement you can see login username and password this is the procurement NT this is the procurement storage all products category product ID product unit and quantity will be updated here automatically when we enter the details here see for each product there is only one line one record if you update few candles shop light items then it will be added with this 50. correct this is a page one from the procurement ENT and we can change anything yes you can see here we have Bim there are 10 products so we will change for the product so we can type the product name here the product is disabled this combo box is disabled so we have to select the category cleaning item after selecting the category the product combo boxes enable so now we can select that when you click the search button you can see there is 10 quantity of Bim there so like which we can find how much of quantity is in the procurement storage now we can preset this click the reset button it will be reseted right also we can edit this only when we type data from the supply invest we can type the supply in base if the supplier name is in the supply database then we can select if it is a new software we can enter the supply details in the supply database so this is one of the supply we can select the procurement date select this command button the calendar will be there then we can pick a date let's say today 24th June right 20 of June we can select the the years and months from this drop down so 24 June after closing this the pick date will be here we can type the supplies invoice number let's say something bus after that we can type each products from the supplies in basically select the category select product then the product ID and unit will be updated automatically well the same as in base we can type anything okay we can add each one by one okay after typing all product finally we can click the serve button it will be served in the procurement storage okay after serving that it will be resetted okay let's type cleaning item from one of the supplier okay different this this was purchased in fourth okay invoice number Supply inverse number cleaning item okay let's say bin here we are typing four okay you like this after adding all products from that invoice number we can serve this okay now we can see in the second seat program storage page we added four beam they are already they have a 10 now 10 plus 4 there are coding okay also we can change from here as well training item here beam okay we will change this okay the total is here 14. now we can reset okay when we type each product I said in the invoice we can select that line then we can edit by changing the quantity unit price and the discount okay by changing that then we can click the edit button it will change ingly result we can go to the menu if you click exit the Excel file will be close so what we have to do we have to go to the menu the next one is new product the same username password I put here my name is the username and the password is one to three fifths here we can add new product first we can select the category sharing item automatically the product ID will be here after selecting the category the next product ID will be here automatically here I initiated the post product ID from Thousand and One first product ID is Thousand and One the first three digits from the category name dri so if you are entering a first product from a new category then the first product ID from first three digits from the category and the thousand one will be here okay then we have to type the unit let's say new product a ring item A1 apple juice okay then in addition number number of bottles then we can add this okay it is added now we will see this one apple juice we added they are in the product database we will set this if you want to add any new category there are many categories there let's add a new category I will type A A that's a new category we can add this it is added we will check it there okay I'm to menu before second this expense one now we will open this Excel message box the reconformation again login to Excel file there's a message as well for asset owning okay you can see lastly we added new product a new category you can see the new categories added here that is AAA so you can see this is updated here new category also here in this table as well under this column under the category the product will be updated here when we add the new products also you can see lastly we added this from the link item apple juice you can see here I will use this is product one product two product three Dr 8003 then they are 8004 the next product ID created VR 8005 also the same product is updated under the link item from this only when we select the category in the product drop down this product will become now we don't need to close the Excel file and reopen it we can select the use of home menu login menu from this customize quick access toolbar this is the icon I created you can see from here you can log into the use of home menu when below login from here the Excel file will be behind the user form if we don't want to see the the Excel file behind this user form now we can exit from here the Excel file will be closed then we can reopen it when we exit from each one each time the Excel workbook will be saved so this username and password next here we have to see the expense this is the expense details we have here we can type any expenses instead from the procurement thing there we have vehicle expenses in the different database so apart from the vehicle related expenses even we are the purchasing of vehicles of fuel any maintenance anything apart from the all expenses will come under this database so we can type anything sorry building renovation okay we can type the building name building one or whatever then we can select the date from this calendar it happened on June 13 depicted will be here when we close this we can type the actual cost but but the cost for this renovation 500 000 then we can type the invoice number which is from the service provider invoice numbers okay then we can type the purchase order we can add this now now it is added into the database okay after writing that it will be resetted now we can change anything let's search any purchase big purchase order date any any of the details from this one description date cost invoice number purchase order anything we can we can change so we will change our invoice number one okay so we can reset this okay we will set invoice number okay this is a scroll button scroll bar we can scroll that then it will move one by one two three three details only we have okay using this scroll bar we can see one by one okay now we can reset this if you want to update anything see this is the invoice number for the purchase order 1111 we can update any things based on the purchase order only via updating here so instead of purchase order any of these four things we can update let's type 0 Dash this okay let's update this updated now we will reset this this is the invoice number 10 based on the invest number which is based on anything we can change okay this is here you can see it is updated ing now we will reset as it is earlier okay now we will updated if you update this okay let's change the purchase order let's update level message but it will not be updated you can scroll this see it is not updated as it is earlier because based on the purchase order only it will be updating so we'll reset this we can go to the menu the next one is the customer database foreign Main Street phone number email address now we can add this new customer details here it is added now we can change what is the name based on anything we can search okay it is here we can scroll and see the another customer details key details are there back and previously same as earlier we can update we can reset right then go to the menu this is the supply database if there is any new supplies same same as customer we can update new custom details we can search and we can update anything also this is the employee database this is the employee database of your company also audio organization or oral Supermarket whatever you can enter new employee details employee number national identity card number name joining date date of birth address mobile number email and Bank details If he if he's if he lived from that organization then we can type the living date then we can type anything in the remarks if he is working we can type as active person he left from the company we can't change this to inactive or something whatever you want we can add this we can update as earlier we can search scroll this this is like a employee database so each functionality is here in this sales invoice and the stock management system again coming to the menu when we when you click this exit button directly you will leave from this use of among the Excel form as you saw you want to exit yes everything disappear and also when you exit from each one it will be saved the Excel file reset now we will reopen it now you will open the Excel file confirmation two times again username password again there's a warning message so for each databases we have you login user form so we can give different username password then we can assign to different stops for the updates we can share this Excel file through a IP address link then through the repetitors network each employees can have the access those each employees can be able to access the file for the updates here you can see this is the Excel file if you change any title so any things by mistakingly then they were thinking able to collapse so this is the invoice when you type the invest this will be updated here when you click the next customer in the data will be like out that here you can cancel the invoice as I said earlier by typing the invoice number in the particular text box then you can cancel the invoice the invoice will be canceled then you have to get the money back from that customer so when you update the pro Community here it will be updated then after entering all the details now I'm going to procurement storage there will be the old products total products will be updated the sum of the product will be accumulated here here is the procurement history for each Supply invest each product payment history will be updated this will be remaining same for the old days for the past Christy we can find it from here here from here a no quantity will be directed this is for the record purposes this is the product list when we type new category that will be updated here in the First Column also in this table as well when we type update a new add the new category this will be updated here as well in this table when we add the new product the new product category will be here and product will be here unit will be here and the next product ID will be automatically updated here also the product will be updated under that category name here as well so this is the invoice to print when we print the invoice this is the invoice template the invoice details will be here and the total discount and total grand total also so this invoice template will be printed this is the employee database that will be recorded here custom database Supply database expense database new vehicles and new vehicle expenses New Vegas will be updated here we will see that from here okay under that new Vehicle Management here's option add new vehicles if you're purchasing a new vehicles it will be not come under the expense that different database expense the only the vehicle the related whole expenses under this vehicle management system BMS so only for the new vehicle purchases we will type each details vehicle number vehicle type van Oka or motorbike Glory whatever then VIN number date purchase host invest number purchase order everything will be we can add here then we can jump to vehicle management system now to the menu VMS Vehicle Management System to this one from here only we can go to the mail right we will exit from here no not exiting we go to the menu then only we can open the Excel right now these are the Excel worksheet each databases now from developer menu we can see the coding from the Visual Basic there also the password is one two three four click this one two three which is the password for this you can see there are multiple user forms for each one first of all we will see that this workbook under this when we open the book book under this one application.visible is equal to force so when I open the workbook the workbook Excel workbook will be disabled and the use of form login menu will be appear then we can type the username and password we can log into the menu right then this is the admin vector user form here you can see the codings command button for the command button to pick the date this is the command button I will show the calendar first this one this is there's a video for the calendar you can check out the current video so to pick the date from the calendars there's a function selected date from that function from the calendar user form you can pick the date then this is for the button details then for the exit reset button for the search button for each thing we have previous videos you can check out the playlist the Excel BBF playlist for update we have earlier videos for each things here we combine each parts in a one project so for the spin button this is for the use of inquiry clause in each use of um this one you can see this is for the you can from the user phone you can select the query cross from the procedure here here's the query Clause by selecting that under that you can type this is close mode this is equal to BB form control menus then cancel a SQL to rule it means when we click the x button in the use of them in the right top corner there's x button to close the news one so after this one when we click that x button it will not be close the use of them will not be closed for that only we are using this one so in each user one you can see this and then customer database this is for the you can see from the down spin button for the update search preset menu to jump to the menu then exit then for that same like for each one you can see now we will see one of the login menu here from here menu you can see from the login menu this is for the clear we are clearing each one if we type any wrong username password we can clear that and we can retype it for the exit one for the lock in the username this is the username for each clock in username this is the username password you can see this is the login menu is the main one if we look into the menu only we can reach each database command button so this is the login menu if you type the username and password this is the username password you can change as per your requirement if this is correct then that user name will be the login menu username will be canceled for that unload me then we can show the menu user form if it is wrong then we will clear that and there will be a message box right in the use of a initialized i restrict that the characters of username and password to 25 25 the maximum characters I rested that you can change this this ing to your wish okay then for each one you can see this this is for the new product from here you can change the username password as per this for each one for each user login you can change this the username and password you can assign to different steps as per your wish this is for the open Excel username password you can give also here in the new username open Excel only you can see this is the username open Excel from the coding you can see under the login click even after the unload me we are not going to open any different user home Channel after unloading this login use of home after signing in we will jump into the particular use of all to enter the data do the work but in the Excel workbook after typing the username password we will jump into the Excel file so application.visibility is equal to row so only in this part we can see the Excel file so now we will close this here we can type the username password and log into the menu right thanks for watching I will share this Excel file the password for the VBA project is one two three four the user login password is one two three four and the username is my name sobanon first letter is capital you can reach this file this is fully free I will share this file in the description and you can download this completely for free and thank you for watching