Create This AMAZING Excel Application that Tracks Purchases, Sales AND Inventory [Part 2]

hi this is Randy with Excel for freelancer and welcome to part 2 of the inventory manager where we've made some updates and we're going to be going deep into the code to showing you exactly how we created this amazing intern manager with both purchase and sales transaction and adding inventory with a few clicks so stay tuned it's gonna be a great training all righty welcome back to part 2 if you have not seen part one I do recommend you view that and also download the workbook although they're extremely similar both workbooks so I'll make sure to include a link to that part one video in the description below so if you haven't seen that yet you will want to see it as it's important because we're gonna take right off here from where we left part one which is getting into the code so let's go ahead and get into the developer and Visual Basic and get started first thing I wanna do is I want to write some worksheet change so I'm gonna click on worksheet and then change then there's some macro zone 101 I want it's important to know the product row so we're gonna have to dimension that as long and then also I want to write some notes on transaction change but not on but not on transaction load so for example if I make a change to event transaction I want that data to save below in the table but not on transaction load or new transaction so the idea is this if we make a change to if I make a change up here to a customer or a transaction date or product or sales price or sales quantity I want that information to save in the table below I wanted to save here in the table but if it's a new transaction we don't know the row yet so I don't want it to save and of course if the transaction is loading in other words if these details are being put in these fields we don't want anything to have I don't want it to change save back to the table right so not on transaction load not on so that means transaction load this must be false let's go ahead and code these are four products let's color them differently it's a little bit so we can see the difference okay so only if transaction if new transaction is false and transaction load is false only then do I want to save the data in the table below only if these two are false so that's only when I make a change to a customer I want it to change down here so that's what I want to happen let's go ahead and set those right up currently into the VBA we can start writing this code so we can say here if not intersect and what is it target range and what's the range well it's going to be e 5 through h7 all of those that cover that or even a 13 through K 13 so those are the ones that we won't be 13 through K 13 and those are all the product fields and I'm gonna say is nothing and range b4 b4 is false so we've got to make sure b4 is false as well and b3 is false so new transaction transaction loading both of those must be false so we need to write those in and range b3 that value equals false so both of those conditions must be true in order to handle that in order to set that up so let's go ahead and update that then and ok then what are we going to do in that case what we want to do is we want to say also we want to make sure that b2 is not empty that's very important we got to have a row we have to know what row to save it right I have to know b2 we have to save it to a row so b2 cannot be empty we have to make sure we can check that if range B 2 dot value does not equal empty then sells and then we say range of b2b choose the row so the row sells the row dot value this is the row next we have the column so if the cells b2 value and then what is the column the column is cells target row target row comma target column plus 10 and I'll show you why it's 10 in a second target column plus 10 so I need to know that's gonna tell us our call and we're looking for our column plus 10 dot value dot value equals equals the target value and let's show you why that is this is called 5 this is called 15 so we know we know it's different so this is equals column so that's calling 15 right if this column 5 we need to add 10 so that means adding we're looking for this 5 I'm looking for this 5 this I know column 5 that means the customer goes in column 5 the product goes in column 7 customer or vendor column 5 product column 7 so we know what column to put it in we just need to know we know the target rows the same so that is how we do data mapping and that's how we know where it goes so that line of code will place it automatically in there so that's setup so now if we make a change if those are false and we set the customer to 21 and we we have a 21 so we can say David right and it automatically changes to David right here so you can see how that works it automatically look down let's continue with our code also we want to say if not intersects 8 7 you know what is a 7 let's take a look at that let's remove that first ok now we're going to say if H 7 let's take a look at H and here if it's a sales price I also need to add that to the product I need to add that to the product so if there's a change here to the price I also want to say change that change it to the product price here so we need to make that here purchased sales price right here right here in this column so that can be it we can format that to a dollar sign so it's a little bit clearer okay good so we want to format it we want to put it here as well so we can do that so if there's a change to 87 we need to know to do that let's program that in right now we can say on change of product price okay we can say if not intersect target range 87 is nothing and range e 7 does not it can't be empty of course range we have to make sure it's not not empty so we can change that and range b6 dot value does not equal empty what does b6 b6 is the row of our product right we need to know our b6 is going to be our product row so it cannot be empty then sheet 2 dot range e that's the column where prices e and and range b6 Valley b6 values gonna tell us what row b6 dot Valley that's gonna tell us with what row so that's important we need to know that dot value okay so that's gonna give us our equals target dot value and that's gonna update the product price that will update the product price there so we have that on let's get now let's get back in and do that product row so we know what product row is so we can know we put in the formula for that let's go do that now now we know our products we've named the rain so we know our products and we know they start ro4 right so first one's going to be on Row 4 into the transaction we can wrap this in an affair if it's if there's an issue we'll make it blink equals if air and then we're gonna put in match formula we're going to look up our product number here and we're going to eat comma and then look up our a product number here and 0 we want an exact match plus 3 1 add on that because our starts in row 4 comma double quotes in case there's an air ok we're good there now we've set that up double adding those parentheses there okay reset that all right so we're good there now we've got our now we've got our product row here so if we were to add in a product number let's go ahead and put in that product number here as a data validation so data data validation and add in that and we're going to clear the list and we're gonna say equals product number believe that was what it was okay uh one more thing on that on the data validation I want to be able to enter new as well so we need to clear any errors that we need to non-stop on air so air alert no air lurtz because I want users to enter new from there as well I'll show you how we're going to do that so now when we select the first row it'll show row four and that's what we want now we know where to save it when there's a product change we know where to save it let's go ahead and put in those mapped named ranges now the product name is going to be in column five the type is going to be in column seven the standard purchased quantity that is going to be a nine and the description is going to be in ten so that is it so that is how we let's take a look at those columns here equals column alright just temporarily so we can see all those columns and how they fit in product link is 11 description is in 10 sales prices and 6 and product names in five sales prices in EF let's go ahead and update that code there that should be F Nonnie this should be F that is why our prices call them F and that's what we want to update all right so we have that an F the type product name we've got all of that there alright let's go ahead and stand it out so it looks a little bit better there we go next we want to set now we have our map ranges so this way when we make a change to a product type it changes in the product database when we change the quantity or the description is going to change here that's where I want to change that's why we're mapping it so the changes are automatic go ahead and setup the code to do that back in to our code let's update that and make those updates accordingly so we've got the update the cost is already set up here now we can say on the product changed load product let's let's do that let's add in that ok we can bring this down here we can wrap this in so that both of those changes are done on on all of this just in case and next up we're ready to go on product on product change load load product so what I'm trying to do here is if I change a product if I select a new product I want its sales price I want the product name and I want the product type I want that all to appear and I want it to pull from the database and I want to and I also want the picture link I want to picture the show up here as well so let's do that back into the VBA code and we'll start writing that code if not intersect target range e 7 that's the best the product number e 7 is nothing and range e sevens not equal NP R IE 7 that value does not equal empty then right then what then we're gonna do a few things and if first we're gonna say if range b6 we need to have a row number that's very important B six dot value does not equal empty that's the product row row row then okay on change of existing product but not a product load existing product but not on product load same thing for transactions and here we go here's what we can do update product in the table and road if range be too dot value does not equal empty does not equal I'll go over this with you again so not don't worry empty and range b4 equals false then range G and range B - that's the transaction row B 2 dot value equals range e 7 that's the price e 7 dot value that's gonna be a save product number save the product number in the transaction nice ok dot value ok now we're done with that oh it's very good now we've got that done let's go ahead I'm gonna copy and paste some code on a prior version so that we can do this a little bit faster and we'll go ahead and copy that and I'll paste it right in here so I've got some code here and I'll explain everything to you so that you can at least see what we're doing here and how it's going about it because I want to move this on because I know it's going to get long for you so we're right here and we're gonna run macro called product load and we're gonna add those macros now I'm going to add all the macros for you and then you can follow along so we're gonna insert the module and we're gonna call this module product macros give it a name here product macros and then we're gonna add another module we're gonna call that transaction modules and macros so that you can see the difference because we have macros for both transactions and products ok so now that we've got both of those written I'm gonna copy/paste from a prior version so you can see how we're gonna do that so product macros I'm gonna paste in some macros that I've had from previous version and then we'll go over basically we have product load we have attachment thumbnails and display thumbnails and we've gone over a little bit that in Prior versions but we're gonna go ahead go over that again we only need one of these and also with the transaction macros we're gonna add some as well on to that I'll copy and paste those and then go from with you that's just an order save time now I've got them and we have for our transaction macros we have transaction load we have transaction save we have transaction new transaction delete and cancel new so we've got those up and let's assign those macros to the buttons that we have created here in there so we have saved transaction let's go ahead and click on that button and then also the icon right click and assign the macro to the ones we just copied and we'll go over that transaction safe that's the one we just copy it will go over those macros and then cancel new we're gonna assign that as well assign macro and then transaction cancelled new good so we've got those and let's bring that down a little bit and then assign macro to our new transaction and delete transaction as well so clicking on the individual icon and the individual button right clicking as well signing macro to the new transaction which will be transaction new here and then we have delete transaction can assign macros to that for as well transaction delete which can be right here okay now we've got all those transactions we also want to add a picture so let's assign a macro to that we can in this case this is not part of group we can assign macro directly to that so we can assign product attach them here and then die good so now we have that end with the hide in show we also want to create macros with that let's go ahead and add those in under the sheet macros here we have already one which is the move button and we're going to update that with all of the macros that we have and the move button we've created some more add and we've also details show and details hi so now we have those I've just pasted those macros in and then I can go over them with you if I just type them out it's gonna take a little bit longer so for the high details let's assign that macro assign macro and then we will hide product details hide that that macro and then of course the show details right click so now we've got our macro as assigned and we can move a little bit quicker now on debts and the show product details show ok so let's take a look at those bring that up again I'll format those two so we can align the top so that they're all set and then align the middle as well the lie on the left perhaps okay so those butts are alright so now we have high detail button we click that it's gonna hide it and show the details button let's go ahead and go over those two macros the product details show button all we're going to be doing is 9 through 12 we're gonna hide those rules and then we're going to hide the view details button and show the high details button so those buttons are gonna be opposite and then the height is just the same we're going to hide 9 to 12 here we're showing nine through twelve rows nine through twelve or hiding hidden eagles true and then again the view details button we are going to show that visible true and the hide visible false so that gives us that effect to hide so when we can hide the details and show the details just by doing that for these two rows okay great now we're set up with that now we've saved the translation let's go over what else we've changed in here now remember we have the move buttons but what I also want to do is I want to change the validation here take a look at this customer or vendor so now we have the difference so for customers we've changed the data validation this is vendors but if we change this to sales this becomes customers and how did we do that let's take a look at that under sheet macros we can see that we're going to delete on move button we're going to delete the validation on e5 we're deleting it first then what we're going to say is if b1 equals purchase then move the button to the left then b1 equals sales and then we're gonna set the validation and set the formula to the customers however if shapes move button equals else if move button move it to 30 like left b1 equals purchases and the validation equals vendors so that's how we set it up when we move the button it changes the validation either with vendors or the customers so that's how we do that through VBA so that is how we get automatically that validation to change and that's really important so that we can set that up so when we change it automatically changes here and we can change it put in the transaction date and we'll go ahead and format that for a short date and then move it to the left short date all right good I like it there alright so now we're said now let's get these buttons updated all right clicking save transaction you want to save it alright so we selected it now we have our new transaction we have a delete transaction we have our add picture and it looks like our add pic let's go ahead and take a look at that all right so we've got our die comes from our data so we want to add a picture and click on a picture and click OK and that's gonna add a picture and I'll show you how we did that it looks like it's a little bit low we can actually increase this little bit so it's there we go that should be fine we've increased it and then we can move this up a little bit all right good so now we've arranged it now we've got our high details and show details we've got our quantity let's go ahead and update the formula for our quantity on hand but we're gonna start out we want to wrap this in an error in just in case there's an error we don't want anything to happen we don't want it to show an error so if error now we're gonna use some if something is because it's gonna be multiple ifs in the sum if we're going to start out with the transaction quantity transaction because that's what we're gonna be summing that's their sum range and our first criteria is going to be transaction product number transaction the product number right what is that criteria a little must be this number here so we're gonna we're gonna do all sum all the transaction in all the product quantities and we also want to know all the purchases so the transaction and I want to know only on purchases for right now so the transaction type for our next one transaction type must be purchases and be purchased minus sales so purchase right so that's what I want to know I want to know all of that right but I just don't want it I don't want that needs to subtract purchases - the sales purchases - the sales so - some ifs and again here we go the sum range is also going to be transaction quantity then our criteria also is going to be transaction product number I want to know that as well and the product number should be this as well isse Evan and this time the transaction type is going to be sales transaction type sales so the criteria sales here there we go now if it's an error double click ok so that is our that is our quantity there in this case it's minus 22 we should probably increase the column a little bit so we can there we go okay right click their shirts a little bit close not perfect but you get the point okay so now we know now we know if we choose another item we can see minus 10 or minus 20 in this case our numbers are not to specifically correct but that's okay we are not concerned about that let's format these dates here under a short date all right there we go so we can format these prices as well for currency good the quantity now we were getting set up now it's looking and looking more and more like a software now we've got quantity on hand at 10 and the total is we can we can change the total we can add in that formula as well that's basically going to be equals sales quantity times the price times the sales price okay all right let's also give this total format that of background because we're not going to be entering it's going to be a straight formula so there's no user entry into this total so that's gonna work and we'll go ahead and update that perfect all right so now we're set up we can also format the total column here as a number there dollar amount okay we want to format these as well all the same accounting okay good all right now they're all the same now we've got the quantity we can even Center quantities here okay we could put that in all right centered quantities okay let's go ahead and now when we select a particular item we've got our totals that come up we've got it looks like we got to add conditional formatting to here we need to add this cell to the conditional formatting notice how it's not so we can go ahead and that's pretty easy just click on any cell that contains the conditional formatting man is the rules go ahead into that specific click on here and then we'll go ahead and add it to that by clicking on the cell itself and then make sure we enter a comma and then apply that and now we've got that last sell that's covered into our conditional formatting now we've got it set now we've got our conditional formatting set on the background we've got it set on the fonts and now we're ready to go into more detail on the particular VBA that we've had because we've got ad picture let's go ahead and show you what we did there and how that was I just copied and pasted that from another application just to save you from typing but saved me from typing and you from watching me type but what we'll do is we'll go ahead and go through each one of them it's on the sheet Mac are the product macros here into that module and we have what's called product attached thumb now when we click that button it's gonna look for a particular file it's gonna open up the file dialog right application file dog this is gonna open it up it's gonna set that picture file and then with that picture file it's going to give it a title of set a product picture it's gonna use these filters all filters type of pictures JPEGs jpg gif PNG jiff so it's gonna give those all that type and then in case there's in case the user doesn't make any selection it's going to go to no selection it's just in case the user cancels out of it we want to we don't want to skip this in an assuming they don't and they do select it that sheet 1o 14 is gonna equal that filename that's the full file name in Oh 14 so that's gonna transfer there and then with and we're also what we want to do is we want to say hey if this is an existing item let's say let's say we already have this item okay let's pull up an item that doesn't okay this one B AIDS there's our one there's no picture associated with this and we go into products and we look at be a zero zero one you see there's no picture link right what I want to do is I want to say if it's an existing product if it's an existing product in this case it is so let's make sure that set to false and we'll update that if it's an existing product what I want to do is I want to take this product row and I want to add that file name that long filename once it's put here in oh but i also want to add it to K and the product Rho the product o is 6 K is a column I want to put it right there so we know the column because it's always in K we know the product row because it's here using our match we know it's 6 so we know where to put it now so in this particular vba code what we're saying is if b 7 equals false that means it's not as long as it's an existing existing product B 7 stalls then sheet 2 K and B 6 value the row the product row equals o 14 which is the filing so when we go ahead and we add a file names gonna have so if we add a picture to that and we had a specific t-shirt let's say we add this small red one or this big red one and we add that it's gonna add it both you can add that long found them here and also here k 6 you see it's just been added so that line of code adds us so that's time the next time we pull up that picture the next time we pull up that product the picture will display so for example when we create a new transaction or if we even just check on another transition and we pull up that old product ba 0 0 1 it's gonna come up again so you see automatically because it's been saved and now it comes up so saving it not only to the current file but to the actual product information database here is very important so we want that so we can that will come up so that is wide now let's focus on so now we know how to get the picture and file name here and now we know how to get it into products but how do we get that picture displaying right here and in that manner let's go over the macro that's going to do that pulling it up back up we have a macro called display product display thumb and you see right after right after we insert a picture we're gonna run this macro product display thumb now let's go through that macro it's a very simple macro we're going to dimension picture path as the string that's the minnow full path and then what we want to do is I want to delete any picture that might be there the pictures that get created always have the same name some pic always gonna have that same name so that way when we we always know we delete it we don't get out that way we don't get a hundred pictures every time we add one we only have one display at any given time that one always has the same name so we can easily delete it this on error resume next and on air go to zero we use these two lines we wrap them in air so that in case there is no thumb picture doesn't in case there is no picture it doesn't throw up an error next we set the picture path to Oh 14 of course if picture path equals empty we exits up if there is no file path we don't we can't hip sort a picture so we need to exit out and then next with pictures and of course where it's already sheet one right what's already sheet 1 so it's dot pictures which means sheet 1 dot pictures dot insert picture path this is the full name this inserts the picture it doesn't tell us where it doesn't tell us what size he just inserted the next few lines is gonna tell us what size and what name first we're gonna lock the aspect ratio this makes sure that setting the aspect ratio first locking that true too true is important we're gonna set the picture name because once we have a picture we can then work with it and we need that name in the next set here because we're gonna say with the thumb picture then I want to place it I want to place it in cave 3 on the left and on the top but I also want to move it to the left remember -60 is to the left plus 60 is to the right so we're gonna move it to the left of k3 the left of k3 60 pixels we're gonna move it down below top 20 pixels down negative would be up so that is how we get that place that's how we size it and that's how you place now let's say that's going a little bit too far down we need to make it go up because it is kind of close so let's let's set that increment top to maybe let's just say 17 so it's not so far close to that maybe we don't want to set it that far so now when we just select it and we slide there we go now it's a little bit higher you see even when we load the product it's gonna load that picture always that macro is going to run always when we load or when we add a picture that macro and that macro again is product display thumb so that's it all we are doing basically is we're inserting a shape we're deleting any shape we are setting the aspect ratio setting the height and naming it then we're positioning it at k3 we're moving it a little bit over to left and a little bit down that is it that is all we're doing to display the thumb so it's very powerful especially when you have lots of products all right moving along I'd like to go back into the transactions on sheet macro and focus on the selection change this is a relatively simple want two things to happen I want to load the transaction and I want to put the target rowing be two when a user makes a selection change to any cell between D 18 and K nine nine nine and when D and the target row does not equal empty and so what does that mean when a user selects any cell within that D 18 all the way to K and the nine nine nine I want two things to happen I want this information to load I want that sales transaction to load and I want the row that they've selected to appear in B 2 we use b2 not only to use conditional formatting to highlight the selected row but to use the row two when we make a change to a specific field we know what row to update that information to so that's very important so we do those two things and also we have other macros which are changed macros so that pretty much covers the selection change that's the only selection change now we went over the top part we of this which was the change macro when we make a change to any cell within the transaction we know that how we're gonna update the table below so when we make a change to any of these fields we're gonna change the table below we went over that based on the to call him here based on that these columns are mapped to products these are Mac two transactions transactions okay we can color those differently if it makes a little bit more sense or we can put borders Radek so these are for specifically for products product mapping because products map are in the product sheet so we can put a little border around that so we know that those are products okay next up what I'd like to do is continue on with that and now we have product change on product change or load product product change the look product if the user makes a change to e7 e 7 is the product ID here ye 7 what do I want to happen well I want a few things to happen I want the new product information to load and if there's a picture I want to load that but I also want it to update right here the product number I want it to update so I want that product number to update in the transaction because this is already a current transaction so those two things need to happen so if a 7 there's a change and a 7 doesn't equal empty we want to make sure that they've entered a value and then I want to do some things one I want to check B 6 to make sure that's gonna be what is B 6 B 6 is the product row we want to make sure that there's a row a product that was found right we have to find the product for example this is 17 right test name 3 17 we need to make sure if we're going to pull the information from this table and put it in these fields we need to we need the row of the table so this row is critical we need to make sure that is there so next up what we need to do is then we once we know it's empty we can say if B 2 does not equal F BB choose the current row and B 4 equals false right B 4 and B 2 let's look at those B 2 we have to have a transaction row we have to make sure that the transaction is not loading right if those are the case in that case take this product name we take this product number and put it right here put it in G and the row so that's what we're gonna do with that line of code right here then G range that's the product row G an approach evils isse Evan he's seven you can also say target value we could do that as well cuz that is the target value but e 7 is the same so we're gonna save that in the table and next up we're gonna run a macro called product load product load is going to load all of the details into our product table it's going to load the sales price that's going to load the product name it's going to load the product type it's going to load the standard purchase quantity and the product description it's going to load all of that right there so let's go into the product load and take a look at that macro under the product macros we have something called product load we're gonna primarily focus with sheet 1 so we're gonna say if B 6 empty then exit this up of course we need to have a product row so B 6 is worth the product row will then be in B 6 so we've got that set up so now it's just a matter of setting the information product load to true we're loading the product so we need to set it to true and of course we'll set it to false once we're done the product load value true or false is stored in B 8 next we're gonna run from product column 5 to 11 why are we doing 5 to 11 because that is where we have our map data 5 5 to 11 we already have the product number so we don't need to start at 4 5 to 11 right we can we put all that we've mapped the dated product name goes in ie9 sales price and goes in h7 type goes in h9 H that is the quantity on hand that is a calculated field we already have a calculator there so we don't need that because it's calculated of course I is the purchase quantity and so on it goes all the way so we're gonna map 5 to Y is just 5 let's look at it to column 5 column 5 they call them 5 to 11 5 to 11 okay so that's how we get that now let's continue in on that and look look how we do that all right 5 to 11 if the product column does not equal 8 what is 8 8 is the calculator field we don't have a map in it because it's already a 4 milla right we don't need to add the quantity on hand that's already a formula right here right there we don't want to mess with that I probably should put this in a different location I'm going to the picture over a little bit but it's okay for now you can move it to anywhere you like I just found to put it there the quantity on hand is located there so that's a formula we don't need to touch that so that would be in eighth right so we don't we can skip eighth and continue on so as long as it does not equal eight then the range the range of what we should put the range we should put dot range that should be dot range because we're focused on sheet1 right so it should be dot range sheet one and what is where are we going - what is that range it's the located in in row 1 sheet 2 product column right row 1 that's where we this is our range right here that information Row one product column that is this right here it's this it's this it's this it's this so it's gonna tell us where to put that so that is why we need that information here so that's going to tell us so it's gonna say the range of h2 or 87 or whatever equals sheet to the cells the product row and the product column that is how we're gonna get all of our product data into art and we're gonna loop that all the way from 5 to 11 but make sure to exclude if it's 8 or not going to do it next up we need to shapes view detail but invisible equals false we want to make sure that if the shape view detail button is false we don't need this actually this is to get rid of that I had some shapes up there but it's not necessary I had add product new product but we don't need that I've limited that and giving you the ability to add products very easily just by typing them in so it's even better all right so that's easy product display thumb we went over that this displays the picture if there is a picture and b8 false so that is all we need to do to load the product information in there so it's very very simple and that is how so that's now we've we've gone through here now else we're gonna cover this next I'd also like to be able to add products very easily just by typing them in they don't exist I want to be given the option to add those products so we can do that here we go into the application if we if I want to type in a product number like if let's say something doesn't exist like ba four or five six if that product doesn't exist I want to be given option this product does not currently exist would you like to add it now yes and then done it's that simple so then we can give it a sales price a product name test name and a type and so I want to show you how we do that just by entering a name so let's go ahead into back into the code to show you how it's done in here in the product list if you look the last one test name it's right here so that's the one we just interest so let's see how we do that and remember this the product row is B 6 B 6 and that's calculated based on that so if it's not found if we enter something that's not found that product row is gonna be empty because it's gonna be rare so remember that that product Row is going to be empty so we're gonna use that empty so we're saying here in this if B 6 value does not equal empty then do this however if it's empty else then do this if messagebox this product does not currently exist would you like to add it out VBS or no product not found as the title if VBS then do all of this do all of this if they say no just clear the contents clear the contents of the target so that means clear so it's so simple let me just show you how to do that we know it's not found we know it's not found so all we need to do is determine the first available row and sheet to the first available row of course is the last row in column D on sheet 2 that's our products plus 1 that's our first available row now we know the product row now all we need to do is sheet 2 and the product row all we need to do is add that target value and of course we need to add the formula that counts it now that's formula is the same one we just entered some ifs transaction quantity transaction product name and I'll go over this formula a little bit later on you so you can look at it but it's the formula that we do and so let's go ahead and look at that formula into the products we go and now we can see into the products we have this formula that was just entered automatically some if and what are we going to be summing the transaction quantity and what is the first criteria range transaction product number and that's D 24 right here it's the product number and what is the transaction type purchase so that's the purchase and so what we're going to be doing is we're gonna be summing all of the transactions where the product number in the transactions our product D 24 and the type is purchased - all of the transactions that have the product D 24 and the transaction type of sales so we're going to do subtraction the sum - some of the purchases - the sum of the sales equal the total quantity on hand so that is the sum and that is that is what's entered this way we don't need to enter formulas for all of the fields that do not contain values as we enter data then the formula gets entered so that's how it works within the code let's go back into the code and take a look at and continue with that so once that formula is entered we can then clear the contents of the remaining fields for the product since it's a new product we need to clear out all of the other fields of course and that includes the sales price which is 8 7 of course een ein h 9 e 11 and of course H 11 through K 11 as well of course as the picture link if there is any in O 14 so all those fields need to be cleared out that our product specific and they're all cleared out right here in this so then we're ready we just have to set the new product to false because we've already saved a new product the the product number here the formula here so anything else it's no longer a new party it's now been injured and it's also because we have entered the target value in column D it is now available in our list so this new item is now available at the bottom of the list so let's go over that one more time let's pick a number e d one two three four okay do you this product does not currently exist do you want to edit it add it now yes okay all of the items are cleared out that is now available it's so easy to add to the list and our formula is already entered as well so now all we need to do is enter the rest of the product names because it's now an existing product we now know that it goes into row 25 that is the brand new row so now when we add test name it knows exactly where to put this product row 25 product name test name we know exactly where to put it right here E and row 25 so the same with the rest of the values could all go there so that's how it's done that's how we add items so it's really really powerful so that's easily how we get that information into the product table as quickly as possible we can add quantities such as standard product quantities the product test description as well as type and the sales price very easily just by typing them in and since we know they'll automatically make it to that row so it's very easy and even if we want to add a picture to that we can simply add the picture there select an item that we would want click OK and then both of the picture link gets added 200 14 as well as our new product it's always there and then becomes available for any other type of transaction so that is how we get that information and now let's go ahead and continue with our code else we have target clearance so if the user says no if we enter a name and let's say we have a let's say cancel the new transaction let's enter a new transaction if we enter a product name and the user says no then it just goes back and it clears that so target clear contents that is all that is done there so that we covered that all right next up on change of existing products the last one this is the change update the product change of existing if there is a change from een ein to e 11 changing anything we want to change those products we just went over that that's the same thing if there's any change from e 9 all the way over any of these details here we are then going to make those changes directly into the product here we just went over that so that's covered there and all that that does is it sets the product ro b6 and then it takes our target call them target row target column +10 again we've mapped out all of our information here so we have our product product table column five product table column seven etc etc and that's how we map those out as we've done mapping previous pose ok let's go ahead and get into our last part which is our save transaction and we've been over this a few times but let's go ahead and go over it and that's how we we have we've been over low transaction now we'll go over save and save it's pretty simple first we want to make sure that ePHI contains a value that is the that is the transaction the first part that's the vendor or the customers we want to make sure please select a customer vendor I want to make sure that that contains a value we're gonna set the transaction row to the first available on sheet1 plus 1 so it's d rho plus 1 then we're gonna go ahead and set our transaction columns from 4 to 11 using our mapping that we set up here for all the way to 11 we're setting it up and of course if it's 10 that's like that is a formula so we're gonna put in a formula in that case if the transaction column does not equal 10 then we just go through each transaction row each transaction column 4 to 11 it's going to be the cells row 16 is where our mapping is and the transaction column what is that value well that value right here is either going to be d 1 e 5 H 5 or h7 so it's all located those are the ranges so those are the ranges so each value in the row will equal whatever the range value is here and then of course if it is if it is of course column 10 we just want to put in a formula there which is going to be equal H and the transaction row and times I and the transaction so that's going to enter our formula our formula in the total row in this case it's going to be H 27 through I 27 I just paste it in data but we can copy down the formulas for their existing as well so we can get those so that is how that is going to handle that that is how we enter the formula and so that stays consistent so that way if we update the value for example if we just update the value we want the total to update to and that's why we use a formula or if we update the amount if we update the amount in this row we want also the price to update as well so that's important and so that's why we take care of that so for example 18 15 we can update that sales price it was just data in there all right so that works very well moving on let's continue down and next up we want to hide or show the shapes based on the type so existing transaction we're gonna miss it's no longer a new transaction once we save it so we want an existing transaction group to display and the new transaction group to be hidden that's it for that B 3 will become false with the transaction new all we're going to be sitting is the transaction 2 new and B 3 clearing out all the necessary fields I'm gonna set the current date H 5 to the current date that's kind of a nice and then we're gonna of course set the existing transaction group buttons to false while the new becomes visible and we're gonna delete any picture if it's visible any thumbnail picture for new we're going to delete that for the transaction delete all we need to do is make sure that b2 contains a number which is going to be the row of our database if it's empty we're gonna exit this we're gonna give them an option are you sure you want to delete this transaction yes or no and then title a delete transaction DB know if it's no we're just gonna exit the sub otherwise we will continue on the transaction row is going to be in b2 no we're just gonna use transaction row and colon quotation marks and transaction row entire row deletes we're going to delete the row using the transaction row here and then we're gonna select D 18 which is the first transaction in our table and that just sets a default of the first transaction cancel new does the same thing selects the first transaction so that way we can get out of the new so when we go into new transaction we want to cancel it all we're gonna be doing it's the same thing as selecting new so when we go to new transaction canceling new performs the same option let's go ahead and set that so if we go new transaction cancel new it's just going to set that deleting the transaction will work we can click on any specific transaction click delete transaction are you sure you want to delete this yes that's gonna delete the transaction and it's automatically gone and the next transaction appears in the first that is pretty much it I may make a few small changes to this in part 2 if you see any changes but otherwise I'll go through this application I think we're it's almost done I'll double check all the fields before I release it out to you so I hope you like this part dual part inventory manager thank you very much for this long training and I appreciate it have a great day thanks so much

Channel: Excel For Freelancers

Views: 195,106

Rating: 4.9442139 out of 5

Keywords: Excel Inventory, Inventory Excel, Excel Inventory Manager, Manage Inventory With Excel, Excel Purchases, Excel Sales Manager, Excel Inventory Tracking, Track Inventory with Excel, Track Inventory Excel, MS Excel Inventory, Excel Inventory MS Excel, Count Inventory in Excel, Purchses Sales Excel, Excel Parts Tracking, Excel Inventory Mgr, Manage Parts in Excel

Id: GodLw1LfTj4

Length: 54min 38sec (3278 seconds)

Published: Tue Sep 04 2018