How To Make A Point Of Sale (POS) Application With Inventory In Excel [Masterclass & Free Download]

[Music] hello this is randy with excel for freelancers and welcome to the point of sale with inventory this week i've got an incredible training for you i'm going to show you how you can create this amazing point of sale with inventory and that means for both purchases and for sales we're going to keep track of inventory i'm going to show you how to add items and also increase the quantity also remove items we're going to be able to also search for items just with an entering and we're going to be able to search for vendors or for customers simply by selecting it's going to be an incredible training i've got so much to show you we're going to do it all from scratch so let's get started all right thanks so much for joining me i've got an amazing training for you point of sale this is not the first point of sale back a while ago we did the original point of sale with nearly 3 million views i took a look at over 2 000 comments and suggestions on that video we read every single one of them we put all that to work and we're going to make an even better point of sale today we've got dynamic categories dynamic custom categories are going to be add items if we double click we're going to be able to simply increase the quantity just by selecting on it we're going to be able to decrease the quantity we are going to be able to clear pay print refund void and click next just by a click of a button we're going to show you how you can create your own in this training i hope you do like these trainings if you do all i ask is that you do click the subscribe and don't forget to click on that notification icon bell that is going to ensure that you get these trainings with you each and every tuesday i create these free of charge if you like this workbench you want this workbook and you want 200 of my best workbook templates i've got an incredible promotion right now just released 200 for only 77 and that's going to include in a complete library workbook so that means a single click on your excel to open the application and a single click to open the youtube complete with a library i hope you'll get it that's going to help us out here keep these trainings for free every week if you like that that's the 200 workbook zip file in fact it's even more than 200 workbooks so i hope you'll pick that up all right this is the sample copy this is what it's going to look like you're going to be able to log in and log out also forgot to show you that so that's going to be incredible we'll have multiple users we're going to do it all from scratch this is the sample i'm going to close this up and we're going to recreate this right now from this sheet now let's get started here now i did put a little bit of data in here and some other sheets that's going to help move things along a little quicker we've got a lot to do in this training as it is all right just go a little over a rundown of what we have in this application all the components of it this of course is going to be our pos sheet that's what you just saw in the sample we're going to be creating it right from this screen from this sheet but we also have an admin screen now admin we're going to be able to set categories set icons here so that way you can change this you can learn how to not only change this but to customize it and create it yourself we've got our main categories here drinks and beverages fruits and all the way across here then we have a set of subcategories so for example for drinks we've got soda wine beer and so on and so forth so each category has a list of sub categories gonna help us out a lot to do that we also we also have the tax name so we can customize an individual tax name we've got a tax rate so that we can customize it if we want to set it to 8.5 it's going to automatically be set and we also have a footer message now keep in mind i have some name branch already set this is called tax name notice up here tax name this is called tax rate this is called footer message okay i've also have order statuses so that we can customize if we create an order but it's not paid for it's going to be open if it's paid if we've refunded or if we void it now this way you can customize it yourself i've also got different roles right a role would be for a user for example we might want a certain user to only be able to add sales and do nothing more not void not clear or not do anything we might also want another staff to be able to add sales and purchases right sales and purchase we're going to have both and also we want to make sure we want to have a manager that can do everything add edit void refund sales and purchases so we've got three roles of course you can set up more those are user roles so we've got all that in there and then also in the orders i've just got some data in some order so we're going to have an order id an order time whether type whether it was a purchase or sale a status whether it's paid or open customer vendor if there's any not necessarily we don't necessarily need a customer but we can put one in if we want to we can have a user in fact i'll just copy this put some more data in here so it'll be helpful for filtering a user right whatever the user was just some test so let's update that and we don't need that so and also if they've paid the total price of paid now we're going to come up to this a little bit later on in this we have order items those are the individual items per order so for example if i've got an order id it was a purchase and row three we've got that the sku or upc number the product name the quantity and the amount if there was a discount we're going to go over this so you don't need to get everything right away you don't understand exactly what it's for but i just want to know that these are all the individual items on a per order basis so for example all of these orders here were for order number 1000. all of these order items are for order number one zero zero one we also have a list of customers just a basic list of customers we'll see how far we get if we're going to be adding new customers you can always add them to this list and we also have a sorted name of customer sorted name we want that we want one list of customers and then we want a sorted list so that we can sort them alphabetically also on the vendors exactly the same let's think about putting total sales here total purchases we can do that pretty easily if we want to we'll see it depends we've got a lot to do in this training so vendor i've got a vendor id and just some basic vendors just something very basic a list of products i put in i had my my staff helped me with this a lot of products here so we got a lot of products and that's going to help us get create those product categories and those individual items so set up some products here and just some fake prices here and just a list of users a basic list of users user id username and then the role that they'd have so that is also going to help us so that's all we have there that's all i've set up everything else is going to come here on this point of sale so what we're going to do is we're going to create those first two columns as we almost always do if you haven't had these trainings i'm going to i'll leave these for admin so what the idea is to eventually just hide these columns and then nobody will see them these are for admin purposes and we're going to use probably the first let's say 11 for one purpose and i'm just going to give that a different color and a board i'm going to pin this here and then what we're going to do is just going to give it a border and then i'll give it a green color because i want those specifically for the pos we'll give this light green and then i want to do let's say 13 through 17. we're going to this is going to be used for totaling and subtotaling in other words i want my totals and subtotals and and payment information to show up here so i'm going to call call that a little bit different it's going to be linked here right so nobody's going to see this once we hide these columns then we'll do one space or column and then let's put some headers just we always do all the way up let's go all the way to let's just say y here i'm going to format those cells and i'm just going to give it a fill here and i'm going to do a fill effects so we're going to start out at a darker color here then we'll just go to a lighter color medium color here and then give it that fill effect the line below is going to be one less so i'm going to do that right here format those cells i'm going to do fill effects again we're going to go a little bit lighter on this one so i'm going to go this one to the lightest blue and then it is that lightest blue that we're going to use inside the background the background and that's what's going to be our general color so then what i'm going to do is i'm going to go all the way down here just go all the way down enough rows so that we cover that and i'm just going to give it that background fill which is that basic so we've got that nice fade effect here that looks good okay saving our work more often hopefully we'll remember what i want to do is i'm going to give this a title and so i'm going to use in this case i'm going to we can use whether it's cell based or text based but i'll use in this case i'm going to use a shape-based title which sometimes we do so we're going to call it this is going to be called point of sale and then we'll give it with inventory because i'm going to be tracking inventory on this and that means both purchases and sales all right so now let's customize that we'll increase the font here to about 36. i'm going to also use a different font we'll go with one of my favorites just clear we're going to go with arial rounded which is here i'll increase it and then i don't need any border and i don't need any fill so we'll shape fill will go to no fill and the outline will go to no outline and then we'll increase it so we can see it all point to sale with okay inventory all right that looks good it's nice and big i'm just going to center that here make it nice and big and bring it all the way across here okay i like that and we'll give it a little bit of a different color maybe this color okay point of sale with inventory make sure i spelled that right otherwise you're going to be staring at misspelling for the next hour two hours or however long it takes us to do this so what else do we want here i want to i want to know if it's a purchase or sale that's kind of important right so let's fill in some of the information here what we want i want to know what type it is so we're going to put the type in here is it a sale or is it a purchase very important here i also want to know when the order is going to be loaded we may not need that i'll leave this one empty for now i don't think we're going to need order load but we might need this a little bit later on let me also put in the three i want to know the order database row in other words what row order database bro what roads here's our orders here's our database is it row three four five that's going to be based on that order id i also have some name ranges already set up so that's gonna go here here i want the next order id next order id we're gonna go over the name ranges very soon here i'm gonna want the search order row and so when we search for something i'm going to show you what that's like and then we'll we'll build this out a little bit more but let me put in after that the selected row i want to know what row selected let's go ahead and continue with the design here so we can know what we're going to do here inside d3 this is emergence center i've already had it set up i want the order results remember i showed you that search form right i want these columns to be hidden unless we're searching for something and that i want those results to come down here so we're just going to call this order results and they're going to come here and then what i want is the ordered on day i want to know when it was ordered that date order on and the next one i want customer or vendor could be a customer or vendor in fact we don't know if it's going to be a customer vendor so what we can do is we can just write in customer vendor we'll even abbreviate that make it customer or vendor then what i want to know is i want to know the order id which is the order id so when i select on one of those orders i want it to to load up here we'll give it some a little bit of a color format those cells maybe just the gray fade out so gray will be our accent color we'll use that so blue is our main color so i'll use gray as our accent color and then again i'm going to use the fill effect here and here and then i'm going to do here the same thing a little bit less formatting those cells and then fill effects here then we'll just go to the light gray here and then the lighter gray here so that's going to give it a nice fade effect for the both the heading and the sub heading here then i'm just going to also add whoop and there we go so we got the so i want those results now we're going to put a button here to close that right if once i'm done with those orders i want these to be hidden so we'll add that in very very soon what else do i want i want a spacer here so this row is going to be a spacer here i want the user to enter the order here and be i3 i want them to enter so we'll do enter let's just put order and they'll put an icon there and then here i want them to search for the customer enter customer or vendor we don't know which one it's going to be could be a vendor could be a customer here what i want to do is i want to have let's take we don't need a data validation here let's clear that out data validation and then clear clear all okay so here what i want is basically like a formula based on whether it is a sale or purchase here i'm going to put the customer vendor notice i have a drop down list here data validation it's kind of i was kind of cheated a little bit right okay but that's okay so basically here in this list i want the customers or the vendors drop down list here so here in this label portion i want to show what is it a customer or is it a vendor so but i'm not sure it's based on whether it's a sale or whether it's a purchase if it's a sale it's going to be customers so equals if in this case b1 equals let's get if the word right not iffy it's not iffy it's for sure if b1 equals sale then worse then we're selling it and we want to show the customer right or something we're selling then what i want to appear here is customer and then a colon customer then colon otherwise it's going to be vendor and then colon okay so that's it so basically as soon as this changes to let's say purchase and then it's going to automatically change the vendor that's what i want okay perfect so then what they'll have is a drop down list of either one here and then what i want to have here is this to be able to scan in the u the s the sku or the upc or whichever one that code right i want to put scan the user will scan it in here sku or the upc it's the same thing upc called different so here they're going to scan for that item here should have cleared out the data validation so i'm going to do it right now so i'm going to do that just to clear it out and then we'll go ahead and add it in data validation and then click ok and then clear all okay basically that's what i'm have here so these three i'm going to make white here so we want to put the white background because they're going to be user entered and then borders all the way around for everything so here we'll go all the borders then so basically they're going to enter the vendor or the customer here based on whether it's for sale they're going to enter the upc here and then also what i want to have is a user right we're going to have a user name i want that to appear here and then i want the order on date here order on date right here below that we don't have much here we don't need to put much in here i'll put a formula in here but we're good on that so the username will go here so we'll just put in let's say fred fretters you know fred's a great and then that's just temporary fredders fred's got to make his appearance in this video he's made his appearance everywhere okay now let's say the status here i want to know the status is it open or close status and then let's just say it's open that's going to be that's not going to be user generated so that's going to be oh so then i want to put in let's say after that maybe the order id order id and that's going to go in p order id so the official order id is going to go right in here status is going to go here i like that okay so i'm going to put borders around there notice it's kind of merged and sit because i just cleared it out from the previous that's why things look good just to make things faster it's already going to be a long video so i like that the order opens so none of these are going to be editable they're editable they will stay in blue just like that here what i want to do is i want to have the quantity on hand and so here is going to start out here's going to be where our table starts out so right under here is where we're going to have i want to have that table so here it's going to be the sku we'll put the sku or id whatever it is ku upc id here then the another header we're going to the product name and then also after that i want to know the quantity i'm going to put in the quantity or order quantity then what we'll do is we'll have the amount so then the amount what is the initial amount i want to be able to put in a discount this time if they want to put a discount on a per item basis that was one of the things that was requested so i want to do that for you and then the total okay so that's pretty good so i got that so all of these are going to get uh just a basic color here this is what's going to show up on their receipt this is the header there's the first part of it so i'm going to format those cells and then we'll just give it a let's say this gray color here we'll give it a light gray color that's a good start so it shows out and i like that there and then we just need to give it a border all the way around i'm going to give it a border all the way around it here format those cells and maybe i'll do the border all the way around and then i'll do the fill i'll just do from light to medium i think that should be kind of a nice look for the receipt there so i'm just going to go from light in the two color and then to medium here that's nice okay clicking okay and then bring this up into your screen view and clicking okay so down here is where i'm gonna have all the information here and down here is we're gonna we're gonna have conditional formatting so that as we add rows here we want that conditional formatting and then what i want to do is what you saw was called a floating footer now that footer is going to be based on shapes right so that means that footage is going to include the address the barcode the subtotal everything it's going to be all based on shapes that way we can easily move down that shape as we add or remove items it moves up and moves down automatically very cool so i'm going to show you that today so here is where i want to put let's put in the total let's bring and let's bring this out a little bit here and just like that okay i don't want to have a border between there so i'm going to format that so let's just remove that center border here very good i like that the way it looks good and so here is where i want to put those quantities on hand so i'm going to format this cell and just give it a little bit of a blue border this quantity on hand will not be printed on a receipt so it'll be a little bit different but i do we do want to be able to show when we add an item how what is the stock level of that so we want to know that so that's good so we'll have that stock level every time we add an item so okay just going to put some borders around there that should be fine any border so here's where we're going to have the items and then here all the way over here is where we're going to have our specific shapes right so they're going to start here i've got in fact six shapes here it's going to go all the way over here and it's going to be really cool well let's do that let's let's bring these colors down i want to add a few more columns on this one so we'll just bring it all the way over here that's good so bring it over basically i'm gonna have six different categories six different categories let's build that out now i want those categories to show up based just like these columns are right up here you can have a log out button right up here so these shapes are going to be here so let's add those category shapes in now so i'm going to insert a shape it's going to be just a square shape and i'm going to put it down here now the size of it let's do 0.75 by 0.75 that should be 0.75 and then by 0.75 do that that'll be perfect what i want to do is i want to have the first one show up here right about say here let's give it a look i want to give it a specific look something like this here probably go a little bit darker on the color there but we'll set those colors up inside vba because i'm going to have them switch when we select it's going to go to one color and then it's going to go to other color so i'm going to give this a name because i want this to be called category 1. so i want this so category 1 2 3 4 5 6. so we're going to replace them it's going to also be based on the columns so i wanted to show basically on the left of this column right here and then now it's spaced out according now i've also spaced out these columns a little bit better to help us so i'm going to duplicate that and we're going to create six of them here and this one's gonna be called category two and then the names are very important so then i'm gonna do again category three here and then three more times and then ctrl d again making this category four and the placing is not as important right now and then duplicating it one more five and then lastly six so i've got six main categories that's what i want to do with it so six so we've got our categories in here we're gonna put those icons now what do i want the contents of those i've got these icons we're gonna they'll be spaced out a little bit more like here anyway so a little bit so we want the last one here based on this column here to the left and so what i'm gonna do is i'm gonna hold down the control here i'm gonna make sure they're lined up vertically and then i'm going to format and then i'm going to align those i'm going to just distribute them horizontally so what do i want i want those to be basically equal to whatever is in these categories here so this one if i link it directly it'll be automatic so why don't we do that based on the admin what is in d4 so all i need to do to link that up is just inside the formula bar here do equals and then go over to admin here and then just change that to d4 now i'm going to copy that and i'm going to enter so that's going to add it up automatically there but that's not exactly i want to format that but i want to format them all so i'm going to hold down the control and i'm going to adjust the size and the properties and we're going to go into the text so the first thing what i want to do inside the text box is i really want to reduce those margins down to almost zero so we'll start out with zero because i want to make sure to fit as much text as possible inside i want it to be placed on the bottom i want to put an icon and i want to put it centered like that because i want to put the icon on top of that and i want to do it for all of them all right that looks good i think i'll add a little bit of padding on the bottom notice the bottom there size i'm probably just a tiny bit so the text box on the bottom we could do something like point zero two that'll raise it up just a little bit and we've done it for all of them because they're all selected okay saving our work and now what we're going to do is i've already copied this so now all i need to do this is going to be if that was d4 this is going to be e4 if this is that was e4 this is going to be f4 and so on and so forth this will be g and make sure we have all of ours and then this is going to be h and then lastly i if i remember my alphabet properly it's been a few years but we'll try and then i okay so now we've got them all there so we've got all of them they're linked which i like so if you were to change any one of these it would automatically change of course you'd certainly want to make sure that your products also have the same categories that's very important so we've got our we've got our main categories now and that's i like that the way they look and we're going to change the way they look when we select on them we also want our subcategories to appear here now we're going to have up to 12 subcategories so let's add those in the subcategory is going to look a little bit different what i'm going to do is i'm going to duplicate this one and i'm going to bring it down here and of course it's going to have a different look different feel different size so first thing i want to do is going to keep the same width but i want that height to be about half it or a little bit more so i'm going to do 0.4 on that so then also i want a different look so let's just temporarily give it this look and we'll give it the black font here so we don't know this is going to be dynamic what's going to be the text is not going to be equal so it's not going to be equal it's going to be based dynamically on whatever we say this so we'll just keep that there for now and basically so as what we're going to do is when i select drinks and beverages what i want to do inside the products is i want to put whatever category is in here and then what i want to do is i want to have all the sub categories for that or also what we can do is from our admin we've got it here so all i need to do is i know that so what i want to do is i want to build all 12 shapes based on whatever category was selected and then i want them to be equal so we can use a formula but we'll use it inside vba because it could be d5 it could have could be d you know e5 and so on and so forth so we'll bring that but certainly we need 12 shapes exactly the same size we'll do six across and then six down so that's what i'm going to do now okay so what i'm going to do is i'll just clear out the text here and then i'll duplicate that one here let's clear that out we don't need the text there we'll be adding it in dynamically i'll duplicate it there and place that right here and then we'll do the same thing again here here and here and then all i need to do is just copy those and we'll make sure that they're lined up properly and spaced out accordingly so we're going to line those up horizontally and then i want to space it out i'm going to be based on the right of this one so we're going to right justify align the right here and i want to align the left one on this one here so that everything's spaced out then what we can do is space them out horizontally so align again distribute horizontally then i've got them there then what i'm going to do is i'm going to hit ctrl d and duplicate that and bring that down right here so the idea is this so as you saw in the sample if you saw the beginning here what we want to do is just bring click on here and then have those 12 these 12 populate automatically so that's exactly what we're going to do all right so let's undo the selection save our work so far and then when we select on that i want dynamically the shapes that are going to be dynamically the shapes whatever food then what we're going to do is we're going to put in that category we're going to put in that sub category we're going to run an advanced filter so for every drink there's a soda i want that product name and i want that product to be here that upc or sk you'd appear here then what we're going to do is we're going to populate all the shapes with whatever's located in the product name whatever's down there we're going to show up here but now what i want to do is i want to rename these shapes they've all got to be named by subcategories so i'm going to open the selection pane here we're going to start out here i want to rename we'll start out right up here at the top let's go ahead and here that's going to be 12. so what i'm going to do is i'm going to start at the top and call this sub category and then 12. so i'll just copy and paste the text and then we're going to go down and just make these 11 so paste this and then 11 and then we'll do down here and then this will be 10 and we'll go in reverse here and then because these names are going to help us out we're going to need to know these names in order to assign specific text to them we'll have to make sure that they are okay now each one of these now have their own independent name and they're all lined up and that's going to be ready for our subcategories okay we can close this out and we can continue building out our screen so we're going to add some borders around here we need the borders around here i want to know the user ordered on here that looks good okay we've got our table now let's add some conditional formatting then what we're going to want to do is we're going to add our footer shapes i want to add a shape to a footer but let's add some conditional formatting here so that we can see different how we can show that up so i want to know if there's based on if there's a value here so let's do that right now so inside the conditional formatting new rule and in this case it's kind of a little bit different what i want to do is i want to color this row white so if so that means give the user the ability to enter the next row normally we only color the rows that have valid but this time i want to call it the also i want to consider the row below even if it's empty so that means we're going to start up with the row six so it's saying if six and we're going to apply to seven and i'll explain that in just a moment but first of what i'm going to do is go to conditional formatting and new rule and i'm going to be using two different rules because i want alternating rows but it's going to be based on two conditions so we're going to use and equals and and what is the first condition the first condition is going to be yes i know it's weird i six is that going to be equal value or not so we're going to i6 and of course i want to apply to every single row so going to make sure that that is not absolute does not equal empty then also the second condition has to be a mod bro so in this case odd or even rows mod based on so this would be the even rows so the even rows what i want to do is give it a white color and then i'll give it a lighter a lighter maybe a lighter gray color very light gray color so in this case i'm going to format this cell i'm just going to go to a fill of white so that's going to be white now what i'm going to do is i'm going to actually will change this to one i'll take it out for the odd rows but what i'm going to do is i'm going to copy this first then i'm going to change this to the odd rows and then i'm going to click ok now i want to add one more new but let's absolutely apply this to more than just 14 so 99 then click apply then what we're going to do is click a new rule use a formula again paste this in here now we're going to do the even rows i'm going to format i want this gray but i want a little bit lighter gray now we can do fill effects and we can do if we want to use a recent color we can use fill effects something that i've used here this gray here's a recent color so i've got that here and now i can use that our recent colors are always stored there click ok and now gray is going to be there click ok and then i want also to apply to 999 so i'm going to change this here and then apply again and then click ok all right so i like that now i got that one extra row that's exactly the way i wanted to the reason we started on six but we're applying to seven that's why it's one row down i want that extra row so now what i want is i want to put some borders on here so that we can look at that so let's take a look how do we put some borders on well the best way to do it is basically put the borders on now and then use conditional forebank to clear them out so i'll just put some borders on them and format those cells i'm going to put some left and right borders on there just some dark borders and then it is conditional formatting that will hide or show them accordingly so what i want them is i want them shown here but i want them hidden everywhere else so how would we do that well again it's going to be based on that one extra row so again conditional formatting manage rules this time a new rule here and it's going to be based on a formula it's going to be based on this again i6 is going to be equal when it's empty right because i want to do that and then i'm going to format those and i want to clear all the borders out here clear them all out so none none here clicking okay now make sure we adjust that applies to i need to fix that applies to obviously it's not going to be i6 it's going to be based on i7 and all the way down to 999 and so what that's going to do is when we click apply it's going to actually i got to go ahead and update that and then conditional formatting manage rules but we have to make sure that that row here is not absolute right it's got to be for every single row so click ok and then apply now it's going to be relative so now we have it all those rows are gone except for the ones and that actual row that's what we wanted on perfect like to add one additional conditional formatting for this table and that's going to be based on the selected rows so again i'll highlight them basically and go into the conditional formatting i want a new rule screen based on a row so i'm going to use a formula and what rows where's that row it's going to be located inside here b 6 i want to know the selected row so right now b6 is equal to the row when it's equal to whatever row we selected i want to format that and i'll just give it a light blue effect here something like that maybe just a little bit lighter blue so we'll go into more colors and then just click okay all right i like that i like that that's good enough click ok and so now we just need to update the applies to so we can make sure it applies to everything copy that we want it applying to everything click apply and now so when i select something this row will change so it'll go to 8 and then i want that row and maybe what we'll do is we'll bold the font also so so that it stands out a little bit more so all we need to do is go into the format and then go to the font and bold it so we can clearly see that it's been selected so now you see that one is now bold all right good so below that what we want to do is we want to add our floating footer so as we enter items here that floating footer automatically drops down drops down vba is going to help us with that so let's add in that right now but let's add in a few more buttons i want to add in the buttons and the shapes here and then what we'll add is the icons in after that because the icons will go on top so let's do that now so add in that floating footer so how are we going to do that well first of all i'm going to insert a shape just a larger shape that encompasses the entire area something like about like this and that shape is going to have a fill of white and it's going to have a shape outline of black and i don't want it that i want a very very basic outline you know something similar to like that which is our i think we can go with that one at half point that might be that should be look that looks good okay the width it will be automated and the height but i want the height to be right about 1.25 so what i what i want to do is i want to have about five rows of information in here i want the subtotal on the right i want the tax the total the total paid and the change i want to have that in here so i want to put that in here so as it it builds as we bring it down it builds up but everything stays consistent and it'll all be linked to this information here so it's going to be linked to a subtotal and put the subtotal in here it's going to be linked to a tax i want to know the tax whatever that is and then i also want to know the total total paid and i want to know which change so everything is going to be linked inside here so let's create some of those right now so the first thing what i want to do is insert shapes and we're going to again another text shape here this one's going to be for the total so here i want to put something like the sub total so sub and then we can put total here so and then i want to put the amount there so bring that out so i want to put everything it's okay to have our borders i'll eventually remove the outline on these shapes but i want them to keep them here to help to help us line things up so i'm just going to change it something dark so we can clearly see the the border here so i'm going to put the black outline here i like that and then we'll remove it at the last thing so the subtotal i want to right justify this one because all of our totals are going to be right justified and i want to give it the height of i'm going to have five different ones right so five basically rows so i want to i want this height to be 0.25 and the width on this one can be around one inch should be sufficient because there are some are going to be longer like tax so i'm going to duplicate that and we're going to call this but remember the tax we don't know what the tax is right we the tax name and the tax amount are both relative so why don't we put that in here the tax name and the tax amount right we have it here already in the admin we have the tax name it's located tax name and we have that the tax rate here so all we need to do is put a cell and combine those so let's do that right now inside the pi so instead of this i'm going to put equals tax name and we also want and we want to put the percentage in there and so then what else i want to put in this case the bracket here we'll put the parentheses then also i want to do and i also want to format that tax rate so i'm going to put text because i need that tax rate formatted what is that tax that's going to be based on the tax rate and how do i want to format i want to format it as a percentage so we put in zero in this case point zero zero and then the percent so then the percent here so that is going to be the format here then what else do we want then we want to close that bracket up we're closing that out because now we've got that text format but i don't and i want to add that other bracket the other parenthesis on here and then i want to put a colon there too so let's do that parentheses and then a colon and then in parenthesis okay so that's what i want here and quotations so tax i like that so it says the tax rate here the tax if i want to change it to you know gst here if i change it to gst then automatically in back in the pos has changed dsc now if i take this here which is going to be our tax and i equal to that label we have now a dynamic label based on that just like that so we if we change it back to tax it's going to change automatically same with the rate right if we do the rate at 8.25 here it's also going to change here so i want that based on that all right i like it i like that way that looks that looks good but i do i want to update the text parameters here this is too much right should go to .02 and then left we can keep that point zero two keep things small because we're gonna duplicate these for both left and right so we wanna the top i'm gonna keep zero and bottom zero okay click close and i want this in the middle right so now everything so we've got the tax i'm gonna bring this up here like this and now of course we're going to put in the actual amounts now the actual amount the subtotal is going to be here so if this total is let's say 324 this total is 525 i want the subtotal to show up so what is that sub total going to be it's going to be located right here inside here so it's going to be equals the sum of whatever's in here we'll just go all the way down to a large row we can go down to 9999 so that no matter what so now we've got the subtotal here again now all i need to do is just create another text box i'm going to duplicate that one this time it's going to be equals to here this one here so that now we have 845 so now we can bring this here and we just need to update the sizes great so we got that now we don't need it that big so we can bring it down to about let's say 0.85 that should be sufficient i'm going to line that up to the left here see it helps when we have borders because we can then line things up really nice even though we don't necessarily like the look of the border but it helps us for programming purposes so we can line things up now we've got our subtotal and now we've got our tax rate now we need to put the tax amount but we know that that can be simply automated based on our tax rate here so what is our tax amount it's going to be equal to this times our tax rate so now we've got our tax rate so now again all i need to do is duplicate this place it directly where i want to just bring it down here you can see and now equals instead of b13 of course it's going to be 14 and that's going to be actually so it's very very easy now our subtotal is simply going to be equal to the sum of these two the first two our subtotal is going to be our tax plus our subtotal and that's going to give us our total now all we need to do is just line these up accordingly let's put that bring out here and i'll all right so now all we need is the total so the total is just duplicating that i'll duplicate the subtotal here with ctrl d and then bring it down here there so i'll space it out accordingly then all we need to do duplicate this instead of this one it's going to be b15 so now we've got our subtotal next up i want our paid amount our total paid amount is going to be located here so if they paid 20 our change is simply going to be equal to our total paid amount minus our total so then we have our change amount so we can do the same thing duplicate and duplicate here so i want to line everything up here so now we have it all lined up here okay good so i like that i do want to change make sure the height is set here and the set 1.25 okay good so now we'll bring that up here all right there's going to be the payment amount located in b16 so that's going to change to 16 and then the change and the last one is going to be 17. then all we need to do is just duplicate the labels now the labels you can of course you can change them here or change them here either one you know it's fine if you don't have dynamic labels it's not that important just for the labels so it's fine total paid and then lastly what i want to have is the change so duplicate that again and then change so change just put in change i like that that looks pretty good just add the cone so we have some sort of consistency between these fields i'm going to line these up you don't need to watch me line these up now that we have everything lined up it's looking really good now what i want to do is i want to add in an information so i'm going to let's just say duplicate this one so i want to have a footer message here so it's going to be a nice messages that they can put in dynamically from the admin screen we'll keep that one a little bit longer so let's say three inches on this one and then i'm gonna left justify this one this one's also dynamic it's gonna be based on our footer message we have that already has a named range called footer message here so thank you for your business we'll make that bold so we're going to have that let's click on here make it bold here thank you for your business good that should stretch that can stretch all the way over to here because it's not going to put anything else in there and then also what i want to do is i want to have some information about the company so i'm going to duplicate this here and bring it down here and then also so we'll put a company address company name and things like that let's make sure this is 0.25 i like to keep things consistent here so update so this one's not going to be any cell reference on this one all we need to do is just put in some company reference let's just say fretters groceries one of my favorite places to shop and i can bring this all the way down so this will be a large text box it'll take up the rest of the space here on the left side then we're going to put some additional features inside additional fields inside our so left justify this and then upper on this one so freder's grocery so we can put some information about that one two three four five main street and then we can do let's say any town california and then 90232 you may want to have a phone number and email so 310-555-1212 and then email fred okay so we got an email i'll put in the 310 and i want to put that down here three one oh there we go fredders at fredders we love fred here fred's been with us a long time i'm not going to let him go just yet so bringing that there so now we've got this so what do i want to put in there i want to put the receipt number here i want to put the date the cashier who's the cashier so i want to put some additional fields here so bringing that down here and i'm going to use this space that everything gets lined up so here we're going to put the receipt number so again also not necessarily linked to any cell so this will be receipt number so then what i want to have is the date below that so put in the date here and then the date because i want to know let's just say order on because i want to put the date and the time probably so we'll need more than that so order on last i want to put the cashier who's the cashier so in in our case it's the user and the cashier is the same thing so cashier and then lastly i want the barcode so the barcode is going to take up the entire because it could be a larger so i'm going to duplicate that and then i'm going to stretch this all the way across here and i'm going to reset it to 0.25 because i want to make sure we're exact on that 0.25 on the height bringing it down here all right so instead of this what i want this is going to be the order so if the order id is 1 0 0 0 3 or something like that or whatever it is i want that to show up as a barcode so all we need to do is link it so i'm going to link this i'm going to center it i'm going to link it to our order id equals our order id so that's gonna create the one zero zero three but then i wanna change the font on this so i have one called code in my in my fonts called code 128. so that's going to change it all i do is actually just code 128. there we go so but i want it larger than that so i'm going to increase the font on this one that's sufficient enough so we've got that all right i like that let me line these up like that now again all we need to do is just add in the data ordered on the cashier and here ordered on is going to be here so let's put in the date and time here let's just put in equals now just temporarily and then so we have that okay good so what i want to do is i want to again duplicate any field here is fine order on receipt number here i'm going to bring it all the way over here so when you use a large field for that and then i'll probably put it at 1.3 so then what i want to do is after that i want to put in one for each one of them so how do we do that so just duplicate it once we get the size right after about a year should be able to get that right then i'm going to duplicate it here ordered on so then the cashier so then all we need to do is just simply link these fields i'll make sure they're right in a second so okay so we've got the cashier here so the cashier is going to be equal to whatever is in here located here in l3 so just hit here that's fred and i'm going to left justify all of them so i'm going to hold down the control each one of those are going to be left justified this one's going to be ordered on equals and then in this case it's going to be the order on date here located in l4 and then also lastly 13c number and that's going to be located right here inside p4 so there okay we've got all that up let me line those up all right i really like that that looks really good everything's lined up just the way on it now as mentioned before we don't need these borders anymore so i'm gonna hold down the control and basically on every single shape except the larger outside shape that one we want to border on that larger the back of that one we want that border because that's going to be the end of our whole order so i'm going to take that and then go back into the format here and then shape outline and then we're just going to go to no outline all right so you see how that looks really nice without allens but everything's lined up accordingly and we have lots of room for larger prices here now what i want to do is i want to make sure these notice there's no border here we need to bring these over a little bit here and this one over a little bit here so now we've got the border and we've got i also bring it down a little bit because notice that the top border was hidden there and bring this one down a little bit here and this one so we've got that now we've got our border around it and now what i want to do is i want to make sure that we group this so i'm going to use my selection tool and i'm going to group it and the next most important thing is we don't we've spent so much time on the sizing there i want to make sure that it's not going to be sized with the cell so i'm going to move but don't size with cells i'm going to give it a name because we're going to use this name inside our code because it is our code that is going to place this dynamically so we're going to call this footer group footer group so now as we add our this group moves down so now we've got that dynamic footer okay i really like that let's continue on we've got a little more i'm going to make this one a searchable field i want them to search it so i want to make it look different and i want to put some icons here so i'm going to add some conditional formatting conditional volume manage rules for these tool and then going to be based on contains so if the cell value here is going to contain we can use actually let's do specific text containing the word enter enter if it contains enter i want to form put it gray and italic so the font is going to be italic and i also want the color to be let's say this gray so click ok and click ok i like that so basically if it's enter customer so if they enter fred of course it's going to change over but if it's enter customer or intervender it's going to stay the same that's what i like so now let's put some icons in here we're going to put some vendors we're making great progress okay as mentioned we're going to be able to clear those results so these are search results based on any searches entered by a customer vendor here i want to be able to collapse these columns here d through f so we're going to need a button to do that so i'm going to insert shapes and we'll use this triangle here and we're going to let's say put it here and then i'll bring it up so we can add some text in here i'm going to call this clear results clear results and i also want to give it a little bit of a again let's format the properties here go into the text box i don't again i don't want to have points or two so much space here we don't need that that's good okay and then i give it a color closer to our theme we'll use this gray i'm going to add an icon here so i'm going to center it and then right justify it and make sure we have enough so clearing results so that's going to help us enough for now i like that i also want to toggle button here so that we can switch between as you saw in the samples between sale and purchase then i need a title i need to know if it's a sale or a purchase so let's do that now so i'm going to insert a shape i'm going to put a square shape or a rectangular shape should say rectangular shape and that's going to be between our sales and our purchase so it's going to be this is our background so the shape fill is going to get in this case white and then we'll just do our consistent our black border here and then we don't need it so big we need it consistent with the rest of that so that outline is going to be weight it's probably going to be here all right so what i want to do here is basically click on and have a green button toggle so i'm going to duplicate that using control d i'm going to cut it in half so let's check the sizes okay so let's update the sizes here let's put this to 0.25 consistent with our height this one i'm going to have is our back right so it's going to be called 0.6 on the size this one's going to be our front so this is going to be the size is going to be 0.3 and i'm going to bring it up here this one will be colored green so we have a green color here inside here and that's going to be the toggle that we can use blue with our blue so the shape fill the shape fill on this one we'll use a like a nice blue maybe this one right here so this is going to be our toggle so all we need to do is click this here and it's going to move between either the left or the right side so all we need remover here or here but i want some text here i want to know whether it's a sale or whether it is a purchase so we'll put the sale on the left and we'll put the purchase on the right so we can do that with the text box we already have some text boxes here so why don't we just copy this and then push it up here here okay so this one again i don't need to format that i don't need a fill on here and i don't need a border on here so take that outline no outline on that and this one's going to be called sale so we'll just put in capital edge sale make it very very clear so i'll change the width to 0.5 that's sufficient enough bringing it over next to that and i want to make it bold also so that's clear bold and then putting it right next to which one's a sale so that's our sale here and then we want the same thing for purchase so i'm going to duplicate that and this one's going to be called purchase purchase of course we're going to give it let's say 0.75 on the width let's do 0.85 and then i'll put this on the left side so here so they know which one looks more better so there we go so let's line everything up what i'm going to do is use my selection tool just make sure it's all lined up horizontally properly there so i like that sale and purchase so now all we need to do is write a macro which we're going to get into very soon as we select it's going to bounce back and forth from the left to the right and then all we're going to do is we're going to change inside b whether it is a sale or purchase but i also need to know what it is a sale or purchase so i'm going to duplicate that give it a title i want to know this is going to be a larger font here and i need to know whether it is a sale or purchase so we're going to bring this up so we can know and then put this maybe in like a blue color something a little bit closer to the theme here dark blue okay i like that so i want to know whether it is up and let's change let's go change the font on that a little bit let's go to aerial rounded so this one here all right i like it so that's good let's bring these over a little bit to the left so we have some more room for our title looking really good we're just about ready for our icons and then we're going to add the code in as part of our footer we also want a button set here clear pay print and all the way down here so we're going to add those buttons in now so again let's insert a shape here just a square shape and here we'll start out down here so we'll start out let's take a look right around on the size we'll put something like a 0.4 on the height and then let's do 0.85 on the width giving it a color here making sure because i want these big and nice so the shape fill will do so we'll use this one of these gray colors here i like that that's going to be nice and then i give it a nice font something big this is something called clear so we want to clear that and then i'll use the right and the middle here and then i'm going to of course we're going to increase the font we need to to make sure it's a lot let's do something around say 16 or something and bold so bringing it up here to 16. there we go okay so now we've got enough room for the icon so clearing is going to be one of the buttons now it's going to fit right down here so as we clear it so the next thing we want i'm going to duplicate that and i'll call this this is going to be pay so i want to be able to pay with that so pay we need that and i want to duplicate again this time i want to put to make sure that we can actually print it so we're going to duplicate that this one's going to be print and next up i want to give them the ability to refund so duplicate that and then this is going to be refund this one's going to need to be a little bit bigger here you can have space for the icon next up i want to be able to void it so duplicating that and this one's going to be void and can void a transaction this one can be back to our 0.85 again 0.85 farsi and then lastly what we want to do is we have a next so they can basically it's going to be able to save and get it's like a save and new basically so i want to do that too so duplicating that and then this one we'll call this next okay so we've got clear pay print refund void and next and all we need is the icons in here looks good so we just want to line that up and space them accordingly giving them enough space making sure that they all have space for the icon i think that refund could be a little bit bigger here so refunding here that be bigger so now all i'm going to do is hold down the control on each one of them make sure they're lined up vertically and then we're going to distribute them horizontally so align and then distribute them horizontally okay it's nice now all i want is i'd like to make them part of the group but let's add the icons before i make them part of the group what we always want to do is we want to add our buttons and then our icons and the reason is because the icons we want on top of our buttons so we want to add in all the buttons first and then the icons one more button to do and that's do a log out and log in actually two more buttons to be honest with you i can't lie to you you're going to figure it out sooner or later so clear and then what i want to do is put in log out it's not going to be so big log out and log in button this one's not going to be that big so because we don't have that much space up at the top so we want it consistent again doing with our height of 0.25 keeping consistent and then our font probably back to 11 or 12 on that one let's select that again and then going back to the font and then we don't need the bold we could probably keep the bold or not either way so i've got logos for this so we've got a log out button and we've got a log in button we want them consistent keeping them we can do a 0.8 on the width both can be zoomed so going to duplicate that this one's going to be called log in so control d and this one's login users can log out it'll be a very simple login and log out but at least we have it these are going to be on top of each other eventually so we'll keep them there now now we're ready to add in the icon so i'm going to save our work and we're going to add in the icon so insert here and i've got some shapes already saved we've got a lot of items so basically everything you see here i'm going to need to add so i'm just going to add them all in right now and then we'll size them accordingly because it's going to be kind of a mess for a while so we're going to go down to 0.2 and then we'll size it all right so we've got a lot of icons here the first thing let's take a look at here this is going to be for our log out i'm going to place them this is going to be we're going to side these sizes better these are going to be for our category so we'll bring those over here because it's kind of keeping them together here so all the way over here and then also this plus this one this is going to be for quantity so i'm going to place this here minus and the plus here and then also the x is going to be to delete a line so i'm going to place that right here i want this one's going to be twice so i'm going to duplicate that one because i want to put one of the icons here under clear results so that's going to clear and i want to put one of them under here clear so there we go so also what i want to do is the printer here i think these icons they need to be a little bit bigger but that's okay for now and then also what i want to do this one this arrow is going to go for the next one we're going to size these icons accordingly next then also void is going to be here we also want this is going to be for the login so we're going to put that up here log in this is going to be for the search order this is going to be for the search customer here this one is going to be oh this one's our point it starts our main logo for our application here so we make that bigger gives a nice one and then also oh you guys are asking me about icons where can i find icons randy where do you find icons you always ask me well let me show you i get most of them from this site right here called adioma adioma or however you pronounce it most of them came from here so except for a few ones which is i use it i icon icon 8 icon 8 is another one that i got it from so that's where i've got my free ones in case you want to know so this one here is going to be for our refunds because we want to be able to return the money back those icons are going to need to be bigger this one's going to be for payment here so payment all right so that's pretty much it we've distributed the icons generally so these are going to have to be bigger so these go on top of our categories so how big do we want them all right let's set the height a little bigger so i'm going to use my selection tool here i'm going to select all these icons and we'll set the height to about 0.32 and now i'm going to group them accordingly so i'm going to put them on this so the beverage is going to go right up here and then we have our frozen food this could be dairy i think right theoretically as well this one i can't even see it right now this is our meats here and this is our cheeses so this is going to go for dairy and egg this is our cherry it's going to go for fruits and this is our bread all right so i like that so now we have them now we just need to line them up here and then what i want to do is just group these accordingly so these are going to be all grouped here let's just make sure they're all lined up i want to make sure the tops are all lined up so aligning those and then just simply align the top so everything's done and i'll center them in the button as well i like that so now what i want to do is basically just give each one assign each one a group and make sure that that group is labeled separately so we'll call it category group 1 group 2 and so on and so forth so holding down the control grouping them then we'll just call this category group and then i'm going to copy and paste this because i need it for each one of them then calling it one okay so i'm going to do the same thing for each okay now that's done i've just grouped each one of these and they have a specific name now also remember also want to make sure once you reach a group anytime you size a group i don't want to spend so much time sizing certainly i don't want that to change so make sure we set it because that resets automatically i like that here saving our work now we've got our icons let's update this we also want to have our icons here these the sizes look pretty good here but i want to align them here and when i select a row i want these icons to appear when i select any rows so that i can reduce or increase the quantity just a small macro to do that so i'm going to create a group for that and we're going to give it a specific group name so that we can place that group called the selected item group so i'm going to group it and call this the selected item group also move but don't size size and properties move but don't size here properties we could do this with all the groups theoretically at the same time probably would be better again here i like that i can i'm going to increase the icons a little bit here and then line them up they're a little bit too small for this button here so we're going to format those we'll do 0.22 that's good and then i'm going to line them up aligning there's a lot of aligning but you learn some great tricks on how to do it okay it looks pretty good we're going to individually group each button i don't necessarily need to name each button but i want to make them a part of our footer group we want because it is this footer group that we want we want those buttons to also go up and down based on the numbers so that they're not part of the receipt we don't want them printed with the receipt but we also don't want them you know want them to be easily accessible so all we need to do is when we regroup something i'm going to copy the name i'm going to ungroup it then i'm going to hold my control and i'm going to select each individual button that i have just grouped then i'm going to regroup it here using my grouping tool and then i'm just going to paste in that group there we go so now we've pasted in the name so we've got everything just the way we like it it looks really good and now again we want to make sure again size we don't we don't want to mess with the size here every time we regroup it we need to reset that looking pretty good okay we've cut it we're almost ready to do this coding we've got everything set up i'm going to then assign this here giving this group and naming it also this one's going to be called we'll call this clear results button or just call it because we're going to have to hide this accordingly inside the code so let's call it clear result button clear result button all right resetting the size and properties on this one too just to make sure that we're not going to be resizing it okay what next well now what we want to do is we want to be able to enter a skew and have that enter here whether you're scanning it with a barcode scanner it'll work just fine scanning an item in selecting an item many ways we can do it with this application all right we're almost done from a design standpoint i want to group these and set them as a button so that we can show and hide them so i'm going to group them and then i'll just cited the name so this is the login button and then the other one's going to be the log out button we're only going to show one of those at a time so it is important to group them and then we'll set those up all right so this one's going to be called the log out button we can also place them on top of each other as well so let's put that right now and then both of the buttons are going to make sure we can do not size but move them with the property so i'll just group them and then right click here and then in the properties we'll just make sure that we set that up properties move but don't size now that we've done that i want to do just a little bit of additional conditional formatting before we get to the vba some of the conditional formatting is here so i want to then automate this and a quick way to do that is just to copy the conditional formatting here and paste it here and then make the updates accordingly so i'm going to use conditional formatting and then i'm going to manage rules so in this one we don't need uh the selected row is going to be a little bit different so i'm going to edit that rule and i'm going to it's not going to be based on what's in b6 it's going to be based on a different cell this one is going to be based on the selected result i'm going to put that in b8 so i'm going to put that inside b and i'm going to give that a little bit different color so format that i want it to stand out a little bit more so going to give it the fill fill effects here and then use these two colors and then i'll use a bold white font on that so we'll use that fade out go into the font bold and then make sure that it is white in this case and click ok and that's going to be applied to the ply stud is going to be all the way here and then down we'll go down to let's just say 99 or whatever it's fine and then i'm going to copy this and then i'm going to paste it accordingly to each one of them then all we need to do is just set it up now we're not going to use all these rules we'll be able to delete one this one we can delete this this one again we can use this alternating based on i4 in this case i'm going to change it to i5 because we don't need that additional one we don't need the additional row and click ok and then this one the same thing it's going to be based on i it's actually d5 not i5 d5 right that's the first one and click okay and i'll change the other one to d as well so d5 is going to be there and then the mod okay good so that's going to be a consistent thing we're going to apply that and then what i just want to do is make sure we edit this roll not 54 but five get rid of that four okay apply that good so i like that and now as we add details here we're going to get a nice uh conditional formatting that's exactly what i want and then when we select it we're going to use this on selected result row let's just say we select 8 then we're going to be able to color that that's what i want above that we want to enter the product i want to do though maybe the product database row that entered the product database so when they enter a specific product i want to know what database row that's on here so we can then add that in so it's going to be entered because they're going to enter that sku all right i'll just put it enter product database row i want to know the row based on the sku they entered here so again let's add a data validation so data validation i've already got a named range it's going to be a list and then we're going to use it's going to be based on the product so let's use f3 and take a look at some of the things that we have set up i've got product sku that's going to be just fine click ok and okay and so once they enter this i then want to set a row based on that so how do we find that well if we take a look in our products here we've noticed that our rows start on row three we've already got the named ranges set up so i want that so basically if it's found on if it's the first one found i want to add two so we're gonna use the mash to do that so inside the pos here sheet we're going to say equals and then we'll use if air if it's blank so we're going to match it what are we matching and matching up the looking up the value of the super 2 and i'm going to use it based on this sku here then we want an exact match we're going to add 2 if it's found if it's not found if there's an error it's going to be blank so that way the first one that is found let's select the first one that is going to be on row 3. that's what i want okay very good so i also want to know the next order id orders are here so i want to know the next order id in fact i want to start with larger orders usually start at one but i like the barcode to show up so we're going to start it out at 1 000. so if there's no orders at all that doesn't exist this database is empty i want to start it that's just x so we'll put it ten thousand in this case ten thousand so otherwise i want the max plus one so the next one is going to be one zero zero zero eight so how do we do that we're gonna use the max so the next order id is gonna be equal to we're gonna use if air equal to if air air would be because there's nothing found because the max i've already got a name branch set up that called order id okay so we want the max but i want the max of all of the orders plus one if there's an error that means it's not found i'm going to return it to 1000 so actually to put parentheses around that just to make sure there so it's going to be 1008 but if there's no data it'll be 1000. perfect i also want the order database row what is the database row based on this order number here i need to know that so we can do that equals if air if there's an error i want to blank we're going to match what do we get matching on basing on the order id here i'm going to match it on the order i've got a named range on the order id again we want an exact match so i'm using zero and i want to start out our first order starts out in row three so we're going to add two to that if there's an error we're going to use double quotes so that means ten thousand three is on row six so let's double check that looking in our orders finding one thousand three that's on row six that's exactly what i want i'm gonna need to load or save it accordingly so i need to make sure that we do that so we've got that set up all right in row two we'll keep this blank for now i might do an order load later on we may keep this open for now and then i want the search order row what does that mean that's based on the specific order if i add in one zero zero zero three i want to know what row based on this search so all i need to do is just copy this and then just change that reference there in the cell what reference instead of that cell being here i want to make it this cell so all i need to do that cell this so that doesn't make sense f here i want to base it on this cell so when they enter if they enter a order we know what row to pull that from and i know what it is so that's all i need to do to make sure if it's if they enter our incorrect one then i know this is going to be blank if it's blank they've entered the wrong one so enter order is going to be the default so that's we'll keep it there these icons will stay right where they are i'm going to save our work and we've pretty much got out so we've got the selected result row i also want the user id i want to know if they're logging in they're going to put that user id in just a simple you will put it in a simple form and then i also want the user database row what row they located on inside the user and also i want to know the user role so we have that there inside our database so here we've got the user id username and user role so if we look in the name manager i've got some set up to save a little bit of time this is already going to be a long so i've got username here user names and user id and user roles they're all set up so if they enter the user id of let's say one that we're going to put that user id right here we can then take that and determine the database based on that so how do we do that again eagles if there in case it's wrong we're going to use match as we always do matching based on this and based on the user id exact match you get the point now adding two because it starts from row three otherwise blank so that means one user id one is going to show up in row three user role i want to know what role is this so all i need to do is use equals if error i like to use if air in case there's an error we're going to use index i'm going to index i'm going to index that user roll that's what i want what is the row number that row number is going to be basically based on this but -2 because that index start then i want a column it's going to be 1. otherwise if there's an error blank so that user role for user one is going to be a manager so if we look here in the user it's going to be manager if i change it to well id3 it's going to be trainee so if this changes to three that's gonna change the training so that's what i want okay so we've got that set up again saving our work everything's looking really good i've got a few other things to do just to put some conditional formatting here and then we're gonna go this one's gonna be simple basically it's going to be simple to this so i'm going to paste this here i'm just going to change the colors here so i'm going to do conditional formatting inside the home menu and then what we're going to do is we're just going to update that update those colors because this one we're not going to have in white so there's going to be no selected row we can move that we can then remove this one and all i need to do is just change the colors and change the rules this one is going to be i6 is fine so basically i want some conditional formatting here to alternate the rows but this is not for the viewer so it's going to be a different color so i'm going to edit the rule i'm going to change this to just a light blue i'm going to put a fill this blue right here and then more colors and then we'll just do a little bit lighter click ok and then click ok and then okay and so the next up the lighter color we can just use is the light color so i'm going to edit this roll this is white previously and then again just basically a lighter blue i just want some alternating colors on this but nothing to to dig okay just very light blue click okay and click okay all right and the applies to in this one we're going to be basically applying to starting on p7 all the way down to let's say p999 this is where we're going to hold our quantity on here this is going to be a formula a constant formula that's always going to display the quantity on hand based on the item in that row so we're going to apply that and it's going to apply exactly based on the number of rows that's what i want so just justify just to show the quantity on hand but this won't be printed this area will be printed just this so the print range will be basically like this and all the way down including this uh footer but not including these buttons so that's what we're going to have okay saving at work we're pretty much done with everything on the screen as you see it now we're ready to start some vba we'll take a quick look over so here's what i want to happen when i click here i want it to move to from sale to purchase i want this to change to sale or purchase based online i also want to do i want this to change which will change automatically and i want to drop down list it's going to either be vendors or customers so i need a dynamic data validation based so that means if it's a purchase if we're making the purchase we're going to purchase it from one of our vendors located here if we're selling something we want to sell it to one of our customers here now keep in mind that i've already got some named ranges set up to help us move things along so if we go into the name manager we do new vendors i've got a one for vendor names which is the vendor names and i've got one for vendor names sorted it is that sorted one we've got a macro to help sort them and things like that but is this sorted name that i want to use inside the drop down list this one here so how do we do that i also also have the same thing for customers inside the customers same thing customer name sorted so i want to use customer name sorted or i want to use vendor name sorted one of those two named ranges inside this cell right here j4 so we're going to use data validation but it's going to be based on whether we're selling or whether we're buying right so that's going to be done there so we'll use a macro to do that so the macro is going to do two things it's going to change this it's going to change this you can change a few things so let's get that done right now inside the developer if you don't have the developers tab all you need to do is go into the file and the options and go right in here to the customize ribbon you'll see that that developer you can select on that once you select on that that developer will be open and you can then you go into vba if you want to use a shortcut you can use alt f11 to get you there now this is compromised of just four modules i've got some modules set up but there's no code in here just some macro names to help us move things along a little faster it's already going to be a record long training so no reason to make it even longer than that so basically we've got customers and vendors a few macros on that menu macros now keep this in mind i don't know if we're going to get to customer or new and add new i'm what i'm going to do at least if i don't do these during the recording i'll make sure that they're done for the downloaded version at least in the 200 workbook pack so if you get that pack i'll make sure to do add new basically the idea is if if it doesn't if you enter a name here and it doesn't exist it'll add new so that's the idea i just don't know if we're going to get to that because it's not an integral part of this training okay so the menu macros we need to do that so we're going to load the subcategories order macros and the pos macro so it is this pl s macros that i want to focus on right now the sales purchase toggle this is the one the button that we're going to use that's going to be created but we need to create a name for that we need to set some names up for this so we know that so what our name are we going to use i want to use this i'm going to call this toggle back toggle because it's the back and we're going to call this button here this one right here i'm going to call this toggle switch this one right here talk let's let's we're going to keep clicking on this i want to bring this up a little bit here so we don't keep clicking on it i just group them together okay so we have that i want to call this toggle switch so let's give that a name once we name them they're easy to work with inside vba so we'll do just that so basically when it's a sale what i want to do is just make the toggle switch position left the same as the toggle back position and if it's right i'm going to move it over to the right and i'll show you how to do that so let's do that right now but it's based on the current position so we're going to focus on that now i've dimensioned some variables also to help things move along the last row the last results row the product row the pos row the pos column we're going to use those a little bit later on and the product shape so we're going to focus on this the sheet pos now it's the pos sheet that i've named if you hit the dot here your intellisense will come up you know you've got the right sheet name so pos this is the sheet name how do i name those using the properties if you click on a sheet here into the properties you can then name that sheet up here gonna name that's the code name that i've assigned to it the same as the sheet name so with that we're gonna do something first thing you wanna do anytime you change the validation let's bring this up now that we're kind of finished with the formatting anytime we change the validation or update the validation we first need to delete the validation so the first thing i want to do inside j4 is delete that validation so dot range j4 and then not value but validation we'll do dot validation dot delete so that's the first thing you can't add a new one until you delete the old one so regardless whether it's a sale or purchase we're going to be doing that now we need to determine what is the current status of b1 is it currently a purchase or is it currently a sale so if dollar range b1 by value equals sale then we need to move it over to purchase right then we need to do switch to purchase so else let's put else in here so we know move to else is sale so if it's currently sale then we're going to move it to purchase move to or switch what's called switch to purchase switch to purchase so we clear so now we're going to switch it to purchase so how do we do that well the first thing what we're going to do is set b1 value to purchase so we just copy this down here setting the value that value equals and then in all caps we're using caps for this time and purchase so we're going to set so that's the first thing that's the easiest thing else and then we can just change this right if it's if it's a sale right move to sale then we can just put it down here and this one's going to be sale so all we need to do is just change the text to sale and then we're good to go okay so if it is purchased then what do i want to do i also want to make sure we we're going to be searching probably for a vendor here so j3 is going to change to enter vendor not enter customer right because we're now switching it to a purchase so dot range j3 we're going to set that search set it to inter vendor equals enter vendor so that's it and then again likewise back and forth just copy that under the else this one's going to be inter customer so we can just change this to customer so now that we've got that what i want to do is now is i want to update the validation and i want to add that so how do we do that we can add it so dot range j4 and then we're going to add the validation dot validation and then we're going to do is we're going to add it dot ads we want to add that what kind of validation do we want i want to add a list so i'm going to select list here and then what i want to do is i want to add an alert style in this case i i just want to put information i want them to be free to put anything they want and maybe we'll just give them a little warning message saying if you want to add it let us know and then we'll put a little pop-up saying we can add new customers add new vendors we're not going to get to that just yet but at least we want to allow them to add anything so what is the alert style it's going to be xl valid alert and then information right we want them to get that information so valid alert information which basically allows them it's not a warning it's not a stop it just allows them so then the operator is nothing now the formula is going to be equal to we have that drop down list vendor underscore names sorted that that is the name of that named range so that's the name that's going to go in there so once we have that we're good to go with the validation here we're going to copy that and i'm going to change that to customers here so here another for the customer we're going to simply do the same but this one's going to be customer names equals customer everything else is going to be the same name sorted okay so we've got that for the customer next up what i want to do is we need to toggle that shape i need to move that shape so how are we going to do that so dot shapes again toggle we're focused on the toggle switch so toggle switch that's the name that we assigned dot left i'm focused on that left position it's going to be equal to and this time we're moving it to purchase right so i need to move it all the way to purchase i want to move it to the right but so how much to the right do we have well what i'm going to do is going to be based on the left position of that back equals it's going to be the left position of the toggle back so it's dot shapes toggle right so toggle back i think that's the name we signed toggle back so it's going to be the left position of but not the not exactly the left position it's going to be plus the entire width of that plus the entire width of that back shape so it's going to be plus this plus the width of this right so plus the width of this now if we just have the width only what's it going to happen it's going to be this it's going to move it all the way to here right so i don't want it all the way on the width right i want it on the width minus whatever is the width of that shape whatever the width of that toggle shape is that switch so we need to subtract out that width there so again minus this the toggle switch the width of this toggle switch minus the width of this that is going to get us the exact position that we need okay but what if it's a sale well if it's sale it's much easier all we're going to do is matching the two left so all i need to do is copy that then paste it here so the toggle switch left is going to be the left position so that's much much easier okay great so now all we need to do is assign this macro to both the shapes the toggle switch that means if they click either one of the switch or the background that's going to happen so we're going to hold down the control selecting both right click assigning that macro and then pasting that in here target switch pull this one there we go so now let's take a look so now it's purchase sale purchase sale okay i like that now this what i want to do is i want to make sure that this is equal to whatever's here so this changes it equals b1 so it changes automatically and of course we need to reset that let's do that because it takes on the font of the cell so every time you do that at least the formula so let's drop it down bring it back to what we originally had it which is that arial rounded here all the way up here ariel rounded and then i think it was about 25 or so okay and then we need to reset the call but only happens the first time when you set the link and then you're good okay so now what we have a sale purchase sale and purchase that look works really good okay so now notice this changes and let's check our drop down list purchasing should be a list of our vendors here perfect when i switch it to sale it should be a drop down list of our customers okay good that's working good our first macro is written really well now there's another thing when i load this when i load a sale like if i'm searching i need to also run this macro but it's not going to be based on whatever's here for example let's say i load an order but that order is a purchase i need this automatically to change not based on the user clicking but based on what the order we loaded it could be a sale it could be a purchase so what we're going to do is we're going to write another macro and it's going to be based on the value of b1 so we're going to it's just a little bit different but it's almost identical so all we need to do here is copy this macro here just like it is here all the way up here copy that and then i'm just going to paste that into the next module this is going to be based on the this is going to set the type based when we load in when we load it in okay so it's not but it's not based on the b1 here it's based on something if b1 equals purchase all we need to do is just in this case so what we're going to do is here's what i'll show you i'll explain what i'm doing i'm going to load in when i load in this purchase right i'm going to load this order all this information it's going to load in here this is going to go into b1 it's going to go right here so if this is b1 here now what i need to do is i need to load this order it needs to become purchased not sales so just the opposite of what we did so if this equals pert if b1 equals purchase then make everything purchased you know basically that's all we need to do so switch to purchase all right so if it's b1's purchase then switch to purchase that's it that's all we need to do that's when we actually loaded in this will come in handy when we actually load the order so what about the customer and the vendor filter so when we add a customer vendor filter we gotta filter that order so let's take care of some of that now okay so the idea behind this when a user enters a customer or vendor here i want a filter to run based on those orders here and based on any orders that contain this it could be a sale or purchase either one any order that contains that name i want it to show up i want to run an advanced filter based on either we i guess we could put in a type or we could do that based on a specific type based on the name whatever they put in i want to put the asterisk on either side of them so those wild cards so that means any name that begins or ends with like with whatever they put in then i want those results to come here i want the ordered on the customer and i want everything to come in here then i want to take whatever results are here and i want to bring them inside and put them down here so let's do that right now and i also if this is hidden i want to show it right so it could be hidden so let's do that right now let's add that in so the best way to do that is just of course with an advanced filter and we're going to focus primarily on the orders so in this case with orders that's the screen and what the first thing i want to do is determine whatever is in i want to place whatever the user has put inside j3 i want to take that whatever that is whatever they've searched and i want to place it directly in here but when i do that i want to add the asterisk before and after that so that means anything they search for can come up inside our results so we do that here so order so we get dot range l3 that's where we want to place it inside the orders l3.value equals and then we're going to put that asterisk there then we're going to put and whatever is on the pos dot range the pos where is it located that's in j3 j3 dot value and i want to put that another asterisk there so it starts in so that's going to set our criteria for our advanced filter so we have it set here now our criteria is set here inside l3 now we're ready to run our advanced filter so to do that we need to determine the last row and so the last row is going to be basically equal to that's auto hotkey that automated for that's a free software last row is going to basically equal the range so we've got that there and if the last row is less than three then we want to do is just exit out that means there's no data so if the last row is less than three then exit the sub actually this is going to be the last row here last row exit the sub we've already defined the last row here so i've got it up here as a long variable so we've covered that so if it's less than three so now we're going to ready to run our advanced filter okay so now again again auto hotkey helping me with that so a2 so we're going to start at an a2 we're going to go all the way to g in this case we really don't need to go that far but we can just in case we want to add more later g in the last row we're going to run the advanced filter our criteria is going to be located in k2 through l3 so we're going to update that from k2 to three and then where do we want the results our results are going to come in from o2 through q2 so updating that from o2 all the way to q2 and unique now we want the last results row so let's so now if the last results roll are less than three then x of the sub we need to make sure that again auto hotkey helping me with that that's why it looks like i'm typing super fast in certain instances okay so if but we probably in this case i want to let the user know so what i'm going to do is just add in a message box that there's no results because otherwise they won't know what happened we're going to put message box no results found okay i like that and then we'll exit the sub just letting them know that nothing's done so assuming that we do have results i want to bring those results over so we know that what the results are so our results here start on row three our results here start on row five so we need to compensate for it so d through f d5 through f in the last row plus two inside the pos sheet here so pos dot range d5 through f and the last result row plus 2 remember compensate for the difference of those starting rows dot value equals back in our order sheet where we're bringing the information from all the way from 0 3 through q in the last results row equals dot range o3 through queue and the last results row make sure we get those names right last result row okay so that's going to bring over our search results bring over search results okay but also what we want to do just in case there's any although just in case there's any data we should really clear out any data on the pos just to make sure right we want to make sure that everything's cleared out before we bring over so d5 through f and the longer we're done let's clear the contents of that so pos that's first thing with dot range d5 through f and then we just use a larger row we'll clear the contents out dot clear contents let's clear any previous search clear any previous results okay good i like that that's going to be looking good we've got everything done on that and i also want to make sure that this button here this clear result button i want to make sure that's visible when we add those wheels and i also want to make sure that these columns are also visible too so we can do that right now so that's going to be focused on the poi so pos we're done with the uh we can do we're done with the order so we end with so now we're focused on pos dot range in this case column d through f these columns dot entire column dot hidden equals false right we want those show up and also p o s the shape dot range not dot shapes this shape clear results button dot visible equals mso true see true right we want that displayed so i want it there we go so that's exactly what i want so i'm going to save our work and when do we want this to happen i want this to run when we make a change change to what i want to make a change to j3 but the change it can't be contained the word enter right if they're entered that means it's intercustomer intervender and it can't be blank so let's do that let's update that that's going to be a change event that's when we want that to happen so the change event is going to be found on the pos so this is going to be a worksheet not selection change but change event so when we make a specific change that's what we want so what changed and what cell so it's again based on j3 so if not intersection again auto hotkey j3 we're focused on that j3 there's a change in nothing i want to make sure that it's nothing and also i want to make sure that it doesn't contain the word enter and in this case in string what is it j3 value range j3 it's better to use j3 because the target can create issues some value where am i looking for i'm looking for enter if that string is found then don't do anything right so i want to make sure that and in string equals zero wanna make sure there's nothing right so that this case in this case equals zero but we'll actually i think a better idea just in case let's do this is greater than zero we'll do this interest greater than zero if it's found then exit sub and i'm just going to drop this line down here and i'm going to use a different ant so if in string then exit the sub and i'm going to do and in this case range j3 does not e so i'm checking for two instance value does not equal empty then okay so here's our end if so we're checking two conditions first the first thing i'm doing i'm checking to make sure there's a change to j3 and also making sure it's not empty then i'm going to run another check if the j3 it contains the text enter it's greater than zero that means it's found at least once then exit the sub so assuming that those two conditions are met and we can now continue on then what i want to do is then add that macro here so that's the macro run macro to filter name because it could be a vendor or customer okay so that's what i want so now if i save the work here before you run i always wanted now i want to enter fre fred and then just here and then no results found all right let's take a look inside the order okay we need we gotta skip this step here i think so last results are we're checking the last results right we need to get that last result so last result row what is it well it's going to be equal to whatever's in the last let's use o so in this case we're going to use o so n del x up column o that's going to give us our last row we missed that step of course it's going to be less than 3 because they're running it again let's take a look inside here and now we've got our data here okay good i like that we've got some different font size on here so we need to fix that up we don't need the different font size this looks like 11 this looks like 12. okay let's make them all 11 and fix that up all the way 11 okay good and then bringing this down i'm going to left justify this otherwise it looks very good now when i select this i would like to have this show up we'll get to that in just a moment on selection change what i want to happen i want d there's a few things i want to happen i want to load the work order but the first thing we can do is actually put that row directly inside b6 so that's on selections change while we're inside the pos here we can run that if the user makes a selection change between d5 and f then we want to do something what do we want to do well i want to the first thing i want to do is i want to put the row that they select there and the next thing what i want to do is i want to load the order we're not quite there yet for that but we can write apart so the first thing if i want to do is if target.count large just in case they select large cell is greater than one then exit the sub if they select more than one cell exit the sub nothing we can do so if they select a range that range is going to be based on d5 through f and then 999. i also want to make sure that there is a value in d and range d and the target row whatever the row dot row dot value does not equal empty that has to be value then do something well the first thing what i want to do is range b6 i want to put the row that we put in there so b6 dot value equals the target okay that's good now and then we'll write a little bit of macro a little bit later on where we actually load that so all we need to do is just select that row now and make sure that actually it's b6 selected result row should be b8 sorry b8 no miss that up b 8 there that's the right one so b8 is for the selected result row where those results are coming that's the one i want to use and format this cell i'm going to just remove the border on this side to make it consistent okay there we go so i like that so as we add it and then clear results well i want to write a macro that's going to clear those results and hide these columns because we're not going to need them right now so that's the next macro that we're going to write so that's the focus clear filter so what do i want to do in that one i just basically want to remove the results hide the button so with pos and then in this case i want to take this button here this one here and i want to hide it so that's the first thing that i want to do dot shapes this button dot visible equals mso false next thing i want to hide is those columns so dot range d through f actually let's go d through yeah d through f is sufficient enough for us we're going to hide g let's see if we want to hide g maybe we'll hide g2 because we already have c we don't need that so i've got one already so d will go d through g okay so d through g dot entire column dot hidden equals true and i want to update this one i'm going to make sure this one's d through g too that way we'll show and hide both those okay so we've got that next up we've already hidden the shape we've done that and now what i want to do is i want to run a macro to reset the footer grip but we'll get to that a little bit just in case but we'll get to that also when we clear the filter i want to return this j3 back to either inter vendor or enter customer it's going to be based on whatever's in b1 so we can write that up right now if dot range b1 dot value equals purchase as it does now then we want to say j3 dot range j3 is going to equal interventor j3 equal to enter vendor otherwise j3 is equal to enter customer so we just copy this and then change the text good i like that and we'll test that out okay very good that looks pretty good and then we'll clear that out so i think we're good with this macro and then this one's on clear filter so i want to assign this macro to this button group or group here right here that's going to be the one assign the macro paste that in there clicking ok and then what we'll do is our save our work before running the macro click clear results okay now everything's hidden that's what i want now if i enter a vendor or anything like that just enter here the results show up and we can select it and it's going to load it okay i really like that cool let's move on okay next up if i scan an item or i select from a drop-down list i want to show up on the first available row or or if it already exists in here and this is one of the things that people ask me from the last piece of pos training can we increase the quantity if it already exists well let's do that if it already exists we're going to increase the quantity if it doesn't exist we're going to add it below so let's do that we can clear out our sample data here we don't need that and so here's what we're going to do on change of j5 i want to see if it's found if it's found we're going to know because inter product database is going to have the database row if it's found i want to enter the skew the product the quantity the amount and the formula for the total now we need to put a formula for the total but let's put in the information and then we'll put in the formula because it's going to have to be include the discount so it's going to be based on here so let's write the macro that's going to do it when you first need to check inside the macro that b7 contains a value otherwise it's going to be incorrect right if they enter an incorrect number here if this is going to be canceled here or just even delete we know that b7 is going to be blank so if b7 is blank then we know we have an incorrect item so inside the pos let's go into the order marker so we're going to this one we're going to focus on order add sku so again with the pos that's the sheet that we're going to focus on here we don't need to focus too much on it with pos i'm going to write some macros in first of all again i want to check check if dot range b7 that value equals empty then message box please enter a correct product sku or upc or upc number okay you can change the text there and then exit this up nothing we can do unless we have a row for that assuming that we do have one what we want to do is we want to then set the product sku whatever's in j5 we're going to set that as a string so product sku is a string so this i want to assign whatever's in j5 because that's whatever they've entered here inside j5 that's what i want to enter i want to put that into a string variable so we can do that from here so product equals dot range j5 okay so that's going to put that into variable because i need to look for that inside it's going to be our product sku okay once we have that what i want to do is i want to determine the database that's going to be located inside product database row we already have that when i put that in small letters if it changes to upper letters i know i've got the right variable set equals and that's going to be what's located in b7 so all i need to do is just copy that and paste that in there okay good so now we have a row so now we've got the row so we're going to have our product database row once we have that what i also want to do is i wanted to know the last item row that last item row is going to be based on here if i have five items here i want to know the last row so let's determine that right now so the last item row let's put it in here variable here last item row equals it's going to be based on column i here i let's let's yeah that's what i want to call my okay so if first we just have to determine does it exist i'm really i want to look for it if that item exists i want to increase the quantity if it doesn't exist i want to add it as a new one so first thing we want to do is we're going to set the found product range is going to be equal to found product range already arranged dot range i'll go over that i7 right that's going to be our first through i in the last item row and i'll explain this in a second last item row it's not value so we're setting the range so basically i want to set a range because i need to look inside that range so if i've got many products here i want to set this range starting at i7 all the way to the last one then what i'm going to do is i'm going to look inside this range and i'm going to see if this number exists in the range if it's found i need to know the row and increase whatever the quantity is if it's not found the first available row add the added in so that's all i want to do so selecting that range is going to help us setting that range so now what we can do is use the find command to determine if it's found or not so if the set found print equals now all we need to do is put it in here dot find and what are we looking for well i'm looking for the product sku and i'm looking in excel values and i'm looking in excel whole i want excel whole okay so that's what i'm going to look for it there i need to know if it's found how do i know if it's found so again let's go over that one set the product range we're setting it to that range we're looking for something inside there i'm looking for this product sku i'm looking in the valleys and i'm looking for whole and now we need to determine if it's found if not found product range is nothing then it's found yes i know it's confusing not and nothing to cancel each other out when you see two negatives they cancel each other out so that means it is found it has been found if it's found then what do i want to do i want to determine their own increase the quantity so all we need to do is our quantity is located in column k so let's do this dot range k that's the column what about the row and we need to find the row what is it going to be the found product range dot row that value is going to be equal to whatever it currently is equals whatever currently is plus one else it's not found else it's not found if it's not found then we need to do something else but what i would like to do also not only this i want to set you know b6 is our row right b6 is whatever row so b6 let's put in this 7 right is our select row so notice how it change colors so i want to put whatever row that we change i want to highlight that so if i change b6 is going to do that our conditional formatting would do that so let's do that so one more thing to do on this dot range b6 that value is equal to right here equal to the found product range row right here so let's just copy this and put it down here it's going to set the row we're going to do the same thing but for the next so this is these are the only two things if it's found all we need to do is just increase and set the row if it's not found then i need to add all the details and those details are going to come directly from the products we know the so the we got to put the product name the category no sorry we got to put the product name we got to put the purchase price and the sku we got to put all that information in the brand new row so let's do that right now so what's going to be included first the first row is i of course that's going to be the product sk we're going to put that directly inside i so we know the row so what is the row in this case and the row is going to be the last item row we already know the last item row here it's going to be this plus one so we're going to copy that so dot range i that's the column and the last item row plus one that's convenient row that value is going to equal what equals the product sku equals that product sku we already have that in there so next up we want to put the product name that's going to be located in j alright so we can copy this here and we can put put it in k so paste it down here and change that to k and now what's the value is going to be located on the product database row this right this is important product database row so where is it going to come from it's inside here products our name is in b the column b and the products database row so let's go back in here equals products then we don't have its products dot range b and the what product database row dot value that's the product name product name dot value product name okay so we've got the product name placed directly from the product sheet so we also want to place the additional information what's up next we'll certainly i want to place that quantity that's going to come in column k so let's do that right now k not this just product name should be j not k but let's copy this because we're going to use k right now and then we'll change it back to j k is going to be equal to 1 equal to 1 this is going to be column j i j contains the name k contains the quantity so this set the initial quantity quantity okay so now that we've got k also what i want to do is the amount where's the amount i need to know in this case there's two amounts right y's are two amounts well i've got a purchase amount in our products and i've also got a sales amount based on what type of transaction order it is if it's a purchase we need to put this if it's a sale we need to put this so it's going to be either column e or f and that's all based on whether this is a purchase or a sale so we need to run an if-then statement to do just that so if dot range b1 dot value equals again let's do sale then we can do something and then otherwise else okay that was it's a purchase so break that down like that and now what we'll do is we'll add in so it's going to be base i'm going to copy this here but it's not going to be column k we're going to focus on the amount which is column l l is going to be equal to if it's a sale where is it going to come from it's going to come from the products database and it's going to come from column f and the next one would be g if it's a purchase so we're going to use column f from the product screen so we can just copy this here then equals and of course it's not column b it's column f this is going to be our sale price sale price that's fine next up if it's not sales otherwise it is a purchase right so a purchase is going to come from column all right a purchase price right here column e it's coming from purchase so now that we have that buy the one based on our purchase or sale so we can have dynamic based on that okay good so we've covered that so now all we need to do is add in the total but the total is going to contain a little bit so let's update let's just see how we are first then we're going to add in our total formula let's take a look at that we'll run that and now basically we want this macro to run when we make a change to j5 anytime we make a j5 that's when we want this macro to run so let's copy the macro still have a little more code to write on this macro and it's going to be on worksheet change so we go into bos here bring this up a little bit we're focused on worksheet change now we're focused on j5 this time j5 if we make it change that and we certainly need to make sure j5 nothing and range we want to make sure it's not empty target j5 does not equal empty then we can run the macro but we also want to make sure one more thing i want to make sure that b7 that is correct row just in case i want to make sure b7 contains a proper row b7 is the row that they based on so let's make sure to check that to make sure b7 is not empty or we we can keep it empty and they would know so let's do that let's keep it there if they get an incorrect there'll be a pop-up message that should be fine too because we did compensate for that okay so adding that okay so i double clicked on that it added here good i like that but we do need to add in the total i want to add in the total here how do we do that we do that with a formula what kind of formula are we going to use well it's got to be based on the discount and it's also got to be based on on the amount and the quantity of course so but i want to make sure that we're so i'm going to put a formula up here i'm going to put it here in n5 there's nothing go on it then all i need to do is bring down this formula now i could bring down the formula all but i don't really like to have formulas unless i need them and i don't in case they get cleared out it's nice to have them in a lock cell and then just bring them down when i need them so let's do just that so i'm going to put a formula right here going to be based on a split i'll put it in here and then we'll copy it up here so equals if i only if there's missing if there's no value here equals empty right there's no sk or upc then just do empty otherwise what i want to do is the quantity in this case we'll do k5 times l5 now 7 in this case sorry l7 and then what i want to do is to subtract the discount minus any discount if there's a discount i want to know so how do we do that again let's do the same thing it's going to be k7 plus l7 this one i'm going to multiply that but this time it's times the discount if there's any discount okay good so we've got that times m all right so that's it so now if i decide i want to add a 10 discount then it's going to reduce it by automatically so what i want to do is i want to take this formula i'm going to copy it i want to paste it directly in here paste the formula and then i'll get rid of that so we don't need that so this in this case we can do let's do quantity instead of let's do quantity because there's always going to be like so what i'm going to do so i'm going to change this to here if quantity is empty that's probably good too that's sufficient so if k5 is empty if there's no quantity it'll change okay so now all i need to do is inside our macro is take whatever located in n5 and paste the formulas into whatever row we're focused on so that hidden form is located in n5 so we're going to do just that so let's add that inside our macro right here where we're working at the end okay so this is it here so we're going to do regardless if it's found or not now we're going to add it into the row so actually if only if it's not found if it's found already that formula is already there so we only need to add it right here so how do we do that so dot range n5 dot copy we're going to copy that dot copy and then we're going to paste the special so dot range we're going to paste it in n and the last item row plus 1 dot formula dot paste special and then we're going to paste the formulas if we scroll down all the way down here scrolling down here we can see dot formulas that's what i want to paste in those formulas and then i want to get rid of the dancing ants so we're going to do application dot cut copy mode equals false that's going to get rid of them okay so the last thing that i want to do now we've added in everything looks good but what i want to do is i want to take this footer and we've just we possibly just added a row but we've only added i want to adjust that footer i want to run a macro we're going to add that macro now to lower or lower this footer like if we keep adding rows i want this footer to lower lower lower as we add it i want to place this footer i want to run a macro that's going to place that footer right where it belongs so that macro only needs to be run if we've added a new row here let's just say new row and then we'll put found existing row so i only need to run that macro if it's a new row so we're going to add it inside here that macro is going to be right down here i have it here let's see oh i think it's in the other one here here it is the pos macros here so i've got it down here here it is set footer i found it set footer order set footer so that's the macro i'd like to add in fact i'm going to bring that up right because i want to do that next so bring it up right here put it right over here okay and that's the macro that i'm going to run right here so order set footer reset let's just go place footer just call it place footer okay so good i like that and now we'll test it out again and now what i want to do is i want to run this macro based on where we should want to put the footer now that footer where should that be placed if we added let's say we add another item here now we double click on item and it adds another item perfect and now we've got the total here just the way we like it we've got now we just want to get these items to appear and i want to get this to appear when we select a row write some macros to do that and we want to set this footer so let's set this footer appropriately right now inside our macro so let's write a macro for that scrolling down here setting the footer we're going to focus again on the pos so it's going to be that sheet so with pos and in fact we're going to focus primarily on the footer so dot shapes in this case we're going to call it footer group that's called footer group so just in case you want to copy that to make sure you get it right and then we're going to paste that inside only focused on this shape right now so with that footer group what do we want to do well i want to assign a left position so the dot left position of that is going to be equal to the pos we need to call it the sheet dot range i7 i want to place exactly i7 dot left so it could be any any row it's really column i dot left so that's all we want to know dot left what about the top position the dot top position is going to be equal to again the pos dot range it's going to be i and what and we want pos dot range we're going to use i again 99999 dot end excel up dot row and i want to add a little bit plus what plus how many rows well let's take a look i'd like to have at least one row here's the last row i want to have at least one empty row so why don't we put it two down let's go ahead and put it two down so i'll continue with that so plus 2 that's what we want and then in this case dot top and a little bit above it a little bit in other words i want to put it just a little bit not quite down here i want to put it a little bit above so i want to subtract let's say two on that so minus two it's like an increment right it's minus two a little bit higher than that basically and i also want to make sure the width is right i want to double check the weight in case it gets messed up dot width is gonna be equal to pos dot range i column i i want to make sure that the width is exactly always equal to i all the way through oh the width of those columns must be equal so let's set the width just to make sure that range i through o dot width the width of those columns now we now we're good okay that's all we need to do relatively simple so now as we add items this footer is going to automatically be added as we add items now we increase so if we add another item here that is going to move down adding another item is going to move down that's exactly what i want that looks really good okay i like the way that looks it's perfect just the way it is okay let's continue with this now what do we want to do now what i want to do is i want to do these shapes when i select on in one of these shapes i want all of the subcategories to appear inside here so let's get that written up now so what i want to happen when i click on one of these categories i want to load all of the 12 sub categories that are based on the admin screen if i click on the drinks and beverages i want to load all these subcategories dairy and eggs i want to load all these subcategories now these subcategories start in row 5 going to row 16 starting in column 4 all the way over to 10. so how do we do that okay we did it relatively simple inside the pos so let's call that out now we can use application color to do that so it's going to be focused on the the menu macros and what we're going to do is just three macros to do this not too difficult i've again i've already dimmed some shapes to help us out excuse me dim sum ranges and some dimensions here we've got some long variables we've got some product shape we're going to use product shape in this so let's focus on this so with pos again the same sheet we're gonna the first thing i want to do is i want to remove existing product shape now again as i select a product subcategory i want all the products to display here but if i change the main category i need to make sure that all the products here are gone so those products can have a specific name if there are any so let's put let's remove those so remove any existing product shapes product shapes again let's just go over that with me these are categories these are subcategories and the products will appear down here and i so we'll do that here okay so let's remove any product when i change the main category i want all the products gone so to do that we need to do is for each product shape in dot shapes next product shape and i'm going to give these products a very specific name they're all going to contain the word products and i also want to remove any so i'm going to give them a very specific name i want to know if they contain if in string product shape dot name if it contains the word product product make sure that no other shapes do that is greater than 0 then product shape dot delete so we're just going to remove all those but i want to do more than that then greater than zero means exist then product shape delete okay so then what else do i want to do i want to do more than that i want to rename all of the existing i want to actually put it in the text put in the text for every single one so these all have to get proper names so to do that what i want to make sure is if they contain the word sub category then i also want to change the colors i also want to change the color so the one i select is going to go darker you saw that in the sample the one i select so i want to make sure that all of them go back to this light blue color all of them go back to that before i do that so in other words when i select one of the products categories here i want them all i want all these to change back to their original colors just basically resetting the colors of all these subcategories so we can do that for the subcategories too so if in string then we're using answering product shape product shape dot name what if they include the subcategory sub category make sure we spell it right it's spelled wrong is greater than zero then so i want to do something for those specific shapes as well then i'm going to add it then what i want to do great i forgot the 0 again that means it's contained there if it does then i want to reset the color so product and i'll explain this a little bit later on in case it's confusing dot shape style basically we're resetting the colors equals for only those that are called mso shape style 23 we're going to use 23 so we'll go down here all the way to 23 here this is the one i want so that's going to be that light blue so we're going to just reset those that's all we need to do we don't need the end if here so then we could just we could just do this then keep it on the same line keep it simple here that's sufficient enough so for every shape that includes subcategory just resetting the colors because when i select on the subcategory i wanted that color to change same thing here when i select on here i want that color to change we're going to get to that now okay so but at least we've done our homework and taking care of everything there so now what i want to do is i want to determine what category that we've selected so how do we know that if you notice i've changed i've got to change the icons too here let me do that here notice that this is called category one here so what i want to do is i want to make sure that we actually get the last character i'm gonna change these right away i'm gonna call this called category icon one and i'm gonna do the same thing for the rest of them you don't need to watch me because it's already long enough again category icon 2 and then i'll show you why that's important in just a moment okay now it's done so what i did was i basically just created an icon named these icons the individual icons but the most important thing is the number at the end i need to when i if i select the icon or if i select the shape i need to get the one from this so i need to get the last character in other words the last character if i select the icon is two the last character if i select the shape is two i need to get that two or three or the four or five or six so that's all we can do so now regardless of what they select whether they select the icon or whether they select the shape we're going to be able to extract the same number that number is very important because i need to know is it category one category two three or four so on and so on that's very important so let's continue on so let's extract that i've already got the category number here and a variable and we can bring that out so the category number here actually let's call it the selected category because i want to know selected let's use selected category because that's the one you've actually selected is equal to all i need to do is just use the right command the right of what the right of the application caller application.call that is the shape name that called it remember could be the icon it could be the back shape so we've got the right of that now but how many characters i only want the last character which is one get and we can you remember this is important but if you have more than nine it could be two characters so you'd have to use another different formula but because we have only six we can use the last character which is always the number regardless of what they select that's the category number so now we have that in a variable so what we want to do is i want to remember i said i was going to change these well i'm going to change the color of this but before i change the color i need to reset all the other colors i know which one i've selected i've already got that in a variable so i know which one to color so all i want to do is take all these and reset them back to their original blue it'll when you see this macro it works so basically i need to run a loop through notice category one i need to run a loop and reset all the colors here four category number this is where the category number comes in equals one two six closing our loop next category number what do we wanna do here inside there dot shapes category we know every single one starts with category category and the category number when we combine them we get the shape name category number dot background style is going to equal we're going to use equal preset 3 this time equals preset 3 so we're going to go down here that's the one i want that's going to give it that blue so it's going to reset it back to that blue all of them automatically so we're going to set all of them but now what i want to do is set one of them just one the one we've selected to that selected color so we can copy this it's going to be a little bit different here i'm going to copy i'll just copy this here and this is going to be for the one that we selected it's a little bit different the one that we selected remember is selected category here that's the selected category that's the one we've selected going to give that i'm going to give that one that's going to be the 30 background style preset 30. so we can do equals and we can find the 30 in here and that's the one i want a little bit off the screen here let's bring this up we can also type it in but it's easier to do it okay scroll up here go equals and then i'm going to locate that 30 equals 30 that's the one i want let's do 10 and then let's change that to 30. that's fine back press at 30. it'll come up okay so that's the one i want background preset style 30. good so now we've got that so now what i want to do is i want to populate those cast subcategories i want to populate these our subcategories are named subcategory one subcategory so i want to run a loop from 1 through 12 and i want to populate every single one so how do we do that well we can run a loop for that so let's do that right now 4 we've already got subcategory located right here inside a sub category number as long so here 4 subcategory number equals 1 to 12. there's 12 of them then next subcategory number inside here what do we have i want to again take there in the shapes dot shapes sub category we know the name if you're never sure if you're not sure the name just again copy the portion of it that's got the name just to make sure you're right that's another way to do that because i've got enough bugs as it is okay and what and of course we have to add the number to that the sub cat number dot right what do we want to do i want to add the text frame so where's that dot text text text frame to dot text range dot text that's going to give it that text that we assigned so what is that text that text is located where it's located in the admin sheet right we know what that is so let's do that let's find it in the admin sheet so it's going to be equals admin dot cells why is it cells instead of range because both the column and the row are dynamic so the sub cat name again let's take a look at that the admin equals the sub category number sub cat number as we loop through these right if i'm going to loop through them what do i need to do i need to do two things i need the column i need to know what it is what's the column and i need to know the row the row is going to be all the way from 5 through 16. so if i'm looping 1 through 12 all i need to do is add 4 to get the row adding 4 will get us the row 5 through 16. but what about the column the columns either 4 5 6. so the column is based on the category if the sub category is 1 right if we're at the first category it is going to be column 4. so we need to add 3 to that so that we can do right inside the code so we're going to start out at the row so the sub category number plus 4 is going to get us our row but what about that column it's the selected category remember it's one the selected category if we've done the first one plus three that's going to get us our column plus three dot value okay let's just go over that again the sub category number it's 1 through 12 right but i need to loop inside the admin 5 through 16. so all i need to do is add 4 to get the right number row to get the column again the selected category is going to be 1 2 3. it's going to be that column d is column 4 so we add that that's going to populate this all right so that's it so we're going to save that work here and we're just going to loop through that that's the end of this sub so all i want to do is take this particular macro and assign it to all of these categories the same macro to all these so again all i need to do is just click hold down or of course i could could select them all right click and then assign the macro to those pasting that click ok so now when i click out of here and drinks here let's fix that all right i think that's going to have to be shape style here not background style dot shape style there's more than one equals that's where we're going to get that 30 shape style there's different ones that's what i wanted shape style 30 okay that's the one i'm looking for here that's the one i want not background style different okay let's take a look at that okay nice now that's what now we got the darker color okay so now look at that you see how that works so quickly so easily looks are all populating with just one little macro we've done all that work okay great so now what do i want to happen now what i want to select on one of these subcategories i want to populate all the products inside that subcategory okay so let's get that macro written up basically what i want to do is when i determine what category it is i want to take whatever's information there and i want to display it inside the products i want to add that subcategory here i want to run an advanced filter based on this and then i want to bring those results in here i want to loop through these results and i want to populate some brand new shapes but what kind of shapes well i want to create a brand new shape and i want to use a sample so what i'm going to do is i'm going to duplicate this but i want to change the look of it a little bit i don't want this exact shape so i want to put a lighter so i'm going to go from darker to blue to lighter so i'm going to format that let's give it a fill effects let's do let's right click and add add some fill effects basically just more of a light so i'm going to do format that and then we'll do i'll remove this one here we don't need this i'll go from the background color which is this and then i'll go to white i want something pretty pretty light like this that's going to be good i like that that looks good so then here placing directly in here will start out and based on a specific row and then we're going to add that in here so we're going to place it in the start probably start on row 9 this column what column is equals column 18. okay so column 18 all the way over to column 23 18. so one two three six here and then go down then what i want to do is i want to go down two rows and i want to put another one so place one here and then another one here and then another one here however many there are but what i want to do is i want to create a sample this particular sample i want to use as a sample so we'll call this and i want to make sure that this one never gets deleted so sample called a sample product sample product and then i'll give it a different name i want to give this a name so that we know we can duplicate this depending upon how much we are so we're going to call this sample product shape so inside here we're going to sample product shape and i want to be careful not to use the whole word product because anything with the whole word product is going to get deleted so we're going to take this and we can take it off the screen in our hidden columns and we can just store it right here inside here this looks like a good place for it okay so sample products so basically the idea is depending upon how many results that we have here in products we're going to duplicate that this many times i'm going to duplicate that and place them dynamically all the way around and then when they assign a magnitude so when they select it i want so when they select a product i want whatever sku or upc is tied to that i want to place that directly inside here and that's going to add it up so that i'll walk you through it but that's basically the idea of it so we're going to use this shape here sample product to display it so let's make sure i may i thought i created that sample product shape so now that we have that so let's write up the macro to do just that saving our work and then going back in here now we're going to locate it called load products that's the macro we wanted to do so the first thing we want to do is with the pos sg with pos we're going to remove any any products that are there i want to clear out any products and add the new ones in so the first thing we want to do is clear them all out so again we're going to do just like we did up here so we can almost copy this here i'm going to paste it down here but i'm going to say remove any existing products paste that down here so for each product and shape if it can clear that out we're not gonna then resizing it this one we're going to do this but again when i select on a specific category i want to color this unique and i want to change the color of all of them so basically i want to recolor all them and make sure that the one we selected is colored differently so to do that we first need to recolor them all so that's what i'm do here subcategory reset all the colors that'll do that here delete any pro any in this case not products right oh yeah sorry in this case products is correct delete any products products will be down here we need to remove them and because we don't know how many we don't know if there's 5 or 50 or whatever so we need to remove all the existing ones before we replace them this code will do just that okay so now what i want to do is i want to color the shape style i want to color what we've done so i want to remember i said when i select a specific product category subcategory i want to color that so we can do that with one line of code here dot shapes because we've already got application color application that's the name of the shape that called application.collar so that shape style in this case again i want to give it that 30 equals mso preset 30 giving it that specific color that we did same with before but this time we're focused on the category so this 30 i want to give it that 30. that's going to give it that unique color that we have unlike all the others okay once we have that i also want to focus on the product so now we're going to focus on the product so we can do end with we're good with that in fact we don't need to use we can just do pos in other words i want to assign only this needed the sheet here pos where everything else is going to be with the products we'll do this now probably clear that out so with products so with products because i remember we need to run that advanced filter on those products so a lot of the work's going to be done in there so dot range the first thing what i want to do is we need to set based on whatever subcategory we selected inside the products i need to place that subcategory directly in k3 inside the products so dot range k 3 dot value is going to equal what it's going to basically equal the text of this here this right here the text whatever the text is that's going to be so equals the text application color dot text whatever text was in that is what we wanted text frame to dot text range dot text that's going to be so whatever text is in that shape is going to be placed in here so if it's butter or what soy or whatever it is it's going to be placed inside k3 that's going to give us our criteria now we're ready to run our advanced filter we need to determine the last row we want to make sure if for some reason k3 is empty then exit this up if dot range k3 equals empty then exit sub just in case we select on something and for some reason it didn't go just to make sure okay now what i want to do is determine the last row and the last row is going to be based on the product so the last row a okay that's gonna get us our last row if the last row is less than three then we need to exit the sub because there's no the last row is less than three then exit the sub okay so last row less than three exit the sub that's going to be automated with and then now we're ready to run our advanced filter so advanced filter is going to be based on all the product we just need really the information on the products all i need really in this case is the product name and the sku and the category in the subcategory here so we can actually run it all the way here but we don't necessarily need it a through f and then our criteria is here so let's go ahead and write that in a making sure that we have the last row that's correct a through f a2 our criteria is going to be based on j2 through k3 so we're going to write that j2 through k3 where do we want those results to go well i want those results to go located in n2 through o2 so n 2 through o2 now we need to determine the last results row so we have the last results rule we're going to base that on n2 so we can do that here so the last results row last results rule we can do here last result row is going to be equal to based on column n now if the last results row is less than 3 then we exit this up again double checking there last results were less than 3 then exit the sub so now that we have that we know in this case the last results and now we want to do is we want to loop through these and creating those name and bringing all that information in here so how do we do that now we can turn our focus back to the pos sheet we've gotten the results that we need so now with pos now our attention is back on here so we need to set the initial i want to set i've got some variables we need to set the row and reset the column now i've defined them up here pos row pos column those are the ones we're going to be using and again i mentioned them briefly also here inside the pos that the column was 18 and the row was 9. so that's where i want the initial one to go right here 9 and 18 and then i want to bring it all the way over here first row here second row here and so on and so forth but we need to set the initial the row is going to be 9 the column is going to be 18. so let's do that right now so so the pos row is going to be equal to 9 equal to 9 set initial and then the pos column is going to be equal to 18 equals 18. set initial column all right so now what we want is we're ready to run our loop four the product row is going to be equal to three and the last results the row last result row next product row right we're looping through all the product in those results we're looping through right here product all these starting at three going all the way down we're looping through them so what we do we're just going to assign those names to those shapes but first we need to duplicate those shape the shape we have our sample shape located right here in the pos we've created that sample shape it is that we need to duplicate it is that we need to assign the information and name and we need to also assign a macro and then place it directly here so we can do that with just a few lines of code so we've already said the product row so we're going to focus on that shape so the first thing is dot shapes i need to duplicate that that shape is going to be called sample product shape that's what we want to duplicate let's spell that right just in case making sure okay shape dot duplicate i want to duplicate that and then we're going to use it we're going to assign a name to it what name do i want to assign to it it's going to be equal to giving that specific name with product and i want to do the protocol and product row why is that important because it's important when i select a product i want to know what row it's on if i know that that product is on row 3 and i know the upc is in column n then i know exactly where that upc is because it is that one that i'm going to place directly inside here in j5 once i run the macro to select it so we can do that here so i wanted to find that row directly with that product much easier that way if we do that okay so now i want to place the location of it so we've created it already product row so now we can focus on that shape only so which shape are we focused on that one we just created right here so with this shape here dot shapes this shape right here that we just focused on here we don't need the extra quotation mark there product row we're going to do a few things with it i want to place the left position the top place and width make sure we close that loop first thing i want to do is place that left position where is that left position is going to be equal to i need to call out the sheet again because i'm inside another width dot ps cells we're using cells in this case p o s row p o s column dot left then also i want to do the tab and then we're gonna do the same thing with the top pretty much the top so all i need to do is just copy this here put in the dot top position top position is going to be equal to pos dot top position the top position of that one one equals is sufficient for our purposes now what i want to do is i want to add text to that right i want to not only do i want to put the text inside here must be the product name and where can we find that product name it's going to be located directly here inside row column o row whatever row we're focused on here as we loop through all the rows so that's relatively easy so dot text text frame two dot text range dot text is gonna be equal to the products that's where it's coming from dot range o that's where it is and the product row dot value set the product name okay next up i want to assign a macro to this we haven't created a macro but we're going to we can do that and now on action equals what macro i'm going to use this macro i've got it right here but there's nothing in it pos select product so it is this macro that i'm going to be assigning here that's the macro i want to do okay good so we want to assign a macro because when we click that we want to make sure that that object gets added to our point of sale great we've done that but now what we've got to increment right we've placed the first one we've only placed the first one but what i want to do is i want to place the second one and i want to increment the column or possibly the row i only want to increment the row down if we're already in the last column otherwise only increment the column so we can write an if then statement to do just that so if the pos column is equal to 23 that's the last column then do something then reset the row the pos the row has to be two down row equals whatever it is currently plus two we're incrementing two rows down and also what we're gonna do is reset the column back to 18. pos column is going to equal back to 18 right otherwise else all we need to do is increment the column pos column equals pos column plus 1. we're just going to increment the column plus 1. okay increment the column increment column and we're going to put this in this case new row new row all right that's looks good and then we have our end if here our next product row is here and with an n sub i like that let's take a look at it and then we're going to save that and then i'll be going to see how that works okay saving our work always and let's take a look inside here and what we want to do is we want to make sure that when we do this we want to assign that macro to these right so we need to assign the macro making sure that we know the macro so let's take a look inside that code and add that macro on to that so here we do on action all we need to do really pretty much is just load the products here let's go ahead and load the products this one here that's the macro that i want to assign to all of these so all we need to do is use our selection tool highlight these and make sure they look a little bit off don't they make sure they're lined up here perfectly just the way i like oh that looks pretty good all right so we're going to assign the macro to this just highlighting them all right click here assigning the macro pasting in the macro that's the one i want now that we undo our selection tool and then click bread now let's see product shape in dot shapes we need we need to set the sheet name because we remove that sheet name remember we had it but we moved it and that's fine okay let's take a look at that all right so there we go there we go we still need to get our our raffles and i want to make sure that we get the color right on that okay a few updates what we want to do is we notice look inside our products we still have the same category we want to make sure that that category gets updated as soon as we select a main category so let's write into that macro right now so inside this main category we need to put that and where do we want to put that inside the products directly inside j3 so let's write that inside the product the main category macro right here so that's just right up here loading the subcategory all we need to do is write it right down about here is fine so products.range j3 dot value equals dot shapes and then in this case category right which category category and what and of course we're going to need the category the selected category dot text frame i want to know what text is in that backtestdrain.txtrange dot text putting it and that's what we want we want to place that directly in there so that's all we need to do that's going to so now when we do that select the selecting the breads taking a look inside the product we now see breads and cereals that's what i want meats and seafood going back into the products meet some seafood okay good i like that so now all we need to do again is sect on beef here or chicken and now we want to make sure that we have the chicken we can see the chicken okay so now all we need to do is get our information up here so let's take a look at that all right let's take a look at our macro because we didn't have any we don't notice we don't have anything we should everything is right here so let's take a look inside the macro here and we got our advanced filter this should be n not n a okay let's take a look here now we could run the map now remember keep in mind that if you try to run a macro and we're using application caller it's never going to run from here it's going to create an issue why is that because application caller is looking for a shape to run that right we didn't click on a shape we tried to run it from here this macro only runs when you actually click on this specific shape here so we click on here we click here there we go so now we go okay i just want to change the color of that and then we'll get those to load up let's take a look back inside the code take a look last row here we need dot range okay it's going to otherwise it's going to be whatever sheet you're on obviously we don't want we want to make sure it's with the products take a look at that here and all right i think we need an e at the end of here and then continuing down all right let's take a look at that okay good i like that that looks pretty good cookies now we just want to i want to get that i want to get that font to white let's take a look at that and see if we can set that up automatically to white we'll just use my selection tool here i'm going to select all of these here and actually these here and i want to make sure that we assign them the right format i want to give them this formula i'm just going to reselect this now let's take a look and now i select them yeah then we got the that's the font that i want that white font okay that looks better so now i want to make sure that we assign the macro to this so we can actually create that and we can do that so now everything's looking good we have the right information we've got the right products for the right subcategories and the categories looking really good all we need to do now is just simply co select on one of these products and have it done that and that's a very easy macro so let's write that macro now okay so now all we need to do is write the macro now so let's very simple so let's get into that so that is the one where you need to select products that is the macro that we've already assigned on action to those subcategories so we just need to write that now so the product row is going to be equal to we need to get the product row we have let's bring this up product row is going to be basically whatever this is minus the word product so we can just extract the word product and we're going to get it out so the product row is going to equal to replace application color whatever is calling it dot collar replacing what we're going to replace product the word product with nothing and what that's going to leave us with that is going to leave us with the row replace product with nothing that's going to get us the row if product row equals 0 for any reason then exit sub assuming it's not all we need to do is pos dot range j5 dot value is equal to the products dot range and remember remember it's independent where is that located products here right here and i want to use that upc that's the one i want to put inside i want to put it directly inside as soon as we put in here it's going to load it in so all we need to do then is just write in n and the product row and and the product row dot value that should be it very very simple okay saving our work before moving on now let's take a look spinach it's going to add that spinach if we click spinach again it's going to change it to 2. all right i like that now what i want to do is i want to get this group automatically to the selected row whatever row we select i want to put it at i want to write a macro to select a specific row and add that group so we can increment or decrement or remove a specific row okay so let's do that now and that's of course going to be on selection change of any event inside any cells inside between i seven and o in the last row whatever the last row so let's write that up right now inside that okay so inside our pos here we're focused on selection change and we're going to start with i7 so going to be if not and then the range is going to start out with i7 all the way through o and we'll just go down a lot o and then just a large row number is nothing they make a selection change on that and what else do we want and i want to make sure that i contains the value and range i and the target dot row dot value does not equal empty i want to make sure there's a value there if they're going to be then we want to do something what do we want to do i want to set b6 first of all i want to set that row whatever row we selected i want to make sure b6 gets that selected row so we can use that conditional formatting so range b b6 dot value equals the target row okay once it's got the target row then i want to place that shape what group of shapes this is the group right here this is the selected item group it is that group that we want to place directly right about where it is now i want to place it directly inside between those so that we can then add macros to that and we can increase or decrease that so that everything is going to happen at the same time so with shapes and then the selected item group we want to do something the dot left position i want to place that left position it's going to be able to based on k dot equals range k that's column k and the target dot row dot left that's going to be left position the top position is going to be equal to range again k as well a and again the target row and this in the top position but a little bit higher so a little bit lower sorry plus one a little bit lower than that of this ui and then dot visible equals mso true now i really don't want this display if they select anything else so i want to hide that inside the selection change so let's do that up here if not okay so that means if what i want to do is i want to hide this regardless if shapes if they select anything first hide it the first thing is hide is selected if shapes dot visible equals true then hide it then shapes dot visible equals false that means regardless of what they select the first thing we're going to do is hide it then we only display it so if they select any notice that it's gone right if they select anything it's gone but if they select inside a row it appears otherwise it's gone inside so that's what i want now it looks nice so now we have the selected row right away that looks good very very easy let's write up some macros to at least to increment the rows up and down so let i want to increment the quantity up and down for this and this so we can write that inside the macro so and where are we going to find that we're going to find that inside our order so we're going to go back inside our orders here orders and we have item remove we have increased quantity and we have decreased quantity so we can do increase quantity start out with that so if pos dot range b6 b6 is our selector that's very important without that select row we can't do anything dot value equals empty then exit so we have to have that so assuming it's not empty the selected row is going to be equal to that b6 so we know we need to know that row we need to know which row equals v6 to know which one to increment up or down we have the selected row that's the selected row and now all we need to do is set k so pos dot range k is where quantity is located this is for increasing k and the selected row dot value equals whatever it is plus one that's it the decrease is almost the same but we just need to double check we can't go below one on this case so decreasing it it's going to be i want to make sure if the current value is one to let them know so i'm going to copy this of course this is going to be minus 1. if pos.value if the current value equals one then we can't go lower then message box you could say something like item quantities cannot let's see be reduced be reduced below one that's sufficient exit so nothing we can do you know it's not okay so now all we need to do is assign these two macros to those individual buttons that we created so this one here for increase right click assign the macro increase quantity click ok and this one for decrease right click and then assign the macro and then in this case order we'll put this as decrease here click ok so now that we have that so now try to decrease it it's not going to let us good increasing it it's going to let us good decreasing it it's going to let us until we get to 1. all right that looks great all right the third function on this one line is to be able to remove a line so if i click this x here i want this line item to be removed now also keep in mind that we also need to store the database and i'll show you what i mean by that what i want to do is we're going to also move this over here that'll automate so what i want to do is i want to use this column column h to store the database now what do i mean by database well if we take a look inside order items we have specific line items for every single order i've got the order id to purchase the row that means the current database row here it's row 4. the upc with the product name the quantity the amount if there was a discount there the total and the order row this is the row on the order so a 9 here would mean the same thing as 9 here now what i want to do is if i have an order line item here i want to know what row on that database it's stored what row is it here is it row 5 is it row 6 because when i make a change if it's already been saved let's say i make a change to this i increase it's already been saved but i make it increase i need to change this one from the two one to two but i need to know what row of the database it's stored on assuming it has been saved if it hasn't it's not so that database row is going to be stored here it's also going to be hidden so nobody's going to see that so i'm going to call this database row so we're going to put that that'll be hidden nobody will see that so i want to store that here so if i remove a row and it's already been saved what i need to do is i need to clear it out directly from this so i need to clear out i'll keep in the order id but what i'll do is i'll clear out everything else here so i want to make sure to do that and i also want to make sure that when i remove this row that everything under that moves up and now also what i want to do is if there's a row that's stored here i also want to know what row it's on so for example let's go take a look at this if this is on row 9 and this is row 10 when i load that order i also need to change the order so for example let's say i remove this raw in this purchase order but this is row 10 becomes 9 row 11 becomes 10 and row 12 becomes 11 so i also need to update the remaining items inside here because when i look back on the order here you'll see that row 9 everything moves up 9 10 11. everything moves up so the row also changes so i need to do a few more things if it's previously been saved if it's not previously been saved all i need to do is remove it and then move everything up or in the case if it's a last row all i just need to do is remove it okay so there's a few considerations we have to know because if it's been previously saved we have to make additional considerations so we're going to use that with the order item remove macro so let's go into that and start writing that up that's this macro right here order item remove so we're going to focus on with pos of course the same sheet and i want to know if b6 is empty we need to make sure that we have a row right b6 can is our selected row notice how b6 changes so b6 cannot be empty if it's empty then the user needs to select a row it should always be but just in case if dot range b6 dot value equals empty then message box please select a row to clear exit sub so we want to make sure that we've got a row of otherwise not you know it's going to be an issue okay so now we've got a row to move to make sure and we're going to put that row into a variable so selected row is going to be equal to this b6 value i really appreciate you sticking with us on these longer trainings if you do like to create these applications i've got an incredible deal for you with just seven dollars you can join our mentorship program that's gonna get you five weeks and total of up to eight hours of training for just seven dollars great start so if you like that i would appreciate you helping us out that's the mentorship program i'm going to teach you how to design develop deploy your own excel applications all right so back in the row here so i want to make sure that we have the selected row into a variable here and once we also i also need to know the last row what is the last row and it's going to be based on column i right if we need to move everything up we need to know what the last row is so if we're removing the last row i need to know that last row it's going to be based on column i so once we have the last row and the select row into variables then what i need to do is i need to determine is the selected row the last row if selected row is less than 7 right that's going to be a problem 7 or the last row is less than seven just in case last row is less than seven because the sevens are starting then exit this up shouldn't be but just in case right that shouldn't happen okay so now what i want to do is i want to check for proper permission remember i had mentioned to you before that the only users that have certain rights can be able to update and delete and change these orders right they have to be if we look in the admin and we look at only you know trainees can only add sales staff can only add sales and purchases managers can do everything so we want to make sure that they have the proper rights so if they don't include manager then we need to let them know so let's just write up some code for that to make sure that the user has the proper rights that they can actually remove rows because otherwise you if they're only allowed to add then we don't want them to do that so what we're going to be doing is we want to make sure that it's been previously saved so in other words let's say a user is adding an order but it's not saved they should be able to remove whatever they want to assuming it hasn't been previously saved but it's a previously saved order we can't so if h is not empty that means it's been previously saved there's already a database row assigned to it and b11 does not equal manager right if it's manager we should allow them to do it so what i want to do is if it's if it's previously saved there's going to be a value here and if it's manager then we can otherwise not so let's write up some code to do just that so we want to check to make sure they have the proper if dot range h and we know the selected row and the selected row thank you for sticking with us on this incredible training this is a master class on how to create a point of sale application all right so does not equal empty right that means it's been previously saved and dot range b11 that's where our user role is b11 dot value does not equal manager means they don't have the rights been previously saved but they don't have the rights then we're gonna let them know in a message box message box um please see your admin to obtain rights to remove this or let's just say update to uh update this order all right because it's already been saved so we don't want to let them do that okay and then exit sub we're not going to allow them to do that if they don't have the proper right to do that it's a good security feature okay so now i want to clear out the database route clear out database row if any if any means it's been previously saved it's already been saved and they're going back in an order and they're making an edit so what i want to do is if again if dot range h and the selected word just as we did before and the selected row.value does not equal empty that means it has been saved before then what do we want to do we can use double quotes here that's fine then what we want to do is always set the item database row the item database row is going to be located whatever's in here that's the row that it's located on item database row setting that up okay so we've got that variable that's the item database row so once we have that what i want to do is i want to clear this out now because we could make this share and sync in other words because i'm going to you could potentially want to share and sync this with other people in your store or something so what we're going to do is we're going to make it available for you if you want to know how to share sync just search my channel for share and sync and i'll show you how to do that but basically what that means is cell by cell changes only one cell at a time instead of just clearing all this out in one line of code i'm going to loop from two through what what is this column equals column i'm going to loop it through column 10 so from 2 to 10 i want to clear out each individual cell now what i want to do is i want to keep this order id but i want to remove everything else and all we need to do is make sure that when we run the filter we want to make sure type is not empty if it's empty we're not going to load it in so all we need to do is from two to ten clear out the individual rows okay so that's the database so we do that through this it would be order items.cells because we're using variables here dot cells what is the let's go ahead and we want to make sure let's actually we need to go ahead and loop it 4 we have item database column equals 2 to 10. and then what we're going to do is we're going to close our loop so we make sure right down below here we're going to say next item database column so inside that what are we going to do we're going to clear out what is it the item database row item database column dot clear contents clearing each individual item clear out sell by sell because we do sell by sell when we're sharing in sync we do that sell by cell because we want each individual cell to go to all the other users and i explain that in another video for sure and saying but i want to set this software up so you can be able to do that all right so that is our loop that's all we have to do that's going to clear out the individual cell so now that we have that we've cleared out the database for this only if it's been saved and so next up what we need to do is determine is it the last row or not so once we've cleared out the database i need to determine is it the last row if it's the last row we just need to clear it out we don't need to move anything up but if it's we're moving something like this we need to move all of these up one level so we need to determine what is that so we can do that let's click this update order item so within that we need to know if the selected row does not equal the last row then we do need to move the output then we do need to update that so then we're going to end up so then it's not the last row not last row else we'll put an else in here last row so we do one thing if it's the last row we do something else if it's not the last row so concerning if it's not the last row what do we want to do we want to update the item database if necessary and we can move on okay so let's assume we want to move this row unless we assume we have database rows here so what i want to do is i want to determine this database row here inside our item database let's move it over here and in case remember if we're if this is the order row 10 i need to change this to order row 9. this needs to go to 10 and this needs to go 11. everything needs to be moved up on the order row so i need to determine is do we have a database row inside h and if we do then we need to determine whatever is in our order items whatever's in j and reduce it by one so that's we only need to do that for our items that are below the one only items below that if they're previously saved so we can do that with the following lines of code so we're going to determine that we're going to run a loop for the item row equals the selected row plus one not the current row the one we're removing but the one after that to the last row and we're going to close our loop next item row inside that loop what do we want to do first of all i wanted to see does it h contain a value so if it doesn't if it does i'm just going to copy this here if we do have a database row then we need to update it if then we need to do something and what do we want to do i want to update that row to one less so the item database row is going to be equal to whatever is here so we know what row it is here and then copy that equals so item davis row that's the item database row so once we have that what i want to do then is take whatever's in j and reduce it by one so order items dot range j and the item database row dot value equals what well it's equal whatever's there minus one so we're going to copy that equals whatever is there minus one reducing it by one so that's all we have to do so that's just going to reduce it by one and now we're good to go as far as the database is concerned so we've updated that but what i want to do now is update all of the rows below that we need to move everything up okay so how do we do that so again move items move up remaining rows move up remaining rows so continuing on so we do that so let's do that so dot range we're going to do that all the way through h we need to include a dot range h and the selected row plus one row plus one that means the row below and colon m all the way through m and the last row we're going to copy that i'm going to copy all that and i'll walk you through this again what's going on here so i'll type it out so h and the select row plus one through m in the last row we're going to copy that then what i'm going to do is i'm going to paste in those values just the values because we've already got conditional formatting and everything like that that's important paste those values all the way through so that's going to be dot range h and the selected row h and the selector are not plus one and the selected row through and the m in the last row minus one m and the last row minus one dot paste special not value dot paste special paste the value so we're going to go all the way down here and select on those values that's the one i want to do okay and then we're just going to once we use the copy and paste we need to remove the dancing and so application dot cut copy mode equals false okay so once so basically what we're doing is if i decide i want to remove this row i'm going to do the select row h all the way through m our formulas will be handled that all the way down to the last row i'm going to copy that basically then what we're going to do is we're going to paste those values in here then all we need to do is just take that last row and remove it so that's what we're going to do now inside the code okay so once we do that so dot range h and the last row all the way through and colon and in the last row including the formula n and the last row dot clear content so we're just clearing that out so clearing the last round because we've already moved everything up clear contents so clear the last row let's write that clear last row okay good so we're now done that so that's basically everything we need to do if we're not on the last row but if it's the last row it's much easier if if we've selected the last row in this case all we need to do is just basically h all the way through and just remove that last row it's much easier if it's already on the last row else again in this case all we need to do is just copy this and i'll change it from selector oh there we go so that in this case else and the selector will change this to selected row but this is the same thing selected row is equal to the last row in this case and the selector just a little bit more clear but it's the same selected row clear the contents so clear the last row in this case it's the last row so we've selected and removed the last row okay very good so that's all we need to do to move everything up and now what i want to do is reset the footer right we want to reset that footer that that macro that resets the footer it's this one here place footer footer okay so order set footer i'm going to take this macro because once we move everything up we need to reset the footer so i'm just going to paste that right there and now what i want to do is i want to make sure we select at least one row i don't want to select an empty row so to do that dot range we'll do the say i we're selecting something and this case the last row minus one i just want to select that dot select so that's going to select a row on that okay so let's take a look at that that looks pretty good and then select and then we'll test that out so we just need to assign this macro that we just created with that x in the line item so i'm going to copy that now we're going to go down here to right here and this i'm going to take this macro here and then this button here and assign that macro to it pasting that in clicking okay all right so let's take a look at that and then we'll close that out see if there's any okay that looks good we've selected the last row clicking here selecting that that looks good good and good you see how that works now we can just easily delete items okay very very good and we can easily add more items if they're different and the footer notice the footer is automatically dropping all right everything looks really good now let's continue with this order all we need left is just to create these orders save the order update and clear it just a few macros left so i want to be able to save and update save or update that order it's going to be the same macro that handles both so let's do that first saving the mac if i click next it's going to automatically save or update the orders the same macro so let's write that macro right now so we've got item removed we've got that so next up is item order save and update so we're going to focus on with pos that's we're going to do i want to make sure to check the last row and if it's less than seven let them know we need to have at least one row item i'm not going to make the vendor or the customer required whether it's a sale or purchase we're not going to make that required but i do want to make sure that we have exact at least one specific line item here without that so let's first make sure that they've entered the nine item so the last row is going to be equal to i'm going to use column i for that and then setting that up if that last row is less than 7 we need to let them know to enter at least one line item so if last row is less than seven then it'll let them know message box please enter at least one item to save this order okay we'll call it order order can be used for sales or purchases that's fine exit sub those are our required fields now what i want to do is i want to make sure if i want to determine is it a purchase or is it a sale because if it is a purchase only user right so let's take a look at the admin again we see that only uh trainees trainings can only add sales right so if it's a purchase and if it's a trainee then we need to let them know you can only add sales so let's just run that and of course b1 is going to handle our what type it's sale purchase and our b11 is going to handle what the user role is so that's what we're going to focus on those two items there so if dot range b 1 not valid equals a purchase equals purchase and range b11 equals trainee then we need to let know they don't have rights to add those purchases so message box please see your admin to obtain rights to save this purchase exit sub okay we're not going to allow them to do that they don't have the correct rights to do that okay assuming that they do have the rights we can then move on so we can say first of all what i'm going to do is i'm going to turn off application screen update make this a little bit faster application dot screen updating equals false and i also want to turn off calculations application dot calculation equals manual so most important thing this is going to make it a lot faster but before that macro ends we need to turn it on and make sure we don't do exit sub so before the macro ends we need to return it back to true and we need to turn it to automatic so equals automatic okay everything else happens in here okay so assuming that we're gonna now turn those off it's gonna make things a lot faster so we need to determine is it a new or is an existing order if it's an existing order it's gonna have a database row already a row that row and order row here this is our row that we're focused on does it have has it been previously saved or not where is that stored that is stored right here it's going to be order database row right here six notice it's six because we have order id one zero zero three it's already been saved if b3 is empty it has not been saved for example if we're on one zero zero nine it's going to be blank so we that's how we determine that based on that so how do we do that so now if b 3 is empty we know it's a new order so let's do that if dot range b3 value equals empty then it's a new order then new order else existing else existing okay so we do some things for new order we do some things for existing orders so new order do more for new order because we have to put this up so assuming it's a new order then we do we need to determine the order database row order database row is going to be equal to the first available row on that so it's gonna be plus one but it's gonna be based on that orders database orders that's the one that's the sheet we're looking for okay all right so orders plus one so we're going to get that new row now what i want to do is i want to put the first of it this one doesn't have an order id so i need to put that next order id and i want to put it in column a so to do that we need to get the next id well we've already stored that located here in b4 so that's going to get us our next order id using the max formula so we're going to place that directly in column a so so we already know that we know the order so it's orders dot range a and the order database row dot value equals dot range b look let's take a look at this b here before so sorry in the next here back in p4 we're going to look at the next available row next one-order ids b4b4 here so we just put that right in here equals b4.value next order row okay so what else do i want to do well that's it for now everything else we can do regardless of whether it is new or existing else but what if it's existing if it's existing we can obtain that database row directly from b3 so order database row equals b3 existing database row now everything else whether it's new or existing is going to be the same now we're not using data mapping in this one so it's a few more lines of code but we're going to save a little bit of space okay so we want to do something in b b c through g so what do we want to do i want to add in the rest of the order i want to put into all the rest of the information because it could change from purchase to sale if they accidentally enter the wrong one i want them to be able to change it so everything from b through g we're going to add in all the order details so we can do that right here so here order dot range b and the order database row dot value so we're going to add in b uh c d e m g so all the way from b so we just continue on c d e f and g so now we're just going to add those in c d e f and g i think this is my longest training ever okay so b what's going to be in b that's going to be the ordered on equals and we'll put the order we can do this now that's the current date and time so inside c is going to be the type that's located in b1 equals dot range b1 is located our type that's sale or purchase type sale purchase and then next up what do we have inside d we're going to put in the status what is the status equal and the status is located on our purchase order sheet here located right here inside 03 so we're going to put that in right now star range 03 that's the status just put the status and next up what do we want next up is going to be whether it's our customer or vendor so it's going to be that name located in e so where's that going to come from that's going to come let's drop this down here that's going to come directly from whatever customer or vendor we've entered into j4 so j4 is going to be there we can just put in j4 and then we'll do customer or vendor and then next up what we have we have our uh inside f we're going to be putting our username so our username is going to be stored located in l4 so l3 so we can do that dot range l3 that is our user name so we'll put that in there i want to save that username and then lastly i want to put in our total paid if they've paid it or not so dot range where's our total paid our total paid is going to come directly from here inside b16 so we're going to put that b16 paid amount i like that so now all we've saved all the information here now what i want to do is i want to go through and save all of these individual items to our database but we need to check have they been saved already if they've been saved already update the row if not add a new row inside our database and so to do that all we need to do is run a loop from 7 to the last row determine if there's a database if not add the new one if it is update the existing one so we'll just do that inside here so let's put a little note in here add order items inside that we're going to determine if dot range actually we can start a loop right now let's do that for let's call it item row equals 7 to the last row make sure we close that loop before that so next item row next item row inside that i want to determine if h and the item row does not equal empty or equals empty we need to determine equals so if h and the item row.value equals empty then do something else do something else obviously okay so if it's empty then it's going to be called a new item database row and otherwise you're going to put existing item database or existing item database so if it's a new one we need to do a few different things so we're going to design item database row is going to be equal to the first available so this sheet is called order items order items put that dot we know we've got the right sheet it's going to be the first available row inside that sheet once we have that what i want to do is i want to put the order id inside column a right we've got our our r items here the first thing i want to do is put that order id i want to put it in column a that order id is going to come directly from what's located in p4 so that's the first thing we need to do so order items.range a and the item database row we only do this for new items dot value equals dot range p3 p4 sorry p4 is our p4 and i'll put a note there that is our order id number okay all right so now that we have that there's something else i want to do inside our order i also want to put in the row here that's not going to change only for new the reason is i want to bring this row back into i want that row there i want to bring it directly back inside here and i'll put it here so i want to make sure we put that and i'm going to put it directly inside column c so column c is going to take on that so here all we need to do is just copy this and then update the column so column c is going to be equal to that item database row now i could use a formula if they were going to change if i was deleting rows i would use a formula so the item database row that's the row number that's going to be changing okay so now that we have that and see what else do we need we want to put only things that are not going to change everything here could change so even this could change so everything else we need to put if it's existing but since we already have it i do want to add in the order row what is the order row what row are we on here inside the bs is it row 9 is row 10 i want to place that row directly in column j and that's going to be for the new item so again pasting that in adding j this time what i want to do is going to be the item row equals the item row and this is going to be the order of order row so this is the order rows on the order and also what i want to do is make sure we have equals item row and then comment that out okay so now we have that now what i want to do is i also want to take this database row and i want to place it directly inside h right here inside h i want to put it because it's not there yet i want to place it there and i'll place this item database row right here placing that in h so what that's going to do is set that row right here set that database row because it doesn't exist yet so now it does so all of that only for new items that have not been previously saved okay so now we have everything for these are all for new database items what about for existing ones so existing ones are going to be a little bit different all we need to do is assign this item database row to whatever is in h so to do that we just say basically whatever is in h the item database will copy this we can copy this here and then just set the item database row to equal this so item database routes equal to whatever's in h item database row so now ev this is for new this is for existing everything after this point we're going to do regardless of whether it is a new or an existing item because we're going to make that update so what i'm doing is i'm going to copy this down here and we start out in column b so remember this is regardless so what do i want to put inside column b well i want to put the type because it could change i want to put d e f g h and i and not j that's not going to change but everything else could change so let's add that in right now so b is going to take on the type so we'll just put that in your type and then we'll add in the rest later d is going to take on the sku so we're going to put in the comment sku we're going to take on the product name inside e you see that's why we use data mapping because it's a little bit slower but for this training we're not using data mapping i'm want to keep the database clear okay so in this case is the product name and also in site f is the quantity f is the quantity and then also in g is the amount and then we have the discount and this is going to be the amount and of course i need to add in the detail on the order still on each line but i'm just putting these down so it's easier if we do this h will be the discount if any and i want the total now this total is going to be one way meaning when we save it i want to put the total but when we load it we're not going to bring the total because the total is handled by a formula so i is going to be the total we've already taken care of j so we don't need to do that so this is the total okay now we're going to handle the order side of things so we still need to do equals and then the order so in this case the type is going to be equals dot range b1 that's the type right b1 is coming from the type here inside here now everything else is in p1 b1 is going to take on i want to know the type because that's going to be important when we run that inventory we need to know based on the type so the sku is going to come from the item row and in column i equals range i and the item row so that's it so the item row is going to handle that now i'm going to copy that here i'm just going to paste it down here and then we'll update the columns we have the product name we have the quantity the amount the discount and the total so now all we need to do is just update the columns accordingly so bring this up here let's slide this over here so next up what do we have is they're going to be just going to make those updates product name is going to be located in column j so we just need to update to j we have our quantity which is located in column k so we're going to update that we have our amount which is going to be inside l change that there we also have our discount if any that is going to be inside m and then lastly our total is going to be an n okay i like that that looks pretty good and now we just need to update we've got screen updating so we're going to loop through the items we've gone through all the items clearing that out we don't need that and then application screen updating order clear so now what i want to do is i want to run another macro even though we haven't created that macro once we save that i want to get ready for the next order so how we're going to do that we're going to run a macro this macro here which we're going to be doing now i want to clear that order so i'm going to do that down here order clear so we're just going to run that macro it is that macro that we're going to do okay we're going to save our work and let's take a look and see how that works and then what we'll do is we'll do let's say i want to assign this to this next so i'm going to assign the macro that we just created sorry i'm going to right click here and then it's going to be order save or update it's this one right here that i want to do and click ok ok again now what we're going to do is i'm going to click next and we're going to take a look so notice we have the database that have now been assigned so that's good we have one zero zero nine that's going to be let's look in the orders first we have one zero zero eight we don't have we didn't put a customer vendor here that's fine let's make sure it's empty we didn't put one in that's fine let's do it now and let's take a look at here one zero zero eight so it created that but one zero zero nine and need to make sure that we have that updated all right we'll get the next order id now look at an order item so we just need to update that because we put in manually 109 but 1008 was the next one so we had a little bit differentiation between that because we manually put that in so what i'm going to do is 1008 i'm going to change this to zero zero zero eight because that's really the one that should have been one zero zero eight and then what we'll do we won't need to manually do that anymore so that's the way i want orders one zeros or eight okay good and then just change this now the next one will be automatically set up so there we go so let's click next again now i'm going to go back into the orders now we have the data so now we've saved again we've saved it here i like that here update the orders now what i want to do is i want to add a single item onto this and make sure that that item gets added to the database here so we're going to go back into the bios i'm going to add one more item here so now it's now i'm going to click next again we still need to clear that but now it's been assigned in the database to 40. so looking inside the orders we have a brand new one here the lettuce got added as the final item perfect i'm going to change the quantity of this and then i want to make sure that that gets updated so i'm going to select on here move up there and then click next and then take a look at the order arms now that quantity is four okay that's really good all right so the next thing we want to do is we want to make sure we use that clear there's that clear macro that's going to clear this and get us ready our database rows are set remember this is going to be hidden sometimes what we want to do is just change this font a little bit to remind ourselves that it's going to be hidden so we use a blue font or something like that all right so this column is going to be hidden and we're going to save our work and now what we want to do is create that macro that's going to clear it out is this macro that we're going to use the same for this button it's the same macro that's going to run when we have the next so let's write that macro right now so inside the order clear we can bring this up again and what do i want to do basically i want to clear out some cells here so i want to clear out obviously our customer here or vendor here and i want to clear out all the rows here i want to clear out the formulas here that's fine because we're going to take the formula here and we're going to bring it down for every individual item no problem and then also the last thing we're going to update that okay so let's use the clear and then write that up so again with pos focused on one sheet dot range j4 i want to clear out that customer it's going to be clear content so we'll end that and then it's going to be clear content so we need to clear that dot clear contents and what do we want j4 along with what b16 what is b16 i want that selected row whatever's inside b16 is going to be the total paid that's got to be cleared out there total paid and so we're going to clear that out also what i want to do is all of the of course the table h7 including the database row all the way to n and then a large row 999 clearing the contents okay once we've cleared those contents i want to set up the next order id i know the next order id in this case is going to be when i now i'm ready to take that order id and place it directly inside p4 so that's what we're going to do right now so dot range p4 getting it ready for the next order equals dot range b4 equals all right b4 it's going to take that next order id next border id so we've prepared that i also want to open the order i want to set the order status to open so our order status is located inside o3 so dot range 0 3 values equal to what is that where is that first status located it's located in the admin sheet and where is it located it's located let's take a look right inside the admin sheet it's located right here inside k7 so k7 is the status that i want to assign to that so admin dot range k7 dot value open order status next up after that what i want to do is again i want to reset that foot right we need to reset that footer every time so but that's why we created a single macro to do just that so it's much easier so let's do that all we need to do is just place footer here copy this here because we already have it and place the footer that's going to be one row down so pasting that here that is there and then what i want to do is i want to select one single cell to allow them to do that let's say j5 so or let's say let's say upc because they're going to scan in a new order using a barcode scanner will work just fine and so we're going to select j5 that way they're ready to put in a brand new item so because they may not be have a customer dot range j5 and then we'll use select i like that that's all we need relatively simple so good so now when i click next we've already done it so now it clears everything out let's take a look at that we do need i want to clear out the current upc so let's do let's add in j5 to the clear contents but it looks very good we're ready to go on this so we now add it but the only thing i want to clear out is j5 whatever was there i want to clear that out so all we need to do is add this comma j5 here and perfect okay so look let's take a look at that and i also want to take that macro that same macro and assigned to this button here so we're going to assign the macro in this order clear so it's this one right here so if i clear that order now it's done all right so now all we need to do is select items here and then select them here and they get added automatically okay that's looking really nice i like that and then we can create that okay so why don't we run the pay print and refund it now as well but before we do that always one thing when i enter a order number here i want that order number to load so if i put in one zero zero 8 i want to load that order automatically so how can we do that well we can do that with just a simple macro called order load that's the next macro we're going to write and basically it's just the reverse of what we did so we're going to focus on with pos in this case i want to clear out content so pretty much the same but a little bit different not everything that i want to clear out so i'm going to copy this line and we're going to paste it here so this time not j5 we don't need that and b16 so everything's okay except j5 we can load j5 is okay too we can keep that in no problem so we're going to clear out existing and i want to make sure that b3 is not empty b3 if i enter an order number here one zero zero eight right i want to make sure that b let's take a look here select order search order row based on whatever's in b3 i want to make sure that b5 is not empty so that's very important but if i take this order id and i place it here in side p then it's going to be b3 so if i so let's say user searches for this i'm going to take this here and place it directly inside here one zero zero eight once i do that using vba this b3 is going to populate with the database row so let's make sure that b3 actually contains a value a database row if it doesn't then we have a problem so let's make sure that if dot range b3 value equals empty then let the user know message box please select because we're not sure select or enter because they could be selecting it from the results or enter in a correct order because they've entered incorrect exit sub so it's incorrect okay so now that we know that b3 does contain a value we can move on so i also want to do the same thing here again application screened up just what i did up here application screen updating going to turn that that's going to make things faster but again we want to make sure that we also reverse it once we have noticed the exit subs before that and then also i'm just going to do this and copy this down here so just as we did before and then make sure that we can put it after the end with is fine okay as long as it exists it's fine actually i like it inside i like it put it there but either one because it's inside the width so everything's the same okay so now that we have that what i want to do we can write the code inside here so what are we going to do well the first thing i'm going to set the order database row that order database row is lo is located here in b3 so we're going to set that into a variable order database row is equal to whatever's in b3 database order database rule because we need to load all the information from there so once we have that row we can then load that in so dot range the first thing we want to do is dot range l4 l4 is going to be located our user so we want to put that in l4 is going to be equal to actually our ordered on here is our ordered on so we'll go in order because i want to go b all the way through g so we already have the order id so we're going to go all the way from b through g so the first thing is b going to be ordered on l4 is going to be equal to the orders dot range b and the order database row dot value and that's the ordered on the date and time ordered on okay what i'm gonna do is i'm gonna copy this and then we'll make the adjustments accordingly for each one so we're gonna do uh b c d e f and g so we're gonna now we're gonna update that accordingly so column c column d e f and g okay so now c is gonna be our type that's gonna go into b1 our type we're also going to have d which is going to be our status our customer vendor customer vendor and then we have our user and we have a total paid user and then our total paid basically it's the reverse so now we just need updates i believe this was b in this i will go in order better to go in order our type is going to be b1 that's going to be one where our type is our status is coming from o3 that's where our status is and our customer vendor is going to be in j4 so we also have our user users going to be an l3 username l3 is where it's coming from and then our last one we have in our total page that's going to be b16 16 is going to double check that i think that b16 is here i want that paid amount to go here so we've got that that's our main information so that's all the main but now we need to load in those items so that's good we're good with that and that's going to be it for the pos let's uh actually move this back up here because i want to focus now on our order item so with let's put in a comment load bring in those load order items with order items and i usually put the period just to make sure i've got that sheet right notice how it comes up so now what we're going to do so our order item so basically what i want to do inside our order items is i want to run an advanced filter i want to base it on this order id this order id is automatically linked to p4 that's helpful right whatever's in p4 we've already placed the order id here so now i just want to make sure that we don't have type as blank if we remove an item type will be blank so our data is going to be here our criteria is going to be here and we want those results to go right in here i want them in this row i want the row first why this is going to be in column h then i want it in this order and this is going to be our order remember this is gonna go in seven or eight what does that mean order row that means we're gonna put it in here in row seven or in row eight so we know what or replace it the database row is gonna be that first so i know where to place it so all we need to do is just run that advanced filter determine the last row and then place each one here so we can do that with the following lines of code so with order items i'm going to determine the last item row the last item row is going to be equal to of course that's going to be based on the order items we already have the sheet located here in column a so that's going to guess what if the last item rows less than three then exit the sub okay so so we can do that if last item row is less than three then exit then exit sub okay so when you have that now what i want to do is run that advanced filter so we're going to do that here and it's going to be based on so that's autohotkey again so order items here i'm going to run that all the way from j right a it's going to start let's go ahead and take a look inside a last item row not last row last item row a all the way through j then our criteria is going to be located in n through o n through o and then our results what do we want those results going to come in all the way to from q 2 all the way to let's take a look here all the way to w q 2 through w going to get those results then our results so then what we want to do is we get our last results row last results row is going to be equal to we don't that's skip one line there last results row last result row is equal to in this case we're going to use q so q is going to be that q is going to be our last results row that's going to get us our last row if it's less than 3 then there's no data so then that's it so we don't want to exit the sub keep in mind here's an issue that could be we're going to exit the sub but we haven't yet turned on calculations so what do we want to do just do this something like no items and then we're instead of going exiting that could be an issue then go to no items it's going to skip that that's better because otherwise we're going to create a problem for ourselves we're leaving the sub without turning on screen updating without turning on calculations could be a problem so instead we're just going to go to which is going to skip everything that we're about to write and it's going to go automatically here so now that we know we have items and then we can continue on so the best thing what we want to do now is just bring all the information over we've got it so we're going to run a loop 4 item row is going to be equal to 3 to the last result row closing our loop next item row so for each one the first thing i want to do is determine the order row what row on the order is going to go to that's going to be located in column w equals dot range w that's where it is and the item row and the item row okay we can just make sure all right that's going to always have a value so we don't have to worry about that that's going to be our order row pretty self-explanatory there okay so once we have the order row then we can then run it so then i want to do the pos dot range h and the order row and through what's going to go all the way to m and m all the way to m okay we don't need the total remember the total is going to be calculated so we don't need that we're going to bring down that formula so don't i only want the total in the database through m and what is it the order row dot value equals what's it going to be equal basically from q all the way to v from q to v equals dot range q and the item row and the item row and all the way through v and the item row dot valley so it's going to bring in all the information over there and then again all we have to do is copy that formula i want to bring that formula down here close that loop right here i'm going to close that okay i like that and also add the ants on here left that off okay good that looks pretty good the way it is okay so now all we need to do we brought basically i've taken everything here and not the order up but everything up to v and we brought it in directly inside here all the way from h through here up until the total so now what i want to do is copy whatever is in and five and paste it paste the formula directly in the cell so we can do that with this dot range and five oh excuse me we need to call the pos there because we're on the different sheet pos and five dot copy that's where our formula is and five our total formula dot copy dot copy we'll call this our total formula yeah yes you could actually put in all the formulas but i don't tend to like formulas that are not being used so that's just personal so we're going to copy that and all you need to do is bring it in so where's it going to go p o s dot range n and the order row dot paste special and then paste formulas right we're just pasting those formulas in here so it's a little bit off the screen i'll select down okay paste formula so replacing the formulas that's good i like that and that's going to do that all right so we don't need to turn off screen up until we get to the last so application dot cut copy mode equals false but i want to do that out of the loop so right because we're going to need to copy that for each row so that's going to paste the formulas okay i like that that looks pretty good so that's going to bring in the formulas the total formula paste in that total formula and cut copy modi goes false okay the last thing when we of course we need to set the footer right if i load in we need to reset that footer so i'm going to place that footer right based on that and also there we go just that's the last thing we need to do place the footer okay so that's going to be good and also what i want to do is i want to set remember we spoke of we need to we need to automatically place this sale or purchase but we need to base it on whatever the order tips when i load that order we created a macro already that's going to automatically switch this based on whatever order we just loaded in so this is that macro that we want to run right now let's take a look at that inside our pos macros we created that here sales or purchase set the type that's the macro that i want to run right now and it's in order macro so once we load that order i want to set it up here so we can run it right there okay sales or purchase set type that's actually we can do it before just in case so it's a little bit faster there's no calculations that are in that set type so that's fine okay so saving our work and what do we want this to run well i want this to run when i basically do two things there's going to be two ways that we can load an order loading an order is going to be when i select on something i want that order to load or when i enter a correct id i also want that order to load so this one's based on selection change this one's based on worksheet change so let's write both of those up right now so inside worksheet change here we're going to go we're going to focus back on the worksheet change we're going to focus on a change event so let's put this on order id change that's the search we're looking for so if we're going to do that if not we're going to focus on i3 i3 is where they're going to put in that i3 we want to make sure there's nothing and we'll also make sure that i3 doesn't equal if i'm going to make sure i want to make sure it doesn't equal in the words enter order enter order because i don't want to do that okay so let's update that now is nothing and range i three dot value does not equal enter order because that's the default text that we're going to be adding in so we want to make sure it doesn't that and range i want to make sure i3 is not empty so i three dot value does not equal empty then do something okay then we're going to focus on that so first thing i want to do is remember there's a database row if i enter ones there's a there's a database row that's here located in b5 that database row is based on whatever order id is here so if there's an incorrect order id this is going to be empty so if b5 is empty they have not entered the right id so let's let them know that if range b5 dot value does not equal just put equals empty then so it's an incorrect let's put incorrect order id incorrect order id so message box please enter a correct id can't do anything without that okay so basically what i want to do at that point is just exit the sub so nothing they can do let exit sub unless they enter the correct one okay they can make the changes or something we don't want to clear it out in case they off by just one character so anyway so now assuming that it is correct what i want to do is i want to take whatever order id as i mentioned before i want to put it directly inside p4 so p4 is going to be whatever they enter because it is correct so range p 4 dot value equals range i3 taking that order id dot value then all we need to do is run the macro and then also what i want to do is i also want to order load that's the macro if i put it in lower lowercase and it comes okay order load so that's going to run macro to load order that's the macro we just created and also what i want to do is i want to return i3 back to the default text so range i3 is going to equal to enter order okay it's not going to loop out be careful when you're making a change to i3 and you have i3 change it could loop out if this inch order is not exactly what this is you're going to create a big problem loop so you want to make sure otherwise it's going to keep changing changes changing so we want to make sure enter order is exactly hidden to order here then we won't create a loop sometimes you get those loops and your program crashes this is how it could crash because if it's not exact right if you don't if i don't put this in here it could crash because it's going to keep looping and looping and looping okay so we've got that so it's going to update that and then we have that all right so that's going to be unchanged so let's test it out so let's add this in here let's double click that and take a look at okay spend it end with we need to end with let's do that end with reset that okay take a look at that all right all right looks like we've got the order but no items let's take a look at why inside the code and load so we have no items let's make sure results row this should be result last result row should have bugged out okay take a look at that running that okay now we've got some items we've got our database row here perfect okay so let's and we've got our result text so let's do that one zero zero seven add in another order okay i like that the footer is automatically being placed just the way we like it now we're loading orders so now what i want to do is when i select an order here from the search results i want that order to be placed here so that's going to be based on selection change so let's write that up right now okay so back inside the orders here the pos actually and it's going to be based on selection change so we can clear that out clean this up a little bit we're going to be basing on new so now we're going to do it on if they make a selection change anywhere between d5 and f and all the way down and we want to make sure the d contains a value so if not intersection d5 all the way through fd5 through f999 we need to make sure that d contains a value to nothing and range d and the target dot row dot value does not equal empty then we need to do some more then we need to load it so then what do we want to do then don't forget to then randy then okay so now that we have that it's not empty first thing what i want to do is i want to take whatever row we've selected we oh we already wrote that out didn't we there didn't we we did here yes we did right here so now we don't need to write that up again so all we need to do now is just run this so now we have it all i need to do is take this order id it was located in f and place it directly inside p for so that's and then of course we just need to run the macro to load it so we can do just that so range p for dot value equals whatever is in range f and the target dot row dot value and once we have that all i need to do it then is just run this order load here that's the macro that we just created up here so we can just copy that run macro to load order copy that and bring it down here so now we're going to save our work and now let's just take a look so now when we select an order it's going to run i like that we also need to find our button here and then clear that out and then use that so we can close that out so let's locate that and find out where that got lost to and then update that so we're going to show all to make sure there it is okay so clearing the results so clear those results i like that okay so that looks good this will be hidden automatically we see that let's hide that now make sure we've got everything set up the way we like it i want to search for a vendor fred here and then it's going to search automatically open that all right i like that clear those results okay we're looking really good now we need just a few more macros and then i'm going to let you go in this epic training we want to be able to pay for that so how are we going to do that so payment's relatively simple all we need to do is make sure that they're entering an amount into a simple input box and then place that amount directly located in b16 so that's just what we're going to do inside the macro so we can go into the order macros and we can continue down here order pay so the first thing i want to do is dimension let's see the payment amount as double and then what i want to do is just put on a resume next just in case there's an issue with the payment so the payment amount is going to be equal just using an input box not a form input box keep it simple please enter a payment amount then we'll give it a title we'll call it enter payment keep it very very simple because this is not enough okay so then on air go to zero just in case you can close that out air chop okay first of all i want to make sure it is a number so if first i want to make sure it isn't it is a number so is numeric then what i want to do you want to make sure the payment the payment that they just entered payment amount equals false means it's not a number then i need to let the user know or maybe it's zero or the payment let's see or the payment amount equals zero then let the user know then do something message box please enter a correct payment amount exit the sub okay assuming that we do have the correct payment amount then we can continue on exit sub then what do we want to do in that case i want to take b16 and put it in the pos dot range b16 dot value equals the payment amount so we've set the payment amount and then also what i want to do is i want to set our status here 03 i want to set it to where i want to set it to whatever the paid status is located in the admin k8 setting that page status so pos dot range 03 dot value equals admin dot range k8 dot value okay paid status that's it so let's take a look at that i'm going to save our work here and then it is this macro order paid that i'm going to assign to that payment button there right here so this one but i want to make sure to assign it to the button and the icon itself both of them so we're going to sign that mac or paste in that and click ok so we're going to pay that amount so if i enter something that's incorrect please enter correct payment amount correct okay good and then pay and then let's just say enter 15 click ok so now our total paid amount our total paid there we go that's it so very easy next okay good so what about the print i want to be able to print this so let's write that macro right now so order refund void print uh let's do the refund let's just go in order so we can we can go let's do the refund so first thing what i want to do is i want to check for the proper user so we're going to mention we'll just go in order because it's better refund amount as double just as we did the payments and i also want to make sure that they have proper rights to that we don't want to be able to give the refund if they don't have the right so if i want to check if pos dot range b11 is where the user rights are located dot value does not equal manager if it's not a manager we can't give them rights does not equal manager then let them know message box please check with your manager for rights to offer refunds i like that now also my with calls this anyway so that's why exit sub so we're next out there if they don't have if they're not a manager that's fine okay so we also want to make sure that b16 does not equal mdi they can't be a refund if they haven't been paid so if pos dot range b16 dot value equals empty then i'm going to put something message box refund which but refunds cannot be applied refunds cannot be applied to orders without payments right you can't offer a refund if you haven't paid without payments we need to make sure there's a payments exit sub i also need to make sure the refund amount can't be greater than the payment amount right i can't give them a 50 refund if they've only paid 20 so let's check that if okay so let's do we can just copy that and then update that accordingly so in this case if b16 value is less is less than the refund amount refund amount so then we have to do that but of course we're going to take in that refund amount inside the let's we'll put that refunds cannot be greater than the payment greater than the payment amount but i need to get that refund amount before we write that so we're going to get that from an input box so we can just do actually we can add we can write it in again so the refund amount is going to be equal to the input box and what is that input box let's just put right same thing so input box please enter a refund amount and then just call refund just call it refund keep it simple okay so that's the refund amount so we're going to set that up there and also again i want to do on-air resume next just in case there's an error in case there's a they put in the wrong error on air go to zero okay so now that that we trapped any errors inside there and just like the payment we need to make sure that it's a numeric so i'm going to do this again here and i'm going to paste this down here just to make sure that they have that correct so is numeric of course it's going to be the refund amount here and not the payment amount so we can update that or the refund amount is 0 if it's 0 or if it's not a zoom please enter a correct let's call this refund amount refund so just to make sure that they've entered the proper refund okay so assuming that they've passed all of those tests then what we want to do is i want to deduct the payment amount by the refund amount so we can do the pos dot range here b16 that's our payment amount b16 what is the new amount we need to subtract the payment amount dot value is equal to the current value minus the refund minus whatever refund so equals the current value here minus the refund amount refund amount okay so this is going to update that okay good i also want to update the status just like we did up here we need to update the status so but it's not going to be paid styles it's going to be refund so i'm going to paste that here 03 is equal to what it's not going to be k it's going to be equal to our refund here which is located in k9 so we just need to update that to k9 so k9 is located right here okay so that then we're just going to call this refund so we know refund status great so we've got that now what we want to do is we want to avoid the order so next up is void so we're going to do that but let's assign this macro make sure it's working before we do that copy this we're going to apply that to both the back and the icon here on the refund so selecting that and holding down control right clicking assigning that macro pasting that in there okay now we need to make sure obviously this there's no payments so we can't refund refunds cannot be applied to orders about payments that's correct so 1008 i think we did that here payments this one's got a payment so that's good we can offer the refund on this one let's enter something incorrect make sure that everything's okay enter correct okay that's good we can enter a refund amount let's say for ten dollars okay clicking okay and now we see the payment amount is ten dollars we see that the status has changed to refund and okay that's working good now i want to be able to void the order but we can't allow them to avoid or if they don't have the right so that's the first thing we want to make sure of so we can do this copy that to make sure that they have gonna copy this here if they're not if they don't have that so can't void an order if they don't have the properties if it's not manager please check your manager to offer the right to to void orders instead void orders okay so that's important so we need to do that of course we're going to be with the pos so let's do with pos on this one and then we can remove the pos here and then we're going to bring that with inside the end width so here bringing it inside there okay so what else do we want to do when we void an order well it's relatively easy all we do is just need to make sure to clear out some items there so how are we going to do that well the first thing we need to do is determine the last row what is the last row so the last row is going to be equal to i want to clear out all the quantities it's going to be based on column i if we want to make sure that we can't avoid an order if there's no items if the last row is less than 7 then what are we going to do then just exit the sub can't if there's no items we can't void an order without items okay so what i want to do is i want to clear the contents of this i want to make sure that we can't really void orders if it hasn't been previously saved right avoiding orders only means previously saved if it's not saved so how do we know if the order data is b3 is empty that means it hasn't been saved yet so we don't need to do anything we can't void an order that has been saved so let's make sure that b3 contains a value if dot range b3 is empty b3 equals empty then i'm going to do message box only saved orders can be pointed and just exit the sub exit sub assuming that we do have a previously saved order we can then move on so what i want to do is basically just clear out the quantities i want to clear once i clear it basically if i do something like this right it's going to clear everything out so that's what i want so how do we do that well let's just first of all let's reload this order so we can get it back to where it was so that's basically what i want to do so we can do that in with just a few lines of code so we've got the last order so i want to clear everything else out now and clear the cons so dot range b16 i want to clear that payment amount because we're avoiding that order b16 and also want to do all everything through k k 7 through k in the last row through k in the last row and the last row in this case clearing those contents out dot clear contents b16 meaning we need to do clear the payments so now what we want to do is we need to set the status so that status the the status is going to be called a void status and that status is going to be inside k10 i believe for the admin so we're going to paste that in there k10 is our void status void all right so that's it so let's take a look at that k10 is correct and let's save our work and then it looks good so now all we need to do is just copy that and of course if they decide they don't want to save it once they voided it they don't have to do that so right click here sign the macro here pasting that in click ok so if they want to avoid it here it's voided now i like this if they click next then it's saved if they don't they because if they click this by accident it's not saved yet so that's kind of a good thing right so only if they don't commit to that so if i reload it it's going to reload only if they save it it's going to be saved in the void status okay good so we've got that now all we need to do is just print that's the only thing we have and then i'm going to set you up on our quantity on hand formula we'll do a double check and i think that's going to be it okay so let's set up that print here order print so this is going to be really easy with pos dot shapes first thing what i want to do is the selected if i don't want this shape to be visible if this shape selected item group is visible i don't want it so we'll just clear just going to hide that so i'm going to copy that to make sure that it's not visible this shape here just hide it dot visible equals mso falls this was definitely the longest training i have done i hope you enjoyed it please make sure to comment subscribe and don't forget to like this video that really helps us so first thing what i want to do is clear the contents of v6 i want to clear any selector notice that we've got a the highlighted row that's based on what's located in b6 so as soon as i clear it that conditional formatting is going to be gone so dot range b6 clearing those contents out b6 actually i think i have a auto hotkey on that dot cc something like that dot or clear oh there it is b6 clear contents okay so we're clearing clear selected row once we have that we want to set up the page i want to set up that printer dot page setup and then what's the print area i want to set that print area to be equal to dot range we know it's going to start in i6 because we want that header to be located i six it's going to go all the way to oh column o right we want to include the totals but we don't we want to include this area here but i don't want to include of course our quantity on hand so it's going to go all the way to o in the last row well what is that last row and the lesser is base located based on i so and going to be based on located on i in the last row the last row look at i but then not just i right it's not just i don't want the last row i need more than that i need to include the footer how many rows are we going to include i plus what here's the last plus what plus i need to go down at least six rows to include that footer so plus six so we need to add six to the last row plus six and it's not going to be dot value it's gonna be the address so dot address dot address so that's the address so now we're ready to print out once i've set that printer we're ready to print out dot print out and what are we printing out i want to print out we don't need to set the from in two copies preview we don't active printer will set that to true then what do we want to do then we'll also want ignore the print areas and that's going to be false so that's going to put it to the active printer in case mine is snagit but whatever yours is the active printer okay so it is this macro that we're going to assign to that button and the icon right here so it's this print button here and this icon here okay so assigning the macro here pasting that in click ok so now it's going to print the order if i click here it's going to print that order out to my snagit printer and we'll take a look at that okay i like that that looks pretty good everything's nice notice that we have the barcode we've got the discount everything looks pretty good i like that everything's lined up we've got the totals down there looking nice okay good so we've got the print working just fine let's get these quantity on hand i want to have a formula that's going to get the quantities on hand basically what is it the quantity on hand is simply everything we've purchased minus everything we've sold so it's relatively simple all we need to do is sum if and based on that now i have some it's again all of our order items everything we've purchased and everything we sold is in here it's going to be based on the purchase so everything we've purchased minus everything we've sold so we've got some name ranges that are going to help us so what is that it's going to be the order items order items here order items based on the type and based on this sku right i need to know everything purchased by the type minus everything sold by the type so it's relatively simple we're just going to use sum if so i'm going to bring this formula all the way down here so inside the orders pos here so what is the quantity on hand so first of all we need to make sure that i contains a value i and whatever row so equals if in this case i7 we're going to bring it all the way down equals empty then just show empty right i don't want to show otherwise we're going to use the sumifs sum ifs what are the conditions here well first of all the sum range what are we going to do i want to see the order quantity i want to know those order quantities so let's give the order items and then quantity so that's what we're summing and what are we basing it on again i want to base it on all the purchase so the type order items type is going to be equal to all the purchases it's got to be equal to purchase that criteria is going to be purchased and then what else is the criteria well of course the sku has to be the one that we're focused on so it's going to be the order items q is going to be equal to what whatever is located right here in i7 and then we're going to bring it down so that's going to get us our total just our total purchases so that's covered there so let's update that okay so we've got we've purchased total of eight but that's but now i need to subtract out how many we've sold right so total so all i need to do is then copy this here all the sumifs use the subtraction here minus paste that in here of course that's not going to be based on purchases in this case we're going to set that to whatever has been sold so in case this case is going to be sale so now we know so now we've sold one of these we've purchased eight so the quantity on hand is seven so all i need to do is just copy this formula and let's bring it down let's just say to p 1000 or whatever a lot a lot of rows that's going to give us enough enough to for okay that's too much but paste those formulas in here now we have everything here so now as we add those items obviously we can't have negatives but if we've sold more but that's going to keep automatically our items our quantity on hand just like that with that now we just need to center that and so we've got a nice little row to let us know exactly how many we have on hand okay great two small macros to go just to log in log out i want to be able to log in log out that's all we need to do very very easy okay cool so let's write those two small macros right now that's going to be on our pos macros here if we scroll down here we've got log out and log in to do so first of all the log out is going to be simple all we need to do is with let's do with pos and then dot range in this case i want to clear out some fields b9 and then i want to clear out what's in b9 well that is located our user id so i want to remove that user id right here removing that once i delete that it's going to clear out all the database row and the role so we can do that very easily just with this line it could b9 gonna clear that out and also what i want to do is l3 through m3 what is that that's the user here i want to clear out this user l3 through m3 on log out so l3 through m3 if you're sticking with us this long i really appreciate you you're only going to get these massive trainings right here on excel for freelancers channel clear comments okay so we're not clear comments clear contents so once we've cleared the contents out what i want to do is i want to make sure that they cannot save any order how do i do that i want to make sure they if they're logged out they can't the best way to do it is just to hide this footer group if this is hidden they can't do anything right they can't save void they can't do anything without when this footer's hidden because it's those are all the important buttons so we can do that dot shapes footer group dot visible equals mso false hide the footer grip hide footer group of course when you protect it they won't be able to show it okay so once we've hit it i also want to change the login button so dot shapes i want to put the login button that's the name.visible equals true equals mso true show the login button and hide the logout button so dot shapes log out button we don't need we don't need to show that anymore log out button dot visible equals i'm also false okay so now we're hiding the logout button so that's pretty much it now now what about the login login all we need to do is to check to make sure that we're going to just use again a simple little user probably just not we don't even need to put in a form and we'll just put in just input boxes should be sufficient okay so dimension the user id just wanna make sure they have the right user id as long okay and then all again on air resume next i want to make sure user id we're gonna put a simple input box equals input box what is that going to be about please enter your user id capitalize that user id and then we'll just give it a title user enter user id user id kind of obvious there okay so and then what we're going to do is we're going to put on and go to zero just in case there's an issue with the user id so we want to let them know now what i want to do is i want to place that user id inside b9 so bos.range b9 dot value equals user id so now what if they enter something incorrect here inside here we know that the user database b10 is going to be blank so if b10 is blank we know they've entered incorrect user id so we can judge that just by use based on whatever's in b10 if pos dot range b10 dot value equals empty then let them know message box please enter a correct user id exit sub sim okay so there's nothing we can do if they did if they did enter the correct id then we just need to make updates so the first thing what i want to do is up put their username i want to put that directly inside l3 where is that going to come from what's going to come from their database row here inside the users users b column b and the database row so we can just write that code right here so pos dot range l3 dot value is going to be equal to users dot range b and where's the database row the database row is located inside b10 right here we have it here so just copy that that's the row dot value username add in that username inside l3 once we've done that we just need to update these and reverse these the footer group we want to show the login button so i'm just going to reverse everything on here so this case c true we're going to show the footer group let's not hide it we're going to show that and then this is going to be false and the next one's going to be true we want to now show the log out button and show the login button so c true okay we'll take a look at that signing those macros saving our work then this login what i'm going to do is assign this to the macro here assign the macro and it's going to be the user login so we're going to go all the way down here to user login click ok we'll log them in even though they're already logged in update this we need i guess we didn't put in the wish sheets that's fine so here and here and here okay update that okay enter the user id put in one okay now they're logged in right everything's logged in good now this logout button what i'm going to do is assign this to the macro that's going to be the user log out all the way down here click ok let them log out please enter okay we don't next log out log out it's going to have to be assigned the macro all the way down here law user logout and that's the right macro log out good so now you see they can't do anything we could probably clear you know what would be good is it would be to clear the current sales so let me add the clear to the log out because i think that would look nice so that's the clear sale here purchased let's go into the order i want to clear the current order out so all the way up here is the one we had declared this one i'm going to add this to log out so it clears the current order i think that would look a little bit better so back into the pos all the way down here inside log out here and then just run that macro to clear the order so good i like that so let's try it in log in logging in here one adding up logging out clears the order that looks really nice all right i think we're good now the only other thing that i'll probably gonna put inside the download version is to be able ability to add a customer or add a vendor and then sort that okay so i'll add those macros in we didn't have a chance because this is already an epic incredible marathon training let's review we're gonna log in with our user here logging into user we're able to clear a specific row we're able to add brand new items just by some collect clicking on them adding additional items we can increase or decrease items as we see fit we can also add items to either scan or something or putting them in here we can add in a customer here we can change it to a purchase or back to a sale here we can then offer to pay for that just by putting in a payment amount clicking ok we can then save it next our orders and we can also print them we can also search for orders and then print them out or offer refunds and do a whole lot and pay for them all right it's been an incredible training thank you so much this has been one marathon my longest training thank you so much if you do like this content don't forget to subscribe click the like button and please comment below i love your suggestions thank you so much for sticking with me on this incredible training we'll see you next week for a brand new training don't forget if you'd like this workbook with 200 of my best workbooks i've got them on sale just 77 brand new if you like this go ahead and click the link below and we'll get that sent over to you just 77 if you did make a previous purchase check your email we'll be sending updates for that for a coupon so you can upgrade that all right thank you so much we'll see you next week you

Channel: Excel For Freelancers

Views: 469,475

Rating: 4.7144322 out of 5

Keywords: Excel Point Of Sale, Excel POS Application, Excel Point Of Sale Application, POS APplication, Excel POS App, POS application in Excel, Point of Sale application in Excel, Excel App POS, How To Make A POS Application, Create a POS Application in Excel, Learn POS Application, Point Of Sale Application, Point OF Sale App, Free Point Of Sale, Free POS Application, Free Point Of Sale Application, Excel Free POS Applicaiton, Excel Invntory, Inventory In Excel, Excel Purchase Order

Id: SbjBgqzbA3w

Length: 233min 25sec (14005 seconds)

Published: Tue Jul 13 2021