Inventory Management System in Excel using VBA - Don't buy it but make it.

let's start with demonstration of inventory tool for new item just click it and select the icon you can select the date import export click the product code and also select double click automatically it's in adding your price number of units review as well and adding item button you can see automatically earphone has been increased if you scroll down can see also the item has been edited in your raw data double click highlighting the inventory also already ready for correction mode shifting from import to export just click it and adding it it has been changed in order to deleting something just select the item click delete button it has been deleted it in this tool you are easily able to adding deleting correcting item and also the statistic of your inventory very easily let's start to make it thank you for beginner please do not worry about it we start from opening Excel file we complete tool at the end of the video if you cannot understand it watching the video twice or three times then you will get it once you open the Excel file it looks like here from somewhere here I'm going to make raw data so in our inventory we need ID right so ID number number one we need a date we need a category so the category means check-in checkout or import export item in this video I will use Import and Export so let's put it import here we need to have a product code let's put a01 product name computer can see they are overwrapping and select like this and double click the line and automatically extended it we need to price up this inventory let's play 500 unit total amount gonna be five thousand lastly remark good review so we can have more of these datas and then it looks like here in general by using filter for example if we have this much of data and then we go for data and by using filter and then you can search by product name and so on but if we have more data this become an issue therefore this filter idea is not really great so remove the filter here this part is going to be our raw data make it this part is a little nicer and then select here in the Home tab in the middle like this we are going to have a control unit if adding some values and automatically link that will be nice right this button control C select here click right hand Mouse button paste the option and Force One transpose double click here we can copy this value as well Ctrl C past option and transpose it make it a little nicer selecting it like here go to all border here we make it like this and make border here in case of total amount let's make a formula price times unit before to go further let's save our file after saving save as button you can select the location where you want to save it I choose a picture folder and save the name and then save time you have to choose Excel macro enabled workbook here save you can see it's a saved as version one next thing is you need to developer tab in order to have developer tab go to file and then go to option go to custom ribbon scroll down and there's a developer tab tick mark OK button you are able to see developers before to go and then here we call it this is input control in case of ID number is going to generate automatically so delete it and we give a color gray in case of the following going to be direct input so we give this color maybe accepting this one this is a calculation is including so we can go with gray color next we are going to generate button go to insert illustrations shapes choose this rectangle holding left side of the mouse button drag it make it inserting the text put adding item go to Home tab click outside middle middle and make it bold 16 depending on your preference you can click shape format you can change the color so let's change it with this color so we finished the creating button our idea is following if you click the button after inserting new inventories that going to be inserting the below one for the moment I will delete the below part so in order to make it happen we need to VBA developer tab Visual Basic insert click module here you just type sub given name put it adding item enter and automatically creating brackets and so so let's testing whether it's working or not so I give MSG Box means message box in case of text you go like this parenthesis you put testing you can see there is a small triangle called runs up automatically this sub is running if you click it and it asks in which macro you want to run so adding item yes and automatically show testing okay so we tested it our macro is running fine next thing is if you click the button the macro has to run click the button right hand side of the mouse button go to assigned macro and you find the adding item which we created it ok button so let's test it again click it you can see automatically testing message box are popping up click ok now we successfully link the button and macros are working together after linking with the buttons what we are going to do is he go back to macro and then enter button make some space and first we put this thing this means like the line is not coding any more but consider as comments for example if you push the Run button now nothing will happen so what we are going to do is this value cell C20 plug it into below table so we can type range and Open Bracket and cell C20 close the bracket again equal range again from C5 and close the bracket and enter so the idea is like a from cell C5 plug it into C20 cell it's not equal then let's test it whether it's working click the button you can see this thing is moving to here let's do it for the category Ctrl C and Ctrl V in case here D20 and C6 right and then I will delete this part again adding and then you can see date and categories are moving properly the idea is this value is input value so the location is won't change however the below part we will add the items line by line which means that range C5 area won't change the location in order to make it simple you can delete this part and change into that bracket same manner for here we will do like that it will be the same so let's test it delete this part click the button it's working properly in order to make even nicer we can change this C5 as a name then go back to sheet click C5 cell go to name box you can give a text name let's give a date and enter button when you click C5 always called date for cells C6 we can give a category in the same manner and enter button so we have both in order to change the this text go to formula tabs and go to name manager you can see all the names here and also you can click add button you can change the name and also reporting cell location as well click OK button close it go back to macro and then I change this part into date here are category Let's test it again click the button and it's working compared to previous formula and much nicer and less error prone extending up to remark so I give a product code product name price up to remark and then link it in the same manner now we need ID as well in case of ID for the moment for ID I will just give you range P20 equal one let's delete it and testing it and adding and then you can see up to one is correctly applied it as you know our intention is once you adding new information it has to be edited line by line in order to that happen we create row num which is equal to 2 and T as you can see these values are fixed we need to change this part and adding row num which means that when row num change all this location is changing it extend it to up to J column in the same manner Ctrl C delete and Ctrl V so I did it up to here let's test it delete the line click the button it's working properly change to 21 Let's test it and it's working properly as well next step is this part has to be automatically changing it in order to do so we need the following if you go click here Ctrl shift Direction down key you can see our last value is one zero four eight five seven six Ctrl shift again Direction up key it's hitting exactly the last row which has a data this either going to be applied into vva code this part is a range Open Bracket B one zero four eight five six seven close the bracket point and close the bracket Excel up double click close the bracket so what is exactly that means in order to check that value just to copy Ctrl C what you have to do is go to view click immediate window then you can have the following window below put the question mark first Ctrl C Ctrl V Point address it shows the address of the this part it shows that cell 21 but what we need is row we put here Point row enter let's do it again first put the question mark copy paste this part here Ctrl V enter button it shows 21 but what we need is actually row 22 therefore we give here plus one as you can see our row numbers are automatically changing it let's test it so I adding button and then it's adding properly here so let's remove it again try again one two so everything is fine the next challenge is this ID number has to be changing it in order to do so worksheet function Point Max Open Bracket from B to 20 to B one zero four eight five seven six close the bracket the meaning of here is weekend testing again question mark copy paste Ctrl C and Ctrl V and enter it's bounding the maximum value in this range the number has to be increased we have to give plus one then after the value maximum value and plus one going to be that value let's test it I will delete it enter the first value is correct enter again second value is coming correct and third value coming correct up to now we complete the adding item into raw data section however what if we click the button two eyes by mistake we will have unnecessary information in the Raw data section in order to prevent this part I'm going to create a new sub so I put sub cleanup and enter button it's automatically creating new sub what I'm going to do is this part going to be empty this part is empty that's happening in this section there are two possible ways one is a click here choose C5 to C10 comma C12 close the bracket equal comma comma that's one way you can do it or the other possibility is that instead of the range and you can use the names convention which you applied in the previous sub equal to blanket the result are the same so let's test it with this value I go for the F5 button or run sub triangle here it's a deleting in my case I will stay with this coding here now we have to combining adding items up so in order to do that call clean up already done with the two subs are linked after combining these two subs let's testing it we're raising this part here in order to confide line by line first of all you put your casa somewhere on top of the sub press f8 button and it's starting with a yellow line and moving on this section if you go mouse cursor here it tells that row 20 is a starting point here B20 going to be the max number after that we press f8 button we got number number one here mf8 button again and we got the date press f8 again we got all this value after completing this part we call the clean up sub and it's going to here you can see here what's happening this become an empty clean up and after that and ends up so basically if you click twice it won't happening anything the value here but we still have this one let's edit stopping processes here what you can do is go from here we can say if the date is empty then exit sub so let's try what's happening with this line so if you go here the date cell is empty then exit the sub basically this line is stopping the next processes so you can also edit other conditions to stop the processes here I added only the data part is empty this sub is stop it but also you can add like Ctrl C Ctrl V let's put it like category is empty then also this part is stopping so here I will change the name I will put error Handler this highlighted part means that what if this input data is missing then I will not adding this inventory into the following raw data table after completing error Handler part next part is we are going to create the correct mode the idea is if we double clicking this slide these inventory values are going back to input control section in order to implement that part we need a new sub so scroll down here put the cursor up and let's call it correction mode and end button the main idea is as similar as this part for example we got a date that value going down to column C and 20 something lines but now backward approach so this state is going to be sitting here equal to range something let's do it before that I will give a name here so I just click here by the way I created this section so you can also see what will be the following steps I'm going to give a name here click here put ID and enter now go back to correction mode sub bracket and inside I put ID which gonna be range cell P20 close the bracket and enter so it means that our cell 20 value going back to id1 let's try it click the button here you can see this one is going back to a verb in the same manner I will create all up to remark click this part Ctrl C and Ctrl V but this part is date range gonna be C20 so I just extend it up to here the next idea is we need to create the row num then raw num change automatically that value going back to input control so I create row num here put 20 in the same manner you have to change it here put same as above row num we extend it up to remark Ctrl C Ctrl V now I extended it up to here let's test it so you go here triangle run you can see that this raw data inventory information going back to input control area next thing is this part has to be dynamically changing it in the above section we applied this function however in this case we don't know which line we are going to choose in order to solve that part we can go like here selection Point row let's see what it means for it so from here f8 button f8 button again you can see row 10 which is the cursor is a sitting now I put the cursor here I delete all this value up here go back let's test it split the cursor here f a button dotted the row number is now 20. so if you push the f8 button again like this but I just realized category parts are missing so I edit it let's rerun again and I delete this part f8 put the cursor here and then I will run it and every value is a correctly injecting into input control section next thing we want to have is if we double click here then automatically ABA is activating and going back to here that would be nice in order to implement that part you go to sheet 1 and double click it then it's moving like this go to here object select worksheet here is a procedure list and you choose P4 double clicking we delete this part what it means that before double clicking it inside of a sub has to be activating it so we are going to call the correction mode you have to start with the module one point correction mode enter Let's test it so I delete this part here double click it error I see there is a typo here small dual I will try again double clicking you can see after Double clicking the correction mode sub is activating it but the problem is after Double clicking the cursor is sitting inside in order to avoid this part we have to create the adder Handler that's pretty simple you can see cancellation so we have to go cancel is equal to true foreign so let's try it and I delete up to here double click it the cursor is not inside of a cell anymore we have a still problem the problem is this sub is activating entire sheet but what we want is only below this row 20 if there is empty cell which represented we don't need to go for a correction mode because there is no inventories are there in order to adding these two error Handler it looks like this if the target which is a selected area so Target and roll the corresponding Row the target row is a smaller than 20 means X is up if the target point value is equal to empty then exit South so we create three error Handler let's check whether our correction mode is properly working so for example I'm going to change this price from 500 to 5000 double clicking the inventory is correctly going back to input control I changed from 500 to 5000 click the adding item button what we realize that is supposed to be changed here but we are having a new inventory with 5000 therefore the correction mode is still incomplete now we can think about two possible adding items one is new inventory here is new item like this or the other is like something number which is existing inventory cases let's go back to VBA click the module one go to clean up sub ID equal to new item click triangle now you see IDs are properly changing with a new item before to go further the name of the sub is a bit confusing now so let's change it new item when you change the name of the sub the corresponding call has to be changed change this part as well let's test it again now run this part again it's working properly now we have to focusing on the adjusting existing inventory information scroll up we need if statement if ID is equal to item then row num is increasing it also means the existing inventory information our role num equal to range bracket and then this B20 to be one zero four four one zero four eight five seven six and close the bracket point oh I'm missing this one and then point find five means finding something find what so we are finding for our ID close the bracket our row num is in this range finding ID then it brings only cell information but what we need role information so we put row here and enter and if the other point is this part this is also adding the next line so Ctrl X we put them into Ctrl V in case of the existing and then it won't change anything clean here a bit delete okay it's up here delete you select these two press tab button here tap button means if statement and inside of the statements Let's test it whether we correctly implemented it double click the second inventory change to 200 enter button you see this is changing to 200. in case of Correction part is correctly working now but something missing is it would be nice if we have a new item button let's make it hold the control button and select the button move across here next to you leave it like this change the name new item go to shape format put this one for now hold Ctrl button select two buttons go to format shape go to here align the middle you cannot drag it put the location where you want to put click the new item button click the right hand Mouse button go to assign macro new items click OK button it means that this new items up is linked to this button Let's test it if I click here click the new item everything is become cleaner going back to our setups whenever we have a new item in ID section our raw num is changing to next lines but in case of existing one it's unchanged by following this line the rest is a same manner and changing here in the end we run new item which means that as a default setting it looks like here the zero is because of the formula and the E shows zero up to now we complete the adding double click going back to here correction mode is done also new item setup has been done let's test the new item Point come to input control 2 0 to 2 minus us adding a new data input data here adding item button then it's correctly applied into here if I want to changing something double click it change it here 400 click adding item again it's changing to 400 so basically we learned how to add in and how to correcting it the missing part is deleting the item let's create a delete function in order to do that we need a new button holding Ctrl button Mouse drag it you can create new button change the name delete go to shape format select this color next thing is we need to create a new sub in here for sub delete underscore item enter button we just create a new sub now go back to button click right hand Mouse button go to assign macro choose delete item click OK button new button and new sub is linked the main idea is following if ID is new item which means that there's no need to delete because it's a new item however the ID is numeric numbers then it has to be deleted in this delete Sub in order to implement that we need if statement is numeric function is needed Open Bracket ID close the bracket then enter once you enter if it is changed like this which means your coding is correctly done the next thing is we have to find the row From Below has to be deleted to find the row is we already implemented it previously scroll up copy row num this line Ctrl C TRL V after that rows and Open Bracket row num which we found it from row data set point delete means that the selected Row in row data has to be deleted how about the rest of the item in order to clean up this category and product code and so on we need to call new items up and if we closed if statements Let's test it whether it's working or not I'm going to delete ID number three so double click it it's in input control I will click delete button ID number three completely erase it after calling new items up this part also has to be set like this we already complete the delete function let's talk about user friendliness go to view unclick grid line then it looks more nicer next we are talking about category part when you are entering new item you have to type here import but let's say you put two r's but it is still possible after click adding item button in order to avoid this part I'm going to create a new buttons which might prevent incorrect data inserting it in order to do that I will make two buttons holding Ctrl button with mouse dragging you can make a button change the name import make it smaller Home tab 11 and make it small and make it another one press here change the name I will give a name so select the Box Guru name box and delete put import enter click the other one export enter create two button and names as well once I click this button it says import here and once I click the other button it says export here color will change that's what we are going to do in macro in order to do that we need the recording function so go to developer tab record macro once you click it it shows this click OK from now on what you are doing it automatically recording it creating in VBA code so select this button go to Home tab I will choose this color select the other one and change the color with this one go to developer tab again stop recording means stop recording macro alt f11 button you can directly go to vva you can see the other module is created double clicking it we are able to see lengthy coding first don't worry about it so the idea is a simple active sheet shape which called import has been selected it this color is a choose in case of the other one which is the export button is selected it we choose this color what we are going to do is reformulate the chord here again we need this fill part Ctrl C Ctrl V we need this part as well Ctrl C Ctrl V for export same manner Ctrl C Ctrl V this part is needed it Ctrl C Ctrl V enter here I will put it this one Ctrl C then Ctrl V now I got two lines which I wanted to have it I found selected is here so I delete it here Ctrl C and I will create a new sub go to module one create a new sub called click underscore import and enter button Ctrl V put category is import category is equal to import next I will change this part is export this part is import which means color will change and go back to button click right hand Mouse button macro select click import OK button now the macro and the button is linked I will click edit the color is changing it go back to the macro copy entire disk Ctrl C Ctrl V change this bar is export this part will be changed Import and Export once click and then the color will change and also this part has to be changed export the area cell has to be changed as well go back to button and assign macro select click export OK button Let's test it so click it here click it here so once you click this and this is also change this make bigger here a little bit for these two put them in here hold the alt button put it here at the moment is that import is selected it let's see just go here so import it if you click this button its export is selected it when you're adding new item you don't have to type just to select so I believe we can reduce some error from inserting input data now let's talk about product code and product name normally Protocols are pretty long like we're a011 whatever like this in case of product name for example you can type computer it is easy to make a mistake in here let's add some user friendliness in these two parts below down there there's a plus sign if you click it you can create a new sheet click right and mouse button rename put product list next go back sheet Ctrl C go back to product list sheet come here click right hand Mouse button and select this one with mouse and select like here and the line double click with the left Mouse button it's extended it then here you can type the product code name a012 a02 and drag it and then you can add a product name so I go computer I create this product list go to selector area go over here choose all borders go with this mouse button double click twice it change like here going back to sheet1 add f11 button now I'm going to make a user form go to insert tab user form click it then you got this toolbox and user form and inside of a user form you can put these controls in our case I will take the list box drag it and put it here you can also make it bigger increase it down like here inside also you can do it like this after that you have to click F4 button then it shows a property of user form so put it here the first thing is user from this part we can change the name delete put product list this part is a changing click the list box and put this one here go down to row sources click here put product list A1 to be 13 enter Then you got this name but what we want is a and b in order to change this you have to go for column count change into two and enter you got this two now we want to put product code and product name as a header and you go for column head change to true this part has been changed therefore we need to change row Source go back change it to 2 to be 13 enter much nicer now I'm going back to sheet1 what I want to make is if I click this area the user form which we made it is popping up in order to do that I need to add in the new box insert Tab and shapes go to box holding alt button and drag it perfect fit with cell size shape field choose no fill shape line choose no outline click right hand Mouse button put bring to front I'm going to make a macro which link to this part alt f11 and close this one for now go to module one here create the sub product list enter then entering user form one Point show enter go back to here select the box right hand Mouse button assign macro product list Okay click it you can see product lists are popping up it would be nice if I click from product list the actual values are popping up in product code and product name close it here alt f11 double click the list box then you can see list box one click change into so double click is this one remove the first one we need the two line something is equal to product code the other one is something equal to product name please go to module one copy these two line go back to user form double click again Ctrl V same as before so something change into product code something change into product name so this part has to be removed what we need is list the Box one and list from in this list I've got one here I need list box one point index list index here double click comma 0 which is the first column second just copy this one Ctrl C Ctrl V and this one is the second row so we put one save it let's test it so I click here product is popped up select this click nothing change double clicking this entering it chair double click it it's changing as well so we can close it alt f11 maybe it's nice once double clicking it's closing as well double click so in order to do that is unload me and enter button Let's test it and click nothing change double click it's changing also the box is closed we cover up category part and also product code part if you want to change for the code and name you can go back and upload it here you go back to alt f11 click the product list and F4 go to row Source change the range or if you want to directly change you just scroll up and then changing it here as well now let's talk about the use of friendliness for dates maybe it would be nice if you click and then select the date it is possible to make it VBA however it might take some time therefore I made some Google research I found this website he built this by vva code scroll down you are able to download it after downloading I unzipped the file it looks like here so you can store where you want to store go back to Excel file options go to add ins click go browse search the file you store in my case I store in pictures this folder found it click OK button hints you can see it click ok now you are able to see date picker click the location here you can see immediately this icon click you can select the date in my case I have a Korean here but it will be also English depending on your operation system just double click it and the data in click here again if you click the ears you can change different year up or down as well if you click the month it'll be also possible to change in addition if you put your cursor in the cell the date picker is not activating however if you change this into date like control 1 button select date okay this date picker is popping up so what we can say is ID is new item has been set by VB EA and date has been done category done product code and names are fixed as next step I will make a bit nicer here so select this area go to Home tab go to field select this color font color choose White [Music] Ctrl 1 button click border select the color orange middle one OK button next part fix from this part in order to do that click Mouse button here and then go to view tab go to freeze funnel select freeze penis you can see a line here so from there if you use scroll bar the upper part is fixed the next part is if I select certain row that going to be highlighted in order to implement that part we need to go back to Eva alt f11 click sheet here double click when we double clicking it we activate this macro what we are going to do is we are going to use correction mode before that I will make a new name so you can make anywhere else I will select here name box put index and enter this cell I will just highlight it for a demonstration this cell has name index go back to macro again alt f11 click correction mode click right hand Mouse button go down to definition it's automatically moving to correction mode Open Bracket index close bracket is equal to row num the idea is the row num will printing into index let me try it double click you can see 23 so I will change the color so it's changing 23 click here it changing to 21. this one will use it for making a highlighted row which is selected as next step I will use a conditional formatting first select the applied area go to conditional formatting select new rule choose use formula to determine which cell to format here equal sign row which is called rows P1 hit F4 button one two three which means that the column B are fixed close it equal to index and go to format we choose this color go to border select disk line and up and down go to font select bold italic OK button and ok now you see it's changing here click here double click the selected area change the color so now I go back to conditional format click conditional formatting manage your rules what it means is apply to means that the area conditional formats are applying which is from column B to column J where our inventory informations are sitting the formula which is index number and row b are equal then apply this format what it means B1 it will counting from B1 to all the way down until the end of the rows then if index number and row number are equal then true if not is not true here we have a 28 so index is 28 the row b also 28. therefore it become a true in this case we apply this format okay so we are already done here select your fold and change color I this one and here as well for this part hiding it so select here go to fill color no fill then this is a disappearing so highlight part is over now now let's make statistic or click the mouse button here Ctrl shift Direction key to right direction key you can select entire area holding Ctrl and T you can see the pop-up window click OK entire raw data set become a table select this area double click shrink it a bit here again put the cursor in the table go to insert tab click butt table from table and rage click OK we create a new sheet click right and mouse button select rename change to Pivot enter button make it bigger pivot table field select product name drag it to rows so we got the Row part go to category click columns so we have a column export import here product names middle we use unit select drag it put into values so total sum of a unit is a 1540. let's check it go here and select them all is 1540 so the pivot tables are working properly click right hand Mouse button go to pivot table option select total and filters deselect these two part show Grand totals OK button the other part is disappearing put the cursor into the table click pivot table analyze select pivot chart choose cluster column okay then we got here click right hand Mouse button select hide all field button on chart and they are gone click again go to format shape field no field shape outline no outline go here grid line is deselecting it select here delete button go to view grid line unselect select the bar click right hand Mouse button data label select here data label now select the label Ctrl V select label Ctrl p depending on your preference you can change the color as well Home tab select the color same as blue we just change only export part select button Ctrl B holding Ctrl m one you can see this part go to fill and line select the line with make it thicker I select this one I will put them on the top close it here select it again Ctrl X go back to our main sheet put Ctrl V here just realize J2 we have this index in terms of for safety move it to somewhere far away from here Ctrl X Ctrl V and I got back here I will adjust the size of it here make it bigger here make it bold and bigger and here also dark blue bigger so you can see our export import item condition in case of keyboard here we have a none so therefore it doesn't show anything select fifth one and automatic key here will change to export adding item it become export but it doesn't change here in order to update this part you have to go to the formula data tab refresh data and refresh all you can see this part is changing it let's add a new feature Force inventory will change from export to import double clicking it selecting automatically in input control select import adding the item back into raw data so you can see now it changed to import but we need to go for data tab click refresh all you can get it to solve this part go back to VBA alt f11 adding item section before calling sub new item we need this comment which called active workbook Point refresh all that is exactly the function we click the refresh all button save and close it let's test it with the mouse again double click go back here extend it to 40 to 50 adding item now you see it's automatically changing it before to go next step just be aware one thing when you see the category even though it looks like the imported is selected it but when you're looking at it here it's empty you have to click it then it's showing import so what I want to say is when you click the new item it become an empty so therefore you have to click one more time the last part we are going to add title extend it further down here and I add it like up to here go to Home tab fill dot blue select B1 edit my inventory select this color yellow bold bigger in cell A1 you can add your company logger or some icon for decoration to add icon insert tab illustration icons search function output business enter select this one insert button very big so make it smaller graphic Wheels choose white color then our tool is complete I really hope that this tool helping your businesses thank you for watching this video have a great day bye bye

Channel: Excel Tutorial

Views: 122,677

Keywords: excel, excel tutorial, excel inventory tool, excel inventory management, excel inventory management system, vba excel inventory management system, Inventory Management System in Excel

Id: RM9ha_OFFqY

Length: 55min 53sec (3353 seconds)

Published: Fri Dec 09 2022