How to Create A Mobile Barcode Scanning Point Of Sale Application (POS) In Excel Free Download

How to Create A Mobile Barcode Scanning Point Of Sale Application (POS) In Excel Free Download Hello and welcome this is randy with excel for freelancers and in this week's training i'm going to show you how to create this incredible mobile barcode scanner complete with mobile scanning just click and scan it's going to be that easy i cannot wait to show you so let's get started all right thanks so much for joining me i really appreciate it i've got an incredible point of sale application that i'm going to show you every step of the way and that's going to be complete with this incredibly cool menu that we're going to be creating these dynamic pictures i'm also going to show you how you can quickly and easily scan in items just like this so if we.

How to Create A Mobile Barcode Scanning Point Of Sale Application (POS) In Excel Free Download

Add an item we do it through a point of sale it's going to be just scanning it and quickly like this and very very easily it'll scan any items just like that automatically adding it to your point of sale so it's going to be a really incredible training i've got so much to show you so we're going to get started right away on this so not only am i going to show you how i built this from the beginning every step of the way every line of code every formula every function but i'm going to show you this incredible application called barcode to pc that is going to help you tie all this together and connect everything just over a wi-fi without any wires just there it's going to.

Be incredible i cannot wait but before i do that i really want to make sure that you know about everything that we have going on i've got a brand new incredible patreon account if you like this application if you want to download these things a few days earlier if you want to get a pdf code workbook or if you want to download the entire video or maybe you even want to get all these resources all these pictures all these icons i'm gonna pack all that into some really incredible packages in our patreon account starting at just three dollars a month incredibly cheap and you can get tons and tons of benefits so go ahead and head over to our patreon account i'll include the.

Link down below that's brand new that's going to help us out make sure that these videos for free you can download this application absolutely free using the links down below either with your email or facebook messenger i bring these to you each and every tuesday i just ask a few things if you could just subscribe to our channel of course go ahead and click that like and don't forget to comment below i always like to hear your thoughts ideas feedback and comments that helps us out all right we're going to be creating this incredible mobile point of sale full mobile scanning and it's going to show you every step of the way just like you saw but first i want to show you how i created this application i'm going to walk you step by step now as opposed to creating it from scratch that would take about five or six hours i want to kind of trim it down but don't worry when i.

Do that it allows me to go every step of the way i go a little bit slower because i'm not typing and i can show you every step every shape every formula again every line of code so we're going to get on that but what i want to do is i want to share with you so you can get started a heads up let me share with you this incredible application that's called barcode2pc it works on your local network it works with your apple phone it works with your android based phone or anything like that it's really great it's called barcode and that's what i'm using i've found this it's really really cool there's a really cool free you can use it free try it for free and then if.

You want some more other features that they have a really good paid for version in fact i got a discount below that's going to help i talked to the owner because i really like this application and so he's going to offer us a 10 discount using our code excel for freelancers there's a free version and they got lifetime licenses meaning it's like a one-time payment and then the license is forever so if we pick the basic or pro version there's a monthly scan limit but of course the program will automatically reset itself each and every month so we have to worry about that that's called so if you want to get started go ahead and click download it's absolutely free to download what you want to do is you want the desktop version of this and you want the app version of this so you want to download it for on your app either on your android app or your apple phone app.

Download the app barcode to pc and also download the exe file because they're going to work together we're going to go step by step into that a little bit later but i want to show you more on this mobile point of sale application that i have created and i'm going to share with you today we have created point of sale in the past but this one's going to contain mobile scanning and that's really really important because it's great to be able to click a few buttons but most people want some type of way to scan in barcodes they want to you know if you're in a small shop you want to be able to use it and today we don't even need to buy a barcode scanner anymore because we can use our phones effectively and easily whether it's 2d barcodes or 3d barcodes this.

Barcode pc will handle that for you all right so what do we have here let's go over an overview and then we're going to get into the nuts and bolts of this and show you exactly how i created this okay so first of all we got just a basic menu so we've got categories these are our main categories you can create as many as you want and then we have like subcategories like tomatoes condiments meats here we've got cold cuts seafood so that's all based on a list of products right we have a list of products and that's located here in our product list each product contains a upc code that's that barcode that you saw an item name a category a type brand origin you unit of measure.

Price and a picture that picture files really really important those picture files i have located all in a single folder here in our product pictures so this product picture i've got these actually available on our patreon account when you see additional resources i'm going to start including all of these and all the icons that are going to go into it that means if you want to recreate this and follow me along you can do that now because i'm actually including all these in a zip file every single week inside our patreon account so make sure you get signed up on that that's really really cool so many great features on that patreon account all right so.

What we have all those pictures here and we've got the picture names located here only the picture names right because what we want to do is we want to attach a folder that way when you get this list and you get signed up on a patreon you're going to have a list of pictures all you need to do is take all these pictures and then put them inside a specific folder now that folder is located in our admin so we have a product picture folder this is my current file path when you get your file all you need to do is just set your own path we didn't include a browse button but you could add that in simply copy the path and put that here we don't need the extra backslash because that's going to get included inside the code the same thing with the icon folder we're going to be building these icons and i'm going to show you how we can put these space them out according those icons are what they're located here we've got one for dairy if you notice we have.

Two colors of each icon like when i click on it notice that dairy goes to green if i click on something else it goes to white so i've got two of each i've got a green one and i've got a white one inside my file so you see dairy here this is in white and green fish and white and green groceries and white green and so on and so forth except for the print and the buttons right arrow button those are on our buttons those are only included inside the button so we only need a single color of those okay so we've got those in the folders that's in our admin screen i've also got product categories now product categories are really going to help us because we're going to be able to categorize.

Them we know all of our categories we could use a named range for this in which we have we use a dynamic name range i've also got our icons assigned to each one that way when i build this item list i can do that inside that means you can i'm going to show you how to build your own list and we're going to use a single vba macro to line these up and guide these very very easily okay so we've got a list of white icons this dairy dairy png or dairy greens can notice the green so each one for the green ends in green that's how we're going to be able to space those out i've also got a dynamic tax name you could put gst you could also put in a dynamic right here if your rates different custom footer message that footer message is going to go at the bottom of the pos and also i've got a signed cashier now the cashier i've given him named range that to make it easier.

Posts Related:

    Inside the formulas so notice this is the assign cache here that is the cashier name up here

    I've also got a footer message this one's called footer message and i've got this called a tax rate and i've called this called the tax name and i've also got some icons here okay so we understand the admin is relatively simple and we got the products we went over now we also want a list of orders i want to track all the orders the order id the date and time the cashier the total amount if there was a payment made and then the change given that's for each order inside the order items is given each item each item in the order for example order number 1004 had approximately six items in that each one we're gonna have the upc code the item name the quantity.

    The price the total the order row this is the order row meaning the order row here notice this is row 11 this is row 12. we can also delete the items i forgot to show that to you earlier but we can delete them and we'll get a lot how we can add payments and print it and next and do a whole lot of things so it's going to be really really cool we got so much to cover on this all right so uh in the other items we have that order row and then we have the database row this is the database row three four five these are formulas that way if we were to delete one it would automatically just.

    Delete here so we could delete it and then all of these numbers would automatically be corrected but i'll shift because it's based on a formula that's why we use it so inside the ps all right so that's basically the overview of everything we've got it's a relatively simple database the database just products orders and order items ran a basic admin screen so everything we're keeping really really simple on this one because there's a lot to go over so what i want to do is i want to really focus on the mobile aspect of this because that's what we haven't done so we're going to go over this and then the code is relatively simple and so basically what we have here is i have how do we design this well the first thing we have is just a simple color here i've just assigned this color i'm going to bring this down and this is just a color that i've assigned the background.

    This is just that green color i'm using this if you want to know what color sometimes you don't understand you see i use different color palettes and things like that well this color palette here i'm going to be using here today we're using flow so notice it's got the red so today i'm using flow but we could use other ones so keep that in mind if you want to match that this one's called flow the one i'm using now if your colors are different you would just change this color to show the color whichever color you want we're going to use flow okay so what i want to do is i want this a header up here and i also want this basically i've got a column a single column column c also with the same.

    Color so that column c is filled with that same green color okay now if we take a look we've got some things here we've got something called how do we get this shape how do we get this kind of menu shape it's very very cool but how do we do that well we do that with a bunch of about three different types of shapes i've got one shape here if we take a look at this shape this is basically a single rounding corner single rounded corner if we were to insert a shape here and we take a look at that we can find that single rounded corner right here it's called single rounded corner so if i were to insert that we've got that single rounded corner right here we can round it as much as we want right but so we take a look at that now if i were to turn it around like this you'd see.

    We'd have the same pretty much the same effect so there's no difference on this one it's relatively simple notice here i've got another shape here this one is also the single rounded corner but this one of course is just simply inverted right so if we were to invert it like that we'd get the same shape that's all that these two shapes are and that was that's what rounds out our lovely little thing here so we can see that all right so let's put those back where they belong right i'm going to put them back right up here i could use vba to line them up and so all they are simply just lined up and then the vertical will take care i've noticed that it automatically and then wherever this one went i'm was going to line that i guess we could probably line it up using vba as far as the left position but the vertical is automatically taken care of all right so that's.

    How we got this so now what about these buttons here these buttons if we look those take a look at this this button is simply a double rounded corner so if we were to insert a shape here we have that double rounded corner which is here it's called round same side corner here so if i take a look at that that's basically what i've done and i've just turned it like this and then i've what i've done is i've also increased the roundness so like this and then what we want to do is we add some font on that so if we had at some font let's say let's say we put some text in here notice it's kind of like uh all different shapes so how do we get that to be the proper shape so we can do it a few.

    Different ways when we go into the shape styles or text effects or home right we see the font here so this one right here you see the vertical text or okay text or rotate text down or rotate text up that is the one that we want to deal with so also when you go in to the format styles and shape here and then what you want to do is you want to go through the text okay so if we go to another text box move that over again so we see that we want to just want to get that up so if we use rotate 90 degrees 90 degree rotate that's the one we want that's how we're going to get that text and then.

    Of course i've just simply noticed that it's no longer right i've actually put it on the bottom that's how we're going to get it and then of course i've just colored all i've done is simply color it that color green so that's all we did to get that so let's move that back where it belongs here but the macro will also take care of that so that's all i had to do there to get that done all right so we've got that there and that's how we get the shape so all these shapes are it's just simply these shapes here if we were to put a border on i've got multiple ones in case you want to see we get an idea of what that would look like to see them you see if we put this outline on we.

    Can really get an idea of how they're placed right so we can see that very very easily now let's just

    Left justify those so that they're automatically lined up so we can see getting a good idea of how these are placed so that's all i've done to give it this really cool menu now it is vba that's going to change those colors and change those icons and that's what we're going to go into right now let's go ahead and update these we don't need an outline on that but i did want to show you that okay and this one too all right so we understand how we're basically using these shapes to create.

    A menu so all i need to do is when i select one what i want to do is i want to make sure that i know the menu item here this is one this is two this is three and that's going to be located in b4 so b4 is going to take on our menu item and i've also given each one a specific name notice this is called main menu item 1. the name of the shape main menu item 2 and so on and so forth this icon here if we take a look at this it's called cat icon white one or category icon white one this is.

    Called category let's unselect that this is called category icon green so the greens have a different name but they all end with the same number five right so we always know so if i click on an icon i want the same shape so regardless if i click on the shape i want the same macro to run so those numbers that we've numbered them are going to be play a real important role in that so keep that in mind that's why we've named them differently icons category icon green or with the shape itself main menu item five okay so keep that in mind all right so why don't we get into the macro that's.

    Going to show you how to create this menu it's relatively simple so let's get into that right now all right so let's get into that if the developers tab is where you're going to find the vba alt f11 is a shortcut that'll get you there if you don't have that developers you can go into the file options and then just click on the customizer but make sure you've selected the developer that'll get you there all right so inside the developer i've got just two modules each with a few macros we're going to go over those first one we're going to go on is set the categories now when i run this macro all that's going to do is just line up these categories a little bit better and then it's going to set them up so that we're setting up the icons setting up these buttons all lining them.

    Up so that way if you add additional categories you add additional icons they're all going to be taken care of now you just want to make sure that your icons are located in the icon folder here that's where they can be found okay so that's very very important all right so back into the vba we go what we want to do is we've i've got a bunch of dimension variables here we're going to go over them as we go over this specific application okay first thing is the setting the categories now with the pos that pos is that pos sheet that's the name of the sheet there that we've called there.

    And that is this main sheet here pos okay so we're going to be referring to that as pos inside that we're going to focus on the shapes the first thing what i want to do is i want to remove any of the icons that might be there if i'm going to replace those icons or i've got new icons i want to update those icons i want to remove any existing ones and then replace them so each one and we're going to focus on that so to do that what we're going to do is we're going to call it a specific variable called icon shape and this is already dimensioned as a shape so when we do for each icon shape in dot shapes remember this is already called out the sheet so it's pos dot.

    Shapes and that means for every single shape on that sheet what i want to do is i want to call out only specific icons in this case i want these shapes called category icon white or category but nothing else notice these are called main menu something different so i'm really focused on these category icons what i want to do is i want to remove any existing ones anything with category icon in the name to do that what we're going to do is we're going to use the in string function and the name the icon shape the name of the shape we're going to simply check if the name of that shape contains the text cat icon and that does not equal 0 that means it is contained in there then i'm going to delete that i also want to delete any type category type anything with category type what is category type well it is this category type right here.

    This shape right here notice this is called cat type one this is cat type 2 here so i want to make sure to delete these because these are going to get created from our sample here so they're going to get created automatically all we need is that sample here to create those so those get removed and deleted and i just want to make sure we clear those out if we're changing anything here we're going to do that so i want to basically create this menu through vba with some of the shapes that we have all right so to do that we need to remove all these first so we're going to delete those and just in case they're not there's this we do want to wrap that in on air resume next and on air and go to zero so our first step is to do just that removal also i want to set the left position i want to set it into a variable now this left position this one here is a double.

    Variable here i've got left position top position has double and width as double meaning they can be numerical and i also want them to contain decimals okay so that's because they are very specific and not necessarily whole numbers so left position as it's going to c1 plus 45 so basically i want this the left position plus a little bit more to the right and that's 45 so that's how we're going to get that that's how we're going to be able to get that i want that left position that's going to be the starting left position and regardless so that way if we increase this and we run the macro it's.

    Going to be regardless because it's based on the c1 the left position of whatever's in c1 so we're going to put that into variable i also want to put the top position into a constant it's going to start at a 3 and then it's going to grow as we add more button i also want to set the icon folder this is a string variable it's based on that icon folder inside the admin c4 that is this icon folder right here located in c4 so we have that i'm going to put that into a variable because it is that folded in which we are going to need to pull those icons okay then when i'm going to run a loop i want to run loop all the categories right remember we mentioned this we've got certain up to 10 different categories so i'm going to run a loop from 8 all the way to 17 and i'm going to pull.

    Those categories out whatever they're here and pull those categories now to get those categories all we need to do to know which categories we need to run an advanced filter getting those unique categories i've already done that and so we have unique categories so i know that i've got you know about six categories or so to do that so we're going to start off with just these five actually five categories here so we've got this so what i want to run a loop from all the way from 8 to 17 or 1 to 10 in this case and then the first time we have empty we're just going to exit out so in this case only five of those okay so that's just what we're going to do inside the lid because i want to pull all those so four category number equals one to ten first of all the shapes main menu item the category width we've already got these shapes set up they're already here okay one's called.

    Again here's those shapes here those shapes are called main menu one main menu two so it is these shapes that we're going to be focusing on so it's these shapes i want to give them this name i want to put the dairy in the name and i want to make sure it's main menu one so main menu and category number this is going to loop equals right the width i want to set the width to the height of cy what what does that mean remember we've turned this button around this is a little bit confusing right this is this is why it gets a little bit confusing for these that's why i did the macro notice that when we did this shape the width let's take a look at this format notice that we have the.

    Height look at the height is 1.42 the width here is 0.42 so normally but it's actually the width becomes the height because we've turned it around remember normally it's like that right so that's why we have the width and height change right so a little bit confusing on that than we normally do but that's cool because we get to learn something new so what i want to do is basically the width of this the width of that shape i want to set to the height of c2 to c3 what does that mean there you see these two rows c2 c3 i want the height of this shape actually the width to hide the width.

    We'll call it height equal to the height of this cell these two cells that means every single button is going to actually be the exact height of these two rows so that way whatever the rows are is going to set so what i want to do is just the rows of those notice the like seven like let's say two to three has a certain height right the height of those two combined i want that basically i want the same height of the button but because we've turned this button around like this it is actually the width notice the width of this in this case it's the width so that's why the width of the.

    Shape right it's kind of kind of strange right and the width becomes the height that's why we're focused on the width because it's turned around so keep that in mind it's going to be able to height so we're going to set the width same as height of two rows so now what i want to do is i want to set the height the height is 102. the height is in this case it's our width she's going to set that width this is 102 right so if it's longer or shorter basically i want to just do that this is the height right so the height becomes the width and that's going to be set to 102. so we set that very because we want them all the same then what i want to do is i want to set the top position.

    Now the top position of this shape is going to be the top position so it's going to be in this case 3 right and the left position is going to become the left position so we're basically shedding this shape up here now what i want to do is i want to add text to the shape i want to make sure that the text dairy is also the same as our first one so how do we know if we're going categories 1 through 10 i'll how do i get this it's b and then it's 7 plus 1 right b8 so if we add 7 we know that once so that's all i've done here inside the code here so it's going to be shapes main menu category.

    Number the text frame the text range the text the text of that shape is going to be equal to admin b 7 plus the category number that's going to be the category name and that's how we assign that category name so now that we have that we also have the top position the top position is going to be equal to the main menu one right in this case we can set all the top positions basically because they're all going to be the same so that's why i've chosen a specific one the width is going to be plus the shapes of the menu this is the top basically this sets the top position this line of code is a little bit confusing because i've turned things around right i've turned them around so basically all we need to know is the width is going to be the top and what this macro is going.

    To do is just going to be able to line these up right so it's it's not you don't need once they're lined up and you set your categories if you're not adding new categories you don't need to run this macro the icon file i want to get that icon file it's going to be located in our icon folder and then what i want to do is i want to extract first of all i want to extract the white one and then i want to set the white one so the white one is located where that white one is located directly inside here in column c seven plus so we're gonna extract that white one to get that white icon from the folder we're going to we're going to set that whole file name it's going to be the icon folder and the backslash the admin c remember it's in column c7 plus the category number that's going.

    To set the file name for the white icon and then what i want to do is i want to make sure that if it's not blank and the directory of it i mean i want to check the file path to make sure it's an accurate file path if it passes both of those meaning it's not blank the value in the cell is not blank and the directory it's a valid file path then what i want to do is i want to create that i want to create that and so to do that we're going to insert the pictures right so pictures dot pictures already on the pos insert we're inserting that icon file we're giving it a specific name i'm going to call it the category icon white and then the category number category again and remember.

    That is why we already deleted the category that comes up here so we're adding the new one up here and it's called white and the category number okay category icon white okay keep that in mind then with that all we're going to do is we're going to place that the first thing what i want to do with that category is i want to lock the aspect ratio i want to set the height very specific height to 17. theoretically we could set the height to basically the height of this minus a little bit that might be better that way in case you increase or decrease your row width.

    The height would be dynamic based on the height we could do that as well so you can base it on this and then minus two or minus three something like that the left position is going to be based on c1 plus 18 the top position is going to be based on c the top position why are we calling out pos again we're calling out this sheet again because we're inside another width remember first we're inside the pos but then we go inside another width so this one we have to again call out the sheet number see so in this case c and two two times so basically all this formula does is going to add that top position it's going to keep going down so basically the top position.

    Of this one is lower than this one it's lower than this one so we're using a formula and we're simply pasting it down simply now so basically the top on the first one we're using a formula based on this based on the selected menu which is in b4 the category number here the category number based on this as we move down as we move down so the category number let's just say for the first one let's say the first one is one right category number one minus one well that's going to be zero.

    If i multiply two times zero we're going to get zero right so all we're going to do is we're going to say c two is the top plus 5 as the top position that means we want the first one right here on c2 the second one we want on c4 write down a little bit under that but undo so how are we going to get that it's going to be c2 plus the second one is going to be category number two minus one so that's gonna be one one times two is two so if we add two plus two we're gonna get four so c4 is the top position plus five so it's gonna be a little bit out so basically this sets the top position of each individual icon then what we want to do.

    Is we want to assign a macro to it we're going to get that macro shortly it's called category select so we assign a macro to that icon and that means whether they select an icon or whether they select the shape it's going to be the same thing icon of the shape so basically i want to assign the same macro to whether they're clicking on the icon or whether they're clicking on the shape itself so we're assigning a macro to that icon that is the white icon that we focused on we're going to do the exact same thing for the green icon let's set that the green this one's going to be the green i should change that green icon and this one's going to be the green green icon is located in column d we're doing exactly the same thing everything's the same we're going to place it in exactly the same that green icon is located in column d so the only difference in this case is we're using.

    A column d everything else is going to be the same notice d and the seven everything else we have is exactly the same so we're going to place that icon we're going to extract it place that column icon directly on top of the white i want them right in the i want them the same size and i want them placed in the same area because as soon as we run the next macro everything is going to be going to be hidden the white icon is going to be either hidden or the white icon is going to be shown or the green or something like that so all we're doing is placing these icons we're extracting these icons and we're placing them and here all we're doing is just assigning the name of the category so basically all we're doing is we're assigning the name to the button here we're adding the icons and we're placing the icons in the right place first the white one.

    Then the green one so that's all we're doing inside that exact macro the next macro categories like this is the macro that runs when we select the category and is that macro that we're going to go over next so inside this category select this is the macro that's been assigned to these buttons here both the buttons and the icons here so in this macro what i basically want to do is to happen is i want to have a filter based on whatever's in here so meets filter i only want to show and i want to show the categories that are located so for example in groceries we see.

    We have those spelled wrong olive oil we've got three items here right and i want to make sure that those three particular types show up here inside the products right so how we're going to do that basically we want to run an advanced filter and i want to select the groceries here and inside those groceries i want to show all of the types that are located i want to take each one of these types and i want to create shapes based on that so for example insider here we have here all of olive oil okay so we have those three items so i want those three items to show up inside those.

    As categories so when i select groceries i want to show olive oil i want to show vinegar i want to show condiments and all of those things that are located right here then when i select the olive oils now i select groceries now you see the spelling is correct and i want to show those three types of olive oils okay so that's what i want that's a macro around so for this macro loan all i want to do is determine the types that are located inside that and then show these based on shapes and to do that what i'm going to do is i'm going to take this sample shape called sample type i'm going to duplicate that i'm going to give it a rename it i'm going to give it a text that text is going to be based on a loop that we run through over here so in other words we're going.

    To take whatever they've put in whatever they've clicked we're going to put it right here into k3 i'm going to run this as a criteria and they're going to have an advanced filter we want those results to come here determining based on the number of results we're going to loop from three all the way to the last row for each result we are going to then create a shape we're going to give it the name of the shape and we're going to give it a specific shape name those shape names can be all category type 1 type 2 here and then type 3 here okay so that's what we want to do so we're going to do that inside the macro that's the macro that's been assigned to both these icons and these categories here so inside the vba we're going to do just that we're going to focus on the pos sheet.

    DISCLAIMER: In this description contains affiliate links, which means that if you click on one of the product links, I'll receive a small commission. This helps support the channel and allows us to continuetomake videos like this. All Content Responsibility lies with the Channel Producer. For Download, see The Author's channel. The content of this Post was transcribed from the Channel: https://www.youtube.com/watch?v=FMq9OYMviq8
Previous Post Next Post