How To Create A Powerful Point Of Sale (POS) Application In Excel [Full Training & Free Download]

hello this is Randy with Excel for freelancers and welcome to the point-of-sale application in this exclusive training I'm gonna show you how we create an amazing point-of-sale application we're gonna start from the beginning from in a blank sheet so every step every line of code and every field and format I'm gonna show you how you can create an amazing application right before your eyes so let's get started all right thanks so much for joining us today I've got a really fantastic training for you today something I've never created a point-of-sale application I'm gonna walk you through it I hope you'll stay it's gonna be a little bit of a training not tons of code more formatting we've got a lot to cover today I'm really excited about this before we get started I want to make sure you know that you can get this application download it using the links in the description below absolutely free in fact this one's gonna be a zip I'm gonna include some pictures for you maybe some bar code fonts the application so you won't want to miss it make sure you download it and also especially with these live trainings I tend to make maybe there's a few updates or a few issues worth the live so you always want to download my version and compare your version with my version I always appreciate when you try to program these yourself and I think it's really fantastic so you always want to compare those if you have not subscribed yet please do so using the button down below and make sure you click the notifications belt I create these trainings each and every Tuesday absolutely free for you and I love to see what you can create so I always appreciate that if you do like these trainings and you love Excel you want to turn your passion into profits I have just the course for you in fact our Excel for freelancers mentorship program is gonna teach you just how to do that turn your passion into profits in fact in this program we're gonna go from concept to cash and everything in between in weekly videos while I create an amazing accounting right along with you and I hope you'll create your own applications right along these are weekly subscription-based it's an amazing course and I hope you'll jump on the bandwagon tons and tons of students are loving it and learning how to create and sell their own applications alright great glad we got that out of the way and also if you do like these applications and you want over 100 of them I do have a 100 workbook pack so I'll keep that in mind I'll put the links down low if you want to get 100 of them it's just $37 okay thanks great let's get started as you can see I've got a blank sheet here nothing on here I've got sheet number one called POS that's gonna be our point-of-sale application I've got another sheet just with a few item names and these are linked to pictures I've got some pictures for each of these it's just simply item ID item name item description price and an image this image is linked to a folder and inside that folder I've got some images here as you can see right here I've just got some pictures and they're basically they're linked actually on my desktop I've got a folder of the same pictures on my desktop I'm include these pictures so you can play around with it as well so you can work you don't have to create your own pictures that way you can try out and add features and customize yourself so I'll include these in this little table so you have everything you need all you'll want to do in your own applications is make sure that you update these links with your actual filings right these are to mine so that's not gonna help you these image links so I want to make sure you get yours and then of course the sheet 3 is gonna be where we're gonna put our sales I want to track all the sales that we've done previously that's important as we create this so XI 3 is gonna handle this so again this is gonna be our main application where we're going to in turn print receipts items here and then sales here so let's get started as always what I like to do is I like to save the first two columns for admin and we hide those columns so let's color those a little bit differently and while we go into the home I'm just gonna call it those gray I always want to hide those columns and keep those for admin so that's a great way to do that next up I want to give it a name let's just call it something like point of sale and that way we'll we can color the columns let's say all the way through oh and then we're gonna save some room for receipt so we're gonna format these cells give it a fade so maybe a fill fate I'll use something a little bit different than I usually do if you're familiar with this bed it will just take a minute and watch some times repetition helps so even seeing it over and over again helps and then I'm gonna code the one below a little bit less of a fade and then we'll give it a fill effects a little bit lighter of the turquoise color so we'll go to here and then I want that fading into the background the main background color so let's call that I'm also going to clear out some of those rows with some for the receipt so not everything's going to be colors on with the background or the receipt and the background is let's say K through N K through and I want this to be our receipt so I'm gonna actually make that white and so color that white because I want our receipt to print out and then we can extend this con so basically the idea is to have our receipt here and our form here and then this column will just be for space just so that you can extend this off a little bit so that way your screen is all the same color so we'll give it a font a larger font and I'll merge and center it maybe to J and so we have a little bit of a larger font and we'll increase the font maybe to about 32 or something like that it's something nice and big oh that's too much okay all right that should be good let's go with 32 I like that a little bit better and we can then add some icons in here and add some shapes what I want to do also is I want to add an item name let's say in three item item name I want to put that in there cuz that's important and then I'll put the description down here and then I want to picture also so I won't put in that picture so we also have I also want to know the subtotal of that so let's put that sub total here cuz I want to keep track of it and then the tax I want to put that there total I want to keep that pay type I want to know what type they're paying with and payment and then change also I want to keep track of the change as well okay actually I want them all in the same column so just copy those over and paste them here I want them all in the same column then I'm I'm going to then have a space for each one of these so we'll block those out and then keep that in mind all right so that's basically what I want I want to keep track of that so all right I'll put a border around that I'm gonna format those cells and put a border let's say the same darker turquoise color and we'll use a border inside and out border and I'll use this dotted line for the middle okay so we've got that we got a total and I'll write justify this so that we can see that and then I want to add codes I want consistency so let's add colons on these here pay type and then payment we want to keep track of the payment and then we want to keep track of the change really there's only the pay type and the payment are going to be user entered the rest are gonna be calculated so let's put these in the background as white okay I want that probably put the item description right here so let's put a box around that format the cells again we're gonna use the same border here same color consistency all the way around and then I'll put a dotted line here at horizontally because I want that description to go let's put it solid light actually and I'll put the dotted line here so we want the item name I want to give it a little bit larger space for that so I'm gonna merge and center it and left justify it I want the item name here in the item description here so I'm gonna merge the center that too and let's give it a little bit more space for that and right justified okay so we've got the item name we're gonna put the calculated information here and then I also want the course the price of the information let's go put that in the sick price I want to show the fresh ball I'll be able to change that price as well so we're gonna put that as white and I also want the quantity we also want to change the quantity okay key here so both of these are gonna have the same formats both of those are gonna be editable color those white there and we also want to make sure that they're both right justified and then in this case we can keep those let's say right justified as well all right so we're gonna format those cells pretty much using the same borders that we have the same border colors that we do throughout the application and we'll use the same border and then I want the dotted line in the middle vertically okay so it's starting to come together now let's save our work and I also want some to scan the item of course we're gonna be scanning atom that is important so we're gonna put that here let's just put scan item here cuz I want to put a little bit large in case it's a in case it's a larger number and then left justified that's put in a colon keep it consistent see we like consistent here and then right justify that and do the same thing here so let's make this white and again put the same borders around that formatting the cells with the border I thanks for your patience on these while I format these but you seem to really likely to watch me create these so based on your views I'm gonna try to do a lot more than that alright so we have our basic I'm gonna put the picture here I want that a little small picture here and I'm gonna put a button set here and then I want to put a button set here as well the receipt information will go here and this will be just a little spacer alright let's add some buttons in here so I'm gonna insert some pictures here and see what we got I've got some saved already so I'm just gonna insert those they're gonna be bigger siliceous bring those down to points there's something manageable so that we can see those and then we'll size them accordingly those are the ones they're gonna go on the button so we'll have to bring those to the top but we have a this is an icon here that I'm going to use for the entire point-of-sale application so I'm gonna drag this up here make it a little bit bigger so it's obvious and then I'm going to added more of a theme color which would be this so get a little bit more theme color we can also add a little bit of a shadow on that so we can click pictures and just add a little bit of a shadow so it kind of looks nice make this bold and we can also add a little bit darker color on to this okay so now it's kind of a nice we get to see that so we have the item name but let's add in some buttons here because I want to make sure that we have the button so the user can actually use a touchscreen which is kind of a nice so we're gonna insert shape or just can use a square shape in here okay let's give it a size try a little bit bigger point six and maybe a width of point seven okay so that's kind of a nice size for that we have a nice big button there so that's going to work and I want to give it something closer to the theme maybe this okay that looks that looks nice you want to actually give it a font larger font so I want to put it make this button number one and then I want to Center that of course and then we'll duplicate it but it's obviously it's gonna be a lot bigger than that so how big should it be probably about let's see thirty-two maybe let's click the button and click 32 here okay that's given a nice button there we go that looks good nice and big and then we're ready to duplicate that let's duplicate it control D and then oh this is gonna be for two and then we can duplicate that again and we'll click on there duplicate that this will be for our number three nice and then we want also of course number four so we'll duplicate that control D because I want one for each one and let me just go ahead and do these I'll pause the video while I create each one you get the point so I'm gonna make this a little quicker okay now I've created all twelve buttons here so let's now that I've created all twelve buds I'm gonna name them and very important to name them at least yous gonna be four letters at least minimum four letters and a number so if I were to if I were to add let's say I wanted to do but1 that wouldn't work why wouldn't that work because that's a cell so if I hit enter it's gonna go right to a cell see that but1 so we want to make sure that we have at least four letter so let's do that b ut n one button one okay so that's gonna work just fine and I'm gonna do the same thing with each of the buttons there's no need to watch me name each one of them so I'm gonna pause this video and just name them all button button - so hold on a second while I do that okay I've named all the buds at least one through nine this is gonna be zero this is not gonna be zero I want this to be clear I want to be able to clear bug so let's type that in clear and then we're gonna have to obviously reduce the font size on this one so let's go ahead and reduce it down to where it'll fit in the cell here let's drop this down here and reduce that down scroll up probably okay that one looks good we can go a little bit higher and then I'm gonna underline it control you alright great so we've got that we've got our basic format buttons there now so let's line them up all we need to do is use our selection here and then I'm gonna line them up like this format and then we're gonna align to the top and then I'm gonna probably bring them a little bit closer in now I've got a lot of to cover here so I want to bring them closer in here and then also importantly we're gonna group everything together once we're set up so there we go that's look a little bit tighter here so we can I want a lot of space I want to add buttons here too so we're gonna keep that a little bit closer together okay good so now we've got everything nice and close together and I'm gonna line the rest of these up and there's no need for you to watch me do that because it's kind of boring so I'll hold on a second I'll pause it while I line everything up nicely and then we'll create some more buttons alright now that I've got everything lined up I want to got a few more things you don't want to name this button obviously clear this one's gonna be different now the different purpose clear button and I also want to add this one it's gonna be a little bit different it's gonna be called decimal button cuz it's gonna have a little bit different macro alright okay excellent we've got a few more buttons to create I want to create some different payment types so let's do that let's create some buttons I'm gonna just duplicate one of these buttons using ctrl D and I'm gonna shrink this down the width because I want to fit more in here okay maybe 0.5 because I want to put three buttons in here the same size and there's gonna be no text on this what I'm gonna do is I want to put a payment to type in here so I'm gonna do I want actually three payment types I'm gonna duplicate this three times total of three buttons and move them over here because I want all three payment types and then all right we can line those up and distribute them just by selecting them control and then formatting make sure we align the top here and then we want to distribute them horizontally that looks nice all right and then I also want to make sure the bottoms are the same as these I want to make sure those are all consistent so let's line up the bottoms or the tops either one would be fine okay so we've got those now let's add some payment text and the first one I'm gonna do is cast so I'm gonna put that icon right here now let's bring all let's bring all those to the top first so what we're gonna do is we're gonna select on all of them because we created the buttons after spring forward bring to the front okay so now they'll be on top of the buttons we're just kind of handy so cash we have cash we have a credit card here I want to bring my credit card as a payment option and then the check option also so we've got those three options here we can line those up nicely so that everything's group properly format and align the middle right here okay so everything's lined up nicely and distributed align I also want to distribute them horizontally so everything's centered and we can group those very soon but I want to create a payment button also on this so let's also duplicate this one I want a payment button and that payment button is going to be larger but I want the same height as this so let's align the top this is gonna this is how you create very buttons put really quickly and easily okay and what do we want to give the name I want to call this payment but we're gonna have to reduce the font so payment and then we're gonna reduce the font so that it fits and I want right justified because I'm gonna add an icon on to that so let's click the button itself and then lower the font here and then I want to increase the font obviously it's gonna be a larger button and I'll give this the width maybe around one point that should be fine width that should be okay as it is and then we'll move that over here and then I'll line the left here because I want to make sure everything is lined up align left and then click on here and then we're gonna align the right to this button that way everything lines up betterment create three more two more buttons aligned to the right okay good so now we've got everything centered and let's add an icon for this so we can put in an icon there can probably increase this font we created that button after salutes again moved to the top bring to the front all right so let's that's it looks like a good I'm going to create two more buttons ctrl D and then I want to bring these two also because we're gonna add an icon to bring those to the front as well alright and this one I'm gonna be next NEX T because I want to go to the next record so we'll use that and I also want to print record we'll increase the font here just temporarily bring this all the way down here and then I want this to be our print button so we'll capitalize and then we're gonna increase the fonts on both of these buttons so holding down the shift I want to increase those just pretty much larger that's pretty good something nice and large okay good now we're getting said we got our button set and what we want to do is we want to group these start grouping these let'em so I'm going to hold down the control and group those and then I'm gonna do the same thing here and I'm gonna group those this is our shortcut for grouping and I'm gonna move this one a little bit to the left and group that because I want those always grouped I also want to group these button sets so I'm gonna do my selection tool and I'm gonna highlight everything because I don't want it and I'm gonna group it and importantly right after we grew up what we want to go to the properties and we want to make sure that we don't size it we just spent a lot of time making sure everything set up but we want to click move but don't size ourselves in fact that's gonna go through all of our buttons once we get sick because when we change these columns I don't want these buttons sets to move but I want to make sure that everything is set up right so we're gonna bring down and make sure those bottoms are lined up align the bottom okay that looks pretty good and we can bring the font so now we've got our next button we can group that and we've got our print button here will also want to line that up okay good that we can bring this up a little bit more looking good now we've got our POS coming together very very well I'm gonna group all of the buttons line them up in the middle so everything looks great and group that and make sure we also group this one line them up everything's going to get lined up and then group it and make sure of course after we group everything everything's not grouped we also want to make sure that we are moving but not sizing that's very very important so we're going to properties move but don't sighs okay so we've got this named and set up our point of sales coming together we're gonna put our receipt information here and looking very good we can align those alright let's get into the naming right now so we can name everything properly so let's put this as the next button and heat n EXT button and this will be our print button everything should be pretty clear make sure you always name it so you know it that way you can see it on the shapes print button and there's specific these are gonna have specific names and I'm gonna show you why because it's gonna be a lot easier to create macro so I can use the same macro for each one if I use specific names not necessarily the group but the individual shapes so let's take a look at this I'm gonna name this let's go into the selection pane it's much easier to work from there and you see the selected what I'm gonna call this is just cash and I'm also gonna sign the same name generally we don't but in this case is gonna be very specific cast I'm gonna sign the same name to that and also I'm gonna do the same thing with credit card I'm just gonna call this card and I'm gonna call this card now we're gonna use the same name because we're gonna turn this name automatically into the payment type and we're gonna do the same thing for check check just so both the picture the icon and the button are going to have the same name okay great so we've got payment we've got this everything's perfect and let's name the payment button and then we're done that we can start writing some code but and I also want to payment payment button okay so we got everything name we're gonna put our picture here we're gonna put our subtotal here and put our receipt let's start working on this receipt I want this gonna be Merchant Center and I'll call this sales receipt and then let's make that a little bit bold and bigger of course that would be good and then I'm gonna put down a also want to merge and center this and the next few lines as well because we're gonna put in some information there that we want merge and center those and I want to put probably the name of the store let's call it Fred or rosary's my favorite name groceries and I also want to put the address one two three four five Main Street USA and then probably a phone number here all right so next up I also want to know the receipt number here so I want to put that in here receipt number is very important because we're gonna keep track of that and then the next up I want to know the date also the date of the receipt and the cashier and we'll just put in Frieda Frederick's that's Fred's sister Frieda Frederick a and then the date I'm gonna abuse a long date here also on emergence enter these holding down the control merge and center merge and center and then left justify it and then these are going to be right justified so we can see our receipt come to life here great so what's next well I'm gonna skip a line and then we want to put the information so here I want the item here the quantity the price and the total great and I want to Center those and then put a dotted line above that and below it so we're gonna form out those cells format and then I'll put like a dotted line something like this above and below so it gets kind of a look of receipt that looks nice and then our items will go here so Apple here would go here the quantity one and then the price would be let's just say $2.99 and then the total be probably equal to this times this so just to get an idea of where we're going there or that okay so let's focus on the formatting now I want to format this entire one probably as a none because we want to use generally could be 1.5 and these are gonna be price columns so let's just format all those down there as a currency or format price okay and this one looks good the way it is I like I like the way it's looking now and so all of our items are gonna go here and then I'm gonna want probably things like subtotal and total down here so I would want to put something like at least temporarily like total here tax your total would be here paid amount I want to know the amount they paid and the change so those are all important I want to know all of those that information is gonna be below the sheet now it's gonna be dynamic right so we're gonna put this in dynamically because we don't know when it's gonna end we don't know how many rows so that would look good I want that there and then also want something like thank you for your receipt but it's gonna be dynamic so I'm gonna say something like thank you come again and then a barcode here let's put those in and using text because I want them standard around there so many she click shape and insert insert a text box here I'm gonna make it about the same size as the width of this receipt and then I want to put in a message so something like thank you come again something like a message and then we'll Center that no borders I don't want any borders around that so just wanted it no outline on that so we'll just click no outline make that bold so we can see what it would look like and okay good I like that and I also want a barcode font here and it's gonna be based on this receipt number so if I enter receipt number one zero zero zero I want a barcode based on that so let's enter another text box in fact let's just duplicate this one using ctrl D but I'm gonna change the font and this one it's going to be equal to whatever is going to be that receipt number here so equal that receipt number so that's what I want good but now I want to change the font I want a barcode font on that let's try code 128 obviously that's too small so let's increase it a considerable amount and then increase this all right I like the way that looks there okay so we have thank you and we have a nice barcode font here that's a good footer message and let's put that lower here and then we can raise it up a little bit nice so we've got those two and let's make them on top of each other and we're gonna Center those format align and I'm gonna put line the middle and I'm gonna group those because I'm gonna use those basically but they're gonna be displayed dynamically because we don't know how big this receipts gonna be so let's group them and always when we group format the cells right format the shape in this case and go to properties and move but don't sighs we don't want that moving we want ed say we mean we may size it accordingly a little bit anyways accordingly just to make sure and I'm gonna give this a specific name calling it footer group okay so now we've got a footer group and now we got this is going to be dynamic of course and I want to put some conditional formatting in here as well all right so our receipts starting to come together I like the way it looks so far we've got a lot more work to do what do we want to focus on some information based on the items what items we've selected I also want to know when I select an item I want to know what I've selected so let's add in some additional information here I want to know when the items gonna load what I want to do is I want to select an item and I want that item information to appear here I want the item name the item description the price and the quantity to appear here when we select it when we enter a scan an item I want the number that we enter just to automatically enter here and then automatically add it down here and keep adding and adding and adding so but I want to be able to edit existing items as well so we need to know when the item is loading meaning the information is coming from here and going into here so that's important as well I also want to keep track of the totals here so we need yeah so I want the item load I also want the item row I need to know what row the item and that's based on the row here then the database I also want to know the receipt row or seat row that's the road that we selected here and I also one more thing I want to know the next receipt number see team number I don't want to know that because that's important I want to understand which is the next one so we can load up so they're all unique the numbers and then we'll highlight those give those a distinct look and feel so that we can see the difference okay good so we're set with that that's all we're gonna need as far as that and let's go ahead and name some information create some named ranges now for the items I want to create a dynamic named range for the item ID because we're gonna need to locate that so that's very important so let's go into the formulas name manager and create a new actually let's highlight this let's make sure we include the header name manager and then new what I want to do is create an item ID but it's gonna be dynamic so we're gonna have to add offset in here let's copy this we're gonna use it in a moment and then we're gonna do is I'm gonna type in offset and I want to include the header but only because if there's no data I want to make sure that doesn't create an error so we're gonna include a two but then we're gonna move one column down and then comma comma count a I want to know all the text in that in that whole column of course I want to increase in more than twelve so we're gonna go let's in nine nine nine nine just in case we have a lot of data and of course since we're counting the header we need to subtract one minus one comma one which means one column let's tab out and tap back in to make sure that the dancing ants cover our data which it does it's perfect so I'm gonna copy that entire named range in control C and I'm going to click OK I also want another one for a item name so let's click new click item name I want to named range there and then I'm gonna copy this and I'm gonna paste it now all I need to do is change the column so I can change a from A to B and then we're set so I'm gonna put this be just in three different places B and then B again tap out tap back in make sure that it's covered okay we're good to go and I want one more new item description I'm gonna follow the same procedure in fact we're just gonna paste it in there and change a to C that's gonna quickly be able to create three different named ranges using offset tap out tab in make sure that it covers our data and click OK alright now I've quickly created three different I also want to create a named range for our receipt numbers in our sales our sales page is gonna have this just call it sales history whatever we want that's fine and inside that sales history I'm gonna put some information I want to know the receipt number so that's important I also want to know the date that the receivers made the cashier who rung it up and then of course the item the quantity price and the total so I'm gonna want all that information here it's gonna be just a basic table so we're going to merge and send to that here and then just give it a basic color it's not so important the formatting of this one okay so but what do I want I want to know all the receipt numbers so I want to make sure that I can have a dynamic named range for the receipt number so let's do that let's create one more dynamic name and formulas name manager new we'll just call this receipt number and of course this is gonna also use the offset so again we're gonna say I'll set using the sales a two through a and obviously you can much larger number so we're gonna copy that back that up comma one means one column we don't want to start at the header comma comma County I want to count all of them again paste it in but increase that made a larger case we have a lot and then again minus one because we need to exclude the header row me including the header so there's no air when there's no data combo 1 and then close parenthesis tap out tap back in make sure that it covers good so it does all right so we've got we've got our named ranges now covered so now we can add in a little bit of a formula so we understand we see our item numbers 1 0 0 1 1 0 2 ok good let's focus on some of these I want to know the next receipt number so how do we find that what we just created an extra seat let's see if we have any sales we have 1 & 2 but I are just going to be probably like 1 0 0 so let's put in some larger ones I want because I want a big barcode font so 2 1 0 0 and then let's just say 1 0 0 1 so our next I want to know what the next one would be so we can do that using if air and Max equals if air if in the air because if it's an error if there's no data we also need to set in initial receipt number so we're gonna include the max and the Max's of the receipt number what is the maximum of that plus one that's gonna be our next increment plus one but what if there's no data if there's an if there's no data at all I want to start it off and let's just say 1000 okay let's take a look perfect one zero zero two now as soon as I delete the data as soon as I delete these two it's gonna get should go to 1,000 so let's go back and check perfect that's what I want 1000 1000 here so we know our next receipt number I want to know the selected rows the user makes a selection change I want to know the row and I want to highlight that row and I'm gonna make sure there's data so for example if they click row 10 I want to highlight that I want to know which row the user has selected so we can add in some conditional formatting let's click on the first one and then go way down here just to there and then what I'm gonna do is add some conditional formatting so go back into the home conditional formatting manage rule so I'm gonna create a brand new rule and use a formula what is that firming and based on based on of course B 10 so it's gonna be equals B 6 equals row open and close parenthesis now I want to give that a specific format based on our theme so let's go into the fill fill effects and I'm gonna choose this darker color here but I want to make sure to give it a font so I want to contrasting so we're gonna make it bold and make it white it's gonna give it a contrasting so that we know what they've selected apply that make sure the form applies to click okay all right so when they click here I want that to be hot and I also want to load the information that's what's gonna happen here and I want the item name here to appear here and I want the item description to appear here all right so we've got the item name how do we know I want to know the item based I want to know the item wrote based on the scanned item information so if they entry 1 0 0 1 which of course is our first item Apple I want to make sure and that starts on of course I want to know Row 3 when they enter 1 0 0 1 I want to know it's Row 3 we have named ranges but they start at Row 3 so we're gonna need to add 2 let me show you what that like so equals if they're always wondering if they're cuz I don't want to make sure we're gonna match is what are we matching I'm gonna match look up this value one zero zero one and I'm gonna match it based on the item ID and I want an exact match so that's going to be zero in case there's an error I just want MP to show up but I don't want one right one's gonna show us our first row I really want the row so we always have to add two because I want to know the road not that it's the first item so adding two is gonna do that so now we get three four one zero zero one and if we change this to 1 0 0 2 we are going to get 4 which is exactly what I want alright so we've got that this is going to be a true or false here because what I want to know when the item loads so now that we've got in that's all we need for that I want to know that item description we can use that based of course on this name so we can use an index for that equals index what are we indexing I'm gonna index the item description which we have here what are we gonna base it on base it on the row we know the row is gonna be here we can also base it on the name the match let's match the name so that way no matter what we select we can no match we're gonna look up the name and we're gonna base it on the item name here item name and then we want an exact match so that's gonna get us the rule the row in this case we don't have to add because we're indexing the same item column comma 1 is a column and let's just take a look at that ok granny smith apples now that creates perfect it looks just right that's just the way I want it we've got apple and if we change this to strawberry handpicks from ok great so we've got the description for strawberries and based on the name that's what I want why do I want that because whether they enter this or whether they select it I want the description to show up here perfect and the price is going to be dynamic based on whether they enter here from the database or based on whether they select here the price so there's going to be two different prices the price if they enter here the price is going to come from right here but if they select on existing item the price is going to come from here and that VBA will handle that let's focus on the subtotal now because we to make sure we get the totals right in the taxes alright so that's gonna be helpful the subtotal is basically going to be a formula based on all what's in this column but I can't just total what's in this column because I also have the subtotal of tax so I have to use the sum if it's got to be based on something some if there's a value in column K then we can sum it so let's use some if for that equals some if and what is it we're gonna sum if and it's gonna be based on this sum if K we'll use a large number I want to make sure that that contains a value before we sum so what is that sum then we're gonna use quotes does not equal course so that's gonna mean as long as it's not blank what that means and what's the sum range I'm gonna sum this right here okay great so I'm gonna add again that to a large number 999 cuz in case we have a lot of numbers so perfect so that means if I add figure here and I add a some kind of a description here again Apple it's gonna add in but if I add something here it's not going to add in right because there's nothing here so that's exactly what I want adding only in the numbers if they're values if there's a value in column cave perfect and the tax is going to be equal to let's just say this times point 10 let's say 10% tax and then we can also format these let's format these dis and disks actually not the payment but that's okay format this okay and the payments gonna have to be text because we're gonna be using that based on a entry so I'll show you what reason for that is in a moment okay so we got pay type that's gonna be based on this total it's going to be equals the sum obviously of the subtotal and the tax perfect okay great so we've got that all right that looks good that's just the way I want it however this information is gonna be dynamic right I don't want it to be here it could be here it could be here we don't know where this information it's gonna be dynamic based on the last row so one of you is I'm going to copy that I'm gonna place it somewhere other than really where I want and then we use VBA to place it back in so I'm gonna place it right here and then what I'm going to do is I'm going to based on the total number of items I'm gonna place it dynamic in the road below this is also going to be placed dynamically too so we can place it here so here basically what all I need to do is say equals the subtotal equals the total tax and then equals write the total and then we're just gonna paste the values is there and the paid amount against again equals also in fact this one I'm gonna do through VBA but I'll show you that in right and then equals tochange okay great then all I need to do is copy and paste this based on the last row all right so what's next so next up what I want to do is I want to start with the VBA we're almost ready everything's looking really good we have our screen all the way updated I really appreciate your patience on this I'll put the picture in here dynamic let's get into the VBA and start adding information here so we can get this working and get you a POS that's going to work all right into the VBA developers Visual Basic as you know there's no code let's add a module here so we're gonna insert a module and we'll just call this POS macros using the properties and we'll just call it P Oh POS macros any name we'll do and the first thing what I really want to do is what a user makes change to e 10 I want to add the information all in here and I want to add it in the first available line here so I want to add it in make it really easy so let's create a macro and call this add item so we can get rid of this and we'll go sub add item all right and I also want to know the item row that's really important so we're gonna dimension the item row as long and I also need to know the available row which is the first available row so available grow as long as well okay so the first thing I want to make sure is that b5 contains a value right we cannot add an item if we don't know the item row that's really important right it can't add the details I can't add the price of quantity and the name if I don't know the row so if this is a bad information if it's four five five right there's no item this is gonna be blank so we need to make sure that item contains an actual row so let's go ahead and make sure that we put in the check in VB that's the first thing we want to do you can reduce this a little bit so we can see both screens while we have them up okay so first thing with sheet one that's what we're focused on if dot range b5 equals empty then exits all right nothing we can do okay so nothing we can do if there's it's empty so we can no next up I in case there's any picture here any previous picture I want to make sure to delete that so we need to write some code so we can delete any picture as we're loading new items if that picture doesn't exist it's gonna create an error so we want to wrap it an on-air resume next dot shapes item NIC dot delete right we want to delete that and then on air I use autohotkey to automatically type it's a free software autohotkey that's what you'll see me typing really fast it's automated okay so next up we're ready to set the item row we know the item row that's in b5 don't we right so we can copy this just write equals b5 item bro next up I need to know the available row where are we gonna put it I need to know the first available row in this case it's going to be 12 so we can use that using K and Excel up plus one so we can write that in the code so the available row is equal to dot range and then we'll just use K let's say nine nine nine dot and Excel up dot row that's gonna give us our first available bro okay good so now that we have our first available bro we we can set the receipt row I need to know the receipt row what is the row in b6 I want to put that in b6 I want to know what row remember we added conditional formatting for that right so if this changes to 11 I want to know what row that we just added so we keep that in mind I want to know the wrong put that in b6 so dot range b6 dot value equals what is it equal it equals of course the variable we just said sheet 2 D and the item price available row set receipt row great so what do I need to know next next up I need to know I want to put in the item price and the item name so let's start with the item name dot range III I want to add in III III is right here I want to put in the item name right here that's gonna automate the description because it's based on the firm so first thing really just put in the name based on where it is what is it it's gonna be whatever is in B and the item bro so that's pretty easy so let's add that in right now so got range e let's put that correctly t3 dot value equals right sheet 2 is where our items we'll look at a cheat to range B and the item Rome and the item row dot value okay that's the item name I also want to end the item price so the item price is going to go in f6 f6 let's take a look at that right here I want to put that right there so that's where the item Row is going to be taken care of so we can add that in right now dot range f6 what is that going to be equal to he's gonna be people to almost the same but of course a different row equals sheet two and of course this is going to be D the D price is located in column D descriptions located in C item price but we don't need the descriptions automated through our formula so we don't need to add that and that's automated I also want to default the item quantity to one I want to default the quantity to one so we can set that up dot range in this case it would be F 8 f8 one setting the default you can set your standing but I think we want to be fine default item quantity to 1 all right so now we've got our default item quantity now we're ready to add let's just put an add item detail to the receipt detail to receipt okay great so we're ready dad so basically what I want to do I'm going to add the name of the quantity the price and the total through a formula I'm gonna put in a formula here so we can do that with the following lines of code dot range okay of course that's gonna be the item name K and what K and the an available row and the available row dot value equals what it's going to be equalled III dot range III cuz we've already added the name in e3 because that's gonna be our item name good to comment everything out what's next we can copy and paste this and make it a little bit quicker here next up is the quantity right so that's located in L that's column L we need to equals here and we need the equals here okay so we've got the equals and what where is our quantity located that is located in f8 so we're gonna change this to f8 and then put in item quantity okay so good so now we have that what are we at what else do we want I want the item price we got to have the price in there so that's going to be in M so we can copy that and change this to M M is gonna be where price is located and it's coming from f6 right our price is f6 which is right here we want our price f6 and it's going to go right here in a column so we can do that so we're gonna call that item price next up what do we want well now I want the actual formula and that's gonna be located in column and n is gonna be where formula is so we're gonna paste that in change this right we're going to actually gonna be quotes keep quotes equals L n quotes and the available row another and sign and then quote and then I want to use the time she goes multiple times which is the asterisk and again and in this case it's going to be M right we want to know the price and am in fact we can compete like those together am and the available row okay so we have L times M right L available row times n again let's just do that here L and the available row times m and the available real perfect that's exactly the formula that I want to enter so we have that there alright now that we have the formula let's just put in total price formula all right so what's the next step next step I want to check to have the pictures right we want to add some pictures in there once we select I don't want that picture to show up as soon as I select it I want to show the picture that's whatever that picture is based on this image here we've got the image here so I'd like to have a nice picture show up so how do we do that let's write just a little bit more code first of all we want to make sure that actually there we don't have any issues with the filename in case it's not found I just don't want to load the picture I don't want it to create in here so we just have to run a check let's just use on-air resume next but I'm gonna I'm gonna comment that out in case there's an air on air resume next but we're commenting it out we're gonna add it in later so it's just in case cuz it it's not good in case there's an error I need to know I know that these are correct so I don't want any errors to show up but if there are if I make a mistake in the code I want I want the air to show up now so keep that in mind if directory we want to check for that final path what is the file path sheet 2 right dot range what is the range it's going to be II and what II and the item row and the item row that is the file path of the picture dot value VB directory does not equal empty then right if it's not empty means the file has been found then we can continue so good so we have that and if so now we can add the rest of the code in if there's a correct picture so what is that well first of all with right we're focused on the she with dot pictures pictures dot insert what are we inserting of course we're inserting this right here this file name right here gonna insert this that's what I want to insert so let's copy that paste that in there not the comma and so then I what I want to do is once it's insert that will insert it so what I want to I want to focus on that with dot shape range because of that shape range I want to do a few things with it what do I want to do well the first thing I want to lock the aspect ratio lock aspect ratio equals NSO true when a lot I want to make sure that that aspect ratio is locked I want to set a height for that and then equals let's just say around 45 maybe something small like that 45 and then give it a name on a specific name name equals item pick that way it's always the same name we could easily delete it and then end with and then end with perfect so that's the way we have so now we we have our shape we've set the information but now I want to work with it what I want to do with dot shapes I want to this which shape the one we just add an item pick with that I want to do something with it I want to place it in a specific cell and I'm gonna and I want to make it visible so dot left what do I want to place it equals sheet one why we need sheet one now because we're on with dot with shape so we need to specify sheet one range what I want to place it on a place that let's take a look where we want to place that back in here and we want to place it right about d6 let's put that d6 inside the code range d6 . left and then the same thing with dot top top of course it's also gonna be d6 but top so let's copy that paste it down here and put the top and I want to make a visible adopt visible equals and there so true okay that looks good so now we've got end with there and now we want the end if so let's write remember this is the end if that's based on this is a directory it's a clear link so we have that perfect and I also want to do a few more things let's go on air zero right in case there's that way if there was an issue with the link and let's comment that out for now but we'll add that back in a little bit later on just in case there's an air with the picture so also what I want to do is I want to clear the item ID I want to get ready once they've entered an item I want to clear this out I want to get ready for the next item and I want to put it in that way they can just scan the item in and it's automatically ready for the next item so let's do that let's clear it out top range e 10 through f10 remember it's a merge cells so we need to clear up both of the cells or all of the cells in that dot clear contents and then clear put in a note clear item ID and I want to make sure it's ready for the next one so let's select that dot range eat and remember we only need one cell in a merged cell there remember we only need one cell and a merge so when we're selecting it so I'm gonna just select that and get it ready for the next either alright so we're done with that let's run this code and see if we have any issues let's make sure we have a specific ID and let's run this code to save it before we run code in case we make a catastrophic mistake then we can always just close it out so we're gonna run this and it looks nice let's take a look at this apple perfect it put in the price it's got the price the quantity and it's cleared it out and to put the picture and just as we wanted okay great so now we've added the item in and let's just put in now what I want to do is I want to make this run automatically when the user makes a change to e 10 so let's add in that code right now so when the user makes a change we want it that's gonna be a change event based on our POS sheet and it's gonna be a worksheet change so worksheet not selection change but change will add some code in selection change shortly so I want that change to happen when we make a selection what kind of selection do we want I want to write on on change of item if the row is found if the row is found and add item to found and add to receipt so what do we do so if not intersection which one e 10 e 10 if the user makes a change to e 10 and I want to make sure eat n is not empty and range e 10 I want to make sure that in case they cleared out that kind of change I don't want dot value does not equal empty then what then I want to run the macro add item well if if you're not sure what the macro name is use it in all lower case and as soon as it changed to upper case you we know that it's the right watch did it change let's bring this out a little bit so you see how it changed let me show you that again I put it in all lower case add items but I'm not sure if I got the item name right so as soon as I hit enter it moves to all caps and that means you know you've got the macro right so add items so that means as soon as we change it it's gonna add an item so let's clear that out and now let's give it a try so as soon as I add an item one zero zero two it should work perfect banana okay great but you know what I messed up one thing notice how I replaced my I wanted all the available role must be plus one so let's go back there go back to PS and because that replace the same row so look at our available row here available row not the last row the first available row plus one that's important okay we got that fixed up now one zero zero one at an one zero zero two perfect so it seems how it's selecting and now what I want to do is I wanna select a specific item I want a few things to happen I want to make sure that that information loads up here when I select it so let's get that that's on selection change so why don't we add that code in right now and that's gonna be called load n so our add item code is working just good just the way I want it and let's continue on with another macro called load item and what do we want that to happen when we make a selection of a specific receipt I want to load that item up because this one's working just fine now so how we gonna do that well that's based on the selection change so let's go back into the worksheet here the worksheet code and focus on our selection change so what are we gonna do let's write some let's write a comment here on selection of receipt item load item details if not what are we going to focus on K through n right K through and that's our receipt so let's look at that K and our first rows k10 through and let's just large number nine nine nine I also want to make sure that K actually contains a value otherwise you can't load anything and range K and the target row target row dot value does not equal empty in that case I want to load then then do something what I want to do then I want to load all the information in so the first thing I want to do in b6 I want to put the row remember our row must go in b6 that's that highlighted row right here so I want to put that in b6 so let's do that that's what the first thing we'll do range remember we don't need dot range because we're on the sheet itself range b6 dot value equals target row that's the selected row okay so we've got that next up now that we have that we're going to set the item load to true I want to make sure that we're loading know we're loading the item so it's gonna come and handy a little bit later so range b4 dot 4 dot value equals true and then just gonna set that back to false but we're gonna do a few things in the meantime so let's do that now it's easy just copy and paste it goes false and then we're gonna add some code right in between there so the first thing we want to do is I want a three to be the item name so range e3 dot value equals what is it K in the target row range K and the target dot Rho dot value that's gonna be the item name okay so what else let's just copy and paste this because we want to add some other details it's a little bit easier so I'm gonna add in next I'm gonna add in the item quantity and next I want the item price so we want all that information now the quantity is going to go into f8 quantities are f8 so let's check that f8 okay and then our price our quantity is going to go in f8 and our price is gonna go in f6 we can format that cell there as a price okay so we want that let's put that back in now f8 and then f6 is where our item price no f6 good so we got item price and f6 quantity and f8 all right so what else do we need to do I want to set no it was before I think that's it so let's take a look with condense that a little bit so we're good to go and we can now select on it okay we're gonna change this here we need to know a K L and M L and then M is the price of course we don't need the total so now when we select something nice let's check let's change a quantity here just for the fun of it to make sure that that gets loaded up hey I wanted to jump back in here I've actually already recorded the video but there's one thing I wanted to add I just wanna show you when I select item I want that picture to show up as well so not only when we enter night of them here but also when we select it so I added some coded after the effect so I just want to let you know what that is all about so also on selection change what we did we set an item I set this is a brand new and of course you can download this set the item range and found item range we set those two items and then what I did is I set the item range to the item name on sheet 2 then what we're gonna do is we're gonna look for the found item looking for the found item we're gonna look for the item name which is in K looking for that if it's found if not found is nothing that means if it's found then we can define the item row as found item rather we can define the found item row and then the rest is the same just as we added the picture before no different also in d6 so we already went over that training I just wanted to jump back in this video so you can see now we have the ability to change the picture on selection I wanted to add that in because I thought that was really important okay great okay so our quantity goes to 2 we can make this a number actually because it's gonna be sometimes 2.5 or whatever all right so let's save what we have so far looking good our receipts coming together it's coming together really really nice I also want to add some additional formatting for total I want this to be different so let's highlight all of these cells here we can go all the way down to something a large number and I want the total to look different so when we add conditional formatting and manager rules I'm gonna add a brand new rule and what I'm going to use a formula what's that formula it's going to be based on column M right so M if it's going to be based that's where our total is going to be local so am but I it's gonna be for every single row in the field so I want to make sure to get rid of the dollar sign before the ten equals what's it going to equal total and then the colon so when that equals total in the colon where I want I want to bold the whole line and maybe put maybe borders around it so fought I want bold and then border I want to add some borders to that so let's put a solid border above and below that and that'll make the call them stand out click OK and apply and we see since it applies to k10 now our totals automatically going to have that if I change the total row it's gonna automatically change to then it's as soon as we remove that total it's going to be gone so great that works good so because I want that to be dynamic so that our totals automatically alright so continuing on what else to f3 we have to have some more code in now so what I want is if I chain if I've selected an item and I change the price here to like - I want that price to be reflected here or if I change the quantity here I want that quantity to be reflected in the receipt it gives users the ability to actually change the quantity and received so how do we do that well that's based on the change that we make a change to f6 or f8 so let's write some code that we can make those changes and that's on change event not selection change but actually change event so we're gonna scroll up here and write some additional code on change of price or quantity for the added items right for added items so we can write some if not if not in a session we're gonna change f8 or f6 right those are the both f8 or f6 then we want to do something want to make sure b4 is not false one make sure that the change that we're making is a manual change not when the item is loaded and that happens when b4 is false and range b4 dot value equals false and I also want to make sure that b6 contains a value there has to be a receipt row and range b6 if we're gonna update the receipt row we need to know what row it is dot value is not equal empty so those three conditions have to be true then I want to do something then what then I want to dimension their seat rows long to mention the receipt row as long because we're going to be using that and I want to set it up re cpt row equals B 6 equals range B 6 okay so we're gonna set the receipt rope so we need to know what row to update of course if we don't have the row we can receipt row once we have the receipt row we can check which did change the user make was it f 6 or was it f 8 if not intersection let's increase this so you can see that bring this a little bit up bring this a little bit over if the user made a change to f6 then what do we want to do then M right range M of course that is our price right that's our price in that case I want to update M C row dot value equals the target value update price right next up we can actually copy this and use it for the quantity except for means to make a few small changes update QT y so if the users f8 if they've made a change to f8 then we're gonna update the quantity so f8m in fact in this course the quantity is located in column l l and we're gonna update the quantity let's take a look at that now and see if that worked okay so we can update this quantity to 3 and an updated to 3 and now if I want to change the price to 2 it updates to 2 so now you see automatically the price quantity update if I want to change the price to 5 it automatically works perfectly alright that's kind of expensive bananas so we don't want to do that alright so we've got that perfect so now that's working good now let's make these buds work what I want to do is I want to actually add in and select when I user selects these buttons I want to add in specific I want to put them in here so that they can actually instead of scanning items and maybe they want to enter the buttons using these for a touchscreen so how do we do that well it's actually very easy I can use one macro for most of these buttons so let's write that macro now back into the VBA and let's go into the POS here and let's go down here and add in another macro here and we'll call that macro sub inter number button and so what we can do is we can since we've named the buttons very specific it's very very easy so with cheap one active cell value active cell value why are we doing active cell because we may use this for the quantity or we may use this for the number so we can use this for different cells equals active cell dot value and now what do I want what I really want is this I want to use the same macro for buttons but how do I do that well the name of this is button 1 the name of this is button - the name of this is button 3 and 4 so I've given these each one a name so the 5th character is the number so how do I do that well we can use application collars they're really really great for you let me just show you something let's just comment this out I want to show you something using application caller it's kind of a cool trick so let's just comment this out I want to show message box application dot collar and basically what that is is that is the shape that's actually calling it so if I take this macro and assign it to a specific button let me show you what happens in fact we can assign it to the group but I'm gonna actually have to change the matter Celeste you right-click assign macro and that's gonna sign this macro into your number button click OK now watch when I click that button 1 you see how tells button 1 if I click here button to sew button 3 so ok so we know now of course it's not for clear in the period we're not going to be able we're going to use some different macros for those two but it works for 1 through 9 1 through 0 so great so we know that but I don't want right I don't want to enter the entire name I only want to enter the last character right all I really want is one all I really want is six so how do we get rid of that we can use right to get rid of the first four characters so we can do that let's go back in here let's get rid of this now you understand that get rid of that active cell value equals the active cell value kiss whatever's there we want to add to it and right of what the application dot collar comma one I only want the rightmost character and I want to put that any act or cell so if this is the act of so now I click one two three four see how it works it's really really great so what if I want to clear that out and I want to enter one zero zero one perfect it worked good one zero zero to see how easy that was one single Macker I'll change it for this and the decimal but it worked great to add in items so that's just perfect don't worry about these subtotals we're gonna add those in dynamically when we click print all right it's gonna be long training I hope you heard liking this I know my trainings are long but they are very very comprehensive and I want to make sure that to give you all the tools to make your own amazing applications okay so we understand that now what we need to do is write little macros for the clear and the dot so let's write those right now we've got the interest see how easy that is with one very very simple macro inter let's do that inter decimal button and we also want to enter the clear item button so sub clear item button this one's a little bit different with sheet one so if the active so I wanna I want to double check to make sure that the active cell is e 10 if active cell dot address IRA focus not add comment dot address is equal to e 10 e10 then what I want to do then I want to clear because it's the merge cell we must clear them all range dot range e ten through F 10 because I need to clear remember when you clear off both those dot value clear contents okay great so what if it's not else because that's then in this case we can just do active cell dot clear contents okay great so what that's going to do is that's going to clear the contents based on whatever so we've selected and if it's a merge self that's that e town I want to make sure that we do that because it's this will create an error if we try to clear the contents of a single cell when it's a merge cell so now let's see we put in this and let's assign the macro right-click I'm gonna scroll up so you can see it right click assign the macro clear item button ok so what I want to do is when I run this I want to clear whatever we have so click let's click on this specific it up clear it okay works just perfect seven eight nine clear great one zero zero four it's gonna enter the item perfect clear alright great so that's working but I want to enter the decimal maybe you want to injured decimal we enter quantity I also want to be able to add to that cleared wall also works for that too but what if I want to introduce well I want to make sure we do that so let's enter a button for that obviously it's not and let's create a macro just for that okay so this is actually going to be a text field because we're going into the decimal and then what we want to do want to make sure if we're entering a decimal it really only works in the text field so let's add quantity goes to text okay so next up what do we want to do in there I want to create a macro and we're going to call this inter decimal button sub and make this a little bit bigger so you can see what's going on enter deston button and this is gonna be pretty easy it's gonna be the active cell dot value equals whatever's there already active cell dot value and the decimal and the decimal that's it pretty very simple very very simple and the stuff very very simple so let's assign that macro now right-click right-click the individual number this is a group shape so we need to right-click and then I'm gonna assign the macro to that and that's gonna be entered decimal okay good so now when we into let's say we want to enter the quantity two point five perfect okay and you see now it shows automatically grapes two point five we can clear that out and change it to five point five and it automatically changes here perfect so now we have that and we want to enter so we got we're getting closer to the end so thanks for your patience on this we just have a few more Makris throughout I want to print and enter so we've got the add item good good good good alright so what I wanted also do I want to print this receipt and then I want to save it so we can and I also want to add these payments so how can we do that the payments are really cool all we need to do is use the again application car I didn't change it here the pay type so let's look at that how do we do that very very simple and that's just with one more macro sub inter payment cell how does that sheet 1 range i7 that's our payment cell right I said and let's double check that dot select because I want to enter the payment right when user wants to enter payment right if they click payment I want to all we can do is select this cell that's all we need to do so clicking this is let's just do that enter payment dot select so I'm gonna copy this the slalom learners into the payment for that point so that we can get ready for the payment a sign Mac I'm just sorry it's off the screen right click assign Mac or injure payments all I want to do with this one is just select the payments then I want to add a the payment buttons here so that's pretty easy how do we do that well that's just the inch of the pay type let's take a look at that we're going to also use application caller on that we can do that because remember we've named these specifically check with name these specifically not the group but the individual shapes card and so on and so forth so let's go into that so we can do that inter sub inter pay type and in this case sheet 1 dot H I six I six by six dot value equals application dot collar pay touch simple as that that's all we need to do so when we click now when we click hold down to the controls click assign macro and there we go inter pay type so now we have pay type cash card you see how that works because we know the name it automatically takes the name and enters them but when we're ready into your payment now we can enter the payment just by clicking these buttons one in twenty or clearing you see so we can into the payment now easily put the check and I also want to click the payments automatically so we're ready to select the payment automatically so let's do that once we do that into the pay type I want to make sure that we're selecting the payment so after they into the pay type I want to select the payment cell automatically after they entered after the end of the payment I want to select the payment automatically so they can quickly enter the payment so we do that with just one line of code sheet one range I seven select I seven dot select ok inter pain cell that way he can enter it with the buttons perfect great so now they put on the payment then they click the paint into the payment and then just click thirty six or whatever they want to pay great so we need to change what is that equal to the payment minus the toe now we've got the change and of course I want to form out that currency the payment same thing well there's little gonna leave the payment there because watch what happens if I enter the dollar sign here if I entered that formatting accounting it's gonna not enter the decimal properly see that so we'll keep that as text it's okay for now we're gonna keep that as text text okay and then we can clear it out easily and we can enter the proper 30 let's say they pay 30.8 e9 perfect $2.00 is our change right justify that I like that the way it is alright great so now we're ready to print the receipt so let's save for the work we've done and run a macro and all I want to do is to print a receipt is basically determine the last row add in the subtotal so I'm going to delete that what I want to do then is I want to copy these subtotals copy them I take the payment amount put it here copy them and then paste it right here and then pay actually going to paste it right here and we're gonna do that inside the code paste it there and also just want to add enough there's one more thing actually I want to add a dotted line for the total and I want to do that dynamically through conditional formatting so let's do that I'm gonna highlight actually I'll start here because it's not gonna be highlight all the cell's here and all the potential cells adding conditional formatting conditional formatting a new rule using a formula and what is the formula it's gonna be based on this so based on actually the first possible which would be M 10 M 10 again we're gonna remove the dollar sign and then equals what's gonna equal sub total : so if it equals that what do I want to do want to put a dotted line at the top format dis dotted line at the top click okay perfect now I've got a nice subtotal and a total perfect that's exactly the way I want so it has the subtotal changes we're gonna place this dynamically so let's get ready to print it actually we want them before we want the date always want that equals now I want to put the date in there that's good we got the date now we've got everything we got the ready to put it in so let's save that again and we're going back into the VBA and create a macro called print okay so let's see sub print receipt okay we're gonna do mention we need the last row the street dimension the last item row that's very as long we need to know the last item row so we can place the total and all of that sheet with sheet one first of all we want to make sure I seven less then we want to make sure that they've paid right we can't give them receipt unless they've paid what about how do I make sure I want to make sure that I seven is greater than or equal to the total make sure that they cause if they haven't paid yet I don't want to print the receipt kind of nice okay so if dot range i7 is less than what then I five then dot range i five then message box please enter a payment at or above the total at four above the total all right exit sub make sure that they've paid the bill before we can print a receipt right we can't have them getting away for free and can't steal it dot range what do we want s sixth value on it into the payment amount again oh because it's a text because this is the text and because I don't want this as a text I want this actually as a currency and this is gonna change all these two currencies although it's not going to matter because it's formatted I'm only gonna paste so we want those as currencies and so on this is not a text because this is a text because it dot the text only works with decimals in this case so we just want to copy this value and bring it over to here so s6 is equal to to i7 I'm gonna write that in right now s6 dot value equals dot ranked i7 value i 7 value and that what that's going to do is that's going to into the payment amount enter the payment I'm out okay great now I want to set the last item row and that's gonna be based on K last item row equal to dot range K right kids are item nine nine nine nine dot value that's a lot of nines dot end Excel up dot Rho it's getting this our last item row that's what I want if the last item wrote is less than ten right less than ten then exits sub that means there's no items nothing to print exit so just want to put that into that caveat in there all right great so we're now ready to go I want to clear B six I want to clear the selected row I don't want this this highlighted row I don't want that to print right cause it shouldn't print it should just print as a normal receipts the best way to do that it's just a clear b6 so I'm going to delete that it's gonna delete conditional formatting so we can do that b6 should be cleared dot range b6 dot clear contents dot clear content okay and that's going to clear that's gonna clear selected receipt row okay good so that's kind of important we don't want that show up in there see remember that footer coffee remember that range okay let's set the range actually I want to set this as a range so that we can easily work with it so I'm gonna highlight all the cell's and this click here and put in footer range we also want to move this over and I also want to display this normally this is going to be hidden footer group we want to display and we want to display it based on what do I want to display it well let's take a look here where do we want to display it so what we're gonna do is we're gonna copy this over we're gonna copy it over right about right here paste the values in there so it's gonna be right here so what do I want to place this I want to place it about right there I want to place it so if our last row is 16 what do I want to place it I want to place it about 7 rows 7 or 8 rows and down I would think about 7 rows down from the last row that's where I want to place this group here ok so we can do so we know where we're gonna place an else based on the last row 7 rows down ok great so we've got that so let's do that let's write that code now first of all we want to that range that we just created we're gonna copy that that's this range right here I want to copy that the footer range let's do that right now and since we've specified it's a range we can do just that so dot range footer range and then copy that dot copy where are we gonna place it I'm gonna place it in the last row plus 1 so we can do that dot range and that's the that's M and we're and the last item row plus 1 I 1 below the last row I'm going to paste that in there dot paste special I just want to paste the values because we already have everything else formatted paste valid paste special and then Excel paste values just to paste the values paste values only because everything else already formatted which is fine we don't want to mess up and then application dot cut copy mode equals false that's gonna get rid of the dancing ants okay so next up I'm ready with the footer group so with dot shapes what shape is called footer group remember that with the bar code footer group and our message well I want to work with that dot left want to place that somewhere dot left or don't want to place it equals sheet 1 we need to specify the sheet because we're focused on dot shape sheet 1 range kay and the last item row +7 remember want to go 7 down that's about what we want +7 dot left I'm gonna do the same thing for the top so we can copy this Parshin right here and then just go one down and do the same thing with dot top equals she wasn't in the dot top that's gonna place it and let's take a look then I want to make it visible not visible equals and that's true so we gotta make it visible good good good alright so next up what are now we're ready to now we're ready to print it but want to set the print area because the printer is gonna be dynamic so I'm gonna start it out in k1 and go all the way down to at least here right or maybe here right one row below but how do we know the last row well we know the last row 16 but I want to go all the way down and I want to add in maybe 10 or 11 rows probably 10 or 11 rows here so we can do it let's say 11 rows so 11 rows I'm gonna set the print range so it's gonna start out in k1 it's gonna go all the way down to the last item plus 11 to n so let's set that print rage right now so we can do that dot page setup or we're setting up the page dot print area what is the print area it's gonna equal to k1 we know that's our starting range k1 through and and and what and the last item row plus 11 that's gonna be enough next up we're ready to print out dot print print out what do we want to print out I want to print out from two that's important it's no deed copies you can keep that as default preview active printer let's set that to true the active printer print to file now collate no print to file name no ignore print areas and this put that is false okay that's gonna print it out and then I want to get ready for the next order once we print it out I want to select eat n I just want to get ready for the next order but I also want to click the next so let's select eat n just to make sure dot range e10 dot select okay great so we've got you ten that looks about right let's run that and let's assign it to the button and see if we have any code SAVE and always want to save after writing code let's assign that that's the print here so we're gonna click on the group here scroll up so you can see it right click assign macro and then print receipt okay let's take a look my default printer let's see if we have any issues print looks like it's printing out and it's gonna print to my Snagit editor wow that looks really good let's take a look at that zoom out that looks good that's what it looks like this won't want to move it over a little bit but I think everything looks just the way I wanted to see it we need to do some formatting on here that doesn't look quite right so let's format these I want to make sure everything's formatted correctly formatting make sure we hit the currency so everything's formatted the same that looks good our subtotal looks good our dotted line we have very nice alright great it looks like we're doing doing good making a lot of products one more macro right now what I want to do is I want to take all this information and I want to save it to the sales I wanna save it here so we have a savings so we can use reports and run our sales reports because I'm gonna save all that information so let's write that macro right now everything looks very good we're making good progress so the last macro would be to save it and clear it so this button would be next I want to clear all save it to the database and then clear it all and get ready for the next item so let's see how do we do that we're gonna start writing some macros let's go down here we're gonna keep everything on the same module this time sub we'll call it save and clear thanks for sticking with me on this extremely long training I want to give you tons and tons of value if you like these workbooks I love your support whether you're joining the courses or purchasing workbooks I really really appreciate it that keeps these trainings free each and every week last item row and also the first database row where am I going to put that in the first database row as I need to know that and also the total rows total rows as long we need to know how many total rows so that's very very important okay so now that we got the total rows let's get the last item row with sheet one we're focused on sheet 1 primarily the last item row remember is K we know that already we already did that up here I wanted to just copy that here it's gonna be the same thing we need a new glass room and then we can paste it right down here no difference on that last item row same variable same everything total rows is going to be equal to the last item row minus 9 our row starts and 10 so we're gonna subtract my total items I want to know the total items that's very important so I want to know how many items to add to this database so we're gonna start the last one in this case its total is 7 so all we need to do is the last item row 16 minus 9 is going to get us 7 so we need to know the total items to add to our database all right so we have that so we've got the total items next up I want to know the first available database row I want to know what's the first row that we're gonna add to here in this case is 3 but I need to know the first available row so we can we don't wanna copy over any other information so we're gonna get our first database row is gonna be equal to and it's gonna be sheet 3 XI 3 is our sales sheet 3 dot range a and then we'll just use a large number 9 9 9 9 . and Excel up dot row nice ok so we have our plus 1 our last of our last row of data plus one's gonna be a first available row first available row ok great so we have our first available database row now we can ready to copy over the information so sheet 3 starting in a right what do I want to put in a let's add our invoice before we add our items I want to add our invoice I want to add in our receipt number our date and our cashier I want to add all of that information in with C number date in cashier that's going to come from here receipt number date and cashier so it's going to come from m5 m6 and m7 so we can add that and I want to add it in for every single row every single row of er seat so if there's seven rows here I want to add it in all seven here all seven year in all seven units give me the same information recede date so we can do that with just a few lines of code III got range what is the range it's gonna start out in a of course and the first and the first database row the one that we just defined DB same thing okay all the way and all the way through a and what : actually : a and the first database row plus the total rows and the first database row plus the total rows we also have to subtract one total rows minus 1 because we don't can't count the existing row okay total rows minus 1 and that is going to be our set up so we can set that up dot value equals what is it equal it equals dot range it's gonna be our dot range l5 that's a receipt number now five let's take a look at that and make sure we have our L our receipt numbers what way I want to add in there m5 m5 is where our receipts located so we want to put that in there m5 m6 m7 so wanna make sure we put all that information in okay so m5 is where we want to put that dot sober seat number and we can copy and paste this and we're just gonna add in the rest of course we're going to be calling be calm see receipt number column B what's B be is gonna of course be our date so we want to add that in B is gonna be m6 m6 is our date order date and then our cashier of course cashier is going to go in to C so put C for our cashier you know our cashier comes from and seven let's double check that so we know everything is correct our receipt number is m5 our date is m6 and our cashiers f7 great now that we have all of that in there we are ready to add in all of our items here so we can do that with just a little bit of code so let's do that now so in this case it's going to start out in D D is going to be the first right d D is going to take on our item and all the way to G is going to be at o so it's D through G we can just copy that era d through G is going to be equal to one equal to dot range teh 10 starting at K K times our first K 10 through n and the last item row and the last item row dot value and that's gonna be all item detail alright let's double check that K through n it's gonna be going to be equal K through in all that information all the weight from the from starting from K 10 all the way to the last row taking all that information and bring it right into here alright good so we've got that covered let's take a look let's finish up our arm thing get ready for the next order so we can do that so bring down add a few lines here and what is next in our code next up what I want to do is I want to make sure that we're going to hide our footer group we don't need our footer group anymore that's the receipt number we can hide that no need to dot shapes footer group when we're getting ready for the next order dot visible equals MSO false right let's hide footer group shape don't need that anymore and then also I want to delete the item picture right if there's an item picture here here I want to delete that we don't need to show that anymore we need to clear the contents delete the picture but if there's no picture could create an error so let's wrap that on a resume next dot shapes item pick dot delete and then on air grow okay so now we've got the picture and now I want to clear the contents I want to clear all of you know the receipt we want to clear that's starting at K and going all the way down just clear everything out we can delete everything getting it ready for the next order so dot range K 10 through and remember because we're adding in the formulas dynamically through VBA we can delete them anyways 9 9 9 okay doc clear contents not clear because that would clear the formats just clear contents and then what I want to do is I want to calculate it calculate it and what do I want to do to calculate I want to make sure that new receipt number gets generated a new receipt number is based on a few things it's based on there's the maximum of the receipt numbers and it's based on this formula right here so I want to make sure the next receipt number gets calculated once it's catechin I want to put the next receipt number right here into m5 so we can get up with the following the lines of code dot range m5 dot value equals dot range b7 b7 and that is going to be our new receipt number new receipt or next receipt number next receipt number getting ready for the next alright now I want to clear out several fields what I want to Clara I want to clear out basically certain fields for the previous draw range I want to clear out b6 that's gonna be our our item row case that has a value e3 through f3 through f3 what is that well that I want to make sure that our item number I'm gonna clear this out and I want to clear out our item name and I want to clear out that's getting ready for the next so III qf3 f6 quantity price I want to make sure f8 is cleared out and I want to clear up I 7 I 7 so basically I want to clear out all this information the payment type the payment amount payment I I guess we can keep i6 there case is the same thing type case they're always using cash or something alright so we cleared that out so now we cleared everything I almost finish up that line of code dot I seven dot clear contents clear item fields okay so I don't want to select eat n dot range e ten getting it ready for the next e 10 dot slick that's the order nice ok so let's clear that up check it out for any air save before we actually run this code that's always a good idea save it and assign this macro it's called save and clear so we can copy this and then into that right here I'm gonna this is the button I want to assign it to right click actually right click the entire group sign the macro sorry it's off the screen save and clear and click ok now that we've printed it alright let's bring this up here and click Next and see what happens variable not to find total rows we need to make sure that this is total rows okay all right that worked good now we've cleared it out perfect and already this we want to add an if error we don't want to have this now okay if air wrap that in if air comma double quotes because I don't want to show it like any alright that looks good now let's now after it's been selected and let's take a look at our sales here increase the date that looks good the quantity everything got safe so our next one should be one zero zero one one zero zero one is the right one that's exactly the way we want it perfect so select in here add another one you can use the buttons of course we can also type in one perfect one zero zero seven nice we're adding these in one zero zero two perfect or we can type it in one zero zero two good we can also select something change the quantity nice change the price and of course we can change the price also with our you can change the price any which way we want set a payment and set a payment amount and then we can set it to $20 okay good and we've got that and now we can print our receipt print it adding the subtotal perfect it prints it out nicely good and now we can click Next awesome great now we've created a brand new point of sale it's a really really great program I'm glad I got to show this to you thanks for sticking with me on this extra long training thank you so much of course if you do like this video make sure you subscribe and I can't wait and we will see you next week thanks so much have a great week

Channel: Excel For Freelancers

Views: 4,304,024

Rating: 4.9425735 out of 5

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

Id: C-jw10s8esw

Length: 99min 59sec (5999 seconds)

Published: Tue Feb 25 2020