How to Create Stock Control Management System in Excel Using VBA - Full Tutorial

hi and welcome to stock control management system developed in Excel using VBA so let me show you that's how this works you see all of this information here if I'm move this down you can see it is not right here but let's say would click on to workbook there we go you see that place let's add another one so let me show you how the whole system works anyway and we don't reset I'm supposing we have a customer so our regular customer will have all the information stored in our system so let's enter all the number this time around does the order number and in here have let's say we do for the other product and that's supposed to be eggs and the name of this customer supposing this customers name is Sami salami Johnson right and address of Johnson let's say is number 15 I didn't read let's see are they are taking the street all right pure box there and how many of us brother do we need let's say going for 54 and yeah the one day expired date and I'll be the other date on sales yes method of payment I say is MasterCard account type let's say debit debit account discounts maybe 10% vhe yes and they all need to do is then click on total day that is how the system works so what I'm gonna do now is to take you guys straight into Excel development environment and we put one of these very advanced stock control management system together so let's do that now guys hi and welcome to the excel tutorial of stock control management system let me start by saving this very project before we instead okay I'm gonna save my project in here and it's kind of a call all right that would be the name of my project split and save they will go stop and throw money main system all right the first thing I like to do this right here I'm gonna enter the following here so that will be product [Music] let's say product ID and the next column I'm gonna be that Crudup name unless speed that up then I'll get back to you guys okay and I'll have all my fields ready on the workbook yourself or spreadsheets they'll go so what I'm gonna do now is I'm gonna go to the developer here click on the developer and select Visual Basic what if for those of you are new to VB VBA programming all you need to do is to go to file and make sure you select options then in there you need to come to customize ribbon for you to get your developer menu this is it measure is checked and you click on OK and you should be able to see the menu here click on that and select Visual Basic and this will pop up okay once that is up all you need to then do is to select insert user form right there that's the user form there so all they need to do is to define the size of this form how we want it you can always right click and select the properties and whatever size of the form you intend to use the choice is yours they began tight in there okay for you form which I'm going to define that as 1020 and the height let's make the height of course method 6 and that is done now let's go straight to the two here click on that and I'm gonna select the frame and draw the frame here okay that's my friend the content on the frame go to the caption get rid of that as it done okay the next thing I'd like to do now is up here I'm gonna get a label there and another liberal would enter the follow let's say stop control management system management system so that's going to be didn't I to stop and true management system let's increase the font size to something a little bit readable so I'm gonna meet us maybe 36 that much [Music] and take it up okay intensity step mercy stop right any move just a little bit [Music] alright that is my title there I will reposition that and just enhance it anyway okay we're gonna get that centered anyway that's fun for now alrighty next to not like to do now let's add another friend here on this very frame that's where we have more customers details you wanna start with the label there is a label here label is gonna be called customer ID and let's increase the size today you can see it properly make double maybe 14 customer ID [Music] there will they need copy across and this baby's order order number or order the other number is fine then here you have first name and so that first name then we have sold [Music] and this ad as follows I'm going to enter the text box here and another text box so let's be there before process for these very customer details ok guys this is how the interface is looking now I had to design your interface is this would take a very long time so what is left for us to do now is to start with the coding but just before we start with the coding let me show you how it's gonna look like when we run the program ok this is how it's gonna look like there's nothing happening here and here would have a source of life this is calendar ruling ok we saw Twitter to use that button sour labels and this is from boo box combo box yes well changing customer to combo box and we have buttons here combo box boo boo box how do we do so combo box as well they will help combo box here this is a label level a little nibble here we'll have textbox another label here label that's a little swirl to leave a label label text box change box text books on text box and these are all labels so what we need to do now is to start with the coding part of the whole project if it happens to take more than 20 minutes I'm going to have to divide it into two separate into two separate all three separate cell videos because it might take long time for me to complete the coding I haven't really make up how I'm gonna go about coding box let me start with the exit first so right there we have the exit button okay double click on the exit button itself there we go and maybe if you like you can declare a global variable of exit there or exit I'm going to cut out a signed integer right inside exit line code I'm gonna say I exit equals as follows so message message box then you want to exit and closed at plummer some nagging aunt Alicia basic question then miss applause baby yes [Music] I'm finally understand enter the name of my project still true management system there that is fine those are the built-in functions that I intend to use now we use an if statement if I exit it was really yes then but I'll like the application to do is unload me and so that is my exit taking care so if I try the exits there we'll go and confirm if you want exit no let's try to get more farm as you wanted to do the exit is working out as we expected okay now let's take care of one thing for some like the system when it load up when the form activator I would like you to populate the Product ID customer ID and so on so let's double click on the form we need user form activates right there drop this down I select activate on right here I'm gonna declare a variable D I [Music] omma do B as integer and let's use a for loop for I he always wanted to five less C can be through dot dot let's see what this felon is meant to be pulled at ID product ID dot add item and the item I would like it to policy pmid zero zero close that and I so that is the face or like it but the second one is going to be less a more copy all of this just to speed up your process let's enter see and the customer ID dots add item think already copied in general a spirit of that and paste that in there and that is this one is going to be see see see id let's pull up one day okay the next one is going to be C and B let's call that order our ID look odd item and in this case is going to be PI D 0 0 let's achieve right and next I so that's the false one I would like the system to to load up for me if I run it this is how is what won't happen you see them guys ok let's see how to dance yes as far the game okay that's fine unless you'd approve of that food that's is fine as well ok let's close that and continue with the form activate only four markets I'm gonna enter let's create another for loop so it is for loop in this case is going to be for D so let's change that to D and it's going to be from 0 to 20 and let's say step step 5 okay and they'll be C and B discounts dot add so this country were given a customized will to range from 0 5 10 20 and so on and what do you want it - I don't want you to add that I just nest that a swirl next fire our darkest jacket on the disc on that is the discount 0 5 10 15 and so on alright I guess you guys get full ideas so I'm going to repeat exactly the same thing for for my payment which is right here payment method the v80 and the account type so let's go back in here so for Evie a t-account type and so on we just enter the following okay app form activate all of these are added to dues to those objects so let's run it vt yes no yes or no and account type and this one okay see if you guys can see for clear may have to find a way to move it off all right that is fun and those would be yes now the next thing we want to do is you see this products if I select this rule that I would like you to populate whatever will have in there okay so let's take care that that CSS works so let's come in here now I'll double click on this this very one double click on that and right there we need to end you need to enter as follows I may have to speed up because couple of lines of codes there we go right have a good look at that so if the product ID is selected happens to be P ID 0 0 1 all of this should be populated with the following information so Dutch that's going to be seed the rice 200 and so on so let's write and you see what I'm talking about so we select that okay see you see what happen so let's see why did one not work one is now working three walks okay I really can't find what's wrong up just just move that in product ID and this one has chain dis around I want it to be this year and this year let's run it and see what's gonna happen so select number one there we go alright and it also they will be the price that is fine okay that's good alright let's have a look at the reset button so I'm gonna exit out of the system but a problem with the reset is there's a lot of object to reset so what I'm gonna do is I'm going to put them all together using a for loop but first of all I say dim see control let's just call to control us control [Music] all right so see control [Music] therefore each see control 4hc control and me God's control without controls deep-sea control dot name [Music] is like let's say LDL [Music] then see control it was VB [Music] let's play the spring and in here let's say next so let's try this out and see what's gonna happen so a little rowdy this is for lvl running now and select anything here so let's check this out so that is not working this companions I said L be dealt [Music] let's go back in there okay I'm missing this time that is it so let's run it and see if I enter any value in didn't we say delgo you see that so I need to be able to repeat the same thing for Xbox cause if I click on these I can only claim the label so less planning to thee no reset and just copy all of this I'm gonna repeat it for textile and let's say this is combo boxes or combo box and this won't be whatever iced tea let's see let's see hopefully that should work fine all right let's run [Music] there are four kebabs and this is for these let's enter some data in here [Music] an address gods okay let's check I'll just check it out if you stand a walk the way we want and if it does down they look great try me sounds as warm and reset there we go it's all working how I want that is fun okay basket does the reset button because without dots it's gonna take me couple of lines of code so have a good look at that very line of code for babysits there we go there's the reset button that's boot and here let's bring this up so you can see it these are the lines of code for the CMB through dots ID let's take you down so I can see it worse there and here these are the lines of code for the form activates up to here they so now what I'm going to do is to put together some lines of code for these collector here so the first thing I'm going to do is to create a function I'm writing Els going to speed that up as well so right here are the function does add lines of code for the calendar itself to have a good [Music] a very good look at it all right so that's for the function now I'm now going to call this little function so let's go back in here just go back to our program I need the from [Music] I'm gonna just film activates or maybe actually use for me Nisha lies there's plumbing to initialise okay right in there I will then Paul as famous so have a good look at it [Music] so let's run it and see what will happen you see the price deformers actually initialize as follows date of order valid from an expired instantly and with all the dates may be the expire date and the rial the date should be the same so which means fair click on any of these you should tell me when to reorder okay let's exit now you just go back and show you guys these lines of course Bing this is the function the calendar function have a good look at it again so that you guys can understand how its put together there's a lot of lines of code in it Saavik very good local now I then initialize this formation go right up here I have a variable that is declared those otherwise there's a variable that are declared for the clementa variables function and initialize form initialize now let's go to the easy don't click on that and in here I want to be able to call so if I double I want to be able to enter value for this and this so if I want it I don't want this all that did so let's carry that the other dates double click and go to initialize in the 1/2 it's day dancing all the dudes I'm gonna be a little blood was maybe cut it off unless climbing here double-click on this ver button where this that's India and get rid of this red one so it's great to be dealin dots control chip checks so if I run it's control chip takes food ads will be there so that is now the other dates okay but that's not correct so let's do that oh that's correct a pre yeah this in American Standard okay as is correct on the 4th of April so if I select this shoe give me a 18 solace but I wants the expire date to be the same so let's draft in them at the expire date as well as climbing to the properties copy and double click on this so expire date is going to be the same copy of this test in depth well copy this and go because I'm gonna need them for every single button so if I click on this expired it I recall the date are the same okay so now double click on the next button yes that's in there that is d 2 changes 1 2 D 2 number 3 you repeat exactly the same thing for number 3 number 4 matters once they repeat the same thing for Wednesday that is 4 [Music] is sort of like painstaking so I'm gonna have to speed it all up after their face roll now palm into Saturday best member seven now you guys get the whole idea I'm gonna have to speed it up okay I'll finish putting together the lines of code for each more things so let's see anyway these are the - of clothes it's up to 42 and it's exactly the same lines of clothes anyway yes you guys can see that and there it is done so let's run this program and see okay if I select any of these there we go and add or deform initialize actually entity so if I click on the city 18 shows me the date of a theme that's 12 13 and so on so does the we ordered it it's at spier and with all the date now if I wish say it's this an error somewhere you see this is on gloves because if I decide to enter another one I don't have that those information anymore so what I'm gonna do these scenes is our friend initialize I can come straight to the initialize here and we just call initialize initialize seven of cold-eeze so let's copy that and I'll come in here inside my yeah nice inside of product right up there listen call national office you guys can see that so we called initialize right there so that should take care of that very problem so what we run it now initialize rice then and tell what I wanted in Turkey and above item ordered okay now you need action how many want to all that number tomorrow okay this is for the customer and this is for the product so this should feel this so if I resets devil do you see that take all but now there's more work to be done if I enter whatever I want in year number folder this is the folder the one piece as well associate five year and we'll just want the system to fit through each other says that's fine that's his phone which they need to take care the calculation anyway so instead of talking to motion let's just get on with it get distinct lotta bleep okay all right in the first place this is number of item ordered for customer someone will climb right in here customers laughing that's changes number of order there's a label I'm gonna change it to text box give me the blood level of dance delete and this property suppose right okay let's double click on this and enter swallows number of other equals txt lighten order not tips so whatever anti valuing it it automatically fit truce with a skillet trial you see that that's what I want so does funny now right so you all just introduced now is multiply this by this together right value so what about action [Music] the date option okay reminder selves all right so to deal with the calculation as do the calculation this time [Music] this kind [Music] Dovie 18 just be true take care the population now I can call it the end until proven right no what you need to take care of these we need to feel it Trudi spreadsheet system okay that's climbing to total okay let's take care of the total I'm you double-click on that and right in here the first thing I'd like to do is to declare a variable for my times local variable for the task let's say is double there and use an if statement if C and B discounts doctors if that is equal zero then a lighter for going to take Castle tax equals by value LBL cost dots caption now multiply that by by value the exit number ordered number order dot Tex okay let's say we have x 7 point 5 and then divide by 100 now LBL LBL traps dots caption [Music] equals claps and LBL subtotal the caption equals by value LDL cost got passion and will multiply that by LBL order this year one let's copy that so that is my substitutable now for this october for the tool table so we can just say to o-tar it was as follows let's close this okay total I'm gonna copy that paste is right underneath here so this is to turn the rid of that and total will become by value as well by value let's get that of subtotal let's get rid of this evil sub to the Tom Cruise that's in here and a surplus [Music] so that is my total and this has no need to be Sonia that's fine so you can end that then beef so let's try just this very one out but then [Music] let me convert all of these 2 pants on its place that I've just convert that to pounce on so we can then enter format here let's say format lbl taps both option Plum Island into a pound [Music] come on punish zero Delta zero [Music] I'm close that that's very pants on all right I'm gonna copy this this make sure is corrected I'm gonna copy that and pista to you [Music] the Petersons team here I'm just copy this pace that and yes we can just type to told it right okay I'm gonna try these out that's it's just discount just just count is zero and just click them to talk there we go right that's working how I want good so I just need to do now is to copy and paste and change the values around so double click on that now climb down here copy we can use else if else if if this is 5 so what I want the system to do is as follows in here I'm gonna say subtotal depose so it's going to become subtotal let's wrap this up terra cotta of right underneath here paste that and now becomes the costs we'll multiply that by order [Music] let's see my house [Music] by value I'll be the Costas will jealous can just copy this paste that in there x DS and there we now need to multiply by 5 and divide by 100 the kid has comedy self-empowered in central Seoul right [Music] so in that case tax is going to be subtotal [Music] it's up to at all Muskogee our subtotal multiplied by 7 plus 5/100 covered itself right [Music] [Music] okay so that is fine so I'm going to repeat the same chin to the second or the third one artificially populate a stats in the case of the tag one this won't be content and this will be content as well ok let's try these three out before we continue so let's try it out this plant is 5 there we go I said to discount it is 10 there so I'm going to exist a let's go back inside in total so now we can compute all of the all of the calculation ceramic op-eds now copy and I didn't need to change let's change that to 15% okay 15% and next is 20 percent right so that's is it 40 total taking people's current level you can see now if not coming here select a name yet how many of those items are months and click and that's before maybe 10% supposing we want 5% and 0% [Music] fifteen and 20 there that is fun now reminder and duction is - it needs to be looked after want to because this one will have to officially enter detail so they made here let's say the dead is [Music] okay and the up being as king address [Music] but that's good [Music] so reminder say we have method of payment and basis [Music] now we need to who's gonna select this on these to generate something here is not and sales and sale of not insane idea I want something to appear India reminder reminder which is the reminder can be this the reminder is that a magic wand doc you can just copy otherwise in here to remind XY I did this one again right so let's climb in here expired it we mean that me the name of death expire date and reminder so later [Music] so exploit date double click save my de dot caption equals equals LBL did expires that's option [Music] okay so let's see and the spy dates I think oh that is a click event no we don't want a cookie bank let's cut that off we absolutely want when it changes let's see there anything like that there jump like we don't wanna click let's go back in there so that is a great the supposin will use this where there was a son to me we have a value here okay and here we enter that and here that and in here we say we enter amount here that is fine okay reorder with all the deal all right a stupa in here let's move this a little bit in so that we found yeah so let's try it again the other last day to do okay we need this fish where is it reset is now ice water right that's far as working okay so whatever option option this plumb-bob in es su option is going to be similar which is the option called this LBL option so option can be [Music] so action can use with this anyway yeah okay you can always push and enter something else so that is fine for those two okay to transfer ever content on the form to the walk what book I'm gonna stand by declaring as far as Dean what good as worksheet right Oh what what let's cut it watch it right now Dean add me as that will be for my range then Isis said what worksheets equals one of my spreadsheet then ambience said admin equals watch it or double cadence dot range and let me both war 660 5356 approximately the these cells will have one spreadsheet don't end X target X del up glue that cut-offs it and that would be worn upon a zero okay now the first one let's come in here and see the name of the very first one there we go product ID sir India minute now enter to say scenario here that was supposed to be equals so let's say new admin of said admin that of said now is zero comma zero dot value and what will devalue be in this case the value is going to be CMB first things first let's get the value I think CMB go there go there does it relate this room copy that and paste it right in daaad tits - whatever I have in there now the second one is a product name the product name they are supposed to be this okay now let's copy all of this so I have my product name appears Dutch here and this will become what one next one is the description so we are gonna grab my description yeah copy that I'm is that in TS wall that we have [Music] stock level reorder level so this may be too so let's take your stock level and we on the level stock level or the level of this rally column three and four and this is top level and we all the level stock level is that and with all the level there's 13 years woman okay let's have a look paths we have reorder level we ordered it dojin s 30 days now this is going to be five six seven Nate and okay now we want the following Altaf stop as coming here out of stock is the air for the date I don't know that so someone could ask how to stop here with other dates saved at some common head tuna for we ordered it we need the name ocean ordered it you know the date copy that back on the code and this is that with all the date now as do I have is that number of older so it is gonna be painstaking I may have to speed up the process and get back to you guys so one number of older okay number folder I can use this one or this one number folder Ellis coming to the property and chapter out txt okay we need two coats you see okay I have covered it up to you customer reference so I take off one two three four so let's come to this full copy arrested tried s we love 1112 [Music] 14 so it is going to be a small B txt first name [Music] rats and the other one will be Swami [Music] Andres [Music] they will have to scold to that Steve's in swarm right okay now we hear number of lighting item ordered I remember dance Atem holder you number of my okay let's copy another four based on repeat exactly the same that would be ten sixteen seventeen team so from postcode here up to here number of high-tech water copy that that code and that would be this dot tex thing this will be an early and that's supposed to be Ted's okay item ordered song [Music] 110 ordered some LDL [Music] dock caption method of payment I'll be down test can I see I can't type call me a graduate the accountant mm nothing homes time maybe okay I believe you guys get the whole idea I'm going to now speed it all up and get back to you guys awesome awesome as soon as it's done okay I think I've finished with the lines of codes hopefully this you had the data on to the spreadsheet everything here I should be an alien all right so we'll just be to do now is to to keep the try and see how explain a walk but before then let me show you the lines of code for the spreadsheet again okay that's this lines of code for the spreadsheet and here the spreadsheet itself as it's empty as you can see so I'm gonna move these melodies let me down and we can run it see through enter deaths and all of those data and in here we need to enter the name let's see this Casey Casey Jones address of Miss Jones is hoo-wee was good seven one team yv7 number I 1056 so now vision cells and cells method of payments is a collage ready to plant she deserve 15% is beauty and does it so click on total they will get now smoothie society we remove it aside so that we can add it you guys to see it you see to work but if I click on that there you see that guys myself clicking I need to give to adult that's beautiful ok using this type of interface it's very good so without guys I'm gonna call it the end of this tutorial I suppose you guys enjoyed so y'all have a nice day now bye for now

Channel: DJ Oamen

Views: 28,765

Keywords: How to Create Stock Control Management System in Excel Using VBA, Stock Control Management System in Excel Using VBA, Create Stock Control Management System in Excel Using VBA, Stock Control Management System in Excel, Create Stock Control Management System in Excel

Id: rEtOCIYR9lI

Length: 69min 5sec (4145 seconds)

Published: Mon Apr 02 2018