How To Create A Simple, Yet Powerful, Vertical Menu In Excel, And A TON More [School Manager Pt. 2]

hello this is randy with excel for freelancers and welcome to the school manager part two in this week's exclusive training i've got a lot to share with you including this single click menu in which i'm going to show you how to create it with just a single line of code regardless of how many items we have i'm also going to show you how to create this really cool student list on single click we've got a lot to cover including a brand new admin if we can get to it it's so much to cover i can't wait let's get started all right thanks so much for joining me we have a lot to go over today we're going to be creating this school manager part 2 in this week's training we're going to be adding student lists a menu we're going to be adding a brand new notes field we're going to finish up on student information last week we covered how to create this really cool horizontal tab feature based on cells i'm also going to be showing you how to uh show students based on entering an id so we're going to be adding that we're going to be doing everything from scratch from the ground up as always we did last week we're going to be taking off from last week to this week so i really do appreciate this is a sample i'm going to be turning this closing this one out and going back where we left off last week which is right here so this is the application that we're going to be focused on i can't wait all right if you do like these trainings all i ask is just a few simple things firstly subscribe go ahead and click that subscribe button below and don't forget to click the notification icon bell that'll ensure that you get these trainings each and every week delivered right to you in your email also please feel free to comment below i respond to each and every comment every single day and i'd love to hear your feedback especially on an application such as this where your feedback your ideas your comments is going to help me create the amazing application inside excel this is going to be the best school manager application you've ever seen hopefully with your feedback with your ideas and your suggestions so you're going to play a big part of this it's going to be a multi-part series i don't honestly know how long it's going to go it depends on your ideas it's going to be large we're going to go over scheduling we're going to go over dashboards we're going to be over transactions we've got exams classes tests scheduling it's going to be massive it's going to be really big we're going to do really cool things that you've never seen before so i really appreciate the only way i can bring these to you is if you do follow you do share and you do like that helps me create these larger applications and temp the interest tends to go off as i create more of these you know multi-part but if your interest remains if you continue to watch and share i will make sure to develop these amazing applications in excel and hopefully teach you how to do that as well so that is my goal to do that to help you create your own applications if you do want to learn how to create your own and sell your own applications the best way to do that is with our mentorship program i created a mentorship program that's going to help you design develop and deploy your own excel applications for paths of income it's an incredible program tons of our students are loving it and learning how to do that and making a great income from it so if you want to join us i'd appreciate that that's the mentorship program all right let's get started we have a ton to cover so where did we leave off last week last week we learned how to simply select a student and we're going to save a student and we also learn how to sort student names but we do need to load the student when we select a student we want that student information to load here we also want to add a picture i want to add a student list so we can display the student list button as you saw in that sample and also i want to make this menu active right i want to click on a single all these menus and i want to go to different screens and i want to show you how you can do that with just a single line of code that means for every single one of these menu items we're going to use one single macro and that one macro is going to have one single line of code and you'll be able to use this in any one of your applications it's super simple so i can't wait to show it to you that was actually a challenge i left in our group our excel for freelancers group was to put up a challenge question and i just said how would you do that would you create it so make sure you join our excel for freelancers group tons of great comments if you need help uh we're always there helping out and sharing there's a great great group we have almost 40 000 members as of now so it's really an incredible group all right let's get started so basically what i want to do now is i want to select and have that information load in here including a picture we also have to add the features where we can add a picture of the student and it gets perfectly centered inside this box perfectly centered perfectly sized regardless of the category regardless if it's a vertical horizontal you know regardless of the type aspect ratio we want it to be maintained so we're going to do all that all right and the list so we've got a lot to cover let's get to it right away inside the developers we're going to go in the developers visual basic here or alt f11 get you there so last week what we did we covered how to add a new student and we covered how to save and update a student and also sorting the names right when we add a new student we want to make sure those names were sorted and also one other thing why don't we do this i wanted to add a notes field it's kind of good to have student notes so i'm going to do that right now i'm just going to put here called student notes and we want to make sure to get that added all right so let's do just that so i'll use the same row height 8.25 which is the one i'll reduce this down to that and then we'll do is create a new one that way we're consistent with that so i'm going to put here let's see student notes okay i want to have that and then i'll put in the student notes we'll make a large area probably let's say down to 28 which we have which we already have and then i'll go ahead and color that white and then we'll need to update the student list so we have that so i'll merge and center that let's drop this down because we're going to be using it a lot merge and center i want the left and the upper so that it's all the text is on that so let's just put in test notes here and then also we want some borders around that too so we're going to put some all the way some borders right i want to extend that student we're going to update that i'm going to right click format cells sorry it's off the screen just right click and then i'll put the borders around here here and here and then probably the top two okay and then also here is well on the top so we got our student notes and then i'll just make a quick update and then i'll show you um how to prevent some bugs on this that you might be that might happen too okay so we've got that and take a look at that now we've got our student notes but we need to add that in our database right let's take a look at that e24 e24 is where we want to add it so we're going to go into our student database we're going to slide over here and what i'd like to do is i'd probably add it right in here so what i'm going to do is i'm going to insert here one row and then i'm going to delete one row because i want to keep these consistent so i'm going to delete this row here that way this remains on y this remains on aa because we added our code already we will have to make an update here and i'll show you so what was v what do we need to put the data mapping when we make that kind of a well we want to make sure it's e24 that's where our student notes are so back into there we want to put notes here that's the type and then we want to put e 24. so that's where our notes are going e24 okay so we just have to make one update if we try to run the sort again let's just show what's going to happen now that we've made the change and we'll understand that so here's the students if i run this we're going to get a bug right now you're going to get this too so i want to show you debug it says the extract range has a missing or illegal field name we debug it we say wait a minute let's take a look av where's the air now okay so here's the air so when you see this right it's going all the way to v right column v right but we needed to extend all the way to column w why do we need that because it's looking for full names our results our results are going to go into a a in this case our results are going into a a right but our result header name is full name but our original data doesn't come all the way to because we added a row we added a column here so it doesn't go all the way to column w so all we need to do is extend this to column w then we continue on and it works just fine without the bug we run it again it works so just keep that in mind if you see that no worries right you just have to make sure that the names match here and you have to make sure to extend that advanced filter all the way to column w because it's got to equal this so now it works just fine and we've sorted our names okay great so we got that covered so we got the student notes inside there perfect we also need to make one other modification for that when we're saving right we're going to go all the way to column 21 when we're saving it but we now need to extend it one additional column one additional column there and also we need to update this the full naming is now going into w so w is where that full name is going to go we need to make that update so we did that last week so now the full name is coming from w so we're gonna take that full name whatever's in here and we're going to place it directly inside here inside f3 that's going to put that full name when we save a new student let's make sure that works okay so we're going to put in the first name let's go ahead and click cancel new here we don't have that feature yet so we just have the save student let's put that that's fine i'm going to put in a new one let's call it tina and then james and then we'll just go ahead and save that student and then so we'll make sure that we want this here and we want to update that so let's make that update now take a look inside the code right and we take a look inside here we notice that we now we're going to change this to w right we have our formula our formula is now located in w so we're going to look in our student database notice that we have the test notes that working out well but we want to make sure that w is now our formula our full name is going to w so we want to make sure we do that so let's let's update that accordingly when you make a change w1 is now where our formula is w is where we want to place that we're going to 222 everything looks good here so let's try that one more time and make sure that that is working just the way we want it so when you make those updates don't worry and then we'll just clear all we need to do here is just clear out this go ahead and clear out the student database here so we the one we just added just delete that that's no problem and then what we're going to do go back into the students and we want to make sure that it's recognized as a new student and it is because the student row based on the id is empty so when we click update it's actually going to save save the new student now you see formulas tina's now in here right because we're now focused go back into the student database we now have our test notes here we now have our name our formula's been brought down from w this is the creates the last name first name here so perfect we've got that going on really well that looks good so now what i want to do is when i select another one i want that to load so when i change this i want abe lincoln to load i want all that information to load up here and i want to make sure we clear test notes i've got to clear this now e24 through l28 so we need to make the update e24 through l28 so in our add new we're going to go all the way to here to clear the contents comma e 24 through l 28. we want to make sure that we cover that if i just use e24 it is going to create an error because when we're clearing contents out we need to make sure to clear the entire range when we're focused on a merged cell the entire range so let's click add new make sure there's no errors and everything gets cleared out our test notes are cleared out good that's just the way we want it okay let's move on so now let's focus on this one we want to change this and on this specific cell change we want the student information to load here so how we're going to do that we can do that with a macro called student load so let's do that let's we have add new we have save update let's vote we have the sort names which is important on anytime there's a name change or anytime a new one's added now we're going to focus on a macro called student load so how do we get that to load it's relatively simple we're going to focus on of course with our students sheet so we have with students and of course to double check that you've got the right sheet name use the dot and then the intellisense should activate that's correct so we know we've got the name and then uh what i want to do is first thing unload if there's any picture there's any we haven't added those we're going to be adding those down here and down here but if there's any picture of an existing student we want to delete that right so the best way to do that is just to do if there's if it doesn't exist it could create an error so we want to do is start with on-air resume next and then what i want to do is i want to do shapes and then student that's the default picture that we're going to be calling every student picture student pick dot delete and then on air resume next if there was an error if we want to catch any errors after that so we're going to do on air go to zero actually on air go to zero that's going to resume so that we can catch any additional bugs and the first thing also what i want to do is i want to clear any of cells that contain values want to clear that up so all i really want to do is take this and copy this the only thing i don't want to do is f3 okay so let's take a look at that what do i mean by that i'm going to paste that right now it's going to clear the contents now if a student has been loaded we want to make sure that this name maintains f3 so i don't want to clear that out but i do want to clear everything else out here so let's remove f3 from there we don't want to include f3 in this case why is that because it's very important f3 is going to trigger our row and i've got to know what row to load is it row 4 5 6 the 7 i've got to know what row all the information is how do we determine that row well we would determine that row based on what is located here remember we added the student row based on the name and also we're going to be adding another one based on your suggestions because i take all of your suggestions very seriously what if we want to enter an id i'm going to do that right here too so we're going to show you that okay so this particular one the row is going to be based on b11 b11 here that's based on the student row so i know that if we looked at that in the last training we covered a match based on the student name here so we want to make sure to clear that if for some reason this is b11 is empty we know that there's an issue but the best thing to do in this case because we're going to have i might as well add that in right now so let's call this i'm just going to copy this and paste the formats copy this and paste the formats in here and i want enter student let's see we'll just put student id student because we don't have a limited space student id maybe we can get enter in there enter student id so give the user uh ability to enter there we go that'll cover it so i want to enter the user id and then i'm just going to copy this and then paste this here paste special and then paste the formats okay so what i want to do is not only the name but i want to enter the id so if they enter one then it'll load up that's kind of going to be helpful too so we have two options to do that so what what i want to do is basically i want to add another row here and then we're going to say it's going to be something like the student role based on the id but student row based on the entered id based on the injured id this so we have the student row three different times we have one this is the most important one here based on j the student row based on j7 that's the one so any time we have the correct student id it's going to be in this this row is going to be here notice it's empty as soon as we change that to one it's going to give us a proper row so here's what i want to do i want to add another one here i'm going to call it student row based on entered id entered id okay and that's going to be based it's going to be very similar to this formula so all i'm going to do is i'm going to copy this actually let's go ahead and copy this one here it's based on the student id which is what i want and i'm going to go down here and then all i'm going to change is the cell accordingly we're not focused on this one on j7 we're going to be focused here on what is the entered id which is here okay that's going to get us i'm going to color this the same as we do with all of our formulas give it a border and then also give it a yellow okay so now we have the student row this is going to be coming handy so here's what we're going to do if they've entered a name and this contains a row that means we know they've entered a correct name all i want to do is take the id based on this row what is the id the id in this case it's column a and whatever row that's set and i want to take that id and i want to put it directly inside here what that's going to do is going to generate the row that we're going to use so i'm always going to use and i'll show you this i'll walk you through it don't worry if you're it's a little bit hard to follow we're always going to use when we load up b7 so the best way to do that is make sure that we always enter the correct id here into j7 so that id is going to be based on whatever they've selected here student or inter student so this is a great way you can learn to load records based on multiple factors one based on the name or based on the id so how we going to do that well the first thing what we want to do is on change of either f3 or h3 we want to write some code i want to make if the user makes a change to f3 and and this is empty we know they've entered the wrong amount right if they if they delete this right we know that if this is blank it's an incorrect student so let's write some code right now to do that so first thing is on we make a change to f3 check to see if b11 is blank if it is let the user know to select the right one if it's not if it is not if it's not blank then they've intro correct then what i want to do is i want to put whatever's in a and the row here whatever row is i want to take that id whether it's 1 2 3 or 4. and i want to place it directly inside j okay so let's do that right now let's write some code that's based on the change right so if we're making a change it's going to be on our students sheet here so we're making a change this is selection change we're not focused on selection chains we're actually focused on a worksheet change so what i'm going to do is i'm going to select and i'm going to go to change right here what that's going to do is automate worksheet change so we're making an actual change okay so if not intersection that's an automated text based on auto hotkey okay remember we're basically not f3 but i also want to make sure there's some different kinds of change what kind of changes are we talking about we're talking about changes that the user is going to make not the kind of change where we have this should be new student or student load here student load let's update that so we're going to focus on don't worry about that that's okay we're not finished with the code yet so i'm going to focus on the kind of change remember when we clear this out that's another kind of a change we're clearing it out for a new students when i enter new students i'm going to clear that out so but i'm going to set b 3 call b3 to true when i do that and then i'm going to set it back to false why do i do that because i want to differentiate between the the type of change where vba is clearing this out that's one change or where the student is actually entering a specific student from the drop down list so i want to differentiate between those two types of changes i can do that with the code right here in b3 so i want to make sure b3 is false so let's continue with this code right now if f3 is nothing and range b3 it'll become a little more clear soon b3 dot value equals false then what do we want to do and we can also make sure that there's a value in here but that's that's okay then what i want to do first thing i want to do is once they've entered i want to check to make sure that they've entered a correct student id based a student name based on the row so we can do there if b11 is empty then they have not entered it so what we'll do here is we'll just here if range b11 dot value equals empty then we need to let the user know message box please enter a correct student name then exit the sub there's nothing we can do unless they enter correct one okay so what how what's that going to be like let's just say that they enter blank or something we can cancel that that'll automatically delete that please enter a correct student name good so we have that but what we want to do is we want to make sure to set this true when we're clearing this field out when we're clearing this field out notice that didn't come up as soon as we selected the correct name when we're clearing this feed out we want to make sure to set this to true and back to false so let's make sure we've got that inside the code here so let's go back in here i'm going to go back into the student miscellaneous student add new remember what we want to do is i want to set b 3 to true then to false right because we're actually making a change to f3 here we're clearing it out so dot range b3 b3 dot value equals true set student we'll call it student load to true and then all i need to do is reverse it before the macro ends so back to false before the macro ends so we're going to do it right here so then changes to false and then changes to fall so then we add a new we make that type of change we are not going to trigger what i don't want to do is i don't want to trigger this here with this kind of change to f3 as long as b3 because if b3 is true it's going to be skipped okay great so now we've got it let's dimension the student row so dimension the student row as long it's going to be a whole number and now we can define it what is the student rows located in b11 so we can copy this and then student row equals b11 student row so what do we want to do now that i've got the student row i want to determine what is in a i want to take that id in whatever row a in the student row and i want to place that id right inside j7 and then what i want to do is i want to run the macro to load it so i want to do that because i'm going to do the same thing here when we change an id here but this time i'm going to get the row directly from b13 if it's correct row then i'm also going to place it here so we can use that for multiple searches so let's take a look at that and see just how we would do that okay so we've got the student row so now i'm going to simply use range j7 dot value equals student database a and the student row so let's write that code in right now range j7 dot value equals student database dot right so notice i i put in the wrong one database and nothing came up right so i know that i'm missing the s so then i'm the s is not there so database dot so i know we've got the right one range a and the student row dot value that's the student id okay so now that we've got that now all i need to do is run the macro what is that macro name let's go back into the student miscellaneous it's called student load right here we haven't writing that up right now we haven't completed that but i'm just going to copy that and i'm going to go back right into the students here and i'm just going to paste it here great but we might as well write one more while we're here while we're on the screen and let's take a look at what is that so now we've got that because it's much similar actually we can dimension student row all the way up here because we're going to use it for multiples so we're going to use it i'm going to dimension all the way up here drag it up here because i'm going to use it one more time okay but let's just copy and paste let's make it easier i'm going to copy this and i'm going to paste it down here okay and what i want to do is i want to but this time we're not focused on f3 we're going to be focused here on h3 h3 so let's write let's update that code here it's h3 and also this time we want to make sure that b3 is false that's also correct but it's not b11 we're going to be focused on it's b13 this is the student row based on whatever id they've entered so it's going to be b13 so let's do that write that up b13 is going to be student row if b13 is empty then start to student row now j7 is going to equal a and the student row so that's all we have to do let's write some let's write some comments in there on change of make that look good change of student name and then also we have on change of student id down here on change of student id okay great so now we've got to basically change and we've updated everything and this j value is going to now we can do is we can finish our macro so that on any change of here or any change of here it's going to automatically we also want to make sure that we add a new student i want to clear out h3 so let's update that as well on add new so i'm going to go up here to actually student miscellaneous i'm going to add h3 and we're not going to add it here student load because student load could be h3 but i do want to add it up here so i want to add here h3 that means on a new student on a new student well i want to clear out the search field here and i want to clear out the search field here okay so great so we have that that looks good we've updated that let's continue with our student load once we've once we've loaded it but i want to make sure that we actually have a row right because this is very important all right our student row is going to be b7 if b7 is blank we cannot continue so we want to make sure that we have the right student row because it's based on this id right here so we want to make sure that so let's update the code just to make sure if dot range b let's bring this down here so you can see it here b7 equals empty b7 dot value equals empty then we need to let the need to select a proper student then message box please select a correct student name or id okay all right good so now we've got that and we're going to exit the sub out there's nothing we can do unless we actually have that and then continue on exit sub so that's our way out if we they don't select the right correct one so now that we have that we can continue with our code so now we have this student row so let's copy this because we know the student row we've already defined it in a variable up here student row right here so we've got that all we need to do is just add it down here so continue down here the student row is equal to and then b7 so it's a student row okay now it's going to be easier now all we need to do is run a map a loop here and what loop is that now we could just copy i'm gonna instead of copying and pasting in reverse here's i'll show you both ways because i think it's kind of important to show when you're doing it but i want to do it so we have save here's a here's a great way to do it copy and paste this i'm also going to do it another way because i want to show you both okay paste it down here for the advanced students this is a quick way to do it reverse this okay reverse this now equals so that's that's a great way to do it okay so basically what we're saying is for student row 2 to 22 and how i'm going to delete that now for the for the newer students why don't we write it automatically 4 student column equals 2 to 22. why are we doing that and then make sure we're your next student column so for student column because 2 to 22 why are we doing that why is 2 to 22 well we already have the student id in j7 we've got that we don't need to load that in but we do need to start loading from column 2 all the way now to 22 we want those test notes the full name is already in we've already got that so we don't need that but we do need but we will probably put it in if we need we should probably put it in let's say they have selected a id if they've selected an id then we do need to add that name in here okay so probably good idea to do that it's not necessary but it's important so we'll probably put it in there in f3 okay so let's go ahead and write that code up right now so for student column equals 2 to 22 the last column being here 22 equals column right 22. so that's the last one we want to do so we're going to write that in then what i want to do is remember our data mapping is up here so i want to look in that row one of the student database so dot range where is that range going to come from it's coming from student database so student database dot where is it cells because both the column and the raw variable what is the row the student it's actually row one is that range that range is row one the column is the student column which is variable okay so this value right here this value is our h15 j15 that's the cell that's what we want to get dot value equals what's it can equal is going to equal whatever's in the student database dot sells again student this time student row comma student column okay so that's so that's the longer way of writing but at least you can see me do both right so you can see the shortcut and doing it on manually okay so let's get a student that's going to bring student data type too fast data into the student form great so we've got that we understand that it's going to bring it all in so but lastly what i want to do is i also want to take this name whatever is in w and i want to bring it in here bring it directly into here but i want to make sure not to trigger that load again because we know when we make a change here it's going to load so how do we make sure not to trigger that we bring this to true and then back to false so let's do just that right now okay bringing it back in here but to do that let's just copy and paste this because we've already got it up here inside the add new make it a little bit quicker set student load to true make that change and then back inside because we don't know if they put an id so i'm going to paste that in here set student load to true i'm going to paste it again here and change it back to false and all i want to do is update that name so b3 equals false now i'm just going to update that name so what is that just in case dot range f3 dot value equals student database dot range and then of course it's in w like w is our lo where we're located and the student row so what's in there is that full name dot value that's the full student name and we want to make sure it doesn't trigger so that way we've set it to true and back to false that way it won't trigger that load and create a loop i'm going to reset it i'm going to make sure to save our work here i always want to save it okay so again let's just go over that so w w whatever that full name is going to bring it into directly into here so now we've got that loaded up we don't know we can leave the student id blank here we don't need to load from the student id so if they search name i don't see why we would have to add a student id here because it's already displayed here but i like the full name up here okay so let's take a look we also want to make sure that when they saved a student these specific button this new student group is hidden and the existing group is displayed we have those here already on student save so save here this is all i want to do that means here this one right here would be the existing student group i want to show and the new student group to hide so to do that we can just set it up here so we did the same thing here could have just copied and pasted that but that's okay okay so the last thing i want to do here and the student load again is just paste those things let's clean it up a little bit just bring it on over here and then shift tab and then bring it out okay so it's all cleaned up get rid of the extra space now again saving our work always before running our code and we'll take a look so if there's any errors we'll fix them so double clicking on this or clicking on this all right okay excellent make one quick update we need exit sub this is where the end if goes right there's nothing we can do if we need to exit at okay good and we'll do the same thing here exits up and if here should be not before okay right nothing we can do if we don't have the correct b13 is empty in this case or b11 is empty in this case so that's we want to fix it all right let's take a look now david davis okay debug that let's take a look student row b7 value so we take a look at that b7 we need to make sure that we're placing the student id inside j7 that's very very important so let's take a look at that but you see here we're clearing out j7 we don't we're clear we're adding it but we don't want to clear it out in this case so we want to make sure that we don't clear out j7 if we clear out j7 it's going to automatically clear out there so it was fine before but now that we've decided to add that student id we don't want that anymore okay so let's just continue on let's try that again double click here there we go so now we've got it here abe lincoln now everything's loading in here just the way we want to let's go ahead and and save and update that main street okay we'll click update make sure that's working and now we're going to load in something else and then we're going to load in fred make sure that address okay excellent everything's looking good so we've got update we have new student and what else do we want well we want cancel new when we click what do i want to do when i cancel new well in that case what i want to do is simply load in the first available students so that we can get out of the add new mode so how do we do that well let's write some code up right now on the cancel new so we'll go down here cancel new simply what i want to do is i want to check to see if there is a value right i want to make sure take whatever so the only way to do that i want to check to see if if the first one which is going to be the first one here aa3 if that's not empty then i'm going to take whatever's in aa3 and i'm going to put place it right directly in here inside f3 assuming it's not empty so let's write that now if the student database dot range aa3 dot value does not equal empty then students back on the studency dot range f3 dot value equals again all we need to do is just the student database right here student just place it directly inside f3 equals f3 that's going to place it out so now cancel new now we can assign that so let's go ahead and hold down the control over the icon and the button right click that assign the macro call it students cancel new so it's right here click ok so now we're in the new cancel new perfect that's just what we want new student cancel new okay good it's looking really good so what else well i want to be able to delete the student now i'm going to show you two options on this because it's going to be based on whether we're using sharing and sync and based on your suggestions i think we're going to add multi-user onto this and that means sharing and sync that means delete can change so i'm going to show you both ways based on whether you want to use sharing and sync okay so we're going to continue on with our delete so we want to make sure we're able to delete it there's two ways to do that so i'm going to show you both and then what we want to do is we want to add the picture here and show that we'll go in order so we can do it so the first thing is if you're using sharing and sync sharing and sync that means what does that mean that means you're going to be able to i'm going to show you how you can share and sync your macro enabled workbook with anybody in the world simply using a shared folder such as dropbox google drive or microsoft onedrive or any other type of shared folder and so basically each person each user uses a duplicate application and only the data is synced and what we'll do is we'll do that one cell at a time and i'm going to show you that we're going to get into that so it's really cool i do if you want to get a heads up on that i do have a video called share and sync just type in my channel sharensync and you're going to see how to do that but basically what i want to do when sharing and sync is enabled basically we're going to keep the student id we don't want to change these rows at all we want to keep the student id but we want to erase everything else and i want to do it cell by cell so what we're going to do is we're going to run a loop and we're going to clear everything out cell by cell and that's another reason why we've made sure that when we add these names we want to make sure first name is not blank if first name is blank because the student's been deleted and of course generally you won't want to be deleting students especially if you've added them in but maybe we can make them disabled or enabled but basically what you want to do is then take this particular results from the name range and it's going to exclude any first name so that's a great way to do that okay so let's show you how to do both ways so if we are using so the first thing we do is we certainly need the row i need to know the row without the row we can't do anything so where is our student rows always going to be based here on b7 b7 is our student row based on the selected student id so we need to make sure that b7 is a row so to do that let's do that so with we don't need to do with we're going to let's start with if students dot range b7 dot value does not equal or we could say equals empty then message box please select a correct student to delete exit sub next up i want to give them an option assuming that b7 does contain a value then what i want to do is i want to give them an option what option is that i want to say are you sure you want to delete the student or not so if message box and then parentheses are you sure you want to delete this student question mark and the parentheses and give it a title vb yes or no then delete and then delete student just give it that here's the title delete student equals vb no then exit this up then exit sub okay that way they get an out if for some reason they cannot continue on so now that we know that they do want to delete a student and now we know we do actually have a correct student row we can put that in student row is equal to right b7 here right we have it right here already we know it's in b7 so equals b7 okay that's a student row so now we have a choice now we can delete the entire row if we are not sharing in sync so let's do that so let me just show you and i'm going to comment that out so so students database dot range student not database dot range and then what is that row student row and colon and then another end student row dot entire row dot delete not column row dot delete okay that's going to delete it delete entire row only on no sharing and sync so only on that right so what i'm going to do student row entire row delete okay we just need the space here there we go so that's only on so but what if we are going to decide we want a sharing sync i'm going to comment that out because we are going to use sharing and sync but this is what you would use if you're not sharing sync just delete the row but what if we are then we need to clear out cell by cell so in that case we are going to do that so for so let's see here do a loop for student column equals we need to keep the id in there so 2 to let's say 23 we can clear out this full name to 2 to 23 clearing out the full name what i mean by that i want to go all the way to the 23 column 23 which is here equals column 23. okay i want to go all the way to there so clearing out that full name too we're not going to use that anymore because we're deleting it but we just want to make sure okay so let's go ahead and back in there and do that so next close our loop student column so in this case i would simply want to clear it out so i'm going to copy this here which is the what we're going to be doing and then what we're going to be doing is student rows and then the student row comma student column dot clear contents dot clear contents just clearing it out cell by cell clear out sell by sell and what that's going to do is trigger our share and sync which we'll get into in the future but what that's what we just want to clear out somewhere so that way all the changes sell by cell get recorded and then they could get transferred to other users so other so other users can also update their database all right great so clearing out this is for for share and sync only okay so we got you have both options here now perfect okay so you can do either one of those start continuing on next okay once the student's been deleted what i want to do is i want to go back into the add new and i also want to update the names here so i want to update them how would we do that we just run two different macros to do that so the first thing is i want to go to the add new so student add new the macro that we have right here i want to just copy this one and then i want to place it right down here into the delete so here so i want to that's going to run the add new i also want to sort the new names so i want to resort the names because we've now deleted them so i'm going to copy this one sort names and that's going to resort the names and then paste that down here okay i'm going to save our work and then we're going to take a look at that now i'm going to assign the macro so this one i'm going to hold down the control on this right click here assign the macro or click n and then student delete click ok ok so now let's take a look click delete student variable not defined student row that's row here we've got to add that in continue on are you sure you want to delete click no okay that works fine now let's delete this abe lincoln do you sure you want to delete yes okay debug here student row student column clear contents let's take a look student row equals five student column equals two that looks good got to be cells remember when we're using cells of course shouldn't i copy cells when we have variable student row and student column when we have those variables we need to use cells not range ranges for a specific range like b7 okay that makes more sense okay continuing on everything got cleared out we're now in the student mode everything we're in the new student and everything got cleared out make sure our names got resorted they did abe lincoln is gone our names got restarted perfect okay continuing on now we've got student delete but i don't want to add a picture or want to click here add a picture i want that picture to be certain and i also want to clear the picture out so how can we do that okay continuing on let's do add the picture now we can add a student picture so i also want to place that file path remember we've got that path already set up when we get that and where's that full file path that's going to be located in k19 that picture path for that student right here in k19 so of course we can hide that with changing the font color to the same as the background but let's write that up now so adding the picture here so dimension we'll do that here the picture file picture file as a file dialog file as a file dialog we need to open that file dialog so again we can do this set the picture file is going to be equal to application dot file dialog not file converters we could probably use that eventually file dialog here that's the one we want and we're going to be focused on a specific file picker which is the first one if we get rid of that we can see the intellisense here it's the first one here first one here there you go there it is mso file dialog picker so we can see that now what we want to do is focus on just that so with picture file what do we want to do well with the picture file we want to do a few things i want to give it a title so what is the title going to be let's say equals select let's say a student student profile picture great now that we have that i also want to add some filters right we can't have them select any type of file we have to put some filters on that so what is going to be the filters let's see filters we're going to add some dot add and probably going to be let's say all picture files and then what types of those let's add some extension types let's say we want um in quotation marks dot jpg we're going to add that in allow them to do that and we also want to add in in fact any type of let's make sure we put an asterisk so that's any file before that jpg we want to add in that maybe as well we're going to add in dot jpeg jpegs jpeg j p e g and then also the asterisk before that which we got a comma here so we want to separate those there we go and then another comma and then also i want to add in perhaps gifs so comma and then asterisk so it's any name dot gif you can add that also comma another one maybe we're going to put in png so asterisk dot png and then comma again and then another asterisk we also want to put in perhaps bmps and tiff so asterisk dot bmp and then comma again and then another asterisk and then dot again lastly.tiff.tiff okay and the quotation marks comma one there okay that's the position okay let's take a look at that asterisk.jpg asterisk.jpeg asterisk.gif asterisk.png comma astros.pnp okay that looks really good okay so what next and now what i want to do is i want to make sure that they've actually selected some so if dot show does not equal negative one is with they've selected something so if it doesn't equal negative one then go to no selection then go to no selection so we have to put that down there as well no selection which allows us to skip the next step selection okay so i'm going to put in no selection here and then a colon so that's skip but what if they do and if they do i want to put something in picture path we have the picture path defined as a string all the way up here i believe i'm going to double check to make sure we've got that here right here we've already defined it as a string so that's good that's what i want to have picture path is equal to the dot selected items one just one item there okay good so what if we now that it's like where do i want to put that i want to put that inside k19 so students dot range k19 dot value equals the picture path okay great and that's going to add the picture file path to k19 what do i want to do now what i want to do is i want to run a macro that's going to actually show that picture right we've only added that all i've done is simply add the file path so i'm going to copy this we haven't written that macro yet and i'm going to paste it right here if for some reason they don't select anything these steps will be skipped okay so that is it that's all we need to do for that now let's write a macro that's actually going to display a picture let's save our work and make sure that that's working correctly i'm going to click hold down the control click this again the icon along with the back button right click here assign the macro and then we're going to call this we're going to call this student here add picture click ok now clicking add a picture here let's find a picture here it's not going to show it yet all it's going to do is put that file path right in k2 the next macro we're going to write is going to do just that it's going to show that picture and we're going to center it directly in there so show pictures what we're going to be writing right now all right so let's write that right now so here we're going to focus on with the student sheet with students sheet make sure you have right dot then we have the right sheet okay so first thing what i want to do is any existing picture that we have i want to clear that out now we've already written code for that a few times here so we can just copy and paste it right here on the add new we did it here so i'm going to copy this that's going to clear out any pictures that we already have i'm just going to paste it down here that's going to delete any student pictures that we might have i also want to make sure that k19 actually contains a value if it's empty there's nothing we can do if dot range k19.value is empty equals empty then exit the sub and exit sub nothing we can do if it's empty okay not only do i want to make sure it's not empty i want to make sure that it contains a correct file path it's got to be correct although i also want to exit this up so how do we do that if directory and then what is the file path well let's put the file path in a name first before we do that i think that makes more sense so picture path we already have that equals k19 so now we'll do that let's write that first put that into a variable string variable equals k19 that's our picture path picture path okay and comment that out make sure otherwise it's going to be bugged out okay so now we have it so now we can put the picture path inside here vb directory we're going to check that out directory here not hidden directory vb directory there we go okay if it equals empty then there's nothing we can do so we want to make sure that it doesn't equal empty so equals let's just put double quotes then exit the sub that means it's going to be incorrect file path or something exit the sub so now we've checked to make sure it contains a value we've also checked to make sure that it does have a correct path so now what we can do is we can add it so we can use that dot pictures dot insert and what are we inserting we're inserting a picture the picture path picture path that's we're going to insert and then we're going to assign it a name on that too so name what is the name it's that same name that we're using up there the student picture student picture okay great so we've had dot pictures insert picture path that's going to insert the picture it's not going to size it it's not going to position it just inserts it everything else we're going to do to focus on the picture so now let's focus just on that picture so with dot shapes since we've named it already we can work with it student picture we can work with it so now we can focus on that the first thing i want to do is i want to lock the aspect ratio lock aspect ratio equals mso true that's going to make sure that it doesn't get distorted next up i want to know if it the height or the width is bigger right we have to determine which is bigger is the height or the width right so i want to understand that so we can work with it so if dot width is greater than the height of the picture dot height then what i want to do is i want to assign a specific width then perhaps dot width i want to set something a specific width it can't be too big or too small so let's try something like equals 100 okay so we're going to set the width 100. else else that means the height is greater in that case what i want to want to set the height dot height is going to equal let's say 70 or something like that okay so that we're going to set specific widths and specific heights now we haven't positioned it yet all we're doing is simply setting the height and width based on whether it's a horizontal or vertical type of a picture okay so now let's focus on the positioning which would be the dot left left what is it going to be equal well i really want to place it based on let's say somewhere around k7 it's going to be based on k7 although it's going to be in the middle i want it centered but i'm going to it's starting position is going to be k7 so let's set that up and then we're going to add to that based on some conditions so the dot left is equal to and then we got to set up the student sheet again students dot range why do i have to specify the sheet again because i'm inside this dot width here so i have to specify the sheet again dot range k7 is our range here that we're going to specify we're going to base it on that dot left the left position of that plus something plus what all right i want to add to it how much do i want to add to it well i want to base it on the width right i want it centered here between k and l i want that picture centered so the best way to do that is to determine the width of k l will determine the width of both of those columns then what i want to do is i want to subtract out the picture with subtracting out that width of that picture and then dividing that by two that way it's going to give equal spacing on both sides of the picture so we can do that word now dot left is equal to again we're going to be focused on the actually it's equal to not plus right so it's equal to the student's the left position plus something else the width let's focus on that i'm going to put in parenthesis the width of something what is it students dot range k now those are the ones with k through l the width of that the width dot width of that i want to know the width of that minus what minus the width of the picture dot width of the picture and i'm going to take all that and i want to divide it by two and that's the that's going to give us the left position the left position we're going to do very something very similar with the height the top position okay so again let's just look at that we're taking the left position of that we're going to add to it we're going to add to it the total width of the columns k and l and we're going to subtract out the width of the picture we're going to take that total area basically the area and we're going to divide it by 2 that way we get an equal space on both sides now we're going to focus on the top position something very similar dot top is going to be equal to but this case we're going to focus on the top position also of k7 so it's going to copy that but we can focus on the top position of that plus something plus what plus i want to know i want it equal so all the way from 6 all the way down let's just say to 17 right here 6 to 17. i want to know the entire height of all of those rows and then again we're going to subtract out the height of the picture and then divide by 2. so let's write that in here top plus again in parentheses students and what is the range in this case the range is going to be based on the 7 through 17. so 7 through 17 dot height i want to know the height of that so what is the height of that and then what i want to do is i want to subtract out the height of the picture minus dot height of the picture also divided by 2 divided by 2 and that is going to give us the top position all right let's take a look at that double check student k7 the top position plus the student the total height of all those rows minus the height of the picture divided by two that's going to give us that height okay the last thing is i just want to make it visible make sure that it's visible so double check to make sure it's visible dot visible equals mso true okay good good good we're good there let's take a look and see how we've done with that clear out the extra spaces and now we've got that so let's take a look clear that we don't need let's clean this things up a little bit here and here with and then save our work always before running it now let's take a look now let's again start out with the browse picture select our student click ok and perfectly we've got equal space on all the sides top and bottom it's perfectly spaced just the way we like it let's add another picture see what we can this one's a little bit more vertical let's take a look at that one okay nice that looks really good we could probably increase it a little bit higher than 70. it's a little bit small so let's do a little bit bigger let's do 80 and then 110 okay so you can adjust that accordingly now let's try it again add the picture here probably go a little bit bigger here okay yeah that's nice it looks good we can do probably even bigger on there all right but i like it i like it looks let's look really good here nice that one looks good okay excellent we've gotten we've gotten really far now we want to clear the picture right i want to run this macro to clear the picture so how do we do that just a little bit of a macro clear picture is our last macro inside this particular module here so how do we do that well again we've done it pretty much here all we've we've deleted here all we need to do is just copy and paste that and make sure we specify the sheet number here and also clear out me 19. so specify the sheet here so it's going to be the students there we go and then shapes we're going to clear that out and then also what i want to do is i want to make sure clear out k19 so just copy that and then k19 we want to clear the value dot range then k19 dot val or just clear clear contents okay good that clears the picture now we can take this hold down the control again right click here assign the macro student clear picture click ok so now clear the picture out excellent add the picture and clear the picture now working perfectly excellent now what i want to do is i want to make sure let's make sure we have i didn't check this okay that works good we didn't load a student i want to make sure that that works perfect so now you see how we can load a student here automatically here two didn't show up because we deleted three let's take a look at that that works very good excellent good so now what i wonder is i want a list of students i want to put a button up here and shows us a list of students i'm going to show you that then i'm going to show you this really cool menu i'd like to have the ability to click a button and show the student list and then maybe click on a specific student and then have that student displayed so let's add that student list right now where are we going to add it we don't really have space here i don't want to add it down here so why don't we add it what we can do is we can add it we've got lots of space here and then what we can do is just display the student listen and hide the student information so let's do that i'm going to zoom out a little bit and i'm just going to extend this here all the way down just just extend it beyond what we have let's just go let's go all the way out to a h or whatever it's fine and then what i'm going to do is i'm going to add going to copy this here so we can get everything i'm just going to put it let's say in n there we go and i'm going to call this student list so let's just call it student list here that's what it's going to be student list and we can use just the same icon as fine for now and okay good i'll zoom in a little bit so basically what i want to do is but here's one of the issues we face so if i put the student list here look how look how some of the rows are small right i don't want to put a list here so i can't really use this i've intentionally got these rows very narrow which is great but it's not good for a student list right so how do we overcome that challenge well we notice that let's say starting on row 23 we don't change the row height anymore so we can start on row 23 so all i need to do is if i want to show it all i need to do is simply hide these rows here let's say up to 22 hide those rows and hide these columns here so we can do that and then it'll show the student list so we can actually start our student list let's say on row 23 here 23 because 22 is small right we don't want to start there 23 so here's where we can put in our student list so let's just do that in fact i've got some let's just mur unmerge and center this and then what i'm going to drop this down here and then unmerge and center this here and i'm going to copy this just so i can get some good formats i don't need to do it and then inside let's say i'm going to go back to general info here no that's not going to work because we've already pasted it so what i want to do is paste it right here basically here that'll work just fine so we'll change this that's an easy way so i'm going to call this student list and then what we can do is we can add in this list so what do i want to put inside this student list well i want to put in all of the values from our student database so basically i want to put in everything here except for the student full name so i'm just going to copy this all the way copy that and then i'm going to paste that inside there so i'm going to paste the just the values here then what i'm going to do is i'm going to copy this format there we go and then paste it all in here paste special and then paste the formats here bring that up here click ok so that's going to paste it good that's just what i want and i want to size it accordingly so i'm going to highlight these and double click there now the student list i'm going to copy let's just actually merge and center this all the way so merge and center that there we go so that's what i want perfect so all i really want we can make this a little bit bigger actually i'll capitalize that because it's kind of important student list there and we can even make that a little bit bold and bigger it's already bold make it a little bit bigger all right so the idea is this the idea is simply to hide these through vba and create a button here that's going to show it and create another button that's going to hide it what do i mean by that well it's created some buttons here i'm going to copy this button here ctrl c i'm not going to duplicate i'm using ctrl v and we don't want a macro assigned to this just yet i'm going to put it right about here let's say right about here that's going to allow us to show the student list i'm going to left justify this uh right click assign the macro and just delete the macro that's been assigned click okay in case we don't want to click it and that's i'm going to say let's call this student list or show student list either one would be fine but let's call student list keep it short so student and we'll add an icon here too student list and then let's add an icon here so we can see it probably an arrow so i'm going to insert a picture here and then i think we've got one let's see arrow white i want to add an arrow white right triangle i think it's called right arrow here we go i got it saved up here size it accordingly maybe to 0.2 great put that on there that's going to give us our student list and then we'll size the button accordingly a little bit smaller so 1.3 should be fine okay good i'm going to hold down the control i'm going to put them in the middle i'm going to group them and give it a name names help even though it's on the student let's call this show show student list button okay show student list button the other one then i'm going to control d duplicate that and i'm going to call this one hide student so we were going to call this we already have one for hide and we're going to call this back to we'll just call it back to students so back and then we'll change the arrow back to students we'll make the button a little bit bigger let's say 1.45 and our 1.5 is good and then what i'm going to do is i'm going to right justify this one because i want it different so i'm going to go to home i'm going to right justify that i'm going to take this arrow i'm going to format it i know it's kind of small let's zoom in a little bit there i'm going to instead of rotate i'm going to flip it horizontally and then i'm going to move it over here so we can see it back to students good let's take a look at that that's pretty much what i want excellent okay we've already grouped it i'm just going to give this a name instead of show student list button we're going to call this hide student so hide that way we're going to be able to show and hide those buttons as well so we'll keep that there and where do i want to put this i want to put this probably right about here so something like that but i'm going to bring this out i need to bring i want to merge and center this one so what i'm going to do is i'm going to unmerge and center that and then i'm going to merge and center it again based on this specific cell bring this over here so we've got a nice centered student list and then back to students so i'm going to bring this over here so the idea is i want to show this and hide the row so show and hide that's all we're going to so we're going to write some macros to do that then i'm going to show you how we do it on the menu so let's bring these even holding down the control putting them in the middle so that they're the same all right let's write some macros that are going to allow us to show and hide that student list accordingly so how do we do that well back into the vba what we'll do is i'm going to create a brand new module for that and we're going to call it student lists because we're going to write some macros based on that so i'm going to right click insert module then we're going to give it a name so go into the properties we're going to call this student list and then underscore macros we can write some two macros are going to start out so basically it's just to show the student list and hide the student list so let's write that right now so this is going to be sub student list and then show and then we'll write another one sub student list hide so under that sub student list and then hide okay good now that we have that we can write the macros accordingly on them so we're going to focus on of course with the student sheet so with students that's the sheet we're focused on we're not really focused on anything else so we're going to also fill in the data which we will work on that so first thing i want to do is turn off application screen updating because that can be a little bit quicker application screen updating so application dot screen updating equals false i want to turn it to false and then before the macro ends i'm going to turn it back on so pasting it down here equals true now what we can do is we can write our code insert so what do we want to do well i want to hide certain rows and i want to hide certain columns and i want to show certain columns and show certain derivatives so what do i want to do well the first thing i want to do is i want to hide rows 3 through 22. so let's write that now hide rows 3 through 22. so dot range and then we could do 3 and then column and 22 and 22 and get rid of that last quotation mark we did it not dot value but dot entire entire row dot hidden equals true i want to hide those and then i wanted what i want to do is i want to show something show something else what do i want what else do i want to show i want to show let's say 3 23 through 119 i want to make sure that everything 23 through let's say 120 120 i want to show those that's very important because they might be hidden accordingly so let's write that now we can just in this case what we're going to do is just copy this and make the updates to the row so i want to show 23 through 120 120 we want to make sure that they're not hidden all the way from again through sorry 23 all the way through 120 we're going to show that so 3 let's get rid of that 3 through 120 hidden equals false i'm going to show those i also want to hide something else i want to hide columns right there's certain columns that i'm going to be hiding what columns i want to hide all the way from let's say m we already have our spatial column e through m ethereum i want to hide that so let's write that in there dot range e through m entire column dot entire column dot hidden equals true we want that hidden i want to hide all those columns and i also want to show certain columns right we want to make sure we're displaying i want to show the columns which columns do we want to show so dot range in this case i want to paste in that i want to show what columns i'm going to show n all the way through let's say a i so i want to show those n through a i want those displayed so change that to n and then a i entire column hidden equals false right we don't want it hidden false okay so that's going to hide and unhide accordingly so what else right i want to make sure that these buttons are not displayed right this button set we don't want to we don't want to show those we want to show we want to make sure the existing student group is hidden we want to make sure that the new student group is also hidden we also want to make sure that the general info group we make sure all that is hidden so let's write that right now we don't want that display so dot shapes existing student group and then dot visible equals msl false right we want that hidden hide existing student button group so we want that all right we can close this we don't need this part anymore but also i want to do i want to actually hide more than that right we have the new and the existing and i'll double check those shape names too new student group i'm going to copy and paste that and hiding let's say new student group so copy that new and then student group and also we want to do is hide the general info group which is this one here general info group it may be hidden already but we don't want to we don't know about that so let's paste that in there and copy and paste this we're going to be hiding this is the general info let's double check that so we have general info group we have existing student group and general info group let's hide that right in that general change that to general info group we want that hidden hide general info group okay because we won't don't want those shapes to show and then also what if there's a student picture there may or may not be a student picture but certainly if there is we want to hide it so how would we do that again so on-air resume next just in case it doesn't exist and then dot shapes student pick that's the name we've assigned dot visible equals mso false i'm just going to hide it and then on air resume next i want to go in case there's no all right let's take a look what else do we want to do i want to make sure that we've selected just something else let's say we just select something like the first row n25 we'll just select that our first student okay so continuing on so dot range and 25 not dot value but dot select dot select okay i like that that looks good application screen updating too that looks good so let's take a look at that also we need to do one more thing right i want to make sure that our hide student list button becomes visible so let's i also want to do another thing i want to right click and make sure that we're not sizing these things i don't want them size one rows so move but don't size with cells close that out that's super important so that way they don't get messed up okay i'm going to save the work why don't we write the macro that's going to do exactly the opposite here so what we're going to do is i'm going to again copy this one here paste it in here now we need to reverse everything so now how do we reverse it so continue on so now we're going to hide it screen updating false in this case what i want to do is basically i want to show 3 through 22 this is going to be hidden equals false i'm going to also run the general info what i mean by that i want to run i want to select something we're going to run the general info tag but i also want to show e through m in this case column e through m hidden is going to be false and i want to make sure to hide those columns so hidden equals true those are the columns where our list is we don't want to show that also what i want to do i forgot that one more thing here show the button dot shapes in this case i want to do two things with this button i want to show the hide student button and i want to hide the show student list button so basically display and hide that so let's do that let's add that in right now so dot shapes hide student list button dot visible equals mso true right we want to show that the high student list button because that will allow us to show the hide show and hide student list button and then of course the other one we're just going to copy and paste that and then we'll just make a few updates on that so we can show that copy that and then paste that in there show the show student button we want that hidden show student list button equals false hiding that one and then what we're going to do is we're going to simply reverse that on the other one reversing it i want to hide the high student button all right that's kind of confusing but you get the point okay so bringing it down here and then i'm going to paste that down in here we're going to reverse them this one's going to be hidden so we're going to hide this the other one's going to be showing hide and this one's going to be c true i'm going to show this one again make sure we're saving our work especially after we write macros saving our work now we're going to assign our macros we'll take a look at that also but what i want to do one more thing in this case this is not going to be the case here on air if if it exists i want to show it so let's do that let's see if there's but if we run the students into we already have a macro that's going to do it for us if i run this macro student tab general photo it's going to do all of our work for us so why don't we just do that run the macro to do that so continuing on so how do we run this macro well the best way to run this macro is simply just to select this particular cell here e3 so all we need to do is just select that inside our code and that's going to run it automatically for us so in the student list macro all we need to do instead of each one we'll just change this to e3 it's going to select that that'll run that macro automatically the only other thing is we don't we need to know is it a new student or is an existing student because i need to again i've hidden all these button sets so i need to determine which button set to show if it's a new button set if we click general info it's going to show these show these buttons which is good show these buttons automatically but we need to know whether to show the existing student group or the new how do we know that well we can base it on whatever's in b7 if b7 does not equal empty we know it's an existing student so let's write that code in right now here so we do let's in the hide okay continuing on right here if dot range b7 dot value does not equal empty then we need to show the existing so again this we can see let's take a look at this this one right here the new student group we are going to then hide that and then we are going to show that okay let's write that in there i'm going to drop that i'm going to spell that right here then okay bring it down let's make sure that's right insert and then i'll change this to the correct spelling so let's take a look at that and then we'll do else and then end if and i'll explain that and then i'm just going to copy this here and then we'll make the updates accordingly so what we want to do is we want is in an existing student or is it a new student so we can set those buttons just right since they've both been hidden if b7 is not empty we know it's an existing student so existing student group that should be changed to true we need to show see true and then we're going to show existing student group okay and then we're going to hide in this case hide the new student button group right it's not a new student because it's in existing in this case what if it is empty in this case i want to show the new student group c true in this change change it to show and maintain the high we also want to hide the existing because it's a new student so that way we're just making the changes accordingly okay we're going to save again save our work get used to saving a lot and we're going to assign the macros and see how we do right click assign the macro go to student list show click ok right click on this one here assign the macro student list hide click ok ok now let's take a look so we're going to do is click student list that looks pretty good we need to update that i think we want to hide that i'll focus on that we need to change this to don't worry about that and then let's click back to student list okay good we want to update one more what i don't want is i want to hide this take a look at this i want to i want to hide this if we also want to hide this icon too so student list see how that's different a little bit let's make an update and show that or hide it accordingly okay because here's what i don't want you see this i still have this and i don't want to see that and i still have this icon so why don't we move them over so we don't see that all right we don't want to quite see that there so the best thing to do is just make sure that they're not on d but on e so let me show you what i mean by that this we can also change to a actual it might be easier to change it to an actual type of shape instead of based on cell and but this one i don't want to base an id i want to base it on e so merge and center that i'm going to copy this i'm going to paste it in here and then i'm going to delete this one then i'm going to merge and center it because i only want a basis so let's focus on one thing at a time so students so now we've got this we still have to work it by hand but now the text is gone now we also have to worry about this i want that all the way up here regardless how will we achieve that so why don't we use this instead of a cell based why don't we use a specific shape base so how do we do that so insert again shapes and just insert a shape okay we can write it down here for a moment now what do we want to do i want exactly the same comic sans 28 bold and white so let's write that in with our shape here so we'll write in capital school manager part 2 which is what we're on i am going to change it to 28 i'm going to change it to comic sans here and i'm going to change it to bold and i'm going to change it to white here i'm going to center it accordingly and we can't see it i'm going to clear the background out shape fill no fill and then no outline okay good that's the way i want it let's take a look at that bring it up it's exactly the same as what we have so we can get rid of this cell so i'm going to just going to move this over here we don't need move this over here we don't need it in the cell delete that now what i'm going to do is i'm going to bring it up here to look make it look just the same as the way we had it before bring in this icon here here now we've got both okay perfect i'm going to group them i'm going to line them up and i'm going to group them and this of course i'm going to change it now i want to format the picture here i'm going to go into the properties here i'm going to this in this case don't move or size with cells so now when we click the student list it remains up at the top back to students all we need to do we can just change this icon here a little bit we don't need this icon or we could remove it so let's do that let's update this icon accordingly because we don't need both so again we can do the same thing we can delete this icon we don't need this here format those cells again size and properties properties don't move or don't size let's take a look at that and see how that looks all right i like it we just need to move it over it's a little bit bit it's a little bit too far over the student list is a little bit too far over we can bring it over here back to students that's pretty good i like that pretty good we can i also move if you don't want to have this student list all the way over here all you need to do is just left justified and then bring it over here accordingly just by adding the different alignments here we can just bring it over here okay good now we're getting there we're looking much better back to students so what we do have to do and make a little it's too far down we need to make sure that we're actually hiding all the way we need to hide 3 through 22 we need to make sure those are hidden right so let's update the code 3 through 22 entire row hidden right this one should equal true 3 through 22 in the entire row hidden equals 22. to do that we need to make sure that we're actually change this to should be 23. right 23 we want to make sure that that's hidden now let's take a look at that there we go that's the way i like it all right that looks pretty good cool so i want to actually do more i want to actually really build this really cool menu and how do we do that how do we click on a single menu and have it updated let's zoom in a little bit to back to 100 where we want it first thing i want to do is i'm going to continue this black i want to bring it all the way down so it looks nice and consistent so i'm going to take this color all the way down here as far as you want to go and just make this the back color so that's kind of gives it a nice look that's what i want i want to keep this consistent look for all the screens accordingly okay so how do we do that so whatever first thing what we want to do is we want to make sure we're giving them a name before we duplicate this for the other screens i want to give it an actual name so we're going to do is we're going to assign some names so we're going to click on our selection pane if you don't have the selection pane inside the view you can also click on the page layout here all right just click on the selection pane here in this page layout and click on here that's another way short cutter getting you there or of course i've got it in my shortcut keyser okay so what i want to do is i want to name these i want to give these a very very specific and very accurate name i want to make sure it is the same name as our this is going to help us dashboard the same name as our sheet and not only the button the icon as well so we're going to click on there and we can do a quick way here so we can do it here or here either way so if i select it so each one of these inside this group i want to give it a name so why don't we start at the top so if this is going to be student we want to make sure that it's exactly the same as our name of our sheets exactly that's the icon and the button so students is the name of our sheet what is about this picture this is called classes so i want to make sure looking down classes and then we're going to rename each individual one this is going to help us give us a really great way to do it in just a single click as some of you are already figuring out so this one's called admin so we have an admin sheet that is the first sheet let's select over here see admin there okay so what about this one this one is called transactions we have transactions and then also we want picture in this particular picture we look at it it's called notification so we're going to type that in notifications because we'll make sure it's spelled correctly if it's not it'll create an error and you'll of course you'll figure that out notifications next up we have our dashboard so we're going to put that in dash or if we go in order we make sure we don't miss any of them that's why we're going to use the selection pane next up we have teachers and also the last picture here we have exams so exams is going to be the exact same as our sheet name picture okay we have one more that's going to be called schedule we're going to be putting day week and month view schedules can't wait to show you that one so schedule is what it's called here let me double check on that schedule exactly and now we start out with the rectangles the rectangle is going to be this as well we want to give them the exact same name admin this is going to allow us to select those menus in a single line of code next up we have notifications we see the one that's selected so notifications and also we have here our transactions once more for the rectangle okay this will really help us eliminate a lot of unnecessary work through the coding exams here and then next up we also have rectangle 14 it's going to change that to classes okay rectangle 13 the last one there schedule may not be the last one schedule i don't mean to lie to you like that it's rude okay two more to go okay rectangle here this one is specifically we're going to be is on teachers so change this to teachers and then rectangle 11 you know because this is going to save us a ton of time this is students i want everything spelled correctly i'll double check that's always good to double check your work and lastly dashboard okay so now everything is named correctly in here hopefully let's double check we have students classes admin transactions notification dashboard teachers exam schedule admin notifications transactions if it's spelled wrong it'll be a bug but it's okay easy to fix schedule classes okay good so now that we have all of that just the way we like it all i'm going to do is i'm going to copy that here actually why don't we copy the this i'm going to copy all of this up to like let's say m and i want to go into the admin and basically paste that in here right here paste it right in here okay and also i'd like to paste the group not there sorry here right here in column actually let's go with column c here column c because a and b again we're going to use those inside our admin as for our admin column so we're going to call those green that's going to be for our admin we're going to start off in c just as we did inside our students exactly the same and then also what i want to do is i'm going to copy this group i'm going to copy that go into our admin and i'll paste it right here and i also again make sure you right click format the picture super important properties move make sure move but don't size with cells because i want to size this column exactly the same width just like we did here okay very good so now we have it sized out accordingly but now what we want to do is we want to create a macro i want to create a single macro and that macros can have a single line of code and that is going to allow us to select different sheets so how do we do that let's write that up right now we have may have multiple macros so let's go ahead and write a new module for this i'm going to insert a module and we're going to call this application macro so give it a name application macros again a single line of code so what is that it's simple very very simple now that we have now that we've done the work it's super simple so let's just write a single macro sub menu go to sheet and so what is that macro well we want to know sheets and then application dot collar what is that that is the name of the shape that's calling it dot activate that is the name of the shape so this application color is the name of the shape since we have named the shapes exactly the same as the is the specific sheets it's very very simple the shapes and the sheets have the same name i'm going to copy this the cool thing about this i can take this entire group and i can apply the macro to the single group for every button so click assign macro paste that in there and click ok now when i click admin it's going to go directly to the admin sheet i'm going to do the same thing here assign the macro paste in that and if i want to go back to the students i just click here and it goes directly back to the students and if we do that there we go there you go so now what i'm going to do is i'm just going to bring it over here to give it the same look and i also want to actually copy this here and bring it let's see this is also 41.25 i want to match that and make sure if it's match 41.25 it's already done good good good i like that so now you can see admin students it goes back to the students okay perfect it went to the dashboard that's what i want and we don't have anything on the dashboard that's fine let's click here teachers it goes to the teachers back to the classes goes to the classes exams all right looking really good focusing on the admin let's put a little bit of a focus on the app and i can't wait to show you and then what i'll do is i'll leave you but let's focus on this admin a little bit to see what some amazing features we're going to have in this admin and of course we would have to color accordingly why don't we color accordingly this menu right we want to update this menu so we don't want the students displayed so let's in this only in the students do we want it late so let's update the menu accordingly in this so we'll slide on over here and this one we want to change the admin so i'm going to select on here in this case i'm going to right click and i'm going to format this shape so format on that and i want to give it a specific color so we're going to go in to this particular fill here we want a solid fill i'm going to go to a gradient fill and this is the gradient fill we already have it updated here so click close that's nice that's just the way i want it to and but i want of course i need to change the text the font color that's obviously so that's going to be there so what do we want here so we can close that out and i'm going to change it to that our default color here our text is going to take the default color i want to put it to bold here let's change that back and make sure it's set bold that's what i want excellent so admin and of course i want to change this icon right this is going to have to be the blue icon so what i'm going to do is i'm going to delete this icon here i'm going to insert the icon i want that blue admin icon so pictures looking for the blue admin which is here insert that changing that to 0.2 and then of course we need to assign the macro to this too right just in case you know assign the macro and that's also of course the menu go to sheet that way everything's been assigned i'm going to take that right here perfect just the way i want it and then i'm going to and then i'm going to here we call this group 1. i'm going to ungroup this i'm going to hold down the control i'm going to click on here and group it and now i'm going to take this students and i'm going to ungroup it i guess i should have added the white one too so we'll do that in a second so there we go now i'm going to regroup it perfect so i'm going to take this white icon we don't need this icon i'm going to let's bring it up here we don't need this one but i do need the white icon because we need to update that so let's go ahead and update that i'm going to insert i want the students the white icon the picture and we're looking for students so students here i guess it's here students here insert white that's what we want and then point to changing this to 0.2 we're just updating the menu on this one i'm going to bring it up there here and you can't see it yet but as soon as i change it to black the background on the black it's going to change so get let's update the formats on here and we'll change this to background this is the setting the background this is the black color i'm using the font color is going to go to white we're going to take off the bold there we go all right that's the way i like it cool again assigning the macro to this too you can also again a great way to do it you don't need to assign the individual macros you can reassign it to the group so i'm going to ungroup this i'm going to hold down the control i'm going to select that one that we didn't do before i'm going to regroup it you can assign a name to this if you want and then also now again we notice that there's no macro sign to this right there's macro so all we really need to do is click assign macro and then again set that to go to sheet now it'll assign the macro to every single shape within the group perfect give a give this a color also on the black color we want to make sure it's consistent with everything else now we have our brand new menu here bring it up to the top just the way we want it there we go so that's a nice looking feel so now when we select students and you see how it flows so well admin students admin good all right that looks great all right so what do we want to do on this admin screen we have a lot to add so when you have a lot to add basically it's just easy to write things out in the list form and then we can fill it out all right great let's get to it but i see we're missing some buttons here let me let me fix that student list back to student list general info there they are but we want to make sure we add those in there actually it should have selected automatically student list back there and make sure to select i want to make sure to select e3 i don't think it's working so let's double check that i just saw that i want to fix that up right away student list macros right we want to change it to e3 select it should automatically but let's move that down here i think we need to do application screen updating up there because when we select it it should work that should be sufficient let's take a look at that see if that fixes that go back in here general info student list and then back oh i see e5 not e3 my mistake okay that happens every once in a while or more often than not okay so we just selected the wrong one student list let's go back you five forget the parentheses there okay continuing on let's take a look there we go that's what i want that's what i want everything showing up back to student okay good good good let's uh i want to make sure we add a picture there to make sure this looks like a david here okay good back to student list back to students everything's showing up you can remove this change the font color let's do that okay you see how we can hide that nice and then if you don't wanna you see this gap just put a space in here perfect i wanted to focus on that that looks good saving our work so you get a nice look here going back to the admin what do we want to add inside our admin well there's a lot i'm just going to scroll down here and start making a little bit of a list of things that we want to add so i'm just going to use this space down here okay so i probably want to have a general info section so we can put all the general and i'm going to put these across here maybe five or six of them i'm just going to bold that there so now that we have it bolded now what do i want inside that probably i want a school name i've got to put that in i want the logo so the best way to organize these is just to kind of put your thoughts down here and see what you might want before you start designing and then maybe we want a contact name next up i do want an email and then probably two phone numbers so phone one and phone two you're going to have both of those and what else might i want to address city state and zip address have doing writing it out like this really helps because you'll be able to really see how much and you can balance things a lot better to know how many fields state and zip okay that we may add more to the general input that's a good start what else do we want to put in i want to put in application settings application settings i want to know if it's a shared i remember i said maybe we're going to share and sync this so share i want to know if they'll give the user the option to share and sync their application so we're going to put shared option so if it's not shared we can do delete row if it is share so we can have some opportunity there i want to know a shared folder location what is the location of the shared folder so shared folder we're going to put that in i also want to know the what the format is right allow you to change the format of the currency but not only the currency maybe the time date percentage so let's put in the currency format that way it's going to once you change it in this admin it changes everywhere else which is really nice the date format okay let's put in not just the currency but the currency format and the date format that way when you make a change to a currency it changes all the way over when you make a change to a date format it sets the date format throughout the application we want to do that we're going to be using times in the schedule so i want to do that in the time format and also i want to have a percentage format not for ma okay also i want to have the percentage format percentage maybe you want two decimals or one decimal so there's a few different ways it's a percentage format also what do i want else i want to have maybe a table so we can list those formats field let's call it format table it's just a small table to show that application settings okay that's going to be good what about i want to put in users and security you have requested to add multiple users so we need that in the tab user and security let's do users and security we're just simply making a list that's going to help us organize it so once we have it i want to show you how you can quickly create out probably get to that next week but we'll be able to create out so what do i want inside i want the username i also want the first name and the last name okay so last name and also we're going to have of course the password but it's got to be protected i can't have anybody use it protected so it's hidden and protected that password also i want to know if they have access right so access rights to certain screens what kind of access let's say editable visible or hidden right so i'm going to give you three different access types for each user what screens do we want on that well pretty much all the visible screen so that's going to be admin we're going to have students teachers just going by the sheets down below schedule classes exams transactions maybe you don't want users uh adding transactions or viewing transactions notifications and a dashboard okay the databases are going to all be hidden automatically so we don't need to worry about that so here's our main sheets this is what we're going to decide each user are they going to have editable access visible access or hidden okay visible by the way just in case you guys didn't tell me i spelled this wrong i spell everything wrong you guys know that by now what else so that's pretty much user security but i also want to have some classes and lessons defaults some some different settings because we need to create classes and lessons and lots of things like that so what do we want inside here well i want to know the first let's say the rooms or location right you may have multiple rooms multiple locations we want to be able to list those so we can choose to have a class in a specific room or specific location or maybe a default teacher per class maybe it will probably set that up when we set up classes create a class so maybe we don't need default teacher in other words when we set up a class we can put a signed teacher and as a default or we can keep it open so not sure about the default teacher but maybe default class type but actually we could do default teacher let's say you have one teacher pretty much on your school for for a while right when you create a new class would it be nice to have an automatic that teacher name automatically so we maybe we will put that in right it'll help us create classes very quickly if we're allowed if you keep it blank then there's no default teacher what about a default class type default class type we also need class types and class class type so default of specific class type and maybe a class subjects list i want to know the subject class subjects list one it's going to be a list class type list all the different types of classes list and also attendance status attendance status list are they no show attendance let's see attendance let's call this type or status probably status what would be in here something like showed up attended no show sick vacation something like that you can add different statuses to attendance hey that's that's a good start for classes and lessons maybe something also like scheduling settings we gotta have schedule settings too so schedule settings and you guys wanted me to create these from scratch so you have to understand my thought process when you understand my thought process it makes it so much easier because you can really save yourself a lot of time when you're adding these things just by keeping a little bit organized then you can refer to this list as you build them out so hopefully the trainings here will help you build things a lot faster and a lot more accurate less bugs so schedule settings so what do we want to know when schedule i want to know the first possible class time first class time right the earliest class time that's going to start i also want to know the last class end time what that's going to do is going to help us define our schedule right the parameters of our schedules that we can know the first possible if it's 7 a.m or 8 am or whatever that is i also want to know what the time interval do you want to see every 15 minutes every 10 minutes maybe every hour so we want to give the user flexibility time interval on the schedules to show what time intervals are there so probably the week view and the day view schedules will have time interviews the intervals but the month view will not what is the start weekday are you starting your weekdays on monday or tuesday or wednesday what do you want to start weekday what a day is that we're gonna provide that and also what school days what are they monday through friday are they every day a week right school days i want to put give the ability to hide certain days if you never have class on saturday or sunday we can block those out or hide those or something like that also holidays are are there any school holidays let's put in school holidays that we're not working it's important to put those in so we'll have a list of school holidays and also terms maybe some terms with dates like maybe i'm not sure about this one like school terms winter summer fall maybe i'm not sure about that school it's curved with dates not sure about that one's possibility you guys can let me know if you want to see something like that that's a possibility also would be really cool to add some kind of certificates and awards right what if we created word documents that we could map out to excel to automatically generate automatically email certificates and awards based on certain graduation or test results criteria so let's do that certificates and awards okay i'll increase that column and so what do we want inside something like this well we need to browse for spelled that wrong again so this is what happens with my certificates it's okay spelling is part of it certificates and awards okay so what i want i want a list of variables student name teacher name parent name things like that maybe not pair name maybe the completion date the test graduation date there's a whole list of variables that we can put in there so i'm going to want to put that in variables now we can help customize those certificates and awards next up i want to browse for a word i want to have a picture for we'll do word documents browse for word so we can actually insert templates those templates are going to be perhaps maybe also i want to have like a thumbnail i want to see it thumbnail and open so i want to be able to open the word document directly from there and i also want a little small thumbnail so i can see it when i select it that could be very very helpful i want to give it a certificate certificate name certificate or award name give it a specific name and also we need to know the file path of the original document super important and also this file path i'm going to write this down here dynamic i'm going to put dynamic here because if we're building if we have a multi-user right we have a shared folder here location so we may what we want to do is inside this share folder put another folder called certificates so that regardless of the user they're going to be able to locate that specific certificate or reward based on the dynamics so we're going to build out the shared folder is going to be part of it and because each user is going to have their own shared folder each user we'll make sure to put that in there so for example if there's a new user they'll browse for their specific shared folder and put that in there even if they don't have visible rights to this admin we can have a pop-up that says please select the shared folder and have it placed in the admin even if they don't have rights to view the admin we can still place their folder in there okay through a little pop-up or a little user form or something like that okay so great so we got the file path of our certificates and that's going to maybe a certificate type maybe certificate type maybe it's for graduation type or test or award or quizzes okay so we have certificates and awards what else do we want i want grades and scoring grades and scoring because it's going to be based so we want to maybe set automate certain grades based on the scoring so we want to have a grade or level list grade or level list of grades like first grade second grade or maybe you have different types so you can be able to put that and also on a percent grade table percent grade let's put in percent percent grade table i want that table to show up all those percentages based on that also i want a type exam type we can have exam type what would exam type be maybe we have exams tests quizzes assignments put whatever you want there assignments i'm just keep putting some ideas down here okay that would be an exam type and then we have certificate add open right what we can do then certificate is assign a specific certificate to a specific exam to a specific test or to a specific criteria certificate okay not adding new ones but simply assigning them we've created a list of certificates here then we can assign a certificate so that we can automate the certificate sending it to the parent or the student based on that criteria so that's going to be it will be able to do that and then what i want to do is maybe the grade level i want to have a grade level and certificate so we can assign a specific certificate to a specific grade a specific test grade level we'll go into more detail on that there okay what else do we want now that we have grades let's move on over grades level and scoring i also want to have some transactions that last the transactions so what do we want maybe a default date default let's put default to the current date and maybe i want to have a type income and these are just some defaults that we're going to have and maybe we want some categories so we can categorize expenses or income categories income or expense having both and then we can add the current data as default that's fine we did that already and that's about it we'll maybe we'll add some more on transactions not sure how many it's going to be small so basically that's a good group so now what i want to do is i want to add a menu up that and then i'm going to let you go but what would that menu so i'm going to take this we'll remove this for now but i'm going to add this inside i'm just going to drag this here what i'm going to do is i'm going to add this we have it here i'm going to put it inside the general info or app settings let's call this name so inside the application settings i'm going to put let's just put a sort by option okay sort by option and that's going to be for teachers and students we'll be able to sort by first name or last name so we'll make an update and that's this field right here but we'll change that accordingly i'm going to put the menu here i want to have a nice black menu i want it consistent with them so i'm going to increase this a little bit here and i would like to have the same exact since the student so let's copy this here i'm going to copy this and i'm going to paste it right in here paste it right in let's say here inside e so pasting that inside here but it's not going to be students of course this is going to be admin and i'm going to merge and center that on and then just create a list so this one's going to be called admin so caps lock that and i want to put in the blue menu here okay so i don't know how many uh we'll just put in i don't know exactly how many columns but we'll start it out just here we may change that i may change that to again a specific text box probably maybe we'll go you know what maybe we'll go with the text box on here because i think it's a good idea because we're going to be changing so again i'm going to do is pretty much what we did here shapes here i'm going to do this change it to an admin so again admin here also 28 here in the font size let's click on that again 28 make that update comic sans is our font comic sans here blue again we wanted our default blue color our format here no fill on here no outline on here and then perfect okay i think we have the wrong size this one's not 28 this one's 22 okay good 22 22 on that one and then also we bolded bold okay good so that's perfect that looks just way so now we can do that's going to be easier for this particular screen okay and also we want to add a logo on that and then i'm going to center both of those i know it's a long training we're almost done here but i just want to add a little bit more i keep saying a little bit more a little bit more admin okay so we're going to have the admin menu here that way you get a good idea of what we're going to be focused on so inside here i want to have inside this admin i want to have just so we listed out so we're going to have general information general information and also i want to have the application settings and inside this menu we want to have again users and secured users and security okay this is going to be a really really cool application okay i hope you're with us every single step classes and lessons so much this will be one of the best applications that i've built in excel for within youtube at least as opposed to the accounting which is really amazing insider mentorship which you might want to join classes and settings and then of course we have scheduling settings scheduling settings we want to put in all the scheduling settings we also want certificates and awards certificates and awards and then we'll format this and then that'll be it for today grades and scoring we've covered a lot already scoring so much great training here scoring okay it's about that right and then lastly we wanted to have transaction settings we'll automate this width here we'll color it accordingly so i'm going to give this that black color here and then we'll go ahead and then font color is white and then bold that here center that maybe underline it ctrl u now we would just bring this menu down i'm going to bring it all the way down here format those cells and then we're going to give it a fill maybe a little bit of a fill effects here fade about starting with our main color and going just a little bit lighter here then i want to use a vertical and then what we're going to do is just on the right side here there we go that's what i want click ok and that's barely visible that's just enough and then the font here we're going to change that to white here so we can see it clearly and take a look at that and see how we're doing oh that looks really nice and then just to put a line here all right good i want to put a line here so we can separate that and a white line so i'm going to format those cells clicking on the border and then clicking on the white border here on the left side and top you can't see it but it is there okay because it's white click ok there we go very nice we've got a great start to an admin screen all right we're going to add an icon i'll add an icon here next time but we've gone long enough thank you so much if you appreciate these trainings all you have to do of course is click like share of course and comment below and also if you'd like you can pick up 175 workbooks i've got all those of my best workbooks inside a single zip file it's only 66. that would help us out a lot i really appreciate you joining us we will see you next week part three we are going to finish this admin screen hopefully next week and we're going to show you all the amazing potential thank you so much it's been an amazing training we'll see you next week you

Channel: Excel For Freelancers

Views: 26,699

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, School Management App, School Manager App, Student Manager App, School Software Excel, Excel School Software, School Management Software, School Management Application, School & Class Manager, School Class Excel, Class School Software, School Software, Student software, Students App

Id: 7vMRJlFvi_Q

Length: 108min 38sec (6518 seconds)

Published: Tue Mar 02 2021