Specific text blocks or data item or even a global variable such as maybe a date or time we're going to be able to add all of that in even with any type of feel any type of variable we're going to be able to select on it we'll be able to customize that and i'm going to show you how you can automatically print those documents email them as pdf and we're going to send them to all or some or many of your customers or any type of data now in the past we have filled out pdf forms we have also customized word documents but i wanted to create something different this week and this week we're going to be able to create our own contracts our own documents.
All within excel we're going to be able to create unlimited if we want to add new templates we can add brand new templates we're going to blank canvas we can create any type of text block here just add a name and we can create it's going to add that item directly to our document we're going to be able to customize that right we're going to be able to save our template so we can create any type of word we're going to be able to save that information so if i want to go ahead and make this bold and we can even change the font increase the font and we can add a name to this test template it's going to be able to add our own templates our own documents just like this let's go ahead and create that and put it we can set a specific data table we just have customers.
But you can add your own data tables and we can even have a template type let's just say customer and so once we do that we just save or update that template it's going to automatically save and then we're going to be able to add basically simply select and it's going to come out so notice that we have now three templates and we can add pictures we have least agreements so you can make your own templates we can add fields to those temples so if we have a title like this for example we can go ahead and add a field let's say we want a specific customer field we just click.
New item and now we say okay maybe we want a data item what do we want to show here well maybe i want to show the let's say customer name so we can show the customer name we just type in customer name right here and then it shows us our customer name we just click add the item it's going to add that customer name automatically here just like that that's a sample customer so we can put that customer name in anywhere we can add lots of data we can also add a picture if i want to we can just select new item click the picture adding a picture here select any browse for any type of picture that we want just like this here and we can pretty much add any type of picture we want let's say we want to add this we can add a picture here it's really really a great training so once we have that we can add the picture to the document just click add let's give it a name here.
Test picture here and then click add the picture it's going to add that picture here then we can shrink it down once we add that picture to our document here it's going to save exactly in the same position we just clicked save and update that now what we do is we select a different template go back to the test template and our picture is in the exact same spot in the exact same size as we had before so we can create tons of templates tons of documents and i'm going to show you exactly.
And that means taking excel applications creating amazing secure protected and licensed applications and how to send them to your customers for free and actually then get paid for it using recurring income once they get license number once they purchase your applications i'm going to show you that entire process because i'm doing that in the mentorship program in that partic specific program i'm going to be creating an entire accounting application we're going through it that's going to include invoicing inventory customer information dashboards sharing in sync all the features that you want to know how to create license your own excel applications for passive income.
That's what i'm doing in the mentorship program hundreds of students are having an amazing time in that program right now you can join anytime i want to show you how to take your excel skills and create financial freedom with that so if you like that my excel mentor dot com my excel mentor.com that'll get you there all the details i'll include the links down below all right let's get started i've got so much to show you in this particular training it's a comprehensive training not only are you going to be able to learn how to create these kinds of documents with these kinds of fields you are also going to learn how to work with shapes you're going to learn special formulas you're going to create buttons we're going to show you every step of course i often like to create these from scratch but when we have these kinds of comprehensive trainings this will turn out to.
Be a six hour training if i do it from scratch so in something like this where it includes a lot of different aspects and a lot of different organization i want to make sure that i give you every detail i move slowly through it so i'm going to do just that i'm going to show you everything i did every macro every line of code every formula so you can not only create these on your own not only get the excel skills but to be able to create your own applications with your own flavor and also this something like this is great to include as part of a larger application where.
Customers can create their own contracts fill them out automatically so for example if i take this contract i can send it to my own customer i can select a specific lease agreement i want to send it to a single customer i want to print that document i just need to print it out and it's going to automatically be filled out automatically it's going to be print to my snagit editor and there it is right there there's the fill it out you can see it's already been filled out with the customer information we can do the same thing with email if i want to email this particular it's going to automatically attach it as an email here it is right here that's the lease agreement and it's automatically filled out with the customers now we can also send that to multiple customers i'll go ahead and open this up and we see it's already been filled out inside the pdf so how.
Are we going to do that how do we do that we can also set it to multiple customers all customers or you can send it to customers by a single type now keep in mind this is going to be from multiple databases here's what i have i've got a customer database just some customer information i've got template items these are items specifically for the template notice we have three different templates so we have a template id template number one number two and number three the one that we just created that's the test that we just created that's template number three so all of this and we're gonna store all of the information what's really cool about this particular template is any changes you make get saved now i really like that it's really important let's say i want to increase this font so let's drop this down and increase this font a little bit i want to make it.
A larger all i need to do is click save and update that information save now let's clear that out go
To let's say the non-disclosure agreement notice we're back in a different agreement go back into the lease agreement and notice that font's been saved if i want to bring it back down again to something smaller again save the template bring it back into the non-disclosure back in here see how fast it is and notice that everything's been saved the position if i want to bring it down and i want to increase the font a little bit back to where it is i can do that just saving the update the template so i'm going to show you how to do all of that delete template that's something i'm going to leave for you i've left this macro open because i want to see what you can do with this i want.To see what you can do it this is your challenge i've gotten you to add new save i'm going to keep delete there's no macro assigned to this because i want to see you do that part of this training it's not just me giving you templates that you can use but challenging you to create and challenging you to add your own flavor and also complete these when they're you know there's always a lot to do i only create these in let's say one to three days so i want to make sure that there's things left for you to work on and challenge yourself to increase your excel skills not just to give you free software but to increase your skills so that you can create these and of course get that financial freedom using your excel skills so delete template is something that i'm leaving open.
Don't ask me for that because i know you can do that i have confidence in you if you follow my videos you will be able to assign a macro and delete that template it's very simple okay so let's go on how do we do that there's a lot of features in here so we're going to go right if i select on a specific item in the document it's going to show me that item if i select on this top it's going to select that specific top section and it's going to make those changes the text in here so if i want to change this to something else i can do that i can add different text let's say if i want to have a text block here notice there's a text block but if i want to change that or change the name of it i can do that as well what if i want to add a new field i can just.
Click new let's say i want to add a date field i want to say i want to click let's just say i want to add a specific date field so i want a global variable and i just want us to call it current date i can do that now we can add data variables what if i want to add the current date i can add that too and just click add item it's going to automatically add that item here maybe we want to do something like month or change it out so maybe we want to say uh this day i want to know what day it is we can just click the update item and it's going to update 13 that's the current day but what if i want to know the weekday just and also if we select on a specific item we can then change it this weekday is going to do just that it's currently sunday the data recording so i can show.
That so there's lots of global variables what are those global variables and where do they come from well they come from a small table right here you can add anything you want here and you can add any value you want here and format them accordingly so we have global these are constants these stay the same of course these are based on a formula so the current date would be today the current date and time would be now the current date is also today but it's formatted as a day so how do we do that notice these two are the same but this one's showing 13 this one's showing 12 13. how do we do that well it's just simply with a custom format so if we go in more number formats and we take a look at this we see that this is the type d that is the format that we have assigned to that.
Same thing this would have the format of ddd four ds on this that will give us the custom format on that the dates if we have this for december we want a full month again we use month m and then we use four months if we wanted to create a shorter we would just do m that will give us something like december but adding additional am will give us the full range and then of course the year would be 4y so you can figure that out so all i did was add that so now what i want to do is i want to basically add this select on this and i can change it to anything i want i can create the year and what it's going to do is just click update item it's going to update that.
Automatically very cool all right so we can also delete any item just simply selecting on it is going to delete it and that will clear it out so just like that we can delete an item very cool and automatically refreshes and clears out the list so that's great so we want to do that we want to actually update that so i want to put in really four different types of items here i want to put in pictures so let's say i want to put in a logo on here i click new item and then i click and let's just say picture and then we can add a picture just as i showed you before we can also click on data item and this is a specific database based on.
Our customer right so we have data table based on customers when you add additional tables you can create any kind of table you want here then what you want to do is you can assign this template to a specific data table so if you have items or inventory items or customers or vendors or invoices or whatever you want you just add this to the list here and then we have template lease then we have lease agreement so then you just give it a specific name you can add any type you want that's just information and then you just click the save or updater ad so it's really comprehensive and the ability to do this is going to be fantastic so let's go through oh an overview and then we're going to get into the vba exactly how we did that so as far as the overview basically i have a very.
Very simple print and send documents all we have here is a selected template we're going to send to who we want to send it to whether it's all customers right all customers here or we want to send by customer type or again by a single customer if it's a single customer we're going to select from a single customer here a customer list if we have a select by customer type we have a drop down list we're using conditional formatting for this to show and hide fields accordingly or if we want to send to all customers of course there's nothing so how do we do that well we've got conditional formatting here so let's take a look inside the conditional formatting manage rules and we see basically some fuel rules here we have based on g6 through h6 of course that is going to.
Be the customer type consent cut type or prospect here edit if we edit that rule we see that if h4
Does not equal customer type then we want to give it a specific format i want to give it a font color of this light blue which is the same as the background i want to clear all the borders out which means clear or none actually including none it's going to see no borders around here and i want to give it a specific fill called that fill color is the same as a background okay so that's how we do that's how we hide it what if we want to use the single customer if it's not single customer if the h4 does not equal single customer then what i want to do is i want to hide d and e.So we're going to focus on that same thing what we're doing is giving the fill of the color same color no borders and the font the same color so that's going to hide that specific d through e d through e right here okay so that's how we get it hidden so if we want to click on single customer it does equal then we do want it to show up this is a drop down list i've got a few named ranges so we can take a look at some of the name ranges that we have here it's going to help us moving forward so we're going to ignore the criteria those are created based on advanced filters i'm actually using a table this time it's going to be the customer table these are going to focus on the table this is our actual table that we're using here in the customer so i've created a table that's going to help us with the project itself so we have the table here all right moving on what.
Else do we have in the named ranges back inside the name manager we have the customer name this is a specific using customer name that's for customer names we actually have our customer headers which is going to be focused on our customer headers that's going to be important because we need to select when we want a data validation if we want to show a customer email or something we need to do that i've got a global name remember we focused on our global these the names of global we're using offset that means as this grows it grows itself we're going to use offset m7 1 i'm using m7 why am i using m7 because in case there's no data i want to include the headers but notice i'm using.
One row down one row down that means that we don't want to start with that header row and we're going to count everything from 7 to 20 but i'm going to subtract 1 because i'm including the header but subtracting it because this way when there's no data it will not create an error that's why it's offset i also have one for global values so these are the values based on this an item id if we take a look at item id i've got the items row i want the i these are the rows of the items and i'll show you that a little bit later just keep in mind so we have these are all the template block items so we create a template then we have all the template ids that's for so one would be the first template two would be the second three would be the third template then we have the row these are rows i should really call this bro so i'll go over i'll change that should be item row not out of.
My d okay print area we need to set a print area that's going to be a dynamic print area and i've got a really cool trick to show you with that how do we create a dynamic printer even when they're all shapes right how do we do that i'm going to show you exactly how that template id is going to be based on the individual templates we have three templates we have a template name a table that is associated with and the type here and then we also have the template name here so the name associated with those templates so that's for the named ranges so what i was mentioning just before what how when i look these are only shapes right these are only shapes so how do i know where the last shape exists how do i know what row to set the printer id normally.
When we have a dynamic print range we have us we use the last row but when they're all shapes how do we know to get that last row and i'm going to show you that trick it's a really cool trick to show you the last row based on some shapes so that'll be coming up very soon i'm going to show you that as well walking you through that so continuing on with this so this is relative we'll just set an email subject and email message this training we're not going to focus too much on this because we have in other trainings i want a template id i want to know what ideas assigned to this template so we're just going to simply use a formula for that and that formula is going to be the index based on that template id we're matching the template name it's really just a simple index.
Match formula i'm going to do the same thing for customer i want to know the customer using a match i want to know what row this customer is on so in this case i want to know that air is on number four we have different customers this is just the data validation based on the customer this will tell us the customer row that's important because i need to get the data from this customer so if i change this right i want to make sure that when we print the document it is that customer information that gets in there and i want to know basically this is going to be true or false based on whether we're going to display those emails or whether we're going to send them without displaying so that's it for this screen relatively simple we're going to focus on the designers so just briefly here's the list of templates as we create as an unlimited number of templates here they're going to get saved the main information that's saving the template name table and type.
Same thing here name table and type that's where it comes from we have the template items now for each specific item what do i mean by items this is an item this is an item this is an item this is an item now they're different types as i select it really cool so if i want to select this and i want to change this to least period child b for two years i could just change this for two years and all i need to do is just click then update the item it's going to automatically change it here so notice it's been changed saving the template is going to save or update it's going to ensure that that template gets automatically saved and so it's really great so what we need to do is we want to make sure that these individual items all the details get saved i need to save everything about this item i need to save the font the size the position if i want to move this.
Position over right if i don't save that right and i send you know go back to a different agreement and i go back of course it's not going to be saved right so what i need to do is i'm going to make sure that that position gets saved exactly where it should be so when i click save it's going to save that position now when i leave that and come back to that i want that position saved exactly where i saved it so that's really important also the size the position the font everything is going to get saved that way i can easily recreate these and notice that you know as i choose a different customer it's going to change automatically for when i have data value no disease or data items so this is the customer this is the customer address so we can choose data items now data.
Items are going to be based on the date value so remember these are all here these are all based on the specific customer information customer id the name the office all right let's say we want to add a phone number all we need to do is click new item click on our data item here and just put in the number here phone number and then of course getting the data value all we need to do is select let's say office number and click add item that's going to add it automatically wherever we want then what we need to do is just drag it over here resize it accordingly wherever we want we can also.
Then change it here and then that's it and just click save and update that that's going to automatically save it so now when i come back in to that i go out of it and come back into that that phone number that new data is going to automatically be saved this is data so it's going to change with every customer inside the form all we need to do is then print it so we've got a lot to show you so that's basically the idea of the template let's get into some of the hidden stuff we've got three particular columns that are going to be hidden and we'll show you about that we have the table row now this is going to be the row of the table we need that we have i'll show you a little bit this later we have the template id that's going to be this this will change so when i change this template we're going to change that that's going to change.
Automatically based on whatever is template we also have a selected template id so this template id is going to be put in by vba so when they change the name i have a formula here formula is going to be based on this name right here selected template so what i have here is i want to index the template id indexing the template id and i want to get that template id based on the match formula i'm going to match the template name based on whatever's in e3 so what this is going to.
Do is going to get us the template id as soon as i change this template template id 1 template id 2 template id 3. so it's going to change it based on that and so all i want to do in that case is take whatever is in here and place it here and then i'm gonna load that template based on the id so we also have the template row what row is it on right so notice template number one is on three right template number one is on row three that way i know what information to load i need to load the template name the table and type notice if you paid attention to my trainings before we're not.
Using data mapping there's only three fields so no need for data mapping on something like this if you follow my trainings when we have large tables we always use data mapping but in this case it's only three columns so it's really not necessary in this case so what i'm going to do is i'm going to load that information here here and here so that's pretty much what i'm going to do then what i want to do is if for example it's template id i want to run an advanced filter all the items all of these items these are all items all these items associated with that template i need to load in here not only need to load in here i need to load the values the position the text the font everything i need to load exactly how i left it before however.
When it was saved i need to do that so how do i do that and let's we can also update the format notice this date is formatted differently all we need to do is just change the date format here and that format is going to stick with it so if i want to update that format to a date format here i can just change it to a short date that's going to automatically change so we can update the dates accordingly too and it's always going to be format it's going to take on that format so a lot to show you in that all right so how do we do it let's go ahead and save this how do we do that how do we get this information in here so again all we need to do is have this listed template and just like if we were creating an invoice and we have invoice items it's the same idea in the past we have an invoice main invoice and then we have invoice items this is the same thing.
We have a larger template or something like that we have a template all we need is that unique id all we need is that unique id the template needs so that we know which items go with which template so then i'm just going to run an advanced filter so that i want to extract only those items for template number one we can do that with an advanced filter so in here in this case template id equals one so all i need to do is just create a link all the way to the designer b2 designer b2 of course that is the cell that's going to hold our template id so when i run an advanced filter i want to extract only those items from that template i can do that right from here using.
This room and what that's going to do is going to extract only the items in our results here only those items that are from the temp that specific template and then i can bring in the information i want to know what table row what this is the row of the original row of the data that's this row right here table row i'm bringing it here in the beginning of course we can sort it in any order this is the database row notice row 3 row 4 row 5. i want to keep that why do i want that why is that important that i save that because when i go back into the designer and i make some kind of a change to a name i need to know what row to save those names on notice the database row three four five these are the same rows that we have here in the template the same rows three four five six.
So that way i know exactly what row to save those changes so for example let's say in address right this the value of this is customer address so when i go back into the designer and i click on address and i want to change the name and let's just say i want to change it to customer address and i update that item and then save and i want to save that update that template i need to know that that must be saved in the specific row so when i go into the address here we save that it's right here in customer this is the name that name gets updated customer address right here.
We need to know what row that was in row six of the data so that's really really important so we see here that row six customer address so we have to have that six because we need to know what row so that's really important what else do we have in the template items we can have the item row right this is the row of the item i need to know what row when i bring this information back into all of this information in the designer i need to know that it's row 9 row 10 row 11 or whatever i need to know what row to place it because i want to do the exact same row when we added the designer we just added the current date on 12. so i need to know actually on row 18.
I need to know the database 12. i need to know what row to bring it back on so i need to make sure that the current date comes in back on row 18 of the designer so that's why we i must save 18. so when i save this if we notice here right here template number one this current date must be we need to know that the item row 18 here is the same as 18 right here okay so we need to save current date must come back under row 18. so i need to know that if it's a table called let's see it's a specific data let's say it's a signature name right i want to know that signature let's.
Say down here i have the signature name here i want to know what column this is what i mean what column this is this specific name column is that inside our database here name is on column two offices on column three mobile on column four so we need to also know what column it is so when we bring in the data i know what column name office so we need that i also want to save that that's going to have to be saved inside the templates so only for those items that are associated with data we need to know what column names on column two addresses on columns so this is the database column we need to know what save this so we have the item name right that's the name we associated with the type is it a data item is a text block text block would be something.
Like this this is the text block we can add that information we can update that any which way all we need to do is select on then we can update the text block this is a text block item so we also have here the signature right the value what is the value of it notice it's very very long this is the entire value because i need to bring that in sometimes it's longer sometimes it's not so we can bring that in so we have that we understand that the template id we understand the item row right we understand the item name the name we've signed the type the value which is the value we also understand the data column then what i want to do is i want to save the left position now it's not just any left position or sheets left position based on some other cell the left.
Position basically what i want to do notice that our position here everything's going to start on what we're looking at let's just say cell let's go into a specific cell here i it's going to be i5 so basically everything's on i5 that's our starting point wherever i5 is so what i want to know since i know i5 is our starting cell that's the top left corner of our document i want to know how far this is from i5 on the top and on the left i want to know how far this is from i5 so i5 is going to.
Use our measurement that way if i increase this if i want to it's going to also increase when i load it right so it's not going to be based on the sheet it's going to be based on this cell here which is exactly what i want so i want to save that information and i want to save it inside here i want to save the left position based on it so basically this particular logo is 13.5 pixels to the right of i5 so if i move it over so let's say i move it right over here let's go back into the designer here let's move this right over here right up on the top right let's just say right here up in the top and i save or update that i want to know that left position.
Change so when i go back into the template items now we see that it's only 0.5 left position and it's only top position is 1.25 from the top if it was 0 and 0 it would be exactly on it so if i change this value to 0 and 0 then it's going to be exactly on the top right so if i reload that if i reload that just by double clicking here reloading it it's going to be positioned a little bit higher a little bit to the left of that just like that so now it's right on it okay so we see how we do that and if we move it across over and then just save it those changes so i need.
To understand the left position based on i5 and that's and everything is based on that one cell so it's distance from either the top distance from i5 or in the left distance of i5 that's going to help us position and keep those positions so we have both the left and the top positions notice how on our logo at change we have the height of whatever it is whether it's a picture or a text box and the width of that i want to know the font size notice that our non-disclosure font size is 16 our picture doesn't have any font size but everything else is 11 in this so we want.
To keep track of that font size so if it increases when i build it back up it is going to also increase i want to know the font type if i change a specific font type i want to know that so that the other font types can be i want to know if it's bold italicized underline or align horizontally left right vertical align and the table row vertical line what is that well that's this here not something we use too much but if we you know we're vertically lining here i want to know that it's been top vertical line or the middle or anything like that so that's going to help us so when we change that and click save update that's going to change as well on the top vertical so when we go back into the template items we see that now the vertical line is one here so one.
They give different values one or three in the middle so but that's fine because all i need to do is just save whatever excel saves and bring it back so we're going to save all that information it's really cool that's going to allow us to build these templates if you use this you can build any number type of documents and fill that in just by doing this so we want to keep track of all of that all right so let's get into the vba we've covered the overview now the vba is a lot but we're going to go step by step through it i'm going to show you exactly how to do that so you get to create that so the first thing what i want to do is just start with simple we'll start with the selection change so on this specific selection change i want to do a few things i want to select whatever items associated with that i want to then load in the details of that i want to know what details and.
There's two ways we can load it if we select on a specific item i want that item selected here or if i select on an item here i want it to load up so basically we can load the items from the document here notice the signature has it here that's the signature down here and we can do that we can make changes as we want and then all we need to do is update them and because we do have a lot of code to go over i'll be going over a little bit quick but of course you're welcome to watch this as many times you want to slow down the video as you like into the developers if you click on the developers visual basic alt f11 will get you there now we have some macros on the designer nothing on the.