Alright thanks so much for joining me today we've got a lot to cover in the third and final part of the label the name card creator I've done so much work with this so there's a lot more I want to show you and so see we can now review and print so even if it's something like ID cards we can do that as well so I wanted to show you how we did that so now we can save a template so we can have many different types of labels many different ID cards simply put in the name here and click update the template and it's gonna be update and I'll show.
You exactly how we did that so let's go into the design phase and start with that but before I get to that I want to make sure we bring you these videos each and every Tuesday brand new ones with products you can use and show you new and amazing ways to use excel you may not have thought of if you want to get those just make sure you subscribe to our Channel and go ahead and hit the subscribe button and we'll be sure to give you notifications each and every week so that you don't miss a video also if you love creating these applications you want to do it full-time.
And create amazing income potential with your applications passive income I'm going to show you just how to do that just as I've been doing with my own applications for many years I'm now teaching how to do that in our mentorship program so if this is something that's interesting to you have a look at our mentorship program I'm going to take you through every single phase of the application including the defining phases the design and the development of course we're going to teach you even how to deploy including websites and licensing securing your.
Applications and every part in between it's going to be the full path so I will be revealing the entire process in the course so if you'd like to pick that up and I click the links below and I'll make sure to include that all right thanks so much so the first thing is let's show you how we added a text box and basically what the idea is when a user adds a text box I'm gonna take a copy of some type of a text box here it's in black we can't see it but I'll drag it over here I'm gonna take it a text box and give the user the power to create their own text box so all we need.
To do in Excel is make sure Excel tracks the number of text box that we're using and we know that this specific text box has to be included in the label so we want to make sure that so what we'll do in VBA is make sure we give it a specific title like text bar txt box 5 and if I add another picture it's gonna be incremental and so this one will be 6 just as we know so receive text box 6 so we have to make sure it has a specific name and assign it and all I need to do is really create a copy I can copy.
Number I want to make sure to include that and group that with errors so that's pretty much all we have to do in VBS we're gonna walk you through that code it's really simple I'll delete these particular so you don't see we don't need to see those and let's move into the VBA and show you how we added this particular thing into the developers there we go and Visual Basic and if we look we've got down additional macro here in our label design macros if we scroll down a little bit we see we have add text box add text box that's a brand new one and all I'm going to be.
Doing essentially is copying the existing text box that I have there's a sample and then what I'm gonna do is I'm gonna take that sample rename it so let me show you that sample it's over here if we slide over here to the left we see we've got this box temp but we use this as a template before and it's a simple text box called sample text box so all I'm gonna do is make a copy of that I'm gonna bring right up here around H 83 and then the user can then take a change of the font they can change the text they can change the background the color or do whatever.
They want with it once it appears and as long as it and then we're gonna sign a name of a very unique name and that's the important thing it's the naming of that tech box so that in VBA when we look over all the shapes those shapes with that specific name or don't yet include so we can do that with the following lines of code alright so text box number as long we need to keep track of the text box number in fact I want to increment that text box number accordingly so that each one is different to it track that summer where are we tracking that I'm gonna.
Track that right here in B 82 so as we go up the number goes up and of course when we have a brand-new design this is going to go back to one so new designs it'll go back to one and we'll just increment that number accordingly and that's course in B 82 all right so back into our code so the text box number of course is B 82 that's gonna sign it a number and then with the shapes sample text box that's the one I just showed you that one on the left we're gonna.
Take a copy of that then I'm gonna select H 83 and then do events and then application wait this helps remove any air is associated with when we paste it sometimes if it pasted too fast or it doesn't get it the first time we want to make sure that the paste works well sometimes this can avoid some airs that I found so we're gonna paste that right where the selection is and hidden a H 83 and then with that selection we're gonna give it a name remember I want to give it a very specific and very unique name text box it's always gonna start out.
With txt box and then I specifically could left out the e there because I
Wanted it very unique so a txt box is something of our specific name and then I want to assign at the textbox number this numbers gonna be unique each and every text box it gets added then all we need to do is just set it up there with now we can refer to it with the name that we've just assigned with shapes text box and text box number the text frame characters text we're going to go to the text characters of text box just.To give it a basics and users gonna change texts of course and then I want to increment left a little bit off not right on the corner of 83 I want to move it to the right a little bit so we can increment left 10 and then I want to move it down a little bit so we get in commit to top 20 that's gonna move it down I also want to make sure it's visible this ensures that it's visible in case it's not but it should be already next up I just all we need to do with this macros increment for the next shape the next text box that the user entered I want to make sure that this has a unique number so we need to.
Increment the text box number by one by doing this ba t2 equals dot range beauty to the current value plus one that's gonna increment the text box numbered by one that's it that's all we need to do and then what I added in when we designed those labels when we group the labels let's take a look at the loop so now we've added a little bit of a feature this was the group shapes remember we have to group all of the shapes all the data all the labels all the pictures and now all the text boxes.
So what I want to do is I'm gonna loop through all of the shapes in the sheet all of them and then those that include certain types of names certain names that meet certain criteria we want to group up so for example if it exists the word data we want to include it if it exists the word label we want to include it if it includes the word label pic we want to include it and now our item picture we want to include it or text box we want it now I've added this text.
Box so if includes txt box we also want include it so you can really see the pattern here of how we grouping shapes based on if they their name includes a certain specific text that no other shape on the sheet exists so I would make sure that every other shape you want to customize this doesn't include these because these are very specific to the label that we're designing and then the rest of the same and then we just prove it so that's all we have to do to include the text box and of course I have assigned that macro and the macro of course is add text box I've decided.
To that shape right here so if we right click any individual shape inside the group of shapes and we click assign macro we will see that it's add text box that's the macro that's been assigned to that specific shape all right so that's it so the next is how do we get these label templates to save so if we click update template is given out update the template and it's gonna store that template on the specific sheets gonna go through a few different macros and now it's been saved and if we see it we now.
Have the same template here in our saved templates we just have really one I had one small test but we don't need that but basically we just have one at this one it's gonna save a picture and it doesn't matter if they're gonna be all grouped or together the templates they're specifically named so that they can be copied back so what we did is we took all the information from this template along with the picture give it a specific name called template one included the details and we have actually more details than that we also.
Have imported the details database this is a database that takes care of all the called headers the first row of data we have that twice we have to know any filter data I want to want to keep the filter data the format the format type the data row label text the data also for its gonna show label remember show the label will show the data we need that all the information has so that when we load that data it's gonna all come back so if I were to double click this and reload that label it's all.
Going to come back but it still may not be perfect because there's a lot of different features that need to be saved here and already you can also make changes of course and then click update the template so there's a lot to show you I want to show you how we save that template and basically all we need to do is we have an update template button or we have a save template button both of those have the same a Mac or so if we right click that we going to assign to macro we see that we have the save template save update that's the macro we're gonna focus on so I can show you just how we did that all right.
Back into the VBA we have a brand new module called template macros right here template backers and the first macro is
Called save update now we're gonna use this macro for both save and for both updating and all we need to do to check it's just a few different things so if we slide back over here to the right and let's go on the desired page so we can see a little more of the group we have a few different items we now have the template ID we want out of the template row and we have a template ID every single.Template now has a unique ID so when we look here into the safe templates we see that there's a template ID also when we look for the template IDs we also the template details we see that we also have an idea that's very important each template should have a unique ID so we have more IDs they're all gonna be saved down here so keep in mind and we also want to track the next template ID so if I look in here in templates and I go into a brand-new name range I have to named range so into the formulas name manager and we're gonna scroll down here.
Under template and we see template name template ID let's just look those are two dynamic ranges using offset we can see that template ID here is an off so we've been over this offset a few times but basically it's going to track it's gonna grow as our template IDs grow it's gonna grow and then of course we have template names also based on the idea but this one is going to be template names I want to try both of those in dynamic named ranges those are the two really important fields on this so we're gonna use those in formulas to pull it so I need to know and remember if we.
Look at this the first one starts on row 4 that's important so if I'm gonna find the first record in this case it's one where do what row is it I need to always need to know the row so I can load the details in this case the first one is located on row 4 that's important because when we go into our label creator and we have the template idea of one if I have vba put in the one right here in b7 it's gonna put in that template ID but I need to pull the row number from that based on that one based on that template what is the row number well we can use this match Formula match.
B7 remember that is the template ID we're looking in the named range template ID that's in the dynamic named range I just went over for you and then we want an exact match we're adding 3 R at e3 on this because we want to find the first row I don't want to find the first record if without that it's gonna return 1 but I actually want the road remember our first one started in Row 4 so we must add 3 so I wanted to determine the row based on the ID but I also wanted to turn the RO based on the name we have the name I want to do both of those there.
Are times when I need to look up each one of those one based on the ID and another one based on the road they're gonna be the same of course but there are different reasons for that okay so it's important we'll go over that so the template row based on the name here again it's almost the same but in this taste in this case we're matching template names based on what is it e - based on that's the one that user selects so if the user makes a selection change here of a template I'm going to look right in b9 for the row that make.
Sure that they've selected a proper selection why do we do this well this gives us the ability for users to type in a brand new template name here so simply typing in a new name and they can save a brand new template based on the new name that they change so it gives us the flexibility to save brand new ones so I want to make sure to show you that as well and also I want to know when the template is loading so for example if I select here which I just did it's gonna load all of the details from a local label and the data filters and the design details so I'm gonna turn this to.
Fall I'm gonna turn this to true while it's doing that and then back to false this prevents other things from going on at the same time when it's loading so I think that's pretty important and I'll show you why it's just a moment okay so just important thing know we we have a row based on our name here we also have a row based on our ID here both of those will return the same values but in different areas they're important for different reasons alright so let's get into it right now into the VBA when a user makes a change to e2 that it's the label name so we're going to label.
Creator and we scroll down a little bit we have some new code here on change of template add in ID if the found alright so the first thing you want to do is I want to look up that row right so on when they change the template we need to do a few things we want to make sure that b9 does not equal empty what is in b9 let's just review that real quick that is of course the calculated row b9 is the calculated robe based on the name we want to make sure if it's an air it's gonna return empty so we want to make sure that it is not empty so we can do.
That range b7 value equal to sheet 6 which and sheet 6 is our imported details we're gonna put in that ID I'm gonna put a note here add in template ID I want to put that template ID because it's been found right we know that b9 is not empty so we have a row we know the row so b7 is where template ideas can go we're gonna put away take it from sheet 6 call it a and then b9 value let's just review what what that exactly is sheet 6 which is our seat element column a here the row.
Number we know the rows four and we know what to call me I want to take this ID where it right where it's found and place it right in here in a b7 so that's all we're doing with that line of code okay so next up then we're ready to load the template I'm gonna go over that macro very shortly I'm gonna load the template run the macro that loads takes all the details and loads them in and essentially what is it doing it's taking all the details from this first row including the the label name the data file the datasheet the first row at all.
The data details it's also going to take care of the import details also going to load in all the data information the first row data from that's to load all that into the label creator based on this and of course we're gonna run an advanced filter because I only want to go template IDs number one if there's more well I'll put them in here so we're gonna use an advanced filter with criteria and it's gonna return our results our only one in this case we only have one I didn't have time to create a lot of labels on this because.
I've been working so hard I want to get you this training so we're gonna bring all this data back into it based on the results because we only want to focus on template ID number one and bring those back into the load template and I'll go over that just a bit so the question is they don't ask you is if it's not found what's gonna happen well if it's not found it's gonna be a new template right so I want to make sure that that that this button changes to save template save template so if it's not found and they type something in I want to make.
Sure that this button changes to save as template notice it just changed from update template to save template so that's very important but when they select something else like test two then I want it load the template basically loaded but and then it changes back to update template because it's now an existing template so we have to recognize whether it's an existing or new one and then change the buttons accordingly so that means else else means it's not found right b9 is empty meaning it's not found then then I want to make the save.
Template button that's the button they look the same but they're just different shapes equals true and the Update button equals false so we're gonna show or hide save or update based on whether it's an existing arguing what this does is allows us to use the same field for both new so this is a drop-down list data validation but in this data validation we all allow the users to use any type of value in there so then notice there's no error alert on this one the settings are the template names where there's no.
Error learn what this is going to do is allow users to use the same field for selecting existing templates or adding new ones it's a multi-purpose field so that can be really powerful in your applications okay so let's look into it back into the code so we understand how this is very simple but now let's all go into the template load and see just how we temp load it but before that I want to focus on the templates say is save and update and remember when we are loading and saving templates we just we're going to use the same macro between saving and update so even though.
We have two buttons a number if we have a say button we have an update button both of those are assigned to this macro it's more for a user purpose to save as new or update same macro but all we need to do is focus on just a few things we're gonna do the template row as long and you know what row that template is on and of course the template column we're using data mapping air I've gone without a few times look over briefly basically what we're going to be doing is we're going to go into this save template and if you notice these cells.
Up here these cells are related to the same cells here where the data is located in these label design area and they're right here so these are the same cells so the label the table detail of course is e8 while the labels per seat is nine so those are all based on this and of course this information is going to always come from the label selected so when we load the label disinformation going to come so we don't need to save this information but of course the filter and the data this information is.
Important such as f-22 you know f23 so this information does get saved at the same time because that's based on the data so we want all make sure that f-22 it's the f23 so all those values are going to come from here so that's why we use data mapping here so the top is it's a web assistant those are unique so those are based on the label here the position of the label we need to know where the position is and that's the position of course according to H 83 the position how far away it is if we move.
It down lower it's gonna be so it's based on the position the top position here so we can always put that label back in the exact part that we had it before alright so we understand data mapping just a little bit because I'll run over that quickly so we need to basically loop through all the columns when we're saving it and I want to take whatever is in for example here in f-22 and put it in the right spot in our template in our in this database right here alright so let's continue on with the code back into the code so we have.
The template ID the last data row needs to know the last data ref we're adding brand new data we need to have last data row also we need to loop through all the data row the data rows long the detail row as long and the detail row we're calling disk so we have the detail rows here we have a save templates are here and of course we have to label the data rogue we're gonna loop through these days we need to know how much I just know that the last data rows in this case on D 35 because we need to loop through all the data to make sure that.
That gets saved inside the detail starts 27 goes all the way to 35 I need to make sure that gets saved as well okay back in to our code let's take a look we're gonna dimension the label shape as a shape to me I need to bring it bring that over into the template with sheet 1 if range 2 value e 2 value equals empty remember we need the template name if it's empty we can't save the template without a name please make sure the label template has a name e 2 select we're gonna guide the user where to put in that name rather than you to and exit the sub we.
Can't save a template by the name so we this ensures that the user does give it a name for the template if b6 value goes empty it's a new template b6 let's refresh your memory on that b6 of course is right in here that is going to be that bro based on the template ID so if that's empty we know it's a new one we know it's a brand new label so when you differentiate between new and existing so we can use b6 to tell us whether it is okay back into the code if b6 then.
It's a new template if it's empty so what do we do for a new template well the first thing I want to do is give it a template row we're gonna use the first available in sheet five sheet five is where our main template information is we're gonna use a nine end and Excel up this is going to get us the last row of the value this is gonna give us the first row after that it first available row so we have that and all we need to do with then is add in a template ID how do I get a unique ID for this brand new template well we can use something called the Mac the next template idea.
Here in b8 what is that well that's gonna be the maximum of all the template IDs remember these have to be numerical down to all be numbers plus one so we're gonna met the maximum number that's been in of all the template IDs plus one that's gonna get us a unique number in this case we only have one so the next number is 2 so we where you now take this 2 for our new template and put it right here in a 5 a 5 would be the first available so next up we can then run a loop it goes all the way through from here to here for the next line so we can.
Take all that information and pull it from u2 and put it here pull it from EA to put it here so that's we're gonna do inside our code alright so we could do that so sheet 5 that's these two things are if it's new else it's an existing else we know our template rows and b6 so that's all we need to do so whether it's new or whether it's existing or getting the template row the only difference is in a new one we're adding in the template ID into a and the template row and we're getting it from be a that's the one with.
The max so everything else after this and if is all the same regardless if it's a new or an existing template and the template ID now is located in sheet five regardless if it's new or existing it's gonna be located in sheet 5h a in the template row so we can assign a template ID next up we're gonna run that loop I just spoke with column one would be the template ID but we've already taken care of that so we can start our loop and column to go from columns to tonight so sheet five sells template row template.
Called value what is the value that we're gonna be placing in the database we know the column we know the road we're looping through the columns from two to nine the Rho is constant we know the row there what does it equal it's gonna be will range what is the range will that range is found in Row one of the tempo column so this range here whatever cell at e 20 3 F 20 through whatever it is it's gonna go right here and we're gonna add in the label data just with this one line of code and as it loose there's gonna add each one from two tonight next up we're ready to add.
In the design information or delete existing if any the first thing we want to do is the template ID if I'm saving a template ID here I want to make sure that in case two users made any changes or an existing regardless I want to make sure the template one here gets deleted because I'm gonna bring in a brand new and the user man made changes I'm gonna bring in a brand new one in place here so we need to delete it but if it doesn't exist it could cause an heir and therefore we've wrapped it in on-air resume next right here on there's our.