Much to cover we don't have a lot of time to spare i'm going to go over this with you this is the sample of course we're going to be going from scratch every line of code every formula every mistake every bug you will see and hopefully learn from i'm happy to help you here with our excel for freelancers youtube videos each and every week all i ask that you do is just subscribe go ahead and click that notification icon bell that'll alert you to brand new trainings each and every tuesday this workbook is absolutely free all you need to do is click the links down in the description.
Either using you with your email or facebook messenger we'll get that sent over right to you of course if you do like a pack of these i've got 175 of my best workbooks all crammed into a single zip file complete with a whole library so all you got to do is just single click on the workbook to open it or single click on the youtube video to view that it's a great deal it's only 66 dollars for a short time so that'll help us out if you can pick that up i'd appreciate it we've got so much to cover in this video we're going to get started right away this is the sample file i'm.
Going to be putting this away and we're going to be starting right where we left off last week but basically what we're going to be doing is we're going to be able to add an icon to different classes in the scheduling component once we build that we're going to be adding icons to schedule i've chosen white i'm not sure if you'll like white but you can choose any color you want i'll show you how to create those no worries on that so we've got that we've got a few others we've got users and security we'll be protecting passwords as mentioned in the intro also in the students we've got that i'm going to i'm going to update the inactive status since i want a date instead of a field that's going to help us out i'm going to show you how to prevent from duplicate names so as soon as we enter something that's automatically let's say we load this.
And then we just change to let's say first name freddie as soon as we enter that duplicate we're going to get alerted and that's exactly what we want here just like that so it's going to be i'm going to show you how we can do that we're going to program that in update classes i want to show you classes enroll classes exams a whole lot in this id card all right i'm going to close out this sample program this kind of alerts you to what we got going on and prepares us so that i can do that all right we're going to open up last week right where we left off which is this part four this is right where we left off last week so we've got a lot to cover here so why don't we get started the first thing what i want to do is i want to make a quick update on the student now we have active here but what i'd really like to do instead of just active inactive i'd actually.
Like to set a date for that as you saw in that sample so we're going to do that what i'm going to do is i'm just going to delete this thing because i think it's nice not only do we know if they're active but we can know the date that they've been inactive so that's a little bit more informative notice that the group name remains here in the general info group we need that group name and i'm going to move that up and i'm going to create a field right here and we're just going to call this date inactive and of course if the student becomes active again all you need to do is then just remove the date so we'll have a date field and i'm going to drop this down format them based on our cell styles that we've been working with this is the field style here that's the one we've been working with and of course i'm going to change this to the label style here so we've got that so now we notice the date inactive is now l21 we're going to format that as a date short date.
There's no issues with that but that's pretty much it no longer true or false so we can remove that clear that data out it's now going to be a date so i like that better with some ideas that came up so we'll just go ahead and update that student check our database make sure it's now a date and it is okay that's what i want to see a date in there and then also what i'm gonna do is i'm just gonna load it into check so we've got fred here david's going to clear it out we do need to want to clear this out which is going to be clear make sure we clear that out on new right on new we need to update that l21 we want to clear on new so when we load back in fred fetters the date loads which is fine but i need to new student i need to make sure this one is cleared out l21 so.
Into the developers visual basic we're going to go and that's going to be focused on the student macros here student macros i'm going to bring this up so you can see it so student we're focusing on let's see student miscellaneous here add new again let's see l21 that's the one i want to focus on for you so let's clear that out l21 adding that in anywhere anywhere is fine l21 comma okay so it's going to clear that out and add new so that's what we want so so if we cancel the new and then just click new student it's cleared out automatically load up student that's what i want okay good so we've got that that kind of was really easy change so what i want to do is make sure we covered that.
Also what i want to do is i want to know when we have a duplicate contact if i let's just say i'm at david here just as i showed you and then i enter freddie freder's now freddie has already been a student so as soon as i hit that i want to show just that sample show you i'm going to show that this turned red i want some text here i want to know that right away that that's a duplicate student so how are we going to do that well the first thing we want to do is i want to make sure i want to know exactly what name should be there we're going to use the student full name let's look in the student database this is the student full name i have a named range based on that so we.
Look in the formula's name manager student names here we tab over so it's student name so what i want to do is i want to check is there a duplicate of student names now this is going to be based on a formula so it's last name first name or first name last name so what i'd like to do inside our students is actually show what that student name would be so we don't need this stream or i'll be able that's on add new let me make sure to get rid of that because we don't need that add new right we don't need we no longer need said new student to active i'm going to clear.
That out right now okay good i saw that that's important all right continuing on here let's save the work we've done so far so what i want to do is i'm going to split this up into two formulas i need to know what the full name is what should the full name because it's a little bit tricky because it could be either first name last name or last name first name so what we want to do is we want to see what that is so let's do let's take a look inside our admin screen here and also i want to go into the general let's say application settings here and i want to look at this here last name first name or first name last name so inside that application it would be nice if we have a named range for that so we can do that let's just call this sort names by i'm going to create a name range for just this sort names by that way we can use it inside here inside here.
So okay we already have it is there we have already has that as the dynamic name range so i'm going to remove it because here's what i mean so i'm going to change this inside the name manager
And i'm going to call sort names by and i'm going to tab that over instead of this here i'm going to change it to this here and then i'm going to hit check and then i'm going to click ok now in the data validation let's go inside the data and data validation and then all i'm going to do is just make sure that we're here we're based on these two fields here click ok and then we make sure that we have it now when i check sort names by it's this one so here selecting sort names by this is the.Field that i want i want to know the value of this all we've done is just taken this we don't need a named range for just just based on those two cells a13 or 14. again the data validation based on that is located here at a13 through a14 all i did was update that so now what i want to do is use this in formula sort names like if sort names by is last name first name so i'm going to copy that here and then what i'm going to do is going to go back into students here and then somewhere right around let's say b17 i want to put a formula in let's call let's just call it i want to call this full name i want to know that full name based on a formula what should it be first name last name or last name first name so what would that formula be it's going to be equals if and then the one.
We just created sort names by equals and then i'm going to paste that in last name comma first name then what do i want to do then what i want to do is i want to put in this last name and then and and then quotation marks comma space and then another quotation and then i want to put in actually it should be first name so this should be last name h7 that's right h7 last name comma first name so that's what it would be right otherwise if it's false what's it going to be it's going to be first name and quotation marks a space and then another and and then last name so that's what i want so basically i want to display this name based on either one so you see notice.
How that happened all we need to do is just move this menu slightly over it's moving slightly over to the right a little bit now when we move it about it should be okay so there we go now let's take a look okay good so basically this is it so that's the full name because what i'm going to be doing is i'm going to be looking for a match in there and when there's a match and i'm going to know but that match is a little bit tricky here's why it's tricky if it's an existing student we know there's going to be a match right so if it's a new student for example if i load in fred freders there's a match here right this is going to equal we know it's already in the database but if it's new so we need to differentiate between new students and existing students and we can do.
That inside another formula so right here inside a15 i'm going to put duplicate name and i want to true or false here duplicate name and then i want a true file so here's where i'm going to put in the formula what kind of formula i want well it's going to be two conditions is it i need to know is it new or is it existing so that's important let's move this over a little bit just the way i like i'm over the bit to the right okay so is it new or existing so let's write in some code and i also want to know how do we know if it's new or existing this right here student row this if this is no match if there's no match let's say new student we know that b7 is going to be blank.
So we can use b7 to differentiate between if it's a new student or if it's an existing student in which we've just loaded up so that's a good start so let's do that so first of all we're going to wrap it on iferror so equals if air and if there's an error now what i want to do is i want to know if another if and then i want to determine is it a new student's and there's two conditions and b7 here's b7 is it equal to empty equal to empty and there's another condition i also want to know if there's a match on the student name so i want to run if there's a match so.
Match but i want to know not only them if there's no match it's going to create an error so what i really want to do is i want to check for an error to see if there's an air inside the mat so is air if there's an air then what we're going to do is we're going to run a match so what are we matching i'm going to look up right here in b17 between that that's what we have match and where do i matching up student names that's the named range that i just showed you we're looking for an exact match so it's going to be zero then what we want to do is if this equals false right that means there's no error that means there is a match if there's no match there's going to be an error so if that equals false then we know that there is a match and if there's.
A match on a new student there shouldn't be a match right we're putting in a new student there shouldn't be a match so if there is we need to mark this as true that means there is a duplicate otherwise what other condition will other condition what if it's an existing student in that case also we have to check for that again we're going to use if and then we're going to use another and in this case right b7 is not going to equal empty right b7 7 because it's an existing student does not equal empty here so in that case b7 does not equal empty and what other condition in that case what i want to do is how do i know if it's if there's a match there should be a match.
But if there's a match in a different row you know what i'm saying so here's what i'm saying it should be found but it should be found only in one single row and that's student row four fred fretters only belongs in student number four but what if it's found or if there's a match in a different row if there's a match in a different row then we know that there's a duplicate right so we can use that row to differentiate that so how would we do that so again match what are we matching here we're going to match of course we're matching looking up whatever's in b17 we're going to match it with the student names here we want an exact match so it's going to be zero then also we want to look for the row so plus 3 we need to locate the row so we got to add 3 on that now.
That's it that's it for the and if that does not equal does not equal b7 then we know we've got
A match right and we don't want to match because it's already a duplicate it's found somewhere else so that would be true otherwise it is going to be false and the quotes and then if case there's an error also we want it false right if there's an error that means there's no matches found okay great now let's take a look i also want to update this let's go ahead and take a look at our student our student database is already sorted by first name last name so let's put that and let's keep it right at that based on that so i'm going to go into admin and then just update it back to.First name last name here we go and then into the students here so let's go ahead so now we have david david so what about if i enter fred and then freder's okay that's going to go to true right now we know we have a duplicate right because fred fretters is already in our list here so we that's what we want true so let's put that in yellow and what do we want i want to change the conditional formatting here so let the user know so conditional formatting a new rule then.
We're going to use it based on a formula and that form is going to be based on this based on what's located in b15 if that equals true then we want to do something different we want to highlight that so how we're going to do that we'll do that with some formatting so the first thing what i'll do is i'm going to fill it with this red color i'm going to change the font to maybe bold and then we'll go from to red and then i'm going to change the border to red also to make it really stand out and then go outline okay but that's not enough and that helps too so when we load something in that's good so notice it loads as soon as we change it to fred and then fretters it changes good but i want.
More than that i want to let the user know that they've have a duplicate so i'm going to put a little message right down here i'm going to merge and center this bring down the font to like 8 or something like that and i'm just going to put in duplicate name found like that and then we'll put it just just kind of small and red and then i'm going to center just enough for them to see it bold it and make it red okay so that stands out but i only want to show that if of course this is true so another conditional formatting a new rule will do the opposite this time in this case right.
If this is false this is equal to false then what i want to do is i want to color it the same as the background color so a little bit different way to do that take that and put it as a background color click ok and click ok now of course this is a duplicate but as soon as we load it up it's going to hide so there we go so again putting in a duplicate let them know now also when we save it we want to make sure that we don't let them save it so we should add that so putting in there going to automatically let us know the duplicate name has been found right it's duplicate name found and so what we want to do is make sure that they don't save it so on save or update if b15 is true we don't allow it so let's update that inside vba so student save or update so we have.
To make sure that the required fields let's add something else if dot range b15 b15.value equals true then message box student name already exists please make a change there we go and then exit sub so now they cannot save it as long as b15 is going to equal true so when i try to update that we're going to get the student name already exists please make a change all right so as soon as we just make a change to david davis and then we could just change it to davidson and then it'll allow us update clicking update it's going to allow us to make that update as long as not all right cool that's how we prevent duplicate names very cool what else do we have you'll notice that.
When we made the change to this we need to resort to students notice we made a change from first name to last name but what i really do if i make a change from last name to first name or first name last name i need that reflected everywhere i need that reflected here right and i need it reflected inside the student database so what we need to do is run the macro that was going to update these names these full names as soon as we make a change so how do we do that well let's take a look at the macro that we want to run so inside the student miscellaneous here or student tabs here we have.
That already add new down here sort names called student sort names so all i really want to do is run this macro as soon as an admin makes a change to that specific cell inside so on the change of r4 i want to make that change so we go inside the developers and then we make that change inside the admin right when we make a change to r4 so let's do that so on change we're focused on a change event and then if not it's intersect and then r4 if we make any change r4 then what we want to do.
Is run that macro then run that macro run student sort names on change okay good okay so let's take a look at that and see how that works so we've got last name first name let's just double click on that go into the student database here now that's working here last name first name r4 great but one we also want to update the formula inside the database so let's take a look at that before we do that student database here now because we've made the update to the named range so now if admin so now we have sort bytes so let's take a look sort because we have to update that sort by names equal last name comma first name then c1 otherwise student database okay student database b1 b1 okay perfect so now we've got that now what i'm going to do is just.
Bring that down now we can copy that down to the existing want to make sure we copied down all the existing paste that formula in there we go okay we don't have a student name in number two let's just add that here lisa lopez make sure we have enough here okay so let's take a look at that now what we want to do fred freder's lisa lopez so we've got that and i'm making a change in the admin making the change there now it's going to automatically and now what we want is to resort this list so this list so automatically here now when we make the change here making the change right here last name first name go back into student database now it's automatically sorted here okay perfect so we got that any change on that it's going to automatically create our list it's going to be.
Automatic now again looking down a list david davidson you see it's now last name first name making a change here back to first name last name going back in there is going to again bearish name all right that's exactly the way i want it perfect what else do we have we've got a lot to cover you see this particular attendance attendance can tend to be a long long list and i think that would be better suited inside our reports and dashboards because we could have a very long list and i think even better than something like attendance we're going to have lots of attendance but let's keep it in the dashboard what i think would be more helpful than that is having like a list of classes that are enrolled so let's let's change that to enrolled classes that's going to be more helpful.
Because we kept it open right now and of course the attendance will run the reports on that makes a little more sense so what do i want for that so i'm going to i want the enrolled date and i also want the class name and i also want to know who is the teacher who's assigned teacher that class as well as perhaps when it was completed completed on maybe a date that it was completed and also grade or score on that that's important and next we want to have a status statist i want to know if they're currently rolled and then maybe notes and then we'll leave the last one blank maybe we'll extend notes all the way throughout that okay good i like that a little bit better let's save the work that.
We've been done so far so we've got exams we're going to be getting to id cards very very soon but i want to jump back into the admin and make some updates and show you what we can do in there because there's a lot of done so we've got already we've duplicate run the duplicate that's focused on and now what we want to do is go into the admin and make a few other updates on that notice we have to actually hide some group buttons in the application setting this group here application settings group i want to make sure to hide that we hadn't done that before this should be hidden except on application settings so that's called the app settings group so let's take care of that right now inside backs of the developers we're going to go to the tabs admin tabs here and i want.
To here we have hiding the admin and here we have showing it okay making sure that does it that's already there good had that covered i wasn't sure if we covered that last name that's it if there's any other buts where you need to hide them as well and also in users and security why don't we do this let's take care of the passwords when i enter a password here let's say one two three four five i want to automatically hide that password and put it somewhere else and replace it with just some asterisks so that users can't readily see it they don't know everybody's password even if somebody has access to this maybe a non-admin they can't see all the passwords it's a more secure way of storing them so how we going to do that well basically when we make a change to here i want to mask it with the number of characters i want to take that password i'm going to hide.
It somewhere else somewhere far away so let's do that let's hide that and so when we do that so when a user makes a change anywhere from y all the way down to just say let's say a large number i want to make that change so let's go into the developer and see exactly what we want to do here so again inside the admin directly we're focused on worksheet change so one inside that focus on a specific range and i'm going to drop this down a little bit i'll drop this up until we start formatting and working with that and inside the vba here we're going to drop it down so you can kind of see both focused on really y6 and all the way down so y6 if y 6 all the way through perhaps.
Y 100 and we'll extend that if we need to okay is nothing and i want to make sure that b 4 is false why do i want to do that well basically what i want to do here's the problem that we run into and you're going to run into this problem and i'm going to show you exactly how to solve it okay so here's what i want to do when a user makes a change to here i want something to happen right what let's comment that out before we get ahead of ourselves so i can explain it okay let's reset that so if a user makes a change here i want something to happen here's what i want.
To happen i want to take this password and let's say i'm going to put it maybe all the way in let's say dd right let's say call them dd we can remember that easy i want to put that that actual password and call them dd and then we're going to hide it okay but so when i want to replace that i want to take what's in here and i want to replace it with asterisks so in one instance user is making an entry here in another instance vba is making the entry vba is going to turn it to asterisk so here's when you start getting crashes and applications and loops that never end is because you're telling vba to when a user makes a change from y6 do something right then let's put in then then end if okay so when a user makes a change but we need to differentiate right so.
That way as soon as vba makes the change it's going to run again you understand that user made a change to why it's going to run a vba makes a change it's going to run again so what i want to do is i want to differentiate between those two we need vba to change those numbers in to asterisks so i want to differentiate between the change that vba is going to make and the way the user is going to make so how do we differentiate that well the first thing what i want to do is i want to just pick a specific cell let's just say b4 and i'm going to make that a based on a true or false so we're going to use that boolean right so let's just call it password here b4 password change.
And then we're going to make this true or false okay so we'll set it initially to false so when a user makes a change i want this to go to true and then when we're finished with everything i'm going to go back to false okay so that way let's say a user makes a change it goes to true so we're going to set it to true so range b4 dot value equals true and then of course we're going to set it back to false before we exit it's going to go to false when we're done so here we're going to do a bunch of things and vba is going to do some things so what i want to do is i want to check to make sure and range b4 dot value equals false that way when vba makes vba is going to make its changes in here it's not going to loop out so what kind of change do we want well the first thing what we want to do is we want to make sure that the target value is not empty so.
If the target because i want to differentiate between when a user a password is being cleared out or when password is being entered so if the target dot value does not equal empty then do something so we'll put else here let's see empty okay so we can differentiate because there's two different things i want to do here's what we're going to do if it's not empty here's what we're going to do if it's empty we're going to do something in here okay so if it's not empty then what i want to do i want to make sure that give the user a let the user know so if let's give them an option message box you have set this let them know users password two and then a space and then we do and then target value so we let them know what the.
Password is just to show them and also then we'll do a period and then of course we can do is this correct so is this correct just go okay that way we know okay comma then what i want to do is i want to let's here there we go missing parenthesis there okay comma vb yes or no give them the option yes or no and then we'll say password change okay so it's going to go from nothing or something password change that's the title we're going to give it equals vb no if it's no then exit.
The sub right but we can do more than that what if it's not then probably we should clear the password so why don't we do this give them two different options okay so if vbs vb yes then do something right else no okay so again we're going to go inside so if they do want to change it if they don't want to change it i want to clear what they've entered just in case right so in case they decide they don't want to change it i want to clear it out so let's do that so else target dot clear contents here is where vba is making a change notice vba is making a change already it's going to continue it would loop out but since we set this to true and this we're ensuring it's false vba won't go back in until it sets it back to false until we're.
Done at the end okay so that's one of the reasons so assuming that they do want to change the password what do we want to do then range dd and the target we're going to use the same row target.row.value equals the target dot value okay that's going to set the password set password in column dd and also now also what i want to do is i want to take that existing password and i want to turn it to asterisks but i want to base the number of asterisks based on the number of letters in the password i think that's kind of convenient so how do we do that target dot value equals what's going to equal it's going to go string and it's going to be based on.
The length so the length of the target dot value comment now how many what do we want in that string how many number i want to know based on the length of the target right so that's how many but what character do i want to use i'm going to use the asterisk that is the character okay perfect so that's it that's all we need to do to enter the number of asterisks based on the length of the string otherwise they we're just going to clear the contents okay great that's it but what if it's cleared let's get rid of the space there but what if it's cleared right what if they've actually cleared it i want to make sure that they want give them the option to clear it.
To make sure so if in this case they've cleared the password if message box are you sure you want to clear this password then question mark and then comma vb also yes or no and then also give it a title clear password okay once we have the title then equals in this case b yes then range okay dd and the target row and the target dot row dot clear contents we're clearing the password from dd and also i want to clear the password from that so also.
What if they don't else they decided not to else they don't want it in that case the target value equals again we're going to do the same thing again here we just need to copy this so in this case basically copy this paste this in there but instead of the target value we're going to use whatever was in dd so range whatever the current password is dd and again the target dot row dot value so basically if they decided they don't want to change it do not let's just my loop automatically with auto hotkey every time i write and do it writes a new not clear password there's.
A do loop that's automated with autohotkey okay so here's the go they want to clear the password dd clear the contents so that's automatically clears that password do not clear the password target value is gonna you're gonna automate and we're not changing anything to the existing password okay let's save that work and let's take a look and see how we've done it here and all right well could be four goes far that's right before it goes false clearing out the extra spaces okay good let's take a look and see how we are that so now all i need to do is double click on this tab out of it do we want to set is this correct just put no for fun okay it clears it out that's.
What i want one two three one two three this time we're going to go with yes okay it brings up an asterisk in column d d all the way over here one two three one two three perfect that password safe all the way over there we've got the asterisk number let's change it to one two three do we wanna change the password yes it's gonna go to three gastrusts that's exactly what i want okay cool i've just shown you how you can add and enter passwords and hide them accordingly because we're going to need that inside the application we're going to be using this a lot application shared folder we're going to be using a lot inside the code and formula so it's going to be really important because everything is going to go inside this shared folder all the attachments all the icons all the pictures you know all of the everything but in independent folders within that.
So that's really important why don't we go ahead and give that a name we're going to call it app folder and we can refer to it inside the code and inside formulas using that i want to make sure that we do that because we're going to need that okay the next thing what i'll do is i want to inside i want to make sure that it's every user is going to have their own so each individual user this will be a multi-user will have their own folder each time a new user gets an application which will be a duplicate of this one they'll need to double check to make sure that is now these users don't necessarily need to enter it so we can create this browser i can create this from any different screen and just have them browse for their application folder and so be it and that's enough so how we're going to do that let's make sure that it's actually accurate so inside.