Create This Restaurant Touchscreen Point Of Sale POS Application In Excel Today [FULL DOWNLOAD]

hello this is Randy with Excel for freelancers and welcome to the restaurant touchscreen point-of-sale application in this week's unique training we're gonna create a complete application from scratch starting with a blank screen and creating a fully dynamic touchscreen application complete with pictures for food restaurants a pop-up keyboard categories for food and a dynamic receipt that can be print to the kitchen or any local printer it's gonna be an amazing training you won't want to miss so let's get started all right thanks so much for joining me today I've got a ton to show you because for the first time ever we are gonna be creating a complete touchscreen application that is fully customisable fully dynamic and we're gonna base it on a restaurant but even if you don't want to create a restaurant touchscreen point-of-sale application you're gonna learn so many techniques tricks and tips in this training that it's gonna be extremely beneficial for you no matter what application or what you want to create because we've got a ton to show you I create these videos each and every Tuesday for you so that you can create amazing applications on your own get those freelance jobs or create applications for sales so if you like too I'd appreciate you subscribing and that's gonna help you get alerted to minding videos each and every week just go ahead and click that notification icon Bell down below and it really helps me in the YouTube algorithm if you click like and comment below so I really appreciate that too we've got so much I want to get started right away on this what we're gonna be doing is I'm gonna be saving these first two columns that's gonna be for admin so I'm gonna color those in gray right away I want to get started right away and basically what we're gonna do is I'm just gonna cut everything just give it a nice blue background for a while so that'll cover it at least for the basics and then what we'll do is we'll customize it according so I'm gonna give it this color here and so basically I want to have a logo around here and then some buttons at the top and then a receipt down that'll be colored white something like that so how do we do that well let's create just some standard buttons first so we can get an idea so I'm gonna have a top bar menu here and I also want a category menu down here based on button so when you click a category all the pictures show up here and then we can add the receipt in here something like that so let's do that I'm just gonna go in for some basic buttons now just a basic shape and we'll create some header buttons first and let's set the size on those may be it right around four point four three on the height and something like around one point six six on the width that's about the style let me give it a shape color something like I'll just give it a flat color something a little bit darker and contrasting on here I'll go like this color here that's gonna be a nice contrasting color and then I want to give it a thought so let's create put some text in that button so I'll call it new dine-in so what I want to do is I want to get at least the format for this shape before we move forward and create more shapes so you want to make sure everything is the same and I'll put that in the center I guess and make it bold so it kind of stands out so that's about what I want and a little bit higher on the font that's it something like that it's gonna be good I'll probably reduce the zoom on this so we can see more so new diamond then I want to put an icon on the right so that's gonna be fine and I'm gonna duplicate that button multiple times so that we can get our header button so I'm gonna hit control D control deacons LD and now on about seven buttons here at least so I'm gonna place them across the top in fact we're gonna gonna extend this all the way to column V actually because I want to I want to go we have a lot more to cover so let's extend that to call them a few more columns over so we can see it there we go call it via will cut cover it and now we have our basic format and so what I want to do is I want to put new orders I'm gonna give it three new types of orders so we're gonna have dine in we're gonna have takeaway and delivery I'm gonna put those up on here right around about here so starting right about here and then I want to have done new dine in and this will call this take away so when we new take away so we can have three different types of orders here and then I want to have one called new delivery so that we can have delivery delivery okay so that's good we're gonna have some icons to on that and then I want one for a keyboard I want to put a pop-up keyboard here so we'll keep that one blinking I'm gonna minimize that because that's gonna be just a small icon here that's about what I want there and let's line those up just so we can get an idea I'm gonna format align the middles and then I'm gonna distribute them vertically or horizontally actually so okay so what else what are the buttons do we want I want to be able to save an order that's obviously very important so let's put that here called will call this save order and that button can be a little bit smaller on that one I'll put that one about 1.35 on that one it's a little bit smaller because we have less room and then I also of course we want to actually send the order I want to send it to the kitchen or the local printer send order and then lastly I also want a payment we need to mark up when a customer pay so let's call this payment okay so payment so if save order send order and payment so we've got those write down let's create these a little bit of smaller these I'm gonna put this one send order at about one point a little bit less than that because it's not we don't have that much text on that and the payment is gonna be a smaller button something around 1.35 okay so these are gonna go above the receipt here so I'm gonna put those let's slide over a little bit I'm gonna put these above the receipt and so put those about right here and then this and then this okay that'll look good that's about what I want I'm going to line those in the middle and then I'm gonna distribute them again horizontally that's about what I want this is going to be for our printer so this can be a little bit smaller okay good so we have our top buds but what about our buttons on the Left what I want to do is I want to create some categories and what are those categories we're gonna have about let's say seven categories and I want those here but I want those buttons a little bit smaller on those categories so we'll put those about maybe 1.5 on that a little bit smaller so 1.5 and this is where my Couture is gonna be so I'm gonna duplicate that and I want them to go down here basically what I want is I want to my category so when you select a category the pictures show up here so we're gonna have seven of them so we got two more to do so let's do that and so alright that's good now let's stretch them below and about here so we can spread them out accordingly here that'll look nice okay so we have that and I'm gonna put something like categories here so we know that it's a category I'll start this error so here's gonna wear a category so I'm gonna merge and center this I'm going to call it this white and we're gonna call this category so we know exactly what type of category those are and then I'm going to Center it and then increase the font make it bold and I'll put just so we can clearly see I don't put some borders around it something nice something a little bit color so we can see using the same color and then I'll use double line all the way around it okay so what I want is I want to have my categories let's pull this up a little bit so here we get an idea of the full screen width and that's gonna be good I'll bring it up a little bit so we're gonna have seven categories and I think that's seven okay and then I'm gonna line them on the left and then of course distribute them vertically here and then just vertically okay that's gonna be nice for our categories but I also want some icons on here and now we're just about ready to import the icons so what I want to do is I want to line these up with this category here so just like lined up here and bring it down here okay I like that that's pretty good nice and separated here it's enough as we bring the screen up you see it's we've got everything covered and separated let's type in what categories do we want well I'm gonna want to have pizza pizza I love pizza so we got to put that in just so we know I have put some additional data here which is gonna help us so I've got menu items we've got menu main and we got a picture that's gonna attach we've got a description we've got item number and item ID so all those are gonna help us out fill that and we have categories so our categories are mains desserts starters drinks salad pizza and pasta so we've got a lot of great food here it's gonna make us hungry this training I'm sure and we're gonna learn a lot so we'll go get a good meal after this all right in the admin section I've got filled it's just a list some pre list and a list of printers that's gonna help us because we need to assign a specific printer for the machine that we're working on in the kitchen so just to help us move along if out of those and I'll go over that with you so no problem and menu items and then we want to track orders so I'm gonna have a list of orders here all the orders that we have and then each individual orders gonna contain a list of items right so we need to track each order number and then the list of items that we have on that alright so let's get started on that also what I want to do is I want to have a default picture so I'm gonna have the picture appear here and I want to have a default description so let's duplicate that and create a little small thing I'll just call this default description with that is gonna do is gonna put a description right below the picture and so we can make that smaller that's gonna in fact I want that pretty much the same width as this column so what I'm gonna do is I'm gonna create five different columns of pictures one here two here so notice we have one we have five term columns I set up the columns to help us a little bit quicker move along cuz it's already gonna be a long training so I want this about the same width as the column so let's put that at about one point four on the format right about that one point okay so that's gonna be good for our default description and then about something about point four on that a little bit less point four on the top so what that's gonna do is we're gonna create shapes as a default description you'll see as it comes to life how this default description the font is a little bit too big on this because we have some more detailed so let's go down to maybe eleven or even ten a little bit less because we have some higher descriptions so we want to make sure all the description of the item fits in so we're gonna use this shape as the default description so each picture and then below it will have a description so I'll show you that how we're going to do that we've got that let's put in our now our icons and I've got some icon saved up and in fact I'm gonna make sure you get all of these icons in a pack it's gonna be a zip pack download if you want to download this for free just go ahead and click the links down below if you want over 150 of my applications I now have that available it's just 56 dollars I still have the 100 for $37 but the 50 includes all of my latest applications so I want to make sure you get that I'm gonna put in all these icons bring right in the sheet and of course the size is gonna be way too big so let's set that down to read around point two three all right so that's gonna set them up so let's see how we're gonna do that first I've got a let's let's pull this out this is my application I comes well let's bring that up that's my logo restaurant point-of-sale logo so we put that in nice and I'll put a little bit of shadow on behind that just so you can see it kind of stands out a little bit okay so what do we have here we have drinks so I'm gonna put that down here let's put in our categories here we have pizza we have salads so we know where to put the icons what else do we have of course we have starters right that's gonna start off our meal we need to know that starters and we've got of course pasta I want to put that in main courses I think I want everything made of course I'm gonna order a lot so I hope I brought enough money I also want to have drinks and of course it wouldn't be complete without dessert drinks and desserts desserts are my favorite part so I'll probably order a lot these arts let me check to make sure I spelled everything right because it's kind of embarrassing when I misspell things and I don't discover it for an hour okay that looks good pizza salads starters pasta main courses and drinks now if you want to get these icons if you want more of these icons a little bit later I'm gonna show you how you can get tons of free icons if you don't see what you like here I'll make sure to give you a link so we can do that make sure to get it this is going to be the edit and this is going to be how we delete items in the menu so we're gonna put that over here that's why there are a different color desserts this looks like a proper dessert and ice cream we also want to have let's see this is going to be dying in icon so I'm gonna put that up here this is going to be our keyboard icon we've got a pop-up that's gonna need to be a little bit bigger here pop-up keyboard really cool and to show you a macro to do that that's a macro that it's been already created cuz it's a long macro so I'll just that's something I downloaded off the internet so everyone's have all new delivery we need more spacing on that one so let's put some spaces on that here so we have more space for the icon and we have a take away what do we got to take away it looks like a bag that's gonna be for our takeaway again need a little bit more space on here just add some spaces on here so everything's okay see our screens starting to come out and we have a default picture that's gonna help us out if for some reason you don't have a picture for a food we want to put something in place of that and we're gonna use that okay this is gonna be for our send order because I need to drop down list so if we're gonna send it to the kitchen or the local printer so we're gonna have a pop up there we're gonna create two more shapes this is going to be for our pop-up and this is going to be for our printer so in other words if we're gonna send to a kitchen I want to create an icon I need to create two more shapes I'm gonna duplicate this here and I'm going to put send send to kitchen when you send the order to the kitchen so they can make our food and we also want another one called send to local okay because that's a local printer right and let's say you have a machine printer and let's bring these two icons we created them before these shapes so I need to bring them to the top format bring to the front that's gonna bring them and then what I want to do is I'm going to drop this down drop this down a little bit I'm gonna put this icon right up here and of course we're gonna group everything in this one group and then we just have to makes a little bit more spaces for the icon here so we'll move that over here a little bit okay alright that's good that we have that sending to the kitchen and send to the local bring it over a little bit okay so we've got our kitchen so basically what I want to happen is I want to automatically when we send it to the local I want to dis drop-down list and send it to the kitchen so we can print automatically that's gonna be a really cool pop-up group so we're gonna bring those up here send to local and send to the kitchen bring it up here I'll group those in a moment let's continue on with our salads so we've got a salad icon here let's continue with our starters to start we got some bread I like bread so let's bring that here and do the starters that's a common starter bread pasta even have a pasta icon here I love pasta I'm already getting hungry okay send two this is going to be here and we have drinks here so we've got our little icons and we've got our main courses here we're gonna have to move that a little bit too because that's kind of a larger name and then payment our payment icon here so it's starting to come together and we got a new order and this one save order I don't think we need this this icon we don't need all right so we're good to go we have everything here now starting to come together so let's line it up and make sure that we have everything we're gonna use something called application call and what that's gonna do is when we click pizza I want a pizza to go in the category so the best way to do that is to name these individual shapes before I group them because when I click on it I want the word pizza to show up in side our menu what I want to do is I want to run an advanced filter so on so I want to know only the pizza categories and that pizza category is gonna go right here into k3 so what I need to do is I need to name each individual shape and preferably before we group them so let's do that so what I'm gonna do is on to bring out the shapes now if I try this watch what happens I want to rename this pizza which is correct but I also want to name the icon but if I try that yeah it's gonna refer right back to the shape notice that because I've done it so what's the best way to do that is to use this selection paint here and then click on the icon here and make sure that we named that icon we located here and named that pizzas well I want them both to have the same name generally we don't but I want them both both pizza because that's whether user clicks on this or this we need to send the name of that shape to right here I want to send the name of the shape right here into k3 so to do that we want to make sure they're both have the same name we're gonna do the same thing with everything so I'm gonna do that right now and and then I'll come right back cuz you don't need to watch me name each one of those other that's kind of boring for you okay if we click on here and we see we've now named everything and what I want to do now is group them so I'm gonna have my selection tool here and then I'm just gonna start grouping each one I want to make sure that they're grouped in together so I'm just gonna highlight or you could also call to control and click each one of them so each one is gonna be group now naming the group isn't critical but it's always helpful to name it so that when you look on your shapes you can easily see which is which and so what we'll do is we'll do that since I all love to develop proper applications I'm gonna name each group and I'm just gonna call this in this case Pizza Group however it's not as critical as the individual names but it's nice when you look at that you know it now here's the critical thing the most important thing is each part that's the icon and the shape inside must be named the exact name as the categories here pizza if there's any misspelling because it's that name that's gonna help us with our advanced filter so I would want to make sure that's what and of course this will just call this salad group and then I'll go ahead and do the rest okay now each group is independently named so that's gonna help us as we recognize what shapes or what okay so now let's group in these now we also want to have art the same thing here the same idea I want to have every name that represents a diamond takeaway or new delivery same exact thing so I want to do that diet because that's gonna help us move in exactly as we want it inside the category and the same thing for our shape here new takeaway and Google's call this to take away and I want this called delivery that way we can send that directly to the order the name is gonna go to the order delivery in fact the same macro is for all seven of these same exact macro it's gonna be really easy we're gonna call that load okay so let me update the icons also remember not just the shapes but the icons have to have the same name as well so we're gonna call that dine-in and then this macro we're gonna call it takeaway it's okay it's gonna have a space that's no problem just locate that on our shapes and call that take away and the same thing for delivery I want that delivery icon also so we'll just go located the highlighted one and then we'll just change it to delivery okay once we group them also going to group them here using the control is another way and then just group it here holding the control down clicking the group and doing the same thing here control and then grouping okay all right and again we can name these two if we want to but the groups is not in this critical as the individual shape so we want to make sure let's just double check that we have the same names dining notice this one is not done so we make sure we do that as the same dying in making sure it's exactly the same we'll take a look at the take away make sure that one has the same names on both the icon and the shape and also on the delivery here let's take a look at that one we can confirm that that one has the same name okay good so we're done with that and now let's take a look what I want to do now it's gonna have some fun let's load up those pictures we're gonna come with this a little bit later on so what do I want to do with this I want to have the order here let's color the lines that we want to show so we know what's gonna be going on basically on let's let's start out at around p5 and go all the way to you and let's just highlight those let's put that in white just for now just so we can recognize exactly what we're going to be using as a background in here I want to know the table and let's move these we're not going to use these just yet so let's move this group in fact let's create that bring it up a little bit so we know this place inked and I'm gonna bring it up and I'm gonna call that I'm gonna call that send group send to group so I'm gonna click both of them and then what I'm gonna group them and then call it send to group so that way we can hide or show this send to grip and I'll just put it over here for now but so I want to attract I want to know what table I want to know when it was ordered on ordered on I also want to know the order number we can just put a number cuz that's a small area and the type the type is going to be delivery take away or dine in and I also want to know the staff or the server and then now let's move it over a little bit here so you can see it and we can merge and center this one because we have a little bit larger area and I'm gonna drop this down for awhile and then let's Center Center them in there and make them bold and let's put some formatting around that because I want that to be at the top of the order so I'm going to choose this color which is our accent color and just go all the way around for now and so basically what I want to have is the information here and then I'm I have another header here this one would be product I want to know what product and then this would be quantity what's the quantity how many and then the price so we could just put the price or each either way and then total here okay so again the same thing here I want to do here then we're going to make it in the middle and make it bold and that looks good and then just put some borders around it format the cells and I can just copy the formats right here and we'll just place the format's right here so I'm gonna paste those formats pay special and then paste the format's that's another way of doing it okay so we have the same format I'll merge the center this one that's what I want let's make those a little bit bigger because it's a little bit small on those so we'll go to 12 on that font and we can clearly see it and then I'm gonna give this just a light blue color so it's a contrasting color something about right here okay so again what I want here is I want to place the information in here I'm gonna merge and center this one this is gonna be our staff merge and center and then I guess I can left justified now I've created a named range let's take a look at some of those as we fill it in but before I do that I just put some borders around here so it's clear format those cells I'm gonna make the top line dotted so we know and then the center is going to be bounced just do it double I like the double line on that there I'm not borders okay so basically what I have will start out with that so what I'm going to do is I'm gonna put the staff here the type here and as we click the delivery the new order is gonna be put in the type I think that's gonna really be half our first products gonna go right here and if there's any details and then I'm gonna put this here and this here so we can edit the item or we can remove the item and then as we select it's gonna appear here so that's gonna be a really nice feature okay so let's give this a little bit of a color as we start to build out this is our receipt this will be printed to the kitchen or perhaps printed to the local printer now I have a created a named range with staff and a server let's take a look at that under the formulas name manager and it's a dynamic named range so when I click staff and tab over we're gonna see that it's offset so that as we add name it's gonna build out this list offset using the admin that's in B for starting it know what we're gonna do is we're gonna count in this case it's four but as we Adam we're gonna use count a so we're counting every single cell with a value between B 4 and B 23 and what that's gonna do in that case it's 5 so we're gonna use offset that means our range is going to be from b4 in this case to b7 so as we add staff it's going to grow and I can take that named range and I can use a data validation right here to use a drop-down list so when we click data and then data validation and then all I need to do is click list here and then just equals staff and that's gonna put our staff right here okay great so now when you have a drop-down list of staff which is nice that's the way I want it and what else do we want to do I want to make sure to put a table and the products are gonna go here the detail the quantity good so that's we got that we've got that done as we build out our receipt that's gonna go here we'll use more conditional formatting in the future but I'll show you how that works what I'd like to do right now is load some of the pictures in and see how we can load it cuz that's kind of really exciting I want to have roast we're gonna have 5 rows 1 2 3 4 5 so actually 5 columns and then the rows are gonna go down here so maybe we'll start out in row let's say Row 4 the pictures are gonna start in before and then we'll skip and then maybe 9 they then our second row is gonna start at 9 and then we'll skip another five and maybe 14 that should be sufficient so as we build out we have a lot of rows so let's build a Macker so what I want to do basically is I wanna when user clicks on a category here I'm gonna assign a macro and that macro is gonna load all the pictures associated with that category we have pictures in fact I've got a lot of pictures in a folder let's just take a look at that so just we see what pictures we have I've got them right here into our folder so there's food that I've got tons and tons of pictures here and that's going to help us those are the same pictures that I've linked in our database so when we look in our menu items here we see that everything has a picture file pound those are linked to all the pictures I'm gonna include those pictures with you so you can experiment make sure you build out your own file path this would only work for me so you want to use your own file path when you're testing these I'll include those the icons the picture is the application everything for free all for tons and tons of value because I want to make sure you're learning and I appreciate all your help of course all right so the pictures are gonna be there but let's load those pictures in so what I want to do is I want to take that category I want to determine the last row in this case the last row is 59 I'm gonna run it through an advanced filter and that advanced filter is gonna be right here in k2 so I'm going to put the category here then all the results are gonna be here then I'm gonna loop through all the results and I'm gonna take the pictures and I'm gonna take the name and I want to displayed in a picture and in there so I want the picture displayed in a specific size and I want the name displayed in a shape here so we're gonna write a macro to do just that so let's get on that right now and that's gonna be call that category load so let's go into the VBA here if you don't have your VBA already you can of course get to it from the developer and the Visual Basic here otherwise you can also click file and options if you don't see that available and click on the customize we're going to make sure you have developers selected here there's also a shortcut to get doing the VBA and that's alt F 11 alt F 11 will get you there so it just simply alt and then F 11 will get you right into there now I've created a few modules but there's nothing in here except for application macros and we're gonna go over that that's the only thing that we're not going to be writing everything else is simply blank modules okay this is gonna help us this kind of keep us on the straight and narrow save a little bit of time these trainings are already long enough so I try to do as much as I can in advance and still make sure you understand the full context of the training so right now we're gonna call this category load I want to load those categories on our purchase order screen so that is demócrata we're gonna create right now we're gonna call that category load category so sub load category and I need to dimension a bunch of variables so let's start with that we're gonna dimension the category I need to know that category is a string that's going to be helpful demand category as string and I also want to know the picture path that's the path of that that's going to be a string as well as string and I also want to know the picture path half of that picture as also string and what else do I want to know I want to know the item ID the item ID is a string as a string we have the item IDs already set up just want to let you know what we have my item IDs that's located right here in the first column it's called item ID this is our item ID they're all basically string and it goes all the way down there automated so feel free to add to those if you'd like okay continuing on what else do I need now that I've got all my string variables I want to know the menu item we're gonna need to create a brand new menu item and we want to delete any one so we're gonna need to dimension the menu item as a shape dimension menu item as a shape okay and also I want to make sure that we demand we need to run through the columns I need to keep track of what column in row as we're adding shapes I want to know what column we're on and what row we're on so that's really important so we're gonna need to dimension the column and the row so dimension the menu column as long cuz it's a whole number and also the menu row menu row as log that's important and I also wonder the last item remember we're running an advanced filter so we need to know the last item row as long as well okay what else I want to know the last result row remember our results our category we're going to run through all the categories and we need to know what the results are so I need to not only know what the last results of our row here in fact that's a it would look like this something like this here's our results I need to know the last row and I need to run through our categories all the way our result rows starting from 3 all the way the last one as we build and out so we need to run through that as well so we need to make those as long - so that means the last result row as long and also I need to loop through the result row so the result row has okay great that's it for our dimensions and we're gonna be focused primarily on sheet1 so let's start that out with with sheet one okay what do we want to do the first thing the first thing I want to do I want to make sure that I understand what the category is the category is equal to the application dot collar now keep this in mind if you try to run this macro like foyou if I try to run this macro here it's gonna have an error and why is that because you didn't call anything remember this means whatever shape you clicked whatever the name of the shape you click we didn't click a shape right we just tried to run it from here so keep that in mind this only works when you actually click a shape this application color is whatever the name of the shape that you click so notice it's an error but as soon as you click a shape it's gonna keep that in mind so we remember this is only going to know so if we assign this if we assign it to a shape here the name of the shape not the name of the group not the name of the group we can only assign macros even if I assign a macro to this group but that's gonna do is it's gonna really assign a macro to this shape and this shape so notice both of those shapes has the same name so that is our application color or application colors pizza here or pizza here so only when we assign that macro to this shape can doesn't work so keep that in mind it won't work if you try to run it from the curd okay so this is our category name and also what else do we need I also want to do make sure that we have I want to put that category in k3 that's our criteria remember if we look inside our sheet 2 let's take a look at that I need to run it so k3 is where we're gonna put our criteria then we can run our advanced filter so let's do that now sheet 2 dot range k3 dot value equals category all right that's gonna place our category name and so what I want to do now is then I'm gonna delete all the shapes from our sheet there's if there was already a category loaded here if there's already something else here I really need to remove everything here but obviously what I need to do is I need to remove all of the shapes only I can't remove every shape on the sheet I can only remove shapes that are here in this area which is basically all the previous categories so how do we do that well what we do is we name these shapes give them very specific names that are very different than any of these names here so that way we only delete shapes with a specific name so how can we do that so we can run first thing we want to do is I want to run a loop for every single shape in decide that specific sheet so we can use a loop so let's do something like this for each remember we've defined menu items of shapes so we can do that for each menu item in dot shapes that means every shape in the sheet and make sure you close your loop always so next menu item okay so now we know so now we're going to loop through all the shapes the first thing I want to do is what I'm going to do is I'm going to make sure that my shapes include the name item item make sure nothing else has the word item in it only our shapes you can include the write up so that's gonna be our differentiating factor our shapes are gonna clewd the work item so how can we do that if we got a check if the name in string menu item dot name and then we're looking for the work item item is greater than zero that means it's found then what do we want to do then delete it then menu item dot delete it's gonna delete the shape because it contains the word item you want to make sure when you use differentiating that no other shape on your sheet includes the word item because that's gonna be the difference okay so what we gonna do is when we create these shapes we're gonna make sure that item is included in fact what I'm gonna do is I'm gonna call them by their item ID item1 item2 that is how I know which ones to add so let's do that now let okay now that we've deleted all the shapes on the item now we're ready to run our advanced filter so what I need to do for our advanced filter on sheet 2 which is where all I had I need to determine the last row so what is that we've already defined it as long last item roads equal to and that's an automated using autohotkey okay it's a free software that's gonna help me automate so we're gonna do is we're gonna look for the last row on sheet 2 column a and that's gonna give me that's the last item row last item row ok so the first thing I wanna do is I want to make sure I don't want to run an advanced filter if there's no data if so if this for our last item row in this case it's 59 but if it's less than 3 then we need to skip everything out otherwise it's gonna create a bug so to do that we can just run it a little bit of a check if the last item row is less than 3 then go to no items and then I'm gonna put new items way down here no items with a colon and now everything is gonna get built inside here okay so assuming that the rows are great last is greater than 3 then or 3 or greater in that case we can continue on so now we can run our advanced filter sheet let's drop this down so we can take a look at sheet 2 as well ok sheet 2 dot range 2 dot range a write a 2 is worse our date to through all the way our last comma in this case is G and the last item Rho dot advanced filter we're copying it to another location and we want the criteria range colon equals to also sheet 2 dot range and remember what it was k2 and k3 k2 through k3 we must include the header as well okay let's just take a look at that right in here k2 k2 through k3 that's our criteria then ok and then we want the results in n 2 through T 2 so let's write the results right now so we would do copy to range with copying that : equals to sheet - where do we want that range we want it in n - through T - n - through T - okay we don't need dot value thing like that then the last one is unique : equals we'll put false on this one but true is fine okay so unique Google's false so that's it that's our advanced filter it's just double check that let me go through that again because it's a longer sheet - a - through G can remember when you're running these advanced filters the header rows the names must be exactly like our original data must be the same okay it's better just copy and paste the values or copy/paste everything making sure that it's exactly the same we want them to be the same so our results are going to go here next up in our code I'm going to determine the last row of our results and assuming that that rows greater than three then I can loop through all of them so let's do that right now inside our code the last result row is equal to and then X XOR I'm just gonna automate that that's my automated again sheet - but this case this time we're gonna be focused on column n right because that's our required field and I'm gonna get the last row in n now we just need to run a check if the last result Row is less than 3 remember there's no like less than 3 then go to no items it's gonna skip everything there's nothing to bring over there's nothing to loop through if we don't have any items so now we're ready to loop through now we're ready to run or loop we're gonna go again we're gonna start in Row 3 and go all the way to our last row by adding the information adding the names and adding the pictures and we're gonna be placing them in so how do we do that let's continue on okay so we've made sure that there is data and we've continued on so now we can now what I want to do is I want to set the initial menu column initial menu row I'm gonna put the column this is going to be our first place our row is going to be for our column is going to be 6 so we're gonna set those to let's set those up initially and then we're going to increase as we move on so our menu column is equal to six initial menu column it'll grow menu Row is equal to four and that's set the initial menu row okay all right so we've got that let's make that the same okay set initial meant now that we have those now we're ready to run our loop we're gonna run our loop through our results on sheet 2 right here starting in 3 going all the way down so let's let's start our loop for result row we've defined result row up here right here so we've did right here result so we have that four result row is equal to starting at 3 and going to the last result row the first thing we want to do is close our loop next result row now that we've closed our loop now we can continue on we don't want the picture path like what does the picture path is located and call them T right so picture path it's already defined as string equals sheet 2 dot range T starting with T and what and the result row dot value that is our picture path but we need to make sure it's we need to run a few checks just to make sure everything's okay so the first thing I will also want to define the item ID that's important too so let's do that right now item ID that's also the string and we can just copy and paste this here because we're gonna use nearly except our picture our item ID is located in column n Collin and is our item ID so now we let's just define that comment that out item ID so we've got our picture path we've got our item ID and I also of course need to now we're ready to go I think we're ready to go so what I want to do first is I want to check to see if we have any problems if this is empty if the picture path is empty or maybe it's incorrect like if I use this link it's gonna be incorrect for you but I want to account for that if it's an incorrect file path where it's a missing file path I want to make sure to check for both of those so let's take a look at that if the directory of the picture path VB directory it's equal to empty or the picture path so we've accounted for a being incorrect but what if it's missing or picture path equals empty then what do I want to do then I want to use a default picture right remember we set a default picture here let's take a look at that here and I want to add let's call that so let's give that a name we need to do that that's important I'm gonna call that default picture just call it default pic and then I also want to name this I want to call this default description the default description okay so that's what we're gonna use I need to what I want to do is I want to copy this and I want to place it here I don't want to copy this and I want to put the description in there so that's important so continuing on with our code so if one of those two if the picture if the path our path is incorrect or the picture path is missing then what I wonder I want to use the default picture so how do we do that dot shapes because we have an out not because we're already on sheet1 default that's the name we just gave it default pic dot what I want to do I want to duplicate it duplicate it but I want to give it a name dot name and what is it what is the name I want to give it is equal to tick and what and the item ID because that because we've already defined the item ID right here so I want to give it that distinct very distinct name so that we can remove it later on okay else what else else then the picture actually exists if the picture exists and it's a correct path then let's do that then in that case what I want to do is I want to insert the picture so again dot pictures in this case what I want to do I want to insert the picture dot insert and what I want to insert I want to insert the picture path that's the path of the picture and then I want to give it a name dot name equal to again this I'm gonna give it the same exact name picture and of course the item ID so they're both can have the same name either one's gonna have the same and that's what I want so that's always it gonna be the same all right so now we've added the picture onto our sheet so what I want now I need to work with the default description so let's we have we've created the default description so what I want to do is I want to duplicate this and then instead of the words in here I want to give it to whatever the name is in this case I'm gonna give it the name when I change the name so how do we do that in that case let's do that dot shapes default description dot duplicate we're never gonna duplicate it now dot name give it a name this time let's call it equals it's called description and the item ID description and the item ID so that's got a very distinct and unique name for each one description all right so now we've created that so what else do we want to do now I what I want to do is I want to set the text name for that this description all we've done is duplicate it right but I want to give it the name I want to put this name inside the description how can we do that let's do it here dot shapes now we've given we know the name we're working out we've given a dis name what is the name the name is this right here so we just have to copy this now we can work with that shape dot shapes item ID what I want to I want to give it the text so that's text frame two in this case dot text range it what is that text range dot text hey is equal to basically whatever is in P so sheet 2 dot range P and the result rope that's gonna give it the name of our work dot value of our product or whatever it is so let's just put that name product name okay so we've got the product name and a shape and now what I want to do is I want to position it okay so I've got the picture and I've got the description now I got a position and right we've created at this point we've created one picture and one name and we've set it all up but it's just the size and the position because I want to position it really right here and I don't want to move then I want position it here and then next and next so how do we do that so let's write that in the code here looks who's set position of shapes okay so what is that position with we've already with dot shapes and what shapes were focused on the description and the item name in this case let's do pick in the item pick we're gonna focus on a picture first and then the description pic and the item ID that's the one we're gonna focus on for a while with that I want to set that how do we do that the left position is going to be equal to sheet 1 why do I have to call it sheet 1 right now when I was didn't have to before because now we're focus on with shapes so we need to specifically call out sheet 1 dot cells why am i using dot cells because both the column and the row are based on values so if one of your cells what is the row it's the menu row what is it called menu column dot left so that's the left position of that and what about the top position top position is gonna be very much the same so dot top again let's just copy this and put dot top equals right here equals and then just put dot top okay so now we have the position let's lock the aspect ratio and put that as false lock aspect ratio equals false okay in that case cuz I don't necessarily wanna because I wanted very very specific width and very specific height and hopefully you've created pictures that have a good specific height I have okay so we're gonna give it I want to give it a very specific width I want to give it a width of exactly this column width I want to give it I want to put everything in a perfect grid so to do that I'm gonna give it the exact column width whatever the column width here that's what I want that way if you increase the column width it stays in the same grid it's a really nice feature so how do we do that dot we're is equal to what the same thing basically is it going to be the it's going to be the width of whatever this is here so all we have to do is copy this just put equals dot width so it's gonna be a width of that column the width of the shape is gonna be the width of the column pretty easily what about the height dot hi I want to set a very specific height I checked with my test I think 48 was worked perfect so 48 is pretty good and then what I wonder is I'm gonna sign a macro to it right because when I click on that shape I'm gonna add it to the menu so that means I got a sign a macro to it so how do we do that we can use dot on action so dot on action and what is the macro well I'm gonna create a macro in the future very soon actually so we're gonna call that item and then call it add to order so that's I'm gonna have to remember that make sure that I create that macro with that name item add to order that's it that's all I need to do as far as the picture so now we need to do that almost the same thing with the shapes so really with the description so let's copy this but now we're gonna focus on the description the only difference is is going to be obviously it's going to be a position slightly different it's not gonna have the same position so what I'm gonna do is I'm going to copy that but in this case we'll focus on the description so I've copied and pasted that this time we're focused on the description and what do we want to do with it in this case what I want to do is I want to put the top but obviously it can't be the same top but it has to be the lower but how much lower well exactly the height of our shape it's going to be that much lower so the top position is going to be the top position plus 48 okay so the top position of the description is going to be farther there down right below right directly below it and then the same thing I want to give it the same Macker so if they click on the description or they click on the picture the same thing is going to happen ok good I'm glad we got that done so now we've positioned it and now what I want to do is I want to take those two shapes and I want to give them a group I want to group them together so they stay together so the description and pictures should have a group so how do we do that dot shapes again what I want to do in this case dot dot reign and then I want to create a range not that range but I want to create an array for that array and what kind of a radar I want first thing I want to do is again I want to create an array based on the two names based on this name here we're gonna copy this based on this name right here based on pick and item ID right here and based on description item IDs so that's the to not value so that's one right what about the other one the other one I want to create is based on the description so all we need to do is copy and paste and this time put description so I'm going to create an array based on those two items the picture and the item ID in the description item ID and what I want to do with that I want to dot group that I'm going to group those two and I want to give it a name on that group very very specific so it's gonna be equal to item ID and the word group so they're in the word group so that's very specific very different but it also has item ID and the group great so now I've done what else do I need to do now I want to make sure to give it the same name I'm one of it's very important that we give the name of each shape the name of this shape we haven't named them yet and the name of this ship very unique name so how could we do that let's do that right now dot shapes pic and item ID dot name what is the name it's going to be the item ID equals the item ID because why that item ID remember that item ID is what's we're gonna use to be able to look up the item and give us all the information to put it right in here so that's called item ID and I wanna be the same thing for the description so we're gonna copy this and just change the from the words pic to description so that's gonna give us the same name for the picture and the description but all I need to do is just change this to the description okay so now they both have the same name name but what do I want to do now now we're done we're done with the first one but I have to loop through all them so what do I have to do I've got to move I've got to move the column over to then I got to move it over to but what if we're on the last column then I got to drop the road down by five because I want to put the first one in four and the second row is starting at nine so how can we write that up well we can do that here if the menu column we're gonna go for them we're gonna start at 6 8 12 and then 14 so that's gonna be if the menu column equals 14 then what I want to do then we need to go to the row below then menu column is going to revert back to 6 equals 6 and the menu rows gonna be plus 5 menu row we're gonna add 5 so it equals menu row plus 5 okay but what if the menu column is not 14 else in that case all I want to do is increase the menu by 2 then you call by 2 right we want to go from 6 call them column 6 to call 8 to column 10 to column 12 and the last one 14 and when it gets to 14 we're gonna drop the row from Row 4 to row 9 and continue on again that's all we're going to do on that so let's do it else the menu column equals menu call them plus 2 so that's it that's all we needed to do to write that and that's pretty much it so now we have no items here let's take a look at this is gonna be a long training so you might have to watch it in two parts all right hopefully I don't lose my breath but we'll do great I'm excited to bring this to you no more multi parts for you you guys don't watch parts 2 & 3 so that means I just gonna create really long videos lots and lots of value that's what you get all right I want to make sure you love them they won't all be very long but some of them this one I'm just so excited to bring you these amazing applications if they're gonna be long they're gonna be long ok all right moving on so now we've pretty much done everything I'm gonna save our work always save your work especially before running a macro that's critical and so now what I want to do is I'm gonna take this and I'm gonna sign that to every single button so again all I need to do is group them and then we're gonna do is we can unclick that now right click and we're gonna sign a macro we can assign the same Mac or click here low category see if you run into bugs probably few or so let's take a look probably default and Caleb's misspelled that deep alt description that's the name of the double check the best thing to do here is to reset your code right reset your code always the best thing to do is sometimes just copy and paste it I think I spelled it right now default description and then paste it right in here and ensures that you've got the absolute okay so let's continue on over the cutter again if we try to run it from here it's gonna bug out because remember we're using out call or application caller we always have to run it from our shapes here there we go we fix that bug old doesn't that look nice so you see how fast and great that works this one's a little bit this one's kind of a little bit big here isn't it a little bit big on that we gotta set the height on the art we want to set the height on that side so let's let's set the height on our description make sure that then that's been set because it's a little bit too big I want it I want the height set on our description to be pretty much basically point 4 so I'm on the set let's set the height to make sure it doesn't grow like that so we can move in under our description here let's say shapes default description this is the picture that looks okay but our description what I want to do is I want to set the height we've got the height this is too much we don't need that right that's only for our picture let's erase that now let's try it perfect that's good might be a little bit small let's take a look especially I think it's nice that looks it's a little bit too small notice we need a little bit bigger on here so let's set the height on that to something that's gonna be something that's going to automatically encompass two different rows so we need to make sure that we have enough rows first so we can tab it and make sure I spell spaghetti right alright so we've got that there let stuff take a look at that so I have to look at the misspellings so we've got pasta that got tons of great things let's take a look at spaghetti not spaghetti although it probably would be good spaghetti it spelled misspelled it twice nice okay let spaghetti I think that's right alright so we can just copy this and I don't have any specific descriptions other than what I've had there so I think we're good to go on that so it's automatically we just need to update our let's zoom in a little bit we just not take the vertical on there and the width on that so we want to make sure we set the within the vertical and put it up what I want to do is I want to format this shape a little bit and just remove the tech we don't need to extra spacing on that because we want to fit in all the text let's remove that extra text padding and I also want to get rid of the border - we don't need any line on that let's take a look at that that should help it out a little bit but I wonder there we go so now what I see notice we removed it and now we've managed to fit in everything just the way we want it there I think that looks really good okay excellent so now what I want to do is I want to click on here and I want to add that to the menu so how can we do that let's create a macro for that remember we've assigned a macro to these shapes here we've decided let's take a look at the Makah to make sure that that's been click assign macro and remember when we click on the group that's not gonna help us but when we click on the individual item and right-click and click assign macro we see item add to orders that's the macro that I want to create I want to create that what I want to do is I want to find the first available row and add that item here to our seat however if that product has already been added I want to increase the quantity so we need to run a check so how can we do that let's create that macro right now ok so into the code we go let's bring this up and I'm gonna call this item macro so what I want to do I want to basically add is relatively simple add the item to the menu if it if it doesn't exist if it does exist increase the quantity ok let's go ahead and create this macro sub item add to order we need to mention a few variables here to mention the selected row as long and the item row is long item row as long and also the item name to mention the item name as string and also I want we're gonna look for that item we need to find it so let's dimension the found item as a range okay moving on with sheet 1 still focused on sheet 1 here what do we want to do I want to put whatever the caller is the call would be whatever the name is when we select a specific item to write notice that we have the item item 2 or in this case if we select inside the individual item item 3 I want to take that and I want to put it inside here fact I want to build out a few items here make sure that we have everything else let's do that new order I want to know if that's true or false let's put it's true for now I also want to know the selected item I'd eat I want to put that in here so item 1 item 2 whatever it is I want to put that in b2 also I want to know the item bro what bro have we selected and I also want to know where the order Row the row of the order that we're on right we're tracking different orders order row and I want to know the selected row what is the selected row user selects a row and in six I also want to know the next order ID so as we keep track as we keep track of I want to know that so how do we do let's let's call these a little bit differently so we can differentiate between them and add borders around them all and then I'm going to give them just a little bit of an orange look so we can differentiate it that's all we need to know to track this information but I'm gonna need a few different formulas here so let's say we have a specific order where's our order gonna go our order numbers going to go right here order number one well Rama knows what's the row of that order I want to know the row we've already defined - named range on here so let's take a look at that into the formulas name manager and we have our order number here tab over that we can see that it's all of our order numbers here so I want to know what row is on notice our first one starts on Row 3 so how do we do that well we can use a if air and then a match so equals if Erin case has not found match what are we gonna look for them looking up this order number right here located in our 4 and I'm gonna look up the array what is it order number the one that I just showed you the name range don't want an exact match so that's a zero what if it's not found if it's not found at squat double quotes but remember we don't doesn't start on Row 1 it starts on Row 3 so I want to make sure to add two right because the order number one is starting to Row three right here so we want to make sure to add two ok so now we know let's left justify that so we can see exactly so we also want to know the item row that I'm gonna put it and also I want to know the next order ID right the next order you can use max for that equals if there it's just in case there's no what is the next one the next available let me knees max for that order number I want the maximum of all the order numbers plus one but what if there aren't orders and that could just started out at one okay so let's make sure that that works it's showing for right now we've currently have three in our orders so the next one would be four so we know it's working correctly okay so we've got that we've got everything we need pretty much here we're gonna work build this out I'm gonna put some conditional formatting for the selected row so we know what select an item so when I select one of these items I want that item ID to go right into b2 so let's write that up right now dot range B to not value equals application color application dot collar that's the name of the shape that called it okay next up what I want to make sure is I want to make sure that we also want to know the row of this item how do I know what row it is so let's say this is item 1 right item 1 is art is let's say we're gonna put it in b2 right let's say we select this and it goes to item 1 how do we know item 1 is located where it's located in Row 3 so let's get that row calculate that row through that so we can do that right here using a very similar formula equals if air we're gonna put a match on that what am i matching I'm going to look up this right here in b2 and of course I'm gonna look it up in item ID and I want an exact match but I'm gonna add two but what if it's not found I'm gonna double quotes okay so that's gonna put our item row if we change this to item 3 in this case it's gonna change to 5 that's what I want right because if we look in our menu items and we see item 3 we see it's in row 5 so that's what I want so good so that means as soon as we click this shape the item whatever that shape is gonna tell us what row that shape in is that what row that item is that's what I want so that's gonna help us out okay so if but I want to make sure that we actually have a row number so we can put a credit if dot range B 3 which is our which is our row number if it equals empty equals empty that means something is wrong they haven't selected the right item or maybe the item doesn't exist anymore or something like that then what I want to let's let the user know message box please select an item to add just let them know and there's nothing we can do so we're gonna exit the sub because we can't move forward if we don't have a proper item okay but assuming that we do have a proper item we can now move forward so what's the next step what do I want to do I want to get that item row we've already defined it here as long but I won't put that into a variable so item row it's equal to whatever is in B 3 so we can just copy that and just put it right in here item rows equal to B 3 and comment that out we'll call this the item row although it's self-explanatory next up now that we have the item I want to know the item name item names brilliant board because I want to know the item name where's the item located that's located in column C so let's pull that out let's get that out we know the atom row and we know the name is in C so we can do that so the item name we've already defined it as a string item name is equal to sheet 2 where items located range C and the item row value and this is value dot and this would be the item name now we have the item name ok so now what I want to do is I want to clear any totals temporarily if there's there might be some totals so what I'm gonna do is I'm gonna create a range so let's do that now so we understand that I want to create it basically what I want to do at some point I want to create totals and what's that's gonna look like sub total but I want to place it dynamically and then we'll have the tax next and I will also under the payment so the payments been made and then up here I'm gonna put in the discount here and then I want to have next up I want to have the total and then also I want to have the change if they're changed you know if they've paid what does their change based on their payment so that's gonna be a total and I also want to differentiate between this type of row and where the products are because remove conditional formatting so I'm going to use something like a tea here differentiated for these three so what I want to do basically is I want to take this information right here and I want to place it one row like if the last rows here let's say the let's say are the pizzas here right I want to put our totals right here but I want to place it so what I'm gonna do is I'm gonna copy this range I'm gonna place it somewhere else and then I'm gonna just gonna bring it in here and paste it down here so I'm gonna put it over here probably and let's move these over let's just move these over here for now put them over here we don't need them so I'm gonna put them somewhere like in x5 and I'm gonna paste the values here so I've got everything here and what I want to do is I'm gonna create a named range for this that way we can easily refer to it so what does that name great so let's just call it total range okay so now anytime we need to refer to it so I can copy so what I wanna do is I want to basically copy this in and just bring it wherever we want and paste the values here that way it's gonna be pasted in here so we want to basically what I want to do is only do that so because it's dynamic we don't know how many rows so let's continue on with our code now that we understand that so what I want to do is I want to set the total range and what I want to do is I want to find T so I want to clear any totals temporarily meaning as we add items here I want to clear the totals just clear any of the totals that might be here so how do we get we look for that T here and we find if there's the T so if we don't let's paste that back in here so that I can help you understand that so we we paste our totals in here and what I want to do is I want to look for the T here and that's gonna tell us our total and then just clear it out and then place back in here because I that I want that total to keep dropping down down down down the totals as we build out this receipt so we need to find the first T so how can we do that inside our code let's take a look at that so let's put a note here so clear any totals temporarily if they exist if they exist on saved orders okay so how do we find that so we have a total range so let's let's dimension the total range we want to define that total range as range okay we're gonna set the total range is going to be equal to dot range and we're looking at column W that's what we're gonna look for that T W starting and let's say row eight all the way through w 9 then I just need to let's see if it exists and we're gonna find I'm gonna use dot find not value dot find and then I'm looking for the T and if it's found just clear it out so how do we do that if not total range is nothing that means it's found nothing then dot range peers so so basically it's it's found let's say if it's found here on row W then what I want to do is I want to take P and whatever row it's found on and go three rows down and just clear it out I just want to clear it out temporarily so we can do that with the following lines of code don't range key starting at P cuz that's our first column and what what is the row the P and the row and the total range dot row and what else all the way through W all the way through : W and the total range row member --tz-- plus three because our total is gonna be three rows three what I want to do I just want to clear that out not clear content so what that's going to do is just clear the total now it's gonna give us some more space to put an item in there so just gonna basically take that finding it we're finding it let's say we find it on row 15 it's gonna clear all the way P 15 through W and the third row in this case it's 17 just gonna clear it out that's all that's going to do and allow us to add more items here so we can continue on with our code now that we've cleared totals out that does it in just one line of code okay now what I want to do well I don't want it what when we click an item to add an I want to check to see if the item exists if the item exists already I don't want to add it again I just want to put the quantity to two but if it doesn't exist I want to find the first available row but we could have like we're gonna have let's say cheese pizza here and we might put in some notes like no onions no onions so we might have our last row here or last row piece so we need to check either these columns to see where the last row is so let's do that now because we're not sure so the first thing what I want to do let's put a note here check for existing item and add quantity otherwise add the item so let's do it look we've already set the rangers found item let's do that set the found item is equal to dot range it's always going to be in P so we can look starting in P 8 P 8 all the way through let's just say P because our names always going to be in : P nine nine nine nine and what we're gonna do is we're gonna find it I'm what do we want to find dot find and then what is it I want to find the item name that's what I'm looking for now that we know now that we know what we're looking for let's see if it's found so if found item is nothing then it's not found okay then it's not found else found and okay so it's a little bit off the screen there okay so we've got two conditions one if it's found one if it's not found so let's focus on if it's not found what I want to do if it's not found if it's not that I want to determine the first available row the first available row if P eight here if this is empty we know that the first available rows on it because it's just starting our brand-new order but it's not order if it's not a new order that I need to check either this column or this column to see what is the first available row by checking the last row the value so we can do that with this line of code first I'm going to check if dot range P eight dot value does not equal empty then we know we have then what I wanted then I want to look for it then the selected row is equal to dot range and we're gonna search a range p8 through Q and then we'll just use a large number let's say 999 Q and what I'm gonna I'm gonna use fine because it's two different columns dot find and what am I looking for what what am I going to look for colon equals I'm looking for any text so we can use asterisk asterisk comma and then what I'm gonna look after what I'm gonna look after colon equals in this case I want to look after dot range pH dot range p8 that's the one I'm gonna look after comma look at colon equals I'm gonna use excel part we're just looking for excel part and then look in look in colon equals I'm gonna look at all the values Excel values comma and then search order colon equals Excel by rows that's for sure warden and what about the search Direction search Direction call it equals Excel previous a I want the search directions previous and then I want to match the case match case is false that's not so important equals false dot row I'm gonna look for the row and then I don't want the last round one plus one so I want the row I have to that plus one let's look at that to see if we spelled everything right it's a long line of code we're gonna select your rose PA through cute and we're gonna find what I'm gonna find any character using the Astor's after range Piett we're looking at excel Park we're gonna look in the values the search order is gonna be by rows the search Direction is going to be previous match case is gonna be false I'm gonna look for the first that's gonna give us our first available row now that we have our first available bro else what about else else pa dozen empty in that case our selected row because PA it's empty our selected rows eight it's row equals eight that would be on a new order in case PA is empty we know that 8 is our first available row ok great so that is if it dad am not found we have a selected row what do I want to do now if I what if it's not found I want to add the information I'm gonna have all that information so first thing I want to do is I want to set the selected row I want to know what row is it I want to highlight that row B 5 is going to handle that for us B 5 tells us our selector oh so first thing I want to do is put that row into B 5 so let's do that dot range B five dot value equals the selected row set selected because we're in use conditional formatting things this is gonna be my longest training ever probably dot range what next now I want to put the name the item name in P of course that's gotta go dot P and B item and then a selected row dot value equals the item name okay but what about the quantity next up what I'll starting I want to put the quantity I want to put the quantity here in column are I want to put the price and I want to put the total here so how can we do that so let's continue on with our code so we have P we already have the item name let's put a comma dot so it's clear item name although I believe it's clear now what s in our dot range R I say we can just copy that's a little quicker if we copy and paste that here including the comment here and then so the next one's gonna be our what I wanted R I want to know the usually it's going to be just one probably the quantity will set the default quantity as one and then we can increase it so but that's gonna go in R and we'll set that as our quantity okay comment that out as quantity so we know what else do we have I want to put in ass I want to put the price so where's that found where's that price we know a price comes from sheet to sheet 2 dot range wear and sheet to those it's gonna come from a specific call and our price is located right here and call them f so we know the item row and we know the column so we can easily place that in right here G 2 range F and the item and the item row dot value is the item price that's it now when you do is put the formula in for our total and that's going to go in locate it in T so we're gonna put T and in this case what is our formula it's gonna be basically whatever's the quantity times the price so we can put that inside right here quotation equals our quantity is located in our and our selected row make sure we use the + sign and the selected row and quotation marks asterisk new times s and the selected row that's gonna get us our total formula all right I like that now looks good okay so we're good to go on that else now what else so that's all if it's that's all if it's not found right if this is not found right data may need Avenue but what if it's found in that if it's found all I need to do is increase the quantity so first of all the selected row is going to be equal to wherever it has been found found item dot Rho that is the item row right we know actually it's the selected row selected item row so once it's selected what do I need to do all I need to do is determine the quantity increase the quantity so how can we do that just simply the our copy this put it down here equals whatever it is the plus one that's going to increase the quantity increase quantity by one okay good so now we've increased the quantity by one and that's it that's all we have to do if it's found it's pretty simple there okay what I want to I also want to display now once I put the item in here I want to display this edit icon in this chart so that the user can add some details like for example if it's here right on cheese pizza right I want them to click here and to be able to add different items here different descriptions or different detail for that specific pizza so let's first of all name this edit icon and I'm gonna name this one - we're gonna call that delete I also won't be able to remove it delete icon so if we've named it so want to position those based on that selected row so we can do that with the following lines of code with dot shapes edit icon okay what do I want position left dot left I want position it and what are on position equals basically what I want to do is I want to position it maybe outside and then bring it inside increment to left so how can we do that we can do that simply by sheet one we need to refer to sheet 1 again because we're inside the dot shapes with shapes dot range V and selected row dot left - about 32 right because we want to subscribe - 32 or dot top right I want to put the same top is going to be basically equal to the range but tops don't click the top position basically equal to that top okay so now we've positioned that and now what I want to do is I want to make sure it's visible so we need to make it visible dot visible equals MS true okay and we do the same thing with the Delete icon but I'm not gonna put it as far the left so I want it in a different left position so with in this case it's called Delete icon delete icon so with that one basically it's gonna go put the left this is gonna be different that's gonna be half so probably about 16 okay I want to run another check if this order has already been saved I want to add back the totals remember we removed the totals if they were there so I want to add back the totals if they if the orders been saved if it's not been saved yet we can just keep adding items so how do we know that well b4 would have a row right b4 is gonna track our order row before here so this knows if this is blank we know that that order hasn't been saved and it hasn't been signed a row the order number one self weirded working our order six you see b4 is gonna be blank because we don't have orders for number six we only have one through three so we can use that and let us know if it's been saved or not so b4 is blank we know it's not been saved so let's write that in the code if got range b4 dot value does not equal empty then what I want to do is I want to run a macro that macros gonna add back the totals cuz we're gonna use those totals were gonna add them back so I'm gonna dent order add totals and that's the macro to add total information and I'm gonna put that in fact let me just put that just so we don't run a break I'm gonna create a macro that's gonna have nothing in it into our order load and we're gonna call it sub add totals and that's it just want to make sure because otherwise it's gonna create a issue okay so back into the one we created so now we have that and I'm going to also make this a little bit faster in a minute but let's take a look at that so we're gonna go back into there and let's just clear this out here we don't want anything in here right now for our test now let's click on a meat and cheese beat so let's fix this total range dot row it has to be dot row continue on okay that looks good that's what I want and let's add this okay debug that we need to get the last row let's fix that all right should be values okay continue on okay I think we're good now so let's take a look now it's create if I click it again it's gonna increase the quantity that's what I want very good and it's controller all right let's have some conditional formatting I want to know what row we are selecting I want that road to be highlighted so I'm gonna hold down the P go all the way down just go way down about here and then I'm gonna reform and then conditional formatting and create a new rule and I'm use a formula that's fine we can use a formula and it's gonna be based on the row and that's our selected row located right here in b5 so b5 it's equal to row what I want to do in that case I want to put some a little bit of a color on it so gonna give it a fill let's say this light color and maybe just a boarder top and a bottom border so that it's very clear which row that we're focused on and click OK ok great so now as I add an item let's zoom out a little bit so we can see it that's a little bit let's go to 90% to be a little more clear here all right so now that we have now that we click here now we click on let's say pasta and we click on a pasta now it's automatically added see how that is and we I like that a lot better pasta okay good we click on another one it increases it that's looking really good let's let's make these bold I want to make this column on the bold so just gonna easily so that it stands out in fact in fact I'm gonna make these bold too and so everything gets a little bit more clear so let's make this all bold okay that's good I like that that's looking really really nice and we can see things really clearly but what I'm gonna do is I now I want to be able to select a row and have that edit and the Delete icon automatically come up I think we were ok with a hundred percent here ok so I want to have that automatically come up so the user can select on that very easily so how can we do that let's make this a little bit bigger here a little more space ok so now when we select on every select on an item I like that how do we add a row below that's on the Edit icon let me walk you through that back into the code we go let's take a look now also under the order item macros here we just completed the add to order now I'm gonna create a new macro we're gonna call this sub item and then we'll call it edit item and basically what I want to do is I want to insert a row instead of so if I'm gonna make an edit on this first thing I want to do is I want to select an item and I want to enter a row below that so we can have some additional information be placed here in column Q so the first thing I want to do is I want it when I select from P to you I want to have that selected automatically that that happens first so let's go in to our mainsheet on our on cheetah let's put in some macros on here so and it's gonna be on selection change so when the user makes it selection change that's what we want that road to automatically do that so when they use a touchscreen or anything else they select on it it's automatically gonna be on that row so let's go into selections I want to know what road in the selected row as long I wanna know what row they've selected so if not the user may just change anywhere selection from p8 all the way over to you and let's just say a large number p8 through you $9.99 is nothing right and I want to make sure it's not a total row so I want to be sure that there's no tea in the dope road W so let's check that right now and range W and the target dot Rho dot value does not equal T we only want to select on rows not total rows does not equal T in that case then what I want to do then I want to put the target row in to be five so the selected rows can equal the target row then what I want to do is I want to put it in B five range B five dot value equals the selected row and also I want to place I want to move my shapes right I want to move my edit and my delete on whatever row they've selected now we just use that so we didn't just pretty much copy that for the most part and then place it a little bit so into the item macros gonna do is I'm gonna copy this and then just make adjustments in the code so then all I need to do is the selector up but we don't need dot shapes so that's important take that dot shapes out and our select row is the same we've already defined it everything else is the same so I think we are good to go also I want to also select outside the order I don't want to select inside that so let's select something else right range let's try F range sorry F for dot dot select okay so we'll select outside of that else what else else that they haven't selected else that means they select outside of this in that case I want to clear B five range be five dot clear contents okay let's take a look at that reset the code save our work and let's go in here now when we select row good but our rows not coming into b5 we want to make sure that our conditional form is coming into b5 our selected row is not going in let's just I think what we need to do is we only want if we select 8f four so we only want to clear that out we can get rid of that we don't need that that's fine probably it's okay to keep the selected row I like that better okay so that looks good that's just the way I really want to have it so now we can edit the row so now when I click on this icon I want to insert a row blow and have us into some details or descriptions below the specific product so let's write that macro now we can write that macro so it's much easier so back into the item macros and now we're gonna do to edit items so let's get that written and the first thing I want to do is I want to know if we need to drop the row so we need to drop everything one row below so we're gonna dimension the last row as long and the selected row we need to know the selector of selected row as long okay now wish you one let's scroll that up with sheet one focus on that of course still on sheet 1 and what I want to do I want to know what's a selector we know it's in b5 the selected row is equal to dot range B five that's important because we're gonna focus on that and then also I wonder the last row again let's do that we just define the last draw ready we just can just copy that code i bred up here we've already defined the last row here right here here's our last row right we're just gonna set the different variable here so in this case instead of select we're gonna call this last row last row again we just used up so we don't need to rewrite that again I want to know if the selected row is the last row right if for example if we've selected the last row we want to add some information here into queue I don't need to move anything else down so I need to know if the selected row is the last row so let's run a check for that if selected row is equal to the last row in that case what we can do is we can skip some of the adding copying this rows copying the data down so we don't need to know then go to on last row so we can skip some things on last row : okay so because we don't need to move anything down because we're already on the last row so now what we want to do is you want to insert row so insert row and answer row below so dot range P and this and the selected row plus 2 right because I want to go one two rows down plus two and what all the way through W all the way through column W and the last row last row plus one cuz I need to move everything down one equal what is it equal well it equals dot range P right in the selected row P in the selected row that's the road that we're festro that we're focused on plus one just like to row plus one all the way through W and the last row so what that's gonna do is gonna copy again let's go over that got to put the hand sign in here okay so let's go over that P and the selector a plus two value equals daaad range penis selector so basically what does that is let's say our selection row is this what I want to do is I want to take everything here and I just want to move it down one row and then I want to have clear the rollout so we can do that so first thing I want to do is bring it down and the next thing I want to do is clear it out so this move copy down copy down one row so that's what that does the next thing I want to do is I'm gonna clear it out so what I want to clear out I want to clear out basically this one right here but just the road and row down so the Select row in this case plus one all the way through W and select row plus one in this case I'm going to clear that out dot clear contest that's gonna clear everything out and clear the row below not to select your rope at the row below okay so now that we've cleared it out now we're ready on the last now what I want to do I want to make sure that we select the row below so how can we do that I want to get put the user to enter some data right in queue in the row below so we can do that with this line of code dot range Q and the selected row plus one dot select okay very good very good that should be it now let's assign the macro right click assign the macro and what macro resigning item edit and then click ok now that we've assigned that Mackinaw it's click and move it down alright let's fix that because that's obviously not what we want to do ok obviously it's gotta be selected row not the last row ok selected row all right think that's gonna do it now let's try it again add here add a one down that's what I want let's add some more items more items here and then we can enter we need to get that selection change that the stock that doesn't make any sense at all so back into the restaurant we need to get rid of this I don't know that is well ok get rid of that that's not necessary so we want to keep that here and the reason is I wanted it off there but it's good now ok that's what I want now they can enter the information in here perfect edit it into the information in here good that's what I know let's format these under the Currency civil all the way down here get that a currency home and then what we'll do is we'll format think currency would work good okay nice now if things are coming together really nicely on okay what do we want to do well I want to be able to delete it now we have edit I want to be able to clear aligned so let's do that let's focus on the delete and get that I get that macro finished up so we've done the item macros we've done the Edit item so now let's do delete item so sub item delete item because I want to be able to clear the item just as we would if we edit it so how do we do that but it's a little bit similar than the Edit item is just some updates so dimension selected row as long and also I want to know the item database if it's been saved before we also have to delete it from the item database so that we need to check as long and also the last row same thing last grow as long so again we're going to do the same thing we're going to define both the selected row in the last row and we also need to know if it's a last row here so we're going to do the same thing here and just paste it down here as well copy that so we've got the Select row of course with XI 1 with sheet 1 we're still focused on sheet 1 as we always are and we're gonna drag that right back into here and so now we have a selected row and the last row we've defined both of those and now I want to clear the database if it exists let's check that remember I'm gonna take the database raw and I'm gonna put it right in here that means what does that mean that means our order item details here's where we're gonna store the details so Row 5 or row 6 we need to know what row it's on and that's gonna be located I'm gonna place that right here off the screen into W so if if this contains a value we know it's already been saved to a row here and I'll have to clear that row I'm just gonna delete this I'm gonna keep the order ID but I do gonna clear I'm going to clear everything else so we can do that with this little bit of code right here if dot range W and the selected row doubt value does not equal empty then we need to do a few things the item database row its equal to dot range and W in the selected row and the selected row so that's what we're going to find the Select row then all we need to do is clear out the database which is located in cheat for it's alright I'm an item database order item details database sheet for all going to Keep Calm a sheet for dot range B starting with B and the item database row all the way through G so and and all the way through : G and the item database row and item database row got clear contents so we're gonna clear that out clear all but the order number clear all info but the order number I want to keep the order number there just cuz I want to keep all the rows the same okay to maintain the rows to maintain row so I want to keep the rows that the rough structure always the same so that's if it's already in the database so now we've removed it so we're good to go on that why do I want to do now I want to remove the roast up I want to move all it because we've cleared it all out and I want to move the rows up so let's do that dot range P remember starting in P and the selected row and the selected row and all the way through W W and what is it the last row of course last row in this case minus one last row minus one dot value equals equals what is it going to equal almost the same but slightly different let's copy this make it a little bit quicker so this doesn't become forever training P okay so but this time what I'm going to do is gonna be P in the selected row plus one all the way through W and the last row so that's it last row and lusser okay good so now we've got that that's gonna and know what I'll all I need to do is clear the last row Wow so how do we do that that's pretty much simple just gonna clear it out so I'll copy this and clear the last row out so that's P in the last row all the way through W in the lasso we just need to clear the contents of that so dot clear contents okay that's gonna clear the last row clear last row okay but we just need to make a slight I need to make make sure that we're on the last row again I want to check to make sure if we're on the last row we can't clear so we can do that up here go to on last remember so we need to run that on last row here just so it's always skip because we don't need to do anything if we're on the last row so all right let's just take a look at that code I think we're good to go save our work always save our work and we've moved up to select the row we want to make sure we clear out the database regardless if we're on the selected row so that's got to go below that here if the selected row there okay but we also want to make sure we're gonna delete the current row if it's the last row if it's the last row we still need to delete it so how do we do that we can just do exit sub in case and then in this case we're just going to do the last row the last row dot range P and the selected row cuz this would be if we're clearing out the selected row P and the selected row all the way through and W and the selected road clear Khan so that would be the last row run last row on a clear contents so that's gonna clear last row but if it's not in the last row of course we've already exited out okay I think that's gonna work really good let's save our code check for errors if we're on the last row and I clear it out and I assign this Macker let's click sign macro and now we're gonna click it to delete item so let's take a look we're on the last row and clear that works nicely what if we're all not on the last row I want to move everything up that's exactly what I want move everything up nice and move everything up perfect so that's working really good okay what next well we need to save that order we wanna make sure it's safe so if we've got to let's say he's put in just a temporary table number here and type delivery I want to make sure to be able to save that order the first thing I want to do is I also got to put in the totals here the totals have to go in and we have to save it so how can we do that let's write some macros to do just that so back into that we have a save and update module here let's write in a macro that we're going to save it so let's get in that so we can start that out and call that sub order save it's gonna be used for both save and update so save update we'll call that save and update and we're gonna dimension some variables mention the last row as long and also the order grow as long and also I need the item row as long those are all important cuz we're gonna have to go through these and I also need to know the item database rope as long and the total row total row as long and okay good so what else maybe I want to dimension the total range just as we did before total range as a range because we're gonna need to bring that total in and of course we're focusing on with sheet 1 so all right what do we need to do the first thing I want to make sure is if there is actual and order information that needs to be at least something here in p8 at least the first item so let's make sure that there is if not range PA value equals empty then we're gonna alert the user then what I want to be on alert the user messagebox please add at least one item to save this order okay exit sub no items and cannot be saved all right so now that we have that item what else do I want to do we need to end if there and if okay so we have that and that's gonna help us just to make sure that they put in the item in what do we want to do next next I want to also clear the total rows if the existing I want to clear them temporarily because I want to make sure that the total rows are set properly because I'm gonna add the total rows in another macros because that's gonna help us out let's do that clear total rows if existing temporarily it's put a temper we're gonna add them right back in so we're gonna set the total range is gonna equal to again we're gonna look for that to know look for that T dot range where are we looking we're looking in w8 all the way through W nine nine nine that's fine enough and we're gonna find them looking for the dot fine and we're looking again for the tea tea and I'm going to look for that and then if it's found if not total range is nothing then it's found right if not space in here then that means it's found then what do I want to do then dot range P I'm gonna clear it out P and Toto range dot Rho and all the way through remember it's going to be three rows of total and all the way through W and the total range dot Rho plus three gonna clear it out dot clear content so that's gonna clear all the totals out just temporarily so that we can make sure we add those in little bit later and later add them in properly through another macro okay so now what we want to do is I want to get the last row that's really important so again we know the last row we've already added to side I'm not gonna write it again cuz we've had it here so let's just put it in right here the last row it's everything we need right here and we'll go back into the save and then just place them right in here so last row make sure we need to find it we know what it is and also what I want to do is so now that we have to find the last row I want to check if this is a new order not right we need to know is it a new order or an existing order how do we know that well if it's a new order we know that b4 is gonna be blank because it's not gonna be found order number six is not gonna be found or we're not kind of any order in there if B 4 is blank so we can use B for to tell us if dot range b4 dot value equals empty then we know it's a new order else existing order and if okay so now we've got that right now we can use differentiate between that so if it's equal to empty then what's gonna be our order row it's gonna be our first available order on sheet 3 remember she threes our orders where we keep in track of it I want to know the first available row right here in this case it's going to be 6 so what is that row so we can use that so our order Row is equal to sheet 3 a in the first plus 1 right that's the first available row first available first available row ok so now we've got the first available row I also what do I want to put in there I want to put in the order number and what is that order number we know the next order we've already written the formula we know it's gonna be located right here in to b6 that's our next order number I'm gonna take that I'm gonna put it right in here into our first available including a so let's do that so sheet 3 dot R ain't a and the order Rho dot value equals actually what we're gonna do is on new we're gonna move it in I forgot to mention what I'm gonna do on new orders if it's a new order I'm gonna take whatever is on next time I'm gonna put it right here put it inside our six so when we click new order when we click this and this or this we're gonna take that next order and we're going to place it right in our six so our six is actually where we're going to get it from that's fine equals dot Val dot range our 6 our six and that's gonna be our new order number okay so we have our new order number everything else is based on the existing order what do we want to do in this case our order Row is located of course in b4 right we know it's in B far because b4 it's not empty and that's where our order rows gonna come so we can copy and paste that in equals order row that's our order row for existing okay so everything else we're going to do everything else we're gonna do is going to be regardless if it's a new order or an existing order so we can want to add all the information in Dayton time order type server show her staff whoever we're gonna put all that in so sheet 3 dot range in this case we're gonna focus on B which is our date and time B and the order row dot value is gonna equal now which is the current date and time curtain date time I want to put that I'm gonna put the current order date and time I also want to put it here right in q6 as well but I also want to put it right here inside column B so we can do that so that's where he's gonna do a next up I want to put the type here dine in delivery or whatever and then the server so we can continue on with our code here so we can copy this here and in this case next up we're gonna do C I want information and C I want information in what do I want in C and C we're going to put in our order type of course we haven't finished that out and the next up is going to be our server or staff and then the next up of course that's going to be in D let's take a look at let's bring this down so we can see the columns in D all right and E's gonna be the table number D and then e is gonna be the table number and what else do we have we also have the total so we're gonna want to put that in as well and that's going to go in half is where our total is gonna be and then we'll pull the information from sheet 1 this is just putting in total and also I want to put in our discount and our payment but that's gonna come in a little bit later because we haven't added it in yet so at least we have that so that's gonna come in where's our order type gonna come from equals dot range that's gonna come from I believe let's take a look here and that our order it's gonna come from order type s6 so let's put in s6 is where it's located that's our order type and we can copy that and just put in the rest of the ditional information here copy the equals you guys didn't know you were gonna get a full course in this did you okay now we've got okay so now let's put in our server now our server and our staff is gonna come from t6 so that's gonna go in T and our table number that's gonna go and come from p6 okay let's make this look a little bit better I'm going to bold it and then Center it so that it's a little bit better and then Center okay so that's more clear order dot and I also want to put do order it on date automatically in to q6 as well so let's do that okay so doc range q6 is also equal to now equals now current date and time okay so we have that now we put in the information in and actually our total that's not gonna be ready yet so what is our total going to be our total let's calculate that is going to be equal to what is equal to it's gonna be equal to the application worksheets can renew some on this sum what are we something we're summing actually we're gonna put in total a little bit later on I want the total with taxes I don't want just the subtotal I'm gonna put that in later we're gonna put that in so we'll put that in just yet because once we add in our totals we can come back and put that in because right now we're just putting in all the major main detail and then we're going to come back and put in the total okay so now we've saved all of the information right we've saved it except for the total where we haven't done that we'll come back to that so we've saved our order numbered a time type server table not the total we haven't done any of this this is all gonna come information at the footer so we'll take care of that later but we've got the main stuff here now what I want to do is I want to take all of our items and save or update either update if it's existing or save it how do we know if it's existing if w contains a value we know it's already has a database road w doesn't contain a value then we need to add a brand new row so we're gonna use W let's let us know if it does or not so let's continue down how do we know that so we're gonna say enter item details so what we wanna do is we're gonna loop through all the items I want to loop that's why we removed the total I just want to loop through all the items starting at 8 and going to the last row and making sure and checking if W contains a values save or update the information if it doesn't contain a value add a new row so we can do that with a loop so for the item row equals 8 to last row next item bro close that loop okay so what do we want to do again first thing you wanna do is check if W contains a value if got range W and the item wrote value equal to empty then what then it's a new row new item new item else existing now we can move it on okay so if it's a new rope item what do I want to do well the first thing I want to do is put the ID the order ID that's got to go in right here in come in bro a and then the first available so let's get that let's get that first available so the item database row is gonna be equal to [Music] possible on our first available row first available row and of course that's gonna be on sheet for where our items are located using common our first available row so now that we have the item database row we can put inside a because that's only for new orders so sheet for range a and the item database row dot value equals what equals whatever is in our six we want that I want to put in that order number now we know that order number is located right here on our six let's put that in equals dot range our six that's the order ID very important order idea we only need to do that for new items so we only need to do that once what else do I want to put it I want to put in the item row in half I want to know what row of this order here in this case it would be nine or ten or or whatever row I want to put that row and I want to put it right here in F and then I want to put whatever row is currently so this is the database Row three four five this is the order row the row here which is starting at eight nine ten okay so I want to keep track of both of those and those don't change those are only for new items so let's put those in right now sheet we can copy this because it's gonna be almost the same thing and then just gonna have some details sheet for in this case it's going to be F and an F we're going to put the order row order row and what is our order row it is located here item row we know the item row and the next thing I want to do is I want to put in G but I want to put that of course it's going to be the item database URL so that is gonna be the equals item database bro okay so now we've got this information here this information here that's only for new items so a f and G's only for new item else what else we've already we know the item database row that's can be stored in W equals dot range you and what row the item broke and the item row that's our item database oh of course it's equal existing right we're either gonna place it right here and one more thing we need to do actually I need to place if it's a new I want to place that database right inside W so let's do that that's really important so do it down here dot range again we can copy this W in this case right here equals again the item database row I need to track that that's going to be in W very important so we have all that now everything else so this is all four new items this is for existing items and everything else is going to be four regardless if it's new or existing I'm just gonna update it sheet 4 dot range you can start up with column B in this case B and the item database row dot value what's going to go in there that's gonna go the product name so where's the product name coming from P equals dot range P and the item row ok so that is going to be our product name what else does so now we could just copy and paste and it'll be a little bit easier and just make the updates now I want to also note the detail right if there's any detail for that I want to put that in the next that's going to be and located and based on column C and also Q's right so we're gonna take Q whatever's in Q whatever's in Q any detail that we have and we're going to put it right in here into the product detail here if there's specific detail I wanted here so next up is the quantity so let's write that in this would be the item detailed product detail next up we have the quantity next up is the price ok of course those are gonna go in different columns right d and call them e okay so P but of course the our quantities coming from our and our price is coming from s so now that we've all updated alright so that looks good so we've got all that there let's just go that we have our quantity our price and our detail all moving - when we're saving it going from here product dealer quantity and price we've already put the order of but now we have all the information that we want here so we can close our loop and we can go to next item row so that's it that's all we have to do now we've saved items now all we need to do is add in our total range so I'm gonna write a macro to do that because our totals are gonna be added in many times like when we load I want to add the totals is dynamic so we're gonna do that with add in total range so we're gonna call that a macro called order add totals order add totals now that's a macro that's going to run automatically so let's I'm gonna write that macro right now Matt girl to add totals after we add the totals then we add the information in right then we add the information we want to save those total informations back to the database so how do we do that the last row is equal to now once we add our totals in once we run the macro to add our totals in so basically what we're going to do is I'm just gonna copy this through a macro we're gonna write that in just a moment I'm gonna place that right in here let's see what I want to do is I'm going to skip one blank row and place our orders here put our totals and now I want to determine the last row after we add a totals in so I'm gonna run that last row one more time in this case it would be 17 so let's write that in the last row it's equal to P in this case it's gonna be P we'll add P in there and so of course not she won't on each children so now so last row last ropes because I want to get the total information put the discount put the total and the payments all the way and remember we didn't add that information in before so we're gonna add it in now so where's our total gonna be our total is gonna be located here so we got our last row in this case our last row 17 so where's our total or total is going to be one less than the last row and it's going to be located right here in T column t remember our macro that we're just about to write is gonna add in all this information and add it in so don't worry about that all I know we just need to know that our totals gonna be here we want to save whatever's here and I'm gonna put that right inside here on put writing here the discount on the payment so I'm gonna save all that information because we know our database wrong so let's do that da range put in our discount calculation right now T or is our discount look at our lower discounts located in T I'm gonna put that in to XI 3 dot range we're gonna focus on again the total I want to know that after we've imported it and then so our orders is gonna be our totals gonna go on F our discounts gonna go in G and our payments going to go in H so let's do that right now III F and the order row dot value was again equal it's gonna equal dot range based on the last row t and the last row minus one value let's just double check that I want to make sure on that T here's the last row right here's the last row minus one is going to be right here T the last row minus one that's our total our payment is located on Q and the last row so let's put in the payments now under the item under the order so we want discounted payment also put those both in discounts gonna be same thing at t but it's going to minus two so we can just copy and paste that this is our total and the next up would be our discount and then our payment so paste that and paste that okay so F G and H discount and our payment okay so our payment our discount ago of course is located right here t minus two so that's pretty simple t minus last four minus two and our payment is located in our last row but it's in column Q so our last row Q and our last row okay so we've got that but we do need to create one more macro here so we've got that all right but what I want to do is I want to just hide it when I want to save it I want to hide these two icons so let's let's write some code for that just to hide it we don't need those visible when we save it we've already saved it so shapes edit icon not visible equals Emma so false and then dot shapes delete icon got visible equals in the self false okay so we've deleted those we've hidden those we've hidden those two icons okay so we just need to write out this knocker right here order add total so let's write that in right now I want to write it down below here sub or direct owes okay so with sheet 1 of course we're focused on we're gonna add those totals in right now okay so how do we do we need to dimension the first few things that mean dimension the last row dimension last row as long and also again we're also with sheet 1 so I'm gonna be stay on that sheet we need to know the last row pretty much just we did what we did here so again we can copy that last row just as we did here no need to rewrite this line of long line of code here and place it right down here so we've got the last row and now what I want to do is again I want to basically take this total range here so we've got the last row let's say the last row is here but I want to skip one and then put this so P and the last row plus 2 through W and the last row plus 4 equals our total range we've already defined it here under total range so that's all we have to do would write that into the code so let's just write that up dot range P and the last row plus 2 because I want to add a blank row plus 2 and W and the last row plus 4 thought value equals dot range what's the name of it total range that's it total range right above total range dot value okay so that but that doesn't put in our formulas that only puts in our only puts in the values but now we want to put in all the formulas so we can calculate our total bill so let's do that with just a few lines of code dot range Q and the last row plus two what do we want put in Q what do I want to put in here I want to put the subtotal Q in the last row plus two the subtotal is gonna be basically whatever's through t8 all the way through t and the last row so let's just write that up that formula right up here dot formula you could use value two equals because it's gonna automatically be a formula equals what is it the sum of again tt8 is gonna be our first one t8 we know that that's gonna be our first one all the way through T and the last row and of course we have to close the parenthesis and close the parenthesis so that is our sum formula that's our subtotal okay so we've got our subtotal written what else do I need I need to know what our total formula is so what is our total formula dot range again here's our here's our total it's gonna basically be equal to our subtotal plus our tax tax gonna put in here we need to put in the tax of course to put in that and minus the discount plus the total so let's put in our tax ball right because that's gonna be next cute and it's gonna go in Q and the last row plus three so let's add that in right now dot range in this case let's just we can actually copy this it's much quicker copy this and put this in of course it's not cute it's gonna be last row plus three what is our friendly it's going to be equal to our subtotal times our tax our tub total Tundra text what does that equals and what's our subtotal located our subtotal okay tada - here's our subtotal right here our value now we just placed it so that's where our subtotal is going to go so I need to know that schools - and the last row plus two and times what is it we already have named range the tax rate we already we already defined it tax tax rate okay good two times the tax rate that's all we need so Q la Souris so what that's gonna do is gonna place it right into you next up now we're ready to add the total so now we know the totals in T and the total of course is going to be whatever's in the subtotal plus the tax minus the discount equals the total so let's write that up so let's what would that look like equals the subtotal plus whatever's in the tax minus whatever is the discount so that's what it's gonna look like that's what it's gonna weigh so if we have a total of $50 and we have a tax right let's see equals in this case this times the tax rate we've already got it there we go and the discount a new discount is $2.00 we want that so that's what I want so this is our frontal right here that's our furnace so how do we take that and put it into VBA we can do that easy dot range T remember putting that in t where is it t if our last rows here it's gonna go our t minus last row minus 1 okay so t we don't need two of these and the last row plus three I should say plus three us and you know our total row right if our taxes here our totals can same plus Rho plus three formula equals I'll show you easy way of doing it if we paste in that code okay so now we pasting them of course that's not gonna work all I need to do is replace 15 with in this case our last row plus two right if our last rows here plus three or last row is here plus one two three our last row 3 15 so 15 is going to be of course 15 is going to be our subtotal so that's our last row plus two so let's write that in and last row plus 2 and Q last row guess this would be last row plus 316 would be last row plus 3 quotation marks and last row plus 2/3 in this case write 16 3 and again quotation marks minus T and instead of 15 we're gonna put in the last row - ok think that's gonna work just fine and that is what we call our total just actually it's gonna be plus Q right plus Q in last row plus 3 minus T in the last row plus 2 perfect that's our total ok great so now we've got it now we've got all of our we've got all of our totals and our formulas are all in there ok let's go ahead and save our code and let's run that I'm gonna run that code here and let's take a look at here ok well we got it let's clear that out cuz we had our last row now let's now let's now let's run it again let's just run it again and see what we get ok let's see 1 87 14 we can reduce tabs let's format that as a currency cuz we can no longer need that as like ok that's exactly what we want perfect and we have no discount let's if we put a $5 discount automatically good that's just what I want although I did need some formats in here we need to make this look a little bit better let's put in some conditional format and know what separates this total from everything else well we know it's got the T here we didn't delete those so we know it has the T here right so let's let's delete it one more time let's run the code one more time so we can clear out just get the right so we see that this differentiates everything else and so how do we know that this total is different because it's got the T so if I take and highlight all of these and go all the way down here and add some conditional formatting but it's based on the T here then I can only for about the totals that's what I want so conditional formatting new rule use a formula and in this case we're gonna start with eight in this case eight here and then of course we don't want to use the dollar sign in front of the a we want to make sure it's dynamic and not fixed equals in this case equals T then I want to format it W and all what do I want to do I just want to at least make it put some borders on top and below so like that let's take a look at how that looks yeah that looks much better that's what I would like to see very good so what else do I want actually let's format all this entire column on the Left um and then left and also I don't want a high-low I want to make sure that there's a value in C I don't want to select anything I want to make sure there's a value at least in P or Q and not T so let's add some more code to this to make sure that we're only selecting rows with contain a value so let's go back into the on sheet code here and let's have just a little bit additional code for that if range P and the target dot zero dot value do not does not equal empty or of course Q as well or dot or range Q and target dot Rho dot Rho dot value does not equal empty then okay only then do I want to place that now did indeed double and sign then I want to place it okay so we had to just add one more ENDIF here and if and then we can bring that out because that's really what I want to see here okay and so now let's take a look at that now now that's the way I wanted nothing here nothing here except probably not that's good that's the surround only on items only on items not anything else very good that's just what I want here okay so we've got that figured out now we've written our save order so now let's update now let's assign a macro let's bring this down and let's actually assign a macro to this so that we can get that all line I'm gonna in the middle on that make sure to look okay and had a little bit of space so our button looks nice and proper okay so we're going to group that and then we'll call it save group and then I'm gonna sign a macro to that so that's the matter we just signed and we're gonna call it order save or update and click OK alright let's save our code and see if we run into any issues on that ok well that looks really nice you see we've now signed in order let's just make those all in the middle so we can see them now it's everything saved now we have our ordered on or six we didn't have any bugs but I'd like to see all these bold probably but it's okay I'm gonna make sure I'm gonna have and I want the totals all in bold so I don't want if I add these I want to add let's see no onion I don't want those in both I don't want that in bold but I do want and let's save the ghent okay good that's just what I want so notice this have the line in 30 so if we look in our item details we see now that we have Noah spelled onion wrong nice but let's fix that and make sure it updates right no onion okay but I do like onion just so we're saying save the order and let's make sure it updated in the item details now it's spelled right good that's what I want perfect just and what I want now we've updated it now it's looking really good but I want everything in bold here but I don't want this and bold but I do want this in bold what's our differentiating factor it's that T so let's do that let's add to that so go back we've already got conditional formatting and we already have one under here so we can just use that one this one here what I want to do is I just want to make the text bold so I'm gonna go into the font and make that bold that's all I need to do now click apply now everything's gonna be bold but not here perfect that's what I want okay we're looking good we're getting close to finishing we've got a few more macros to write so let's get to that I know this is super super extra long training but I don't want to do a multi-part so and I think I'll take a few weeks off training just keep that in mind alright but no problem I'll be back just going to focus on marketing for the next if you do like these trainings you want to support us you can do it in many many ways one of the great ways to do that is I picking up the 150 workbooks for just $56 but if you want to take this training to the next level and learn how to take these talents and bring them into actual passive income you can do that in on mentorship program and I mention your program I'm building an amazing accounting application and I'm teaching you at the same time how to take your skills to the next level and learn how to create passive income by selling your own software and that's through our define design develop and deploy your own application so I'm taking you every step of the way so if you like that our mentorship program is the way to go so put those I'll put the links down below or you go to my Excel mentor.com and get signed up and I'm gonna teach you every step of how you turn your passion into profits all right let's continue on with the training I want to make sure you get the most out of this training one of our most amazing so we now we have to save water but how do we load order let's bring this out of here we'll use this a minute I want to create a something like a search order search order and I want a user to be able to search her so let's put that here and dial put that in white in fact and I'll drop it down I'm gonna take that let's drop this down for just let's pin this down for a second and I want to make that in white and I want to put a border around it I'm gonna be able to search for order so I'm gonna put something clear I'm gonna use this same and put a double order around it and then a dotted line and all the way around a dotted line in the middle here okay so when a user enters and we already wrote this nakhre we can get rid of that because we wrote it on another module okay so continuing on let's reset that and so what I want to do is I want to put the order number right in here and when you when an order numbers entered here I wanted to search automatically so how can we do that well let's do that all we need to do is I want to load that order so let's write a macro as soon as the user enters let's say six I want that order to load automatically so let's write the macro so we can load orders in automatically alright let's get to that on to the module here we've got a module called load unless that's something we're gonna focus on right now so we're gonna call this sub or load okay so let's go to load our order dimension the order row as long I also want to the item row as long the item database because I want to bring it all now that we save don't want to bring it all back in row as long and then the last item row last item run you'd run an advanced filter in our items so we need that and a last result row as long because I need to know all of our results and I need to loop through the results so result row as long and our last row has long cuz I need to run an advanced filter selling you to know all of that okay so we have that now a dimension the order I want to do a search so in dimension the order range as range and the order numbers of string I'm gonna do a search for the order number order number a string okay that's it now with sheet 1 what do I want to do I want to first check to make sure that T 3 is not empty right if it's empty then we can't search all right if they've deleted it so T 3 needs to have a value in it so let's check that first if range T 3 dot value equals empty then message box please enter a correct order number range and then we'll just like T 3 dot slack we'll select it for the user so they know where to go exit sub ok so now we know the order number is going to be located in T 3 so the order number equal dot range T 3 so that's gonna be where orders located and put that as order number all those relatively self-explanatory let's set the order range we've already defined as arranged above order range equal to sheet three that's where orders are range order number that's our named ranges we've already created order number dot fine want to look for the order what do I want to find one finding order number okay now we can do run a test if order range is nothing then let the user know hey this doesn't exist some message box please enter a correct order number okay so we please like correct order number exits itís not a correct order we can't load it okay so assuming it is correct now we can determine the row of that and so how do we do that dot range first thing I want to do is I want to clear all the contents I'm gonna clear everything out because I want to make sure we're loading a brand-new or just want to clear everything out so b5 of course I'm gonna clear out b5 what's in b5 and why do I want to clear out that's our selected row gonna clear that out no reason to keep that brand-new order and I also want to clear out everything from P all the way to W clear all of that out so we can do that with the following lines of code b5 also I want to clear out T also in the information all will also up here T would be p3 all the way through you P 6 all the way through u 6 I'm gonna clear that out and I also want to clear out whatever is in t3 so I'm gonna do that all that information should be cleared out before we load in the information so we can do that t3 close out like we've already got the order number keys 6 through w 6p sixth all the way through you six is fine u 6 let's do that u 6 and what else and of course p8 all the way through W and then nine nine nine okay so let's take a look at b5 T 3p6 you through u6 this should be P 2 and P eight through w99 okay dot clear dot clear contents that's gonna clear all the information clear order now I also want to make sure we also hide the icons we don't that's let's these icons right here I'm gonna hide those no need to have those up so we can hide those hide our edit and our delete icons now that we have those edit what I can do is I can set the order row so the order row is equal to the order range dots row okay that's the order row now we can load everything in once we have that so we can load in again let's load in all the information I want to load in the table number order Don we already have the order row that's going to be order number that's going to be in our six and we have s is going to s 6 is going to be the delivery type and the staff source let's load all of that in here dot range P six dot value is equal to sheep three that's our order sheet dot range e that's the table number all right let's pull that up and we know exactly what we're ordering e is the table number we have the server we have the type date/time we want to bring all that in here okay so E and the order row dot value and that's going to be the table number okay so now we can just copy and add and update those p6 okay we have again in B is going to be of course our ordered on ordered on and we also have in our C is going to be our type C going to be our type and next up we also have our server and our server is going to be in D so we have all the information now we just get get the information so where is it located inside our pulled up here alright now we can see now we can see all the information so we know that our table numbers located in P 6 so we can bring it down table numbers P 6 we've got that ordered on is going to be Q 6 so update that we have our type located in s 6 and of course lastly we have our server located in six okay the only thing we need is of course in the order number in our we need to put that in arm so we can do that we've already cleared it out so let's do it up here so we're gonna put dot range are because we're clearing it out below our six is going to be equal to whatever they've entered in T three equals dot range T three okay cuz it's cleared out here so that's our order number okay so perfect so we've got everything filled in on our top part now when you do is add in our items so we can do that with the following lines of code let's put in that comment it up load items all right so first of all what we want to do is I want to run an advanced filter I'm going to take the order number I don't want to run in advanced filter so I want to put the order number I'll put it in right here in our criteria and I'm gonna run advanced filter I want to get all of our results and I want to bring them all in and put them all inside right here so how can we do that pretty easily just run so the first thing one do is determine the last row so the last item row is gonna be equal to sheet actually X sheet four dot range okay that's gonna be our last row last item row okay so now that we have our left side of row just gonna check if last item row is less than three then go to no items items okay so no items I just in case there's no items okay so assuming that there are item okay so assuming that there are items we can continue on with our advanced filter but we need to set our order number here we defined it up here let's define it up here put it in we know where it's located it's in our six but we can also define it in a variable same thing we know it's located in our six let's define as a variable because we have it up here our order number here as a string we've got it here so let's do that let's put in our order number so sheet four dot range k3 that's where we're gonna put our on criteria k3 dot value equals order number okay so our order numbers here and so that's gonna put our kitchen now that we have our criteria we're ready to run our advanced filter' inside our sheet for sheet for dot range again where is it coming from let's take a look at sheet for while we're typing this out sheet for it's gonna start in a2 all the way through G in the last row criterias k2 through k3 and our results are gonna go from m2 through s let's write that up a2 through G and our last item bro dot dot advanced filter copying it over and criteria range is equal to again sheet for dot range pay to through k3 k2 through k3 okay copy to range what range will be copying it to equals cheap for again range and this time it's going to be m2 through s to m2 through s2 okay good and now unique : equals false is fine okay I think that's it let's just take a quick look at that is longer cuz I want to double check them a to through Gina laughs in their own advanced filter we're gonna copy it to another range the criteria range sheet for K 2 through K copying it to another range correct sheet 4 and to dress to unique equals false that looks good again now we're ready to get the last results row last result row what is it equal to in this case it's gonna be sheet for they were focused on em this time sheet for M is we're gonna check and if we were last results that we just needs to check if the last result Rho is less than three then go to go to no items we've already defined it below so we have that fine assuming that there is now we can run a loop so what is our loop going to consist of it skins consists of four result row equals three two last result row close our loop next result row ok so what's that gonna be first of all I want to make sure I need to know what row so we got a check this is work and check this is our order row so that's very important we'll call that our item row actually because it's going to go inside our items gonna go right here we need to know that row very important so inside that let's pull that value first and if it's got a container value if it's blank we can't put it in so if sheet for dock range are and the result row dot value does not equal empty then we can continue on now we can continue to can't be empty so we can put our item row what's it going to be it's gonna be equal to whatever is right here that's the row we're gonna put it in inside our order equals okay our item order row so now we have the road now I can place everything inside and what's going to go inside well the first thing we're gonna put in is the product or item and that's gonna be of course in dot ranging in column P P and the item row value is going to equal to XI four dot range course that's coming from N and our result row got value it was that's our product product our item name okay now we can copy it and just update the additional so now we have so now next in queue is gonna go our description or our detailed product detail color product detail that's gonna be here in queue and it's gonna also come from in this case oh right so OH okay so next up oh okay so next up what I was told on I also want to put in R so R is going to be our quality so now that we have our quantity in R and where's our quantity coming from that's coming from P so we can put it in here P hello item label that is quantity item quantity okay what next next up now we have s what's gonna go and s and of course in s is gonna be our price and our price is coming from Q and we can label that as price next up we have a formula right we want to put in the formula so what's that formula I want to put in the formula it's gonna be whatever is in R times s so let's put in that formula right now and that's gonna go in T so we can paste that but we can get rid of the first second part of that T it's getting equal in this case it's a formula so it's quotation marks equals again in this case it's P and the item and the item row and quotation times s whatever is it s so it's R it's actually our our times asteroid hard times s and the item row R times s and that's gonna be the total now one more thing in W I wanna put I need to put in the database row in case we make any changes we need to know so I've got to put in the database row inside doubly so doc range W and the item row is going to be equal to of course that's the database role and where is that located that is located right here and inside list we can just copy this and then just place change the order here and where is it coming from let's just take a double look that's coming from our s right s is gonna have our database row because we need to track that so it's putting it s move later that as database Road database row okay nice nicely done okay so W is going to equal ask that's very important we want to make sure David's room that's it okay that's all we need to do to load it now with just a few other clean up well also of course I want to add in run macro to add the totals remember we added the macro that's why we create a macro and if there's no items we probably don't need to run any had any totals so let's not let's write in this so order add totals that's the macro we created already run macro to add totals see a single macro does it so we can use it multiple times and let's take a look at that let's save our work okay so basically we want to when do we want to load this order I'm going to load it when the user makes a change what it makes it change to what T 3 so let's write that in and of course it has to contain a value so let's go back to our main and this is gonna be not on selection change but worksheet change so we're gonna focus on cheat a change here and then what what range will be changing T 3 is the T 3 if they make it change the T 3 then run that macro then order load ok let's try that let's save our work go down here and let's just put in 6 again as if it's just when we just worked on please enter correct order ok we need to update that I think that it worked out but we do need to add that additional information in so let's write that up because we don't want that coming up and then loading so let's do that and you know what happened was as soon as we cleared out as soon as we cleared this out that came off right so we don't need to run that when it's empty so let's make sure that if it's empty we don't need that so again we're gonna do is nothing then let's add some additional information that because we don't need to run if it's empty and if so what I just want to make sure if let's just say range t3 dot value does not equal empty then run the order load okay let's see it like to take a look at that and make sure that that because we do want to we do clear it out so six okay that looks very nice the only thing is a little bit slow it's pretty fast but it can be faster I know we can make it a lot faster we can use stop calculation what we'll do is we'll turn off automatic calculations turn off screen updating I've got that here ready inside a macro let's take a look at this you'll see this one it's called reset calculation and stop calculation but that's going to do is turn calculations to manual and screen updating to false so all I need to write is stop calculation and just make sure by the end of the macro that I make sure to reset the calculations so on the load let's do that but you want you don't want to put at the top I want to put it after the last exit stuff so stop calc and then before adding that after it ends all the way down here here reset count okay that's gonna make it a lot faster because it's gonna turn off calculations and now we can clear up get rid of this and save our work and now let's try it now it's gonna be a lot faster six oh yeah I was super fast okay you see how fast that was I'm putting in different orders the only thing is we don't have the order number we got to put that order number and that's not getting put in property so let's make sure we put in the are six I think I just need to change it to the variables so on the load right are six I'm gonna put this because we've already cleared out so we need just put in the order we've already defined the order number up here right here we've already defined it so all I need to put in is the order number right here that should be sufficient okay I think that's gonna that'll be corrected and we don't need to clear this out we can keep that blank we don't need to clear out those let's keep those blank because they're gonna be filled in with the new data cuz we're clearing it out so that'll be fine there okay so now - okay so now it's better because in case there's anything blank so let's update let's put a Fred and save that order and then let's put in order number one so that clears out order number two nice it's looking really nice really nice okay just a few more things to do and then we're gonna get done with this marathon training we want to be able to of course send this to the kitchen and send it to the local printer and I also don't want this extra whitespace I only want white space where it's needed and basically I want to go with just one extra row below the payment and otherwise I don't want it to be so let's add some conditional formatting of this first thing what I want to do is I'm just gonna color everything blue give it that dark blue and then what I'll do is I'll add conditional formatting to make it white so everything's got the blue and then what I want to do is I want to be add some rules on to the condition for me to make only those cells white when it's necessary so I'm gonna go all the way down here as a start and then I'm gonna go to home condition for a minute I'm gonna add a new rule manage rules and I'm gonna use a multiple conditions to base this rule on so use a formula equal or right so either a few things what I want is either P let's just call it P and then eight not equal empty or again in this case Q we can use Q is out Q and of course we're not gonna use a dollar sign not the rows not gonna be absolute eight it's not equal empty or what else or I also want to know seven why do I want to do seven because I want the world after as well automatically so I'm gonna use actually P comma and then p77 that's gonna give us one additional row below cuz that's what I want one additional row does not equal empty then what I want to do then I'm gonna color those white so format fill and I want to give it a white color let's take a look at that and see how that works click OK and apply and that's good except I don't want this row so how do we avoid that well I think that's going to be avoided automatically on let's just put in a row number here okay so we need one more row what else do we not let's add additional row one additional row because I want at least to be able to skip one row for that in fact I think let's do that let's add two rows so I'm going to go back into the conditional formatting and the manager rules and we're gonna use the gross six so let's do also six so back into this row here in the Edit this rule and one more we're gonna add p6 and that's gonna give us two rows of additional which is what we want give us a little bit and then just change that to six okay click OK and click apply that's nicely that's what I want that gives us a little bit of freedom around there and good so perfect we have that that's what I want okay very good except for let me have that let's try that on the new now I'm on a new order how do we create a brand new order so I want to clear that out and put a newer that's pretty easy let's write some macros to create the brand new order we're gonna sign it all to that so we're gonna go into miscellaneous macros we're gonna write some orders for that first thing I want to do sub order new okay and again we've done that all we've we've already assigned the type so with our if we look at that we know this is takeaway we know this is delivery and I'm gonna put that right into s6 based on the shape that's selected and each one of those shapes are gonna be based on a new order they're gonna be all signed so it's very easy to write this with sheet one I want to see I want to make sure that before we click in the order I want to make sure that we're saving the new order so how do we know if there's a value here I want to make sure that the order is saved before we click in the order I want to make sure we save the existing order so how do we do that well let's do that if range p8 does not equal empty then order save update okay that's gonna save the order automatically assuming that PA is not empty okay so save it cinq ordered before clearing it out okay so now but now I want to clear everything out so what do I want to clear out again b5 I want to be we've already done that remember onload we've already cleared everything out so we could just copy that and then move on so we've done that let's just copy this and then paste it in here so the miscellaneous now we've already done that okay so but now we can add those additional features we can add that T 6 all the way through u6 so we can add those in we don't need those we don't need that information and so now we have everything cleared out okay so also on again Heidi again I want to hide those icons these icons here don't need for them as well paste them in there no need to display the edit or delete icons next up I also want to again make the order ID what about the order ID this time is where we actually add in the next order ID we know the next order ID that's located right here in to be six so I'm gonna take whatever's in b6 and I'm gonna place it right here into r6 let's do that with some code dot range r6 is equal to P six b6 okay that's the next order ID so now we have the next order ID what I want to do I want to set the order type what's the order type remember it's Karla types gonna go right here into S six this is where the orders tab where's it coming from it's coming from our application caller right here so we can do that in this let go dot print s 6 is equal to application dot caller that's gonna call the name of the shape as order time pretty cool huh because that way we can assign the same macro to all the shapes order type so now that we have that now all we need to do is I want to set the order to true I just want to send out range B one dot value equals true in fact let me make sure that we set this to false on saving if I don't think we did order save update the last thing we want to do right we want to set in make sure D 1 equals false order new order equals and usually we don't use this too much in this Trinity equals false new order to false okay we don't use it too much in this training but that's it I want to set B 1 to false so we can differentiate between new order and that okay so we have that's pretty much it that's all we need so now super simple macro so I'm just gonna copy this macro we could easily sign it I'm gonna sign that to all three of those shapes up there so again selection tool you can also use the control by the one would work okay now that's what now what I want to do is assign the macro and then paste the macro that we just copied right here click OK save our work especially before we use clear contents incase we clear too much out click new dine-in alright that's perfect that's what I want just what I want now when we add them let's reduce this a little bit so bring it over here so we can see add in some pizza add in two pizzas added multiple pizzas add in some salads see how quick it is to order add in some pasta I love pasta my goodness I'm gonna be gaining weight on this some main courses I like some chicken piccata and I want to give him some drinks I get very thirsty so I better add in some cokes and I want to add in dessert home dessert so I'm gonna have every one of these this training makes me hungry look at that now I'm gonna save the order perfect Wow look at that excellent that is a nice now what do we do we better sent to the kitchen they've got a lot of cook how we gonna send it to the kitchen let's do it right now we have multiple printers look we've got multiple printers I've got a macro that I'm gonna share with you we don't even need to created because I'm gonna share with you it's gonna put all of the printers there and then I can select which Y which printers for the kitchen which printer is the local printer and then we can print automatically just based on that so let's do that right now so the first thing I did was create a dynamic named range based on the printer list so let's take a look at that into the formulas name manager we're just gonna call it printers so it's again it's a dynamic named range based on this how do I get all the printers on my machine let's take a look at that macro that's already been created in the application macros and we're gonna get to the update this is called the update printer list and basically what its gonna do is gonna loop through all of the printers inside your local machine all the workstation everything on the network it's gonna loop through that so we've got the printer number we got the row number the workstation and so what we're gonna do is first thing I'm gonna clear out all the contents onto you 5 want to clear all these prayers out just like that clear them out then what I'm gonna do is I'm gonna loop through I'm going to set the printers all the way to the printers set the printers for the computer then we're gonna loop through all the printers now keep in mind that for every single printer there's two counts one is the printer number and the second one is the printer name so I want the printer number plus 1 the second position is the name the first positions the number so when I want that printer name and we're looping through this we're gonna look through the printer number plus one because the first one is just the number so we want the name based on the printer number basically it's gonna take that put it indeed that all I need to do is increment the row numbers so again for each one we're starting on four and then just going up it's going looping through all the printers on your machine so when I run that macro it's gonna load all the printers automatically just like that easy so now we have a dynamic named race so now all I want to do is take that dynamic named range I'll put it here and here use data validation to get that so I can select a printer now that I know the printer all I do is need to set a named range called local printer right here or set a named ranges kitchen printer right here so Bakula now we understand how we get so now what I want to do is I want to click this button or click the entire button and have this group show up just like that how do I do that let's let's take a look and zoom in make sure they're all I don't really like the border around here so let's get rid of the border format shape outline and just click no outline we don't want the border on that so and then I'll just make sure there I'm gonna move it over a little bit so that they're perfectly lined up good now they're perfectly aligned so what I want to do is I want to get this it's called the center grip and I want to get it to print to display right below this right when we click it or not click it so let's write a macro that's gonna display this group but first let's go ahead and grip this it's a little bit smaller though don't you think it's a little bit small a little bit let's spread this out a little bit so we can give it some more space and move this over okay and this icon over a little bit so we want to send order but I want to click on this and I want to have this drop down shape appear only when I click it if I click it again I want it to hide so how do we do that number this is called send to groups and to groups so let's write some code to display that automatically and then we'll assign it to this let's group this first holding down the control I'm gonna group it all and we'll just call it send order okay so now we've got a sense so let's write a macro to display that back inside and we have a miscellaneous knockers we're gonna add to this now in this one we're gonna call it sub order send - pretty simple okay so all I want to do is if I wanna do a test is it displayed or not if sheet this is one line of code 1 dot shapes send to send to group dot visible equals true if it's already displayed then hide it then but then sheet 1 dot world shapes send to group that visible equals Emma so false so we're gonna hide it if it's if it's visible we're gonna hide it but if it's not visible then we're gonna show it so how do we do that now we just need to copy that else that means it's currently not visible so else then display it and there so see true easy enough so now we just copied out but I also want to do I may want to display it if it moves maybe we can display it but it shouldn't move in other words do we need to position this automatically or will it maintain the position it should maintain the position but you can easily position it with VBA but since this is your three hour training problem police don't enough okay so now all we need to do is right-click sign that macro order send to now let's click it once it hides it click it another short nice okay that's exactly what it all is position it a little bit great so now when they click it I want to click the send to and print to the local machine or send to the kitchen with those macros how do we do that let's just write to macros very simple macros and we're gonna do it so that macro sub order print print local machine okay we're gonna print the local one and then then we'll just copy and paste it and put it in the printer as well so how do we do that dim the printer range I don't need to set the printer range as string all right we need to know the free range and then the last row dimension the last row has long okay so this time we can say that we're gonna first of all we need to hide the shapes so this one here this shape here we can hide this so I'm gonna copy and paste this right here because we've the first thing I want to do is hide the shapes we don't want to see that anymore dot hide send to crook no need for that what next I want I want to determine the last row based on Pete the last row is equal to based on P right whatever's in P that's gonna be our last row that's can help us determine what to print and what not to print set our printer range so this gonna be the last row with the value last row okay so now that we have the last row we can set our printer age to the print range it's equal to let's set that up it's gonna be P starting with 5 and fact I will P and then 5 why don't want 5 because I'm gonna make sure to include the entire table I'll include this table here table number and everything like that so it's gonna start at p5 it's gonna go all the way to you and whatever the last row is so that's gonna set up our printer it's p5 through you and the last row ok now we're ready to print so let's set up our page got page I've gotta sit with sheet 1 you do that that's important ok so now we don't need this end with we already have it end with there ok so dot page page setup what is the page setup dot print area and equals the print range okay now we're ready to print it out so again since we've set up time remember we've already set up named ranges for the local printers called local printer we've already set a printer for the kitchen printer so it's super easy we're at this point so dot print out what are we printing out well we don't need the from and - we don't need copies and we don't want to preview we can put false on that and you act a printer what is the act of printer in this case we're going to put local printer that's the named range we created local printer and then print to file no print volume and ignore print areas we want that false that's it that's all we need - pretty simple to do the print that's for the local but what about for the kitchen almost the same let's just copy that paste that down here and then of course we're gonna change the macro name can have to print send to kitchen order send to kitchen and all I need to do on this one is just change the name drench to whatever the named range we set up let's double checked it to make sure we have the name it this one's called kitchen printers so we can copy that go back into the macro and then just change the name range here printer that's it kitchen printer okay so save that now what I need to do is assign these macros to those buttons that we saw so let's go back into the restaurant and POS here this is one sent a local song and hold the ctrl down select both of those and then I'm going to click assign macro and this one's going to be of course sent to print to local machine that's good and this one's gonna be sent to the kitchen so again right click sign the macro this one's gonna be sent to the kitchen ok save our work check for any errors sent to local I've got snag it as my printer it looks like it's working fine and that's good that's exactly what I want to see very very nice I'm gonna get rid of those put some normal borders on the upper though otherwise it looks pretty good let's put some normal borders on that format the cells looks kind of funny with those double borders ok good that looks pretty good I like that and let's just check the kitchen and although I've got the same printer making sure that it works you can use any printer alright now we're sending it to the kitchen we've got a few more things to do and all I need to do is set the payment and then we're pretty much done so let's get that done and then we're gonna be done and if you're still awake I really appreciate that all right back into the same module we'll go we're just gonna create a brand new macro called order inter payments sub order enter payment someone put the change so first we're gonna dimension the last of row ass long in the order row as long we need both of those those are gonna help us out and go with sheet one of course we're focused on that and dot first of all I want to make sure that b4 is nothing I want to make sure that the user has saved the room we need to save it before we get there so we want to make sure that b4 contains up value if it doesn't let's go back to 100% here if b4 doesn't contain a value then we know it hasn't safe till we want to prompt the user to save it first so before let's make sure that that contains a value if dot range b4 equals empty equals empty then message box please save this order before entering a panic okay and then exit nothing we can do if they haven't saved and if okay so assuming that they have saved that we can now move on so first of all we can just double check let's just save it let's run the macro to save it first order save and update that's gonna run it save just in case in case any changes in case anything is not saved we want to make sure it's saved so now that it's so saved we can set the last or last row of course is going to be equal to P again we've already set it up here so we can just copy that up here we know that last row here okay so we've got the last room and the order row we know that that's in b4 right so we're gonna copy this and put the order row order row by far my longest training ever order row this is triple what our normal time is now that we have our order row we can let's create a payment where do I want to put that payment and I want to put an input box I want to put that payment right in if we know the last row is is going to be this where's our payment gonna go our payments going to go we need to actually create an additional payment let's put it in now I want to put in actually payments gonna go right in here and queue in the last row queue and the last rows where our payments gonna go so let's do that so dot range q and the last rope value equals what let's create an input box so they can do it equals input box and what should be in that input box let's first give them a prompt please enter a payment amount okay so now they've got the payment amount and then just give it a title enter payment that's gonna prompt them to enter a payment okay so now that we have that and what is it gonna equal and that's it okay so now that we hit that's the input box that'll put it in queue in the last row so what what I want to do is to make sure that we have our formulas we'll double check that I think they're all gonna be already in so we have our foremost our formulas looking good so let's just say the internet payment in this case I'll let's say 200 not some expensive dinner okay of 200 we want to make sure that this equals them we want to make sure that the change we want make that this change get equal whatever is the payments they've entered in fact let's add that in here so the change is going to be we're gonna put the formula into the change so T and the last row is going to be equal whatever's in here whatever is in T in the last or less one let's put in the change okay so how do we do that dot range T and the last row we know it's still last row dot formula equals again what's it gonna equal in this case equals Q and whatever they just entered Q and the last row and what else and quotation marks minus T and the last row minus one and the last row minus one so that's gonna what that was that's the total that they're gonna meet you so payments minus the total so that puts in our formula all right let's take a look at that and see what else we would want to do let's make sure to save it and let's assign a macro to that payment so we've got our payment up here and fact let's bring it over here a little bit make it look a little more centered bring it here and then hold down the control we're going to group it and we're gonna call this payment group and we're gonna sign a macro to it so assign the macro and that's the one we just created located right here under order inter payment so save our work and let's click enter payment enter payment let's say 200 and click OK and scroll down here alright that looks good we just want to format this let's let's format them all we don't know if it's text it's okay format and then the currency up here ok that looks good now we've got the change calculated and let's save the order and let's take a look in here and order number 7 and let's take a look under the orders we've got the payment order number 7 we do need to make sure that we save the payment and the discount information that's got to be saved ok great now all we need to do is save that information here so we need to save the orders I want to save the discount and the payment and G and H so let's do that right now let's add that additional tutor code so we have here in Japan so we can do that with the following lines of code sheet 3 dot range G and the order row we know the order Rope order row dot value equals what is it gonna equal dot range Q T so in this case G what do I want what do I want to put in G I want to put in the discount where's the discount located that's located in the last row this is the last row - - located in T then let's move these over left justify those here all the way over here and I'm gonna put a left justify everything in that column I'm gonna left justify everything in that column right here I want it over there that's what I want right justify everything in this column here because I want that right justify the hell looks good that's what I want okay so now we need to do again I want to save whatever's the discount and I want to save the payment information here so save the discount save the payment discount is located in T and the last row - - so let's write that up equals T and the last row - - and that's good that that's the discount and the last thing is the of course the payment amounts let's just copy this and put in the payment amount and this is the payment amount but of course the payment amount is actually that's in column G h's we're saving it - and where is allocated the payment amount of course is located right here in queue and the last row that's pretty simple queue and the last row no need to change the row on that last room that's the payment amount okay so let's save that and now let's just into the payment once more time payment put in 200 and okay good so everything works out good let's look in the orders now we have the payment there is no discount I guess we should enter a discount double check it let's just double check it five let's enter the payment again and make sure 200 okay so now we have the discount accept the discount disappeared we got to fix that we just need to write a little bit of code for this when we make a change we want to make sure that that discount saves right here in there so let's write some code on that to do that so we're gonna go back into the our change in here we're gonna focus on the change when we make it change let's do that so we can do that let's write in save discount if not if we user makes a change to T let's say T eight of course there's a few conditions through t 999 9 is nothing then I want to make sure that it's actually a total road then what then if range s and the target row make sure it's a discount I wanna make sure that's the only kind of target row dot value equal and let's put discount okay if it equals that and I want to make sure it's a total row and make sure it's not empty and T and the target dot world dot value does not equal empty then we can do it then we can save it now sheet three dot range number G is the column or saving of G and the target and what row where's the road the rosin B for range b4 that's the order road dot value dot value so what are we gonna say they're equal simple the target value target dot value how does that work let me show you how very simple okay and we just we've got to get out of this this we don't want an inside this loop let's clear this up and clean this up a little bit okay let's run it again and ten dollars make sure that's saved now on our order from number seven that's what I want perfect so now we have it all ready now let's see if it's loading right I want to make sure that that loads in again put in seven here load that in discount needs to load up payment needs to load and the change in so let's load though okay back into that now this is our load Macker so after we load our totals we can just load it from the database again so how do we do that well we know the let's get the last row again last row equals again dot range p9i 9p9 a nine and and dot and x a lot dot row okay so now that we know the last row we can easily insert the information we know we know our order number we've got our order row here so we know that and that's coming from here so again we can just continue on and we want into what do we want to enter well of course I'm running into the payment number that's gonna be queue in the last row so dot range cube and the last throat make sure we've dimension the last row up here I want to make sure that it's here last row here good okay so last row what is that equal equals sheet 3 dot range it's coming from our orders and that's going to be located right here in for our payment H is going to work it's going to come that's where I want our payment to show up H and our order Rho dot value that's the payment and then of course the discount dot range where's our discount going to go our discounts going to go right in to our T and the last row minus two T and the last row minus 2 dot value equals and this segrin it's gonna be G right it's coming from G that's where it's gonna go all right coming from G that's gonna be our discount now we've got everything loaded up discount okay let's take a look I'm gonna save that and let's rewrote load that I'm gonna put in seven again here load seven and see now we've got our payment now we've got our discount and the other thing is we still need a formula for the change we just need to add in that formula and I want to add it in the formulas so we can do that that should be lo loaded in side a macro called order add totals so we're gonna have that right in here under the save here and add totals here so that's just one last formula that we need to add in is our change formula okay so let's do that dot range and our change form would be T in the last row now we can focus on T in the last row in this case is just T and the last row in fact we wrote this formula ready but we just need to write we wrote it when we wrote added the payment in its we dot formula let's just just copy it where we wrote it before so we don't need to rewrite it again formula and where did we wrote it on the payment so that was in the miscellaneous macro most of what is gonna be equals of course it's going to be our total our total where's our total our totals located right here again equals in the quotation marks equals Q right let's take a look at the formula right here just so we know equals of the payment minus the total right so what is that so that's going to be equal again all we got to do is copy that and determine the rows so we copy that equals Q and quotation what is 28:28 of course is the last row plus four so we just need to copy this the last row plus four that's going to be equal to and 28 and that's our plus 4 and then again change 27 to the last row and the last row instead of minus 3 right the last row T in the last row so let's give that a try let's click add the totals ok so now we're going to run that code let's take a look there we go ok let's do it again so now now we've got everything here just the way we want it let's run that code here inter 7 now we have everything we need down we have the change we have the tax we have the total perfect just the way we wanted it so we have our our 100 we have our total is going to be again 189 44 minus the 10 percent discount giving us 179 which paid 200 for our expensive dinner and our change again is 20 56 that's exactly right ok so we've got the payments we've got the send order we've got the save order let's add the pop-up keyboard so I'm gonna hold down the control group that this is the cool I'm going to call this keyboard group now I've written a copy actually I've copied and pasted some code I found on the internet for great keyboard no reason to redo reinvent the wheel into the application macros keyboard open I've got a brand new macro that runs that and it's done all with this update the printer list so and it uses this code as well so just keep that in mind and so all I need to do is take this particular code and then assign it to our keyboard and that's gonna give us our really cool pop-up keyboard right click assign the macro paste it in and click OK all right now let's click here and our pop-up keyboard now shows up here it is very very cool so we can easily type in anything we want I know this is gonna be song you can even make the keyboard smaller if you want just by grouping it here it even shrinks down really nicely that's kind of cool so it shrinks up it's a really beautiful keyboard right thank you so much for those of you that stuck around on this incredibly long training I'm gonna take a two weeks off and focus on my marketing and we'll be back with some amazing training thank you so much I think we covered everything in this training to create a brand new point-of-sale application if you like this training and you want me to continue with these Freed i always appreciate your support your likes your shares your subscriptions and of course your purchase of the workbook packages really helped me out a lot thanks so much and we'll see you soon [Music]

Channel: Excel For Freelancers

Views: 529,443

Rating: 4.9490447 out of 5

Keywords: Free Excel Training Course, 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 Touchscreen POS

Id: 1xyvioUP8Ls

Length: 190min 15sec (11415 seconds)

Published: Tue May 26 2020