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.