Training so let's get started all right thanks so much for joining me today I've got a massive application probably gonna take us a few weeks probably two maybe even three weeks cuz we've got so much to show you in this label and named card creator the first thing I want to say is why why are we creating now this is beyond mail merge write mail merge is a great tool for creating labels in work but I want to take it way beyond that I want to create my own labels I want to design my own colors put my own labels I.
Want to be able to positioned very easily simply with drag-and-drop I want to be able to customize the label text I want to be able to color certain new certain fonts and use certain background colors this training is going to show you just how to do that this takes name cards and labels and brings it to a whole new level and it's gonna show you just in a few simple steps so not all begin to create amazing application but I'm gonna show you just how we do that so you can do this yourself so it's a fantastic training so I hope you'll join.
Us for it we've got a lot to cover in this episode but before I get started I wanted to cover business we bring these trainings to you each and every Tuesday so I want to make sure that you subscribe and get on that notification so that you can get videos to deliver to you each and every week it's going to be a great training this week like every other week we bring you something special and unique also I wanted to make mention I've had a lot of questions on the mentorship program it's already launched so it's a fantastic program now.
The time to get in in this particular program I'm going to teach you how to define how to design develop and deploy your own Excel applications in fact we're developing an amazing accounting application inside the program and that's gonna include invoicing inventory purchasing a full dashboard sharing and sync feature so that your applications can be shared with anybody in the world and sing so I'm going to show you all that along with your own licensing and of course the deployment so we're gonna go all the way through a WordPress.
Website it's gonna be an amazing program so make sure you get in and of course if you're a mastermind member you're gonna get resale rights of this accounting application that I'm developing so you can sell it as many as you want at your own price I'll include the links down below if you want to check that out I really appreciate it likewise if you like these applications there's a free download links below but if you want to get over 100 of them I've got a promotion for $37 so that's the 100 workbook zip file you can bribe 100 of.
Below with that this is YouTube or Facebook or your email or within our Excel for freelancers Facebook group let me know what you want to see and I'll do my best to get that included in this application I want to go way beyond word and there's lots of training videos on how mabel's can be created within word and it's a great tool for basic labels but I want to go beyond them I want to create something that's really powerful that's really easy to use and that's really customizable so for example in this training we've got tons and tons of label templates now if you take a look.
At our label template sheet I've got about let's see about 25 or so label templates in fact you can put own label templates and some of them have data like column spacing now let's just take a quick look where did I get this information from well that came from a site called Avery they're like the number one in the US as far as labels and they've got tons and tons of labels here so most of the data that I got from trees from here so if you want to fill it out of bring your own label all unit is just get the data from you know I just took a little thumbnail of the picture here and brought it into.
Cell and you can see it for example like on this first one here it's a 30 label per sheet so if we click on it and on this sheet here if we scroll a little bit down we'll get all the details on that label we've got the size we have the margins here the top and bottom margins we have the left and right margins and so there are all the details here so all I did was pretty much take that at least for some of them and bring it into here so we have left and right margins top margins and then I put a little picture here so that is basically what our data you can add to this list.
As much as you want so this is a really dynamic application let me just take you quickly through it first so what we all we do is we select a label template from a list of label templates here and then the thumbnail is gonna show up in all the details as far as the labels per sheet so all of the information basically is going to come from this table and it's going to go into this form that's relatively simple when we click Next we also have the ability to select a data so when we select a label.
Data we can click the Browse button and we can find the label let's go ahead and I've got some on my desktop here Employee List so let's select an employee all the information is gonna come in and then we want to select a sheet so we for example a sheet within that will go employee list now if you think this looks familiar we did an import management that looked a lot like this so there's a lot of the qualities of the import manager if you remember that we had this but it's a very different application but basically the importing the data is very similar once.
We have all the data in here we can then go ahead and filter it for example if I want only female I can do starts with
Ends with or contains less than it greater than if it's a number but let's say I just want female here labels up for the female does so I can do that then the next up this is all automated the first row of data you can update this accordingly but it should be pretty set its automated then we're ready to design our own label so now we have this let's delete there's one thing I want to add I want to make sure that these are.Automatically deleted when a new data so that's something out Dada impart to you these should any time you create a new data this should automatically be deleted that's just a simple macro I've got a do not the background okay so we have that now notice that when we change the label design if we were to change it to let's say a different label design like this type of label design when we go into design label we see the label shape is different so the background of this change is based on the label which is really cool so you get a really good idea of you know the design so this is.
What our template is the sort of label design so when we then select let's say I want to show the employee ID and I want to show data so all you need to do is drag that around and then customize it just like you would so in fact this one oh go ahead and I'm gonna drop this down because I'm gonna be working with it I'll make this left justified and I want to make this right justified and I also want to change the employee ID I don't want to change it to employee let's say I want it I want to call it ID number I can do that too right and so as.
Soon as it changes here it's gonna change within that which is really cool we can reduce this so now you can customize it just like you would any other fields in Excel it's gonna remember that so we're gonna bring it up here I'm gonna bring it up here like this and then I'm gonna hold those and I'll format those so I'm gonna line the middle here and then I want to make this bold and I want to make give it a color give it a color of let's say blue so we got that ID number and then I want to.
Add in that and I want to add in the last name and bring that down here I want to add in the first name bring it back here reduce that a little bit so we can customize that I'm just gonna quickly customize this here so that you can see how we can quickly create labels that are really really powerful keep that left justified and so I'll line it up and I'll go ahead and so you don't have to watch me but you get the idea so we can just select I want the address and I want the city the state and the.
Zip code I want to bring all that here so we can just drag them so it's very easy to customize your labels and mold left justify those alright so now we're getting our label completed here and I'll bring this date down here and we can so as you work on these you can then align them just as you would so once you have it said it's really gonna be I'm not gonna make this perfect because I don't want you to have to watch me but you get the idea here so once we have everything set here all right let's say.
We have our label set good I like that and we'll put in one let's put in a status and there now we have the status here and okay so it's active so now I got all the fields that I really want here we want to make sure that they're within the boundaries of the label we don't want to go outside right like this we want to keep it inside just as we would so we have a really powerful designer I'm gonna align those as well in the middle all right and then we can make this bold okay so now we've got our.
Label it's not perfect but it gets you get the idea let me just left justify these it's always nice to left to actually left just fine and a line of left okay so now we have them and what's the next step now that we have our label and we like the way it looks we have everything here just set just the way we want to what's the next step well the next step is the review and print that's pretty much it we have everything so now it's a review and print now it's gonna automatically create these and it's going to go through the first time it's gonna go through a little bit of a as we.
Create these labels and there now it's done and it's creating the labels and all now great now we have our picture this is just a picture that's been placed there to give you a sample but the actual labels that are ready to print are actually on this sheet so the printed labels and everything's formatted accordingly notice the calls are automatically formatted the rows are automatically formatted based on this and it's ready to be printed so all we need to do is go into our label creator and then click print labels and it will print all the labels to your default printer - not yet right now so here's my Snagit.
Here so everything is printed just as the way it should be even the colors everything is already set so it's a
Really really cool feature we've got the number of labels to print and the sheets required so that's gonna be helpful you know how many to feed in the printer and so there you have it that's how we do it and now let's go ahead and get into under the hood and show you exactly how I made this and in parts 2 we're gonna be I'm gonna I want to add the ability to add pictures to this so I'm going to add a little browse button for adding a.Picture so we can add pictures to this and it's really going to be a lot of cool trainings so let's get started on that so the first part of this is this little tab design I'm gonna go over this quick because we have been over this tab design and notice that every time you click review imprints it resets it so that's kind of helpful keep that in mind so what do we do alright so this tab design lets get into the VBA and just show you how we did this but this is not part of the core training so I'm gonna go quickly through this tab design basically all we're doing is showing and.
Hiding rows so in this first one I want to show rows 5 through 16 and hide everything else in the filter and data section I wanna show rows 17 and then I guess we're gonna go all the way down to because it's a table all the way to 77 so keep that in mind and then the third one we're going to show 78 through down to 132 and then of course the last one review and print is going to create these labels each time we go to review and print it's going to show you know.
133 to one so we're gonna basically showing and hiding rows and then we're showing and hiding certain different shapes and group shapes so for example this particular group of shapes is called step group step for group step for group so it's only associated with this step for while this particular group of course is called step three group here and so on so you get the idea so let's go in to the VBA under the developers tab if you don't have the developer tap open you can locate that under the options here and then the customizers.
Are going to make sure you've got developers selected here alt f11 will get you there also as a shortcut so visual basic so we have multiple different modules here and we'll go through each one of them as much as we can to today and then we have a little bit of on sheet code on the label creator but there's no other code on any other sheets just the label creator so let's just go into the tab macros real quick there's one macro that we want what if there's one single macro when you have these kind of tabs we want one macro to clear everything so what it's.
Going to do it's going to clear all the shapes no matter what it's gonna clear them all and hide them hide all the shapes it's gonna also hide all the button sets and it's gonna take all the buttons and give them a consistent color so every time we run it's gonna run this macro so notice on creator on label step four we're gonna run that macro on step three we're gonna run that macro so and we're gonna run this macro first because it it basically it hides everything it changes all the callers to the buttons to all the same color and it's gonna.
Give it that dark green color and it's going to give it the font fill color of white so this is the white font this is the dark so it's every tab is going to have that particular color so let's go and that's the database so every every button is gonna get that white font and the dark green even the one you select then depending upon the one you've selected it's going to give it that light green color and black font so we run basically we just hide everything we color everything in that macro then on each individual step what we're going to.
Do is we're going to then change the font the for color the RGB this is that green light green color and then this is the font this is the black font so just that button step one just that button only that button gets the text that's black gets the background that's light green then what I want to do is um unhide the rows remember unhide just the rows that we're focused on so only the rows and then I also want to unhide that shape remember that that group shape that is just concerning I wanna unhide.
That and then of course if there's any in this step one if there's any label picture for example I want to show that if there's one so let's I'm gonna close that for now and let's so for example if there's any label picture this thumbnail picture that's gonna give it a name called label sheet picture regardless of the label that I select it's going to give it that so keep that in mind regardless of whichever one we select we're gonna assign the same picture so we're gonna we want to show that picture no automatically if it exists and I.
Wrapped it in honor for some reason it doesn't exist I've wrapped it with on-air resume next so keep that in mind and then I just want to select one of the cells as we move it so when I select her I just want slightness should probably be e 8 right 8 so let's select that ei cuz we want to select it on up okay so I'll save those changes alright so we've selected that just want to select something so the user can prompt them to select a specific label if they want alright so that's pretty much it and so I've also assigned macro so if we.
Right-click and we click assign macro we see that that's been assigned to step 1 so that means users can either select on any one of these buttons or they select on the next now obviously this macro that would be assigned I'm gonna click assign macro and I'll drag it up here you can see that it's a create label step to this macro is the same that's been assigned to this so if I select here and right-click and click assign macro you see also step two so it gives you an idea as we go through now when we're selecting on step two we have two options of previous this would be.
Assigned to macro step one and this would be step 3 gives you the idea so that's basically how we run through there are specific trainings where I go over this in more detail so keep that in mind you can check it out on my youtube channel so there's various trainings with this tab order so you can show that alright so we've gone over the tab we get oh we've gone over the basics let's go on some on Schumaker so how do I get this to change so when I select another label what I want to do is I want to take the picture whatever is associated.
With this picture and I want to bring it copy it and bring it in here and then I want all the data to now to make things a little bit easier this is a complicated training so I didn't use any data mapping so there's a little bit more code written but it's much simpler to see data mapping is easier to code harder to understand so I'll give you that an idea of that so basically all I do is take this take this template whatever it is here now let's take a look at these templates I've given them a unique name and I wanted to make sure it's unique so I've given them a file name of based on their.
Measurements so this is the file name so I took their measurements which is the height and the width and then the number per sheet and I've combined them to create a file name so each one is based on that so for example this label is one by two point six five inches and then it's 16 so I've taken that information used in small formula and then just created the name and I've assigned that same name to this label so if we click on these labels we can see that the name of the labels it's consistent with based on the shape and it's also the file name here that really gives us a helpful idea.
Of how to name these uniquely so that way when I pull them up I just need to know what what is here and I can know the shape name based on what it's here so in our macro soon as we make a change to cell e8 we're gonna load all the information all the labels per sheet comes from the table the label type and column spacing row spacing marks and all that comes in so let's just take a quick look at that that's a relatively easy macro so into the developers sheet and we have label load and basically this is just a single macro we're focused on sheet 1 so that's that's the only one.
That we're going to be focused on with this for the most part except the data is going to come from sheet to sheet 2 is where our labels are located so I need to know the labeled row that's very important how do I know the label row let's just focus on that real quick we have label templates here so we go into the formulas name manager and let's took a label name here this name right here there's a few additional name ranges I need to remove some here so don't worry about that so if we look at we have an.
Offset offset because it's dynamic I want this to increase as our number of labels increases we're gonna start it out an a to go all the way down to a 40 or whatever you can increase this when you add more Prime maybe I should make this 100 okay just in case alright so 1000 not gonna have that many labels 100 is fine alright so basically what we're going to do is this named range is associated with this now notice that this name branch starts in Row 3.
That's important Row 3 is where our first data is so now when I have if I have a listing of this name here back in sheet 1 here and I want to know the row of it I can just use a simple match formulas so let's take a look at the selected labelled row we're gonna use if air in case there's no air we're gonna use them match what are we matching e8 that's the name of the rectangle where are we looking it up we're looking it up in this named range label name that's the same one I just showed you we want an exact match so this is 0 and then.
We're gonna add 2 why are we adding 2 because I need to know the row so if our if our first one starts in Row 3 we need to add 2 to get so this is going to return 1 on our match but we want the road not the first so we add two that gives us Row 3 that's how we know the exact row so for example this one would be the second one in the list that's why the first one is here so the second one in list is going to give us Row 4 we need to know that Row 4 because I need to know I need to load all the data from Row 4 so we need some other row alright so moving on again this is what.
It looks like when you're not using mapped data more code lots more code if I use map date I can do this in just 3 rows or 4 rows but in this particular I want to show some what it's like without mapping mapping can get confusing to see because it's associated columns so if you if you have if we have a lot of data this probably could be made of mapping you know we've got a lot of rows here but this is without data mapping so you can see both and basically all I'm going.
To do is gonna take from each column in the table and bring it over to one field in the row so that's all we don't need to go line by line but basically we're gonna go through column by column by column and bring all that data into a specific cell here here here that's all that that code does pretty simple easy easier to see easier to understand but but tastes longer to code and the file name remember the file name is located in H and the label row I need that name of the file because that's the name of our picture so that's located in H remember our picture has the same name.
As what's here so all I need to do is pull this copy the picture on this and bring it into here rename it because I want an only one name rename it label sheet and then position it basically position it right at h8 and then move it over to the right a little bit and I'll show you just how we do that in the code so the first thing I want to do is win in sheet 2 which is our data we know the picture name we've already defined that in age I'm gonna copy it and then h8 in g1 because we've already dimensions sheet 1 here so h8 we're gonna select and then we're just gonna paste it right in there and H and then what I'm going.
To do is I'm gonna rename it so if there's any shape that has that name already we need to delete it we need to make sure that we delete any old ones if there's not if it doesn't exist if we created hair so that's why we've wrapped it an on air resume next my art goes there alright now we're ready with the shapes picture name right we've brought it in we know the name but I want to rename it so the first thing I'm gonna do is name it the label sheet that gives it one consistent name regardless of the label then I'm gonna position it on the.
Left and on the top of age then I'm gonna lock the aspect ratio I'm gonna give it a set width because I want to make sure it's all they all the same and then I'm gonna move it over to the right increment left 40 is gonna move it over to the right 40 pixels if I wanted to move it over the left I'd make this a negative 40 and then it's like so this is a very simple macro that's all it does is it just loads the data so as we change the data here it's gonna load so each time we make a change it's gonna load those labels up and it's gonna pull that label so you see how we did that.
And the next up how what I want to do is I want to mark b4 to true and why we mark and B for the two the label creator here when we make a change to be eight we want to run that macro label load and I also want to set b4 to true because I want to every time we make a label change we need to run a macro I need to run this macaron review and print that's gonna resize remember this printed labels these are all set look it's set for three columns right.
Now and set for these rows but I need to reset it now that this is true so when I do review and print it's going to now go through Maclin takes a little bit of a time to reset all those rows depending upon the data and the type of but it's gonna take a little bit of time and it's gonna redo those completely redo those columns and the rows now it's done and so now when we see when we go back in here we can see that it's all now we have two columns and the rows are much bigger of course our label is really set for a smaller we didn't change the label.
So in this case you of course you'd go through the design section right we'd go through the design and we'd make sure that that all the fields are larger of course but for our purposes we'll just stick with the basic label here so gives you an idea so then it goes to so when now but now look when I click review and print it's much faster because we're not resetting we didn't change the label if we don't make the label change reviewing prints very very fast because we don't have to reset the columns in the rows so it's always the same but I'm gonna reset it back to our original label just so we.
Have a consistent label and shape which is this one here and then I'll run the review and printing in just a minute and we'll take a little bit more time because we're resetting the roast we're resetting the columns and then our labels are consistent with our columns and rows so each time this runs our labels that were reset and our labels are built so you can see how that's built up and now it's done and it takes a little bit of time but it's covered and so also keep in mind that look it goes from one to three to five because.
Some labels have a spacing of a row spacing some labels there's a space like for example different labels if we look at some of the labels here like like gallon zoom into these labels this label has some spacing in it so we need to set the spaces so row spacing is one there's a little bit space between the rows and there's some space between the columns so that's gonna set that helps us really figure out exactly the right you can.