You can there are many ways to support us one is with our incredible workbook pack we've got 200 of my best templates available for you for a very low price right and we can also include the optional pdf code books which give you the amazing way to view the code and of course you can highlight the code you can look at it it's color coded it's perfectly organized it comes with a table of contents and of course it's got a great index so pdf code books a great way to learn and that comes with our 200 workbook pack so i hope you'll pick that up also with our patreon platform another great way i'll be taking your ideas your comments your feature requests and putting them.
In every single week on every single workbook right so if you've got some ideas you want or maybe you want to fix or maybe you want me to focus on a specific area i'm doing all that on our patreon platform i'll give you the links down below i've created additional dashboards automation emails tons of great features get added to these templates even after the youtube training and that's going to happen all on our patreon platform that and a whole lot more such as pdf code books you'll get early access early access to deals tons of great benefits on our patreon all right let's get started on this training basically when we create these applications as they do each and every week we want to make them secure and a great way to do that is with a licensing system.
This week i'm going to show you how to create your own licensing system you can use with any workbook for absolutely free right it's a great way to do it and of course there's many features and ability and basically what we want to do is we want to do a few different things i want to create to be able to send my users a license number have them register that application and then have them be able to use that based on expiration date maybe that expiration date is going to be for two months or maybe it's going to be for one year or maybe it's going to never expire maybe it won't expire right so there's a lot of things i want to do i want to be able to track customers i want to know if they've how many opens they've had if we want to do that i want to know the status.
Of the license is it pending is it registered expired or has it been deleted i want to know an email address so who's getting those licenses and a lot more and this is going to actually help us do that of course when we have a lot of license we may want to filter them so we can do that here just simply we'll be adding an automated filter in there and a whole lot more so it's a great training and i want you to stick with us on this of course we're going to be able to refresh the license data and that means as we send out our applications i want to know who's licensed and who's not i want to know that information if there's license they've been added or updated my customers out there anywhere in the world if they're licensing my application i want to know that and i want that information to automatically come back in here and also sometimes what we do is.
You how to do that now so that after that period of time that application gets locked until they actually upgrade or purchase your real version so lots of great benefits that we're going to go over in this and there's a lot of room for growth too so i'm going to give you a lot of ideas things that you might want to implement so let's go ahead and go over the overview of this this is a relatively simple application there's not a whole lot going on and that's on purpose right i want to keep it very very simple so this application kind of has two parts right there's this part that controls all the licensing and then there's the second part that you would implement inside an application and i'm going to show you basically that has a start screen so every application that you want to implement this licensing would have a start screen and if you register the application and it's registered and you send those details it's going to send those details.
And if it's not registered or doesn't register it's going to say this registration has expired please purchase or upgrade for a new one and that's exactly what we want right we don't want allowed however in our application what we can do let's take a look at this barrier we see that it's expired on you know it's going to be expired if we save this license right and i expire today is you know our actually may so we this is fine right we're on may 6. so if i save this licensing and now this license until 2020 obviously that's not going to work right so 2020. so let's update that all i need to do is just make a change to this year and it's going to automatically update that right to may 6 right so now it's in the future so if i save that license.
Right it's going to automatically get recorded now on the client's end all he needs to do let's say they've paid for their license and you've updated this information now they have one year from the registration they've registered it so now all they need to do is simply go and notice the license numbers the same all they need to do is go into their workbook try that again register it they'll put in their registration their name and it's already in here send those details it's now going to be registered automatically has been successfully registered notice may 6 until 2023 this is the client copy click ok the uh the workbook opens up and everything's ready to go that's a great way to do it so that's how we're going to do it i'm going to show you every single step so don't worry we're going to get to get your beverage of choice and we're going.
To get started right away so inside our basically our licensing workbook our limited access workbook this controls everything right we have all of our clients our license keys we can automate generate automated license keys we've got a very small admin and defaults when our customers or end users license those applications or license whether they're the free or registered versions we want that information to come in somewhere we got to put a folder and we want a text document that information is going to come in a txt document and we need to browse this folder and we need to look for anything that's going to come in and we're going to take that information and put it inside.
Our database so we need to know what folder our data is going to be located in it's going to be a dropbox folder it's got to be shared so dropbox is a great way you could try using other shared folders i personally prefer dropbox it's fast it's easy and it works every single time okay we also gonna assign some dynamic license data so you can update your license whatever you want we got new license default details for example when you create a brand new license if i create a new license here i want i may want some defaults i may want a default application i may want some default.
Posts Related:
Expired days when should it expire and when's it gonna aspire so if i create a brand new license and maybe i created a username like let's say teresa and i want to create that all i need to
Do is just simply and just save that license it's now created a license for teresa we can now give this license to teresa and her application will be good for a single month right and as soon as she registers it it'll put in the registration date so we can do it from today or maybe we want to register it from when she registers it right so if i do from registration right if i decide that that's going to clear the expires on it's going to be based on when she actually registers it so if i save that license it's going to get rid of that expiration date so it's going to be based on whenever she registers it so we can decide when we're going to win that expiration date if it's.From the registration we don't know the expires on until she actually registers all right so we have that so we've got some defaults right for new license defaults i want it non-expiring right we can make uh users expiring and non-expiring i'll fix that should be expiring we want those defaults expiring and we're going to set that to expiring and then i want to expire quantity 30 days from today maybe i'm going to put from the first open or from today but what i really want let's go ahead and copy this i really want these two i'm going to copy that data validation because i was.
Doing some tests i'd rather have this and then we're going to paste the validation so i want these two exactly the same as we have it on here so it's going to either be from today or from the registration we're going to set it to registration and then what i want to do is i want if there's a specific application right we can have multiple applications as many as you want really and then that way we can track different applications and i also want to know the cell that it's connected to right the cell that is connected to we can use data mapping on that okay i'll show you how that's done if i've completed that if i haven't we'll do it together all right so what are we going to do well so then we're basically going to store all this in a very very simple database here so it's just three parts of this application we've got our license key the status the application username email the registration date if there is any the licensing type right this licensing.
Type is simply based on an option right here so expiring would be one non-expiring would be two that's going to be stored right here in this license type so this option group is connected to b8 so as we change this we'll see that goes from one or two that's how we get this one or two here right so if i were to change if i were to create something let's say barry i decided we're going to make him non-expiring right and we're going to save that license right it becomes two in the license database so we now see barry has been set to two so it's not expiring that means it doesn't have never expire we have how what's the frequency of the quantity right one year 130 days whatever and then the expired frequency type there months days years or whatever.
Then we have the from right is it from today from registration date or a few other things ns expire date and then the number of opens if i want to know how many times they've opened their application we can set it here that's going to help us if we get something that's been overused that'll let us know right there all right great so we're going to have that along with a filtering or we may want to filter out certain things we may want to only see those active licenses right we can clear that filter out this or a specific registration date if we want to know let's say greater than 5 122 right we can do these types of filters inside here which is really helpful just by putting in the proper date format it's going to automatically filter that we can do the same thing with number open and then user email so we've got a cool filter hearing relatively simple.
I'll get into that and a licensed database that's all there is as far as our licensing now with our client version right all we have here is pretty much a few things i've got a start screen here which you'll want to implement so when i give you this application what i'm going to do is i'm going to give you this application here but i'm going to include this the start screen here all you need to do in fact let me do that now right so what i want to do is i want to make sure that you get everything so all i need to do is just go into the start screen i'm going to then sorry it's off the screen i'm going to copy or duplicate the sheet i'll move this up here so you can see it okay so what i'm going to do is i'm going to move this up here and then i'm just going to right click.
I'm going to move or copy that and then what i want to do is i want to copy it directly into the workbook available for you so i'm going to do limited access workbook and i want to move it to the end and i'm just going to create a copy here that's going to create that copy so now we have the start screen you you will want to customize this obviously it wouldn't be let's just put your workbook name or whatever your start screen can be whatever it is you know whatever you want it to be put a logo here so you'll click in here so you'll drag this sheet into your application and also what you want to do is you'll also want to make sure you drag in of course create that.
Pop-up so this pop-up now we also have a pop-up as you noticed here's the code we'll show you that in a second so inside our warehouse manager i've got a little pop up this particular pop-up this user form is very simple it's got a license key we can have a new registration or we have a license key so we're going to have that if it's a new registration a license key can be automatically generated which is very helpful and i'll show you how to do that all right so let's get into it so all we have inside our client application is a start screen we've got some information here in columns and b you want to make sure that these are hidden and locked right you can't give user access hide those lock the worksheet right protect the worksheet under the review here make sure that we.
Protect the sheet so we've got that and of course with a password that way they will not be able to access any information on columns and be where our licensing information so let's say you're going to be releasing your application of course you won't have any of your user information in it you won't have any opens in there so you'll release it something like and no license key okay so it'll be all cleared out you could do a status let's do your pending okay so you might want to release it in this condition giving it a status and giving it an application name that is it so the user will not see that of course that'll be hidden and locked down so they will see this screen they'll simply go to register their application and of course it's going to be a new right they don't have a license yet maybe you give them a free version and they don't have it so they want to do new registration it's going to sign an automatic license to them right they'll give it.
They'll give it a their own name so let's screw jim johnson johnson and they'll put their email
Down and so this way they can automatically register their free version at let's say jim james gmail.com okay so we're good to go on the email and so we're going to send those details this allows us to create a free version a limited access free version if we want we're going to send those details it's going to automatically register that application your application has been successfully registered until june 5th it's going to give them 30 days you can preset that for any amount of time their registration is now automatically included so now this application is.Open it's ready to go for the next 30 days if we take a look inside our own workbook now this is it here's our license application right and now what we're going to do is we're going to go in and we're going to refresh the license data and we see that we have two licenses have been updated or added and we see that brand new one jim johnson here the one that we just created is automatically we know that they've registered it they have one month they have one open and everything comes in automatically into our application very easily so we know that they've just registered it we know they've given 30 days and then maybe we can send them an email in 30 days saying hey if you like your application maybe you want to buy it and if we do they say.
Yeah yeah i really do they they pay for it we can do two things we can create a brand new license for them with new expiration date or we can take their existing license and we can automatically just reset it back to let's say maybe we want to give them one year or maybe it's not expiring we can just do that save it and automatically at the end of the 30 days they're going to be able to register it their application automatically even using the same license number great so you kind of get the idea of it and what we'll do is i'll make sure inside this limited access workbook that you also get this user form here that you saw that user form back into the start screen here and that's this one right here okay so that's going to be available to you in the workbook and the start screen you'll take this user form and you'll take this start screen and you'll put it on any.
Workbook along with a little bit of code so how we're going to make this happen well that's going to be through vba of course a little bit of vba and just a few lines of code right sami and we're going to continue on so first thing i want to do is be able to create new license save license delete license or i want to refresh that license data flashing license data what that's going to do is any of our users that have made any changes to their license whether they've registered whether it's expired or anything like that is going to come into our application automatically all right so we understand we've got the admin defaults this is relatively simple we've got a database we have a start screen this will only be used in your external you'll drag you'll copy this into your applications that's what the start screens and so that's it all right so first thing what i want to do is i'm going to show you how we're going to create this screen.
And how we're going to get that ability just a few macros to do that we're going to do that through vba which you saw briefly alt f11 or the developer and then of course visual basic will get you there first thing i've got a few different modules as you can see i have a few two different workbooks open i've got the warehouse manager here open and i've got also our limited access workbook open here so we've got these two workbooks open we're just going to be focusing right now on our limited access workbook and we've got a few modules i've got application modules i've got license macros and this user registration macro those are the three modules that we're going to be going over as far as the license macros that's what we're going to focus on first these are the ones that create a new license and we're going to work on that so as you notice when we create a brand new.
License we're going to do set some defaults and we're going to bring that through that and i'll go ahead and show you how that's done first thing what i want to do is show you some variables we've got the license row we need to know what database row the column we're going to use data mapping on that column and basically what that means is we're going to map these particular cells here e4 with the user name we're going to imagine k4 with the status and i want those map to their individual columns notice here username e4 right this status here k4 so we're mapping this in row one that's going to help us quickly do two things one it's going to help us save the.
Data to this table and it's also going to help you quickly load the data once a user clicks on a selected license or user that information is going to load up in the table above okay so that's how we're going to do it now we have some hidden column here that we have just i want to know if the license is being loaded when we load it's going to quickly go to true and back to false we have a selected id i want to know what the license id i have a database row i want to know what row associated the database row that this particular license is associated on and that's right here inside the lesson so i know that this particular one for fred fretters is pending and it is for the contact manager so i want to know what row that is the license ending in nine six zero.
Seven three so i wanna know that that's on row four so we see nine six zero seven and it's on row 4. to do that we're going to start out with the named range a dynamic name range called license key so if we go into the formulas name manager and we see that we have one license key using the offset and basically we're going to use the offset from the header row then we're going to go one row down because we want to exclude the header row that's important because when we have no data it could create an error so we always want to include the header row but include excluded in the counting then we're going to simply count all of the rows including header row and then subtracting one that's an offset dynamic named range that are going to encompass all the license keys so.
What i want to do is i want to figure out what row based on the selected license so when i look into licensed users and i see we're going to wrap it around if air we're matching the license key on b3 and we're looking in the license key and then when it's found we're going to simply add 3 to that because if it's found it's going to return 1 but i want it to return 4 because i want that row 4 number the reason for that is because our first one starts on row 4. if you've been following me you understand that i want a brand new ikea this is brand new how do i create a brand new automated.
License key well the best way to do that is we're going to use randbetween we can use randbetween first we're going to start out randbetween and i want to start out with between 0 and this really large number here and then i want eight characters of that and then the reason is so once i have that here this rand between here then what i want to do is determine i want the decks i want the decimal to hex decimal text dec 2x and that's going to create a alphanumeric string that can close both numbers and letters and a random one at that that's gonna help us create a very unique license.
Key using the dec to hex okay wrapping that around randbetween this creates the random number this creates a decimal to hex automated string here so that every time notice that it changes every time which is fine every time the worksheet calculate it changes and that's exactly what i want so what we're going to do is when we have a brand new license key we're going to take whatever that is we're going to place it directly in side b3 b3 is where it's going to be placed we have a link here this is linked to b3 so this is simply for display purposes only whatever's in b3 when we save that.
New license number is that new license number that's going to be automatically saved in the database so we're going to generate that using this formula and when i select a specific row i want to know that selected row here notice that our conditional formatting automatically triggers and it is based on whatever is in b6 so if i highlight that we're going to see three different conditional formatting so when i manage those rules we're going to take a look at that two of them are going to deal with the row numbers odd or even and one's going to be based on the selected row that's selected rows be based on b6 and we're going to simply give it that dark green background with a bold white font then what i want to do is i want to highlight rows but only.
Rows with data and so there's two conditions one column d must contain data and two it must be for odd rows so for those odd rows i'm going to color it that light green and give it a light border for even rows even as mod of row 2 equals zero those are even rows i'm going to give it that white background and i'm simply going to give it the green border and that's how we get that alternating row automatically as so as soon as we add data here just going to automatically create that so very very simply based on column d okay so that's the conditional formatting so we also have the license type now i showed you this before if expiring and non-expiring so we have those two.
And also i've used some text boxes here so if we take a look at that we see that our option here is without text and that's because i wanted to create some text boxes so i can get the right font size so i've done that and i've created an option group here and if we right click here we format control and we see that it is basically tied to b8 b8 is going to either be 1 or 2 based on that license type non-expiring or expiring now we take a look i do have some conditional formatting here notice that when it says never expires or expires in right so we have a difference of non-expiring so.
I've got a little formula here in h8 and that's going to be based on whatever is in ba whether it's 1 or 2. if it's 1 we know it's going to be expiring so it's going to say expires in however if it's not 1 then it means 2 it's going to be called never expired so that's just a formula based on what's in here and i've got some conditional formatting here that's also going to be based on the same b8 so if we manage the rules there we see that we've got two rules right so the first one we're going to say if b8 equals 2 we're going to give it this green background if b8 equals two also this one i'm going to remove some of those borders so if we take a look inside.
The borders here bring this down taking a look inside the board as we see that the left and right borders have been removed on that and cleared out and that's just going to be so that we don't have any borders when it's going to be one solid thing however if we click expiring we see we've got two borders in here so that is all we need to do for that very cool all right so let's go back into that so let's start out with our first macro which is new license new license what i want to do is i want to make sure that we create automatically here this particular defaults right i want the default based on that notice we have the default it says contact manager right if i change this.
To employee manager right and i click new license this is going to automatically change to employees so these defaults are going to be based on that and we're going to do that through a single macro first thing we're going to do inside this macro that's tied to that button we need to know the default row as long and then we're going to focus on the license we're simply going to clear out the contents of all the cells and now what i want to do is i want to set some defaults and i've got to fix this one default probably because that's not really so the first thing what i want to do is go from 7 all the way to 12 7 to 12. those are the rows those are all defaults and i want to basically inside k4 i want to put pending inside b7 i want to put expiring and this one inside.
This and this so that's basically on that so actually b7 this one i got to change a little bit so that's the only one if you know b7 expiring type expiring and then what i want to do is i want to make sure that it's set here to one great so because that way it doesn't quite work just yet so if it's non-expiring it will in a moment though if it's non-expiring notice license users right if i knew new license it doesn't change it should change the non-expiring so we're going to do that right now all we need to do is just do it if it's equals non-expiring then simply change this to one or two so let's do that so all we're going to do is simply loop through those rules and if there's any issue we're going to simply go on and resume next so we're going to do is we're going to take f and the default row value this is the range the ranges in column f of the admin screen and the.
Default row right if we take a look at column f f contains our cells k4 b7 b8 these are all the cells where we want to place these dates so we're going to loop simply from 7 to 12 to do that and then also we're going to place what are we placing in that cell we're going to place whatever's in location e that that column e and the default row so we're placing in whatever is in here lastly all we need to do is just the first thing is if this is not aspiring change this to 2 if it is expiring change this to 1. so that's all we have to do in the last line so let's go ahead and do that right now so if star range b7 that value equals and then non let's do non-expiring then dot range b8 equals.
Two otherwise equals one equals two okay so we're just going to copy this and then change it to one under the else right so else d7 equals one okay so that should be sufficient let's go ahead and take a look at that and then we'll do new license and we'll see it changes to non-expiring okay we'll change the defaults back here now we're going to change it to expiring and we'll leave it at that expiring and then we'll go back to new license and change back expiring perfect okay the last line of code i want to set that initial license id remember our formula in here is going to take care of that automated license id right here in b5 so i want to take whatever's in b5 i'm going to place it directly inside b3 i want to take that automated and place it here because here it is no.
Longer being calculated it won't be changed taking the value here it's going to continually change until we place it into b3 so we're going to take that put it whatever's in b5 and place it into b3 that's going to set that initial license id okay very good now you'll notice another thing as soon as we made a change to here so if we change this to let's say two months right that automatically here is going to change it let's go ahead and put the expiring here so now you see it's actually we'll set it actually from the registration let's go ahead and put this to from today right so if we set it to a current day right we know that the current date today of course is recording day.
Of may 6 2022 so we see that two months is going to be july 6th if we change it to one month it's going to be june 6. if we change this to one year it's going to be set one year all right so we see from the current day now of course it's from based on the registration date we don't want to change until the user actually registers it okay so how do we get this automated so if i make a change to any one of these i8 through j8 then i do want something to change in here assuming it is from today so that's going to be based on a change event when i make a change however only when.
The user makes a change when i make this kind of change here i don't want anything to happen right i want to i want to keep that expire whatever that expires date to stay the same right so two different kinds of change that's why we need to differentiate those kinds of changes when we're loading it this is going to go to true so we want to make sure and then back to false so we want to make sure when we make a change to these we want to make sure that b2 is false and that's going to happen on the change event when i make a cell change event a worksheet change event here something's going to happen so let's go in to that our licensing sheet here and we're going to focus on run a macro to set the expiration date but not on license load we want to make sure that b2 is.
False when we make a change to i8j arcade we're going to run a macro that's the next macro we're going to go over called license set expiration date so when we move back into the license macros we see that the next macro that we're going to go over is called license expiration date we need to set the frequency quantity as long the frequency quantity is this number right here located in i8 we also want to set the expiration frequency as a string i need to know how often right is it days weeks months or years and then also i want to know here what's in k of course.
Then what we're going to do is we need to know the ex the expiring date as a date and also i want to know the start date the start date is very important the start date of course is going to be from today okay and so we're going to put that into a variable and of course i need to calculate that expiring date based on the variables okay the first thing what we want to do is if it's non-expiring then we're just going to clear the contents and that means if they if it's non-expiring oh i don't need to clear anything out so you see everything gets cleared out nothing right we don't want to calculate anything if it's non-expiring okay so we want to make sure that we don't do that okay but assuming that it is expiring we do then want to calculate we're exiting the sub if it's non-expiring and we're clearing the contents from i10 through k10.
And simply what that's going to do is clear out this is a merge cell so we need to clear out all of the co not just the single cell but all the way from i-10 through k10 because it is a merged cell but if it is expiring we can continue we're exiting if it's it's non-expiring because we want to calculate that expired date i want to make sure that we have values right i want to make sure that there are no blanks in order for us to calculate that expires on date we need to make sure that we have the frequency quantity the frequency and we need to have the from so i want to make sure that those all contain values if any one of them are blank then we're going to exit the sub and also what i want to make sure is if that is set from if it's based on the registration right.
We don't know the registration date because the user is going to register it for us they are going to be the ones that do the registration so they set the registration date unless you want to set the registration drake so but if if a6 h6 is blank and it's from registration then we want to set however let's say it's let's say they have already registered it or you're registering it for them you're going to set the registration date and it's also for registration then we can calculate the expires and then we'll work because we have a registration date to move from right but however if h6 is blank then we cannot calculate the expires on because we don't have a registration date when it's based on from when they're registered so you can register it for them or they.
Can register it okay if they register we can't calculate the expires on date okay so continuing on here so we're exiting the sub if h6 is blank and it's from registration we're just going to clear the expires on date we cannot register until there's an actual registration date okay great so but what i want to know now is i want to set that start date now that start date is going to be based on two parameters the start date's going to be based on the registration date here or it's going to be based on the current date based on this so we need to know that starting point so what is that starting point going to be going to be this date or the current date and we're going to determine that by what is located here in k8 so if k8 equals from today.
Then the start date is equal to today's date which is the date function else the start date is simply what is in h6 that's going to set up our start date and next up i want to set that uh frequency quantity is going to based based on i8 or whatever the number is and then our expiration frequency which is going to be in j8 so we're setting that up then what we're going to do is we're going to use select case because we have different cases whether it's days weeks months or years we need to set that expiration date based on those and great function to use is the date at.