How To Create This Excel Admin Screen With A Single Click Menu From Scratch [School Manager Pt. 3]

hello this is randy with excel for freelancers and welcome to the school manager part 3. in this week's continued training of the school manager we are going to be adding on a full attachments feature to the students we'll also be adding on the ability to make them active or inactive any complete admin screen complete with general information application settings users and security classes and sessions scheduling settings certificates and awards grades and scoring transactions and settings we got a lot to cover this week we're going to do it all from scratch live right in front of you i can't wait let's get started all right thanks so much for joining me today we've got a lot to cover in this week's school manager part three if you haven't seen part one or part two i'm gonna include those links down below we are continuing this i am building this live in front of you each week that is a complete school manager complete with teachers classes students scheduling drag and drop scheduling week view day view month view notifications email automation dashboard and a whole lot more it's going to be an incredible series i'm going to show you how to build this application from scratch week to week so you won't want to miss an episode and i'm going to do that all live in front of you you're going to see my thinking my thoughts all the bugs that we come across and how i solve them the challenges we face and how we overcome them when we're building these large scale applications by the end of this training you're going to know how to build your own applications and hopefully sell them for a lot of money all right the best way i can bring this to you is if you do subscribe so i want to make sure that you do click the notification icon bell as well as the subscription down below that'll ensure that you get these trainings to you each and every tuesday i create them absolutely free for you in fact this workbook is also free all you need to do is click the link in the description below either with your email or facebook messenger and we'll get that sent over to you like i said i do complete these applications for you to help you build these applications and i do that absolutely free the best way to support us there's many ways actually one great way is to grab my 175 workbook zip file i've got 175 of my best applications i put them all in a single zip file and i also added an application library so you can get to the best workbooks with a single click click on the workbook or click on the youtube video and that'll link you right to the training that's only 66 dollars right now so that would help us out a lot if you can pick that up i'm going to include the link for that down in the description or all right we've got a lot to get started we're going to finish up a few things on the student information then we're going to move into this admin this is a sample that i created for you so i can show you what we're going to be doing i'm going to close this up we're going to take off right where we left off last week so what is that well that is right here where we are we don't have an ad in the screen yet as you can see we're just building it on and of course in the student section we don't have that admin we need to add an active so we're going to do that right now because i think having attachments feature is really important maybe you have printed report cards or maybe you have enrollment forms or things that you want to attach or other pictures or anything that you might have scanned in for a student it would be nice to have those attachments with the students so why don't we do that now why don't we add an attachments feature here as another tab we have this tab is really beautiful so we can add that here and it's really helpful actually the best thing we can do is just pause our screen so i'm going to go into the developers and i'm just going to pause it right now pause this code so that's going to allow me to just select on here and drag and drop here and that's going to copy over all the formats now again all i need to do is just change that to attachments so now we've got basically the format set i'm just going to put that white border in there so white is the selected i'm just going to select it here you can't see it i wish they'd change it to a gray background at least not white but you can see now we've got the white so now all i want to do is add in the tab functionality out of the macro and of course program it in now where are we going to put this attachments well probably going to put it down somewhere uh below 120 we've used all this space here but i'm going to put it probably down we're starting it right here 122. now i think the best way we can do is just kind of copy something that we have now what i want to do is i want to add some fields here perhaps for attachments and then i'd like to have a space for a preview so when you select on an attachment you can preview it here if it's a pdf or something we can build out little previews and i'm going to show you that in the future training so maybe we'll add just about four columns so what i'm going to do is i'm just going to unmerge and center this i'm going to come just copy let's say four columns here and i'm going to go all the way down here i'm going to copy that and then i'm going to go down here and then i'm just going to paste that right in here then we can make changes and updates accordingly so the first thing i'll do is again merge and center this and we'll call that attachments of course and so we're going to give this name attach that's that's what i want what will i want for the task well i think a date adam would be kind of nice we don't need a time but we would need maybe the name a type and a file name now the difference between a name and a file name is the name of something that you assign uh the file name is something that we can locate the file with so we want that and maybe what i want to do is i want to put a border right here this is where i want our preview so i'm going to format those cells i'm going to put a border around here black border all the way around here like that and i'll probably just go with it that's pretty good i like the way that that looks and so the idea is to select on an attachment we'll add a button here to add the attaches we're going to need to add an attachment button somewhere around here and then so we'll we'll select an attachment and then that attachment will preview in here then we'll have additional buttons that we can open up the attachment or delete the attachment or something so all right i like that so basically this is going to be rows 120 through 143 so we need to make updates and add an additional tab so let's do that right now inside the vba so we're gonna go into the developers here visual basic and we're gonna go into student tabs right that's what i wanna do i wanna add an additional student tab and all we really need to do is just first i need to make a couple updates here notice how everything goes to 120 well we need to change that right now i need to make sure that everything goes at least to 143 that's hidden for 143 so how do we make a quick update on that what i'm going to do is i'm just going to copy this wherever 120 is and i'm going to change it to 143 so i'm going to copy this ctrl c and then i'm going to do ctrl f and what that's going to do is launch the find and i'm going to look for this wherever 120 is found what i'm going to do is i'm going to replace it with 143 143 and then dot period how did it know that amazing so all right great so let's go ahead and replace it all within the current module not necessarily the project the current module so replace all and click ok six replacements are done the reason it knew that is because i did this before once before in a test right so that's how it knew it because in my sample i had to do it in my sample so i want to make that's how it automatically knew that because i did it before okay so now everything ends at 143. so that's the way we want it but what i want to do is i want to copy student all right i'm going to copy this macro and then just i'm going to add one for attachment so i'm going to paste that in i'm going to reset our which is fine and we're going to call this student tab attachments and this is going to attach so again we want to hide everything as we always do and there's better ways to do this i'm going to show you an updated way a little bit less code when we do the admin so it's going to be cool when we do that okay but what do i want to show i want to show specific rows and those rows that i really want to show are going to be 122 through 143 so we want to show that 122 through 143 so let's update that at 122 through 143 we want to show that and this one we're going to hide we need to hide this one right so it's going to be a little bit different a little bit updated on this one i want to make sure that it's the one above the id card we're showing actually 99 through 120 okay so 99 through 120 that's what we want to do here 120 so well that one we do want to change back to 120 okay i think we're good now let's double check though we're going to hide this we're going to hide the student picture on this and then what we're going to do is we're going to update that so we're showing that hidden equals false so we're going to show and then i'm going to change this obviously it's a task for show attachments tab rows and i really should update this text because it doesn't make sense here so this is the student id right and then we want to update here this of course would be the um exams and also up here we'll just update the memo this is going to be the attendance we've got to keep track of attendance we're going to be working on teachers and student attendance so a lot a lot to go on there and then of course this one we're going to update the fees and also we want to know the general okay so we're good on that let's save our work and we have to add it so what we want to do is just copy this now we want to when do we want to trigger this macro that's going to be based on the students schedule students right here this student sheet right here that's what we're going to add it's going to be based on selection change now it was up to i5 but we need to make it one more column now i need to focus on j5 right if we j5 so we need to update that to j5 and then add one additional here okay so then what i want to do is i'm just going to paste that in what i've created and then what i'm going to do is i'm going to copy this i'm going to paste it down here and of course it's not nine it's down 10. 10. okay so in 10 what we want to do is we want to add the student attachment so let's take a look at that and then click on the attachments okay that's the way i like it looks pretty good that's exactly where i want it id card making sure that's showing up uh hidden attachments so you see how easy it is to add additional tabs we really theoretically we could add two more if we get some more ideas or you give me some more ideas i'm going to add more we've got you know we've got a good place up to two different ones so you see how we can put everything in one area and we can have a really comprehensive all right cool i wanted to make one another update it's really nice to have all the students here and maybe we can archive students but i'd like to know which students are active and which ones are not now maybe from this drop down list here we will have all students regardless of whether they're active or inactive but what if i want to schedule students it wouldn't it be nice to have only those students that when we want to select students to schedule only those students who are active so we should have the ability to add inactive and active so how do we do that well inside the developers i'm going to do insert and i'm going to sort of form control check box form control and right down here what i'm going to do is i'm just going to put something called active it's a simple checkbox and then what we'll do is we'll update the code accordingly active now we have to tie this particular checkbox to an actual cell so why don't we use b23 here so we'll just call it student active it's going to be a true or false component true and then what we do is we'll just highlight this in yellow give it that same color we'll drop this down because we're going to be using it a lot more give it that same yellow in the border so we know it signifies an admin type of work that we're doing here okay so now all we need to do is tie that specific checkbox to an actual cell so i'm going to scroll up here so you can see what i'm doing here scroll up and we're going to use a format control and now we see this is based on a cell we know what cell that is going to be b23 so all we need to select b23 click ok and now when we make changes to that you see how it changes true to false okay that's great here but what we really need to do is save this information this true or false need to be actually saved to the student database so how do we do that well that's very easy we just need to make a few updates now i'd rather not update this just yet in other words i need to add a column in here so what i'm going to do is i'm just going to take this i'm going to un let's just bring this out a little bit i'm going to drag this over here bring it over here i'm going to drag this over here i'm just going to add another column so i'll drag this and then this here and then what we'll do is we'll merge and center this or unmerge it i should say and then re-merge it using that holding down the control merge and center there okay now it's going to let's just hide let's select everything there and bring it up it's getting like a large database there okay so there we go but now we obviously now have to update the code and i want to know if this is active and there's a specific cell that's going to be based on that not e25 it's going to be b23 now we map the data accordingly now we haven't changed the criteria we haven't changed this which is good right because we want to keep it the same we don't need to change the code but we do need to update the code that we created when we're mapping we need to create all the way x we also want to make sure that we're running our save and load all the way to column now 23 23 so let's make those updates accordingly so all we need to do is go into the student macro student miscellaneous here here and we can just update that and just quickly look through the code okay so for a new student what i really want to do i want to set the default for new student i want to set new students to act i want to make sure here's our students i want to make sure b23 goes to true for new students so when i click new student i want to make sure that's true so how do we do that we just need to set b23 to true so dot range b 23 dot value equals true set new student new student to active okay that's all we need to do to add to for the save and update we need to make sure that we are adding all that include columns so we need to update that to 23 column 23 when we make those saves so that's it for the save update but we do need to make a few others when we sort the names we want to make sure that we're sorting let's make sure that y20 remember we didn't change the criteria still y however we need to include that so i want to include this new column right we're now going over if we take a look in the student database here we're now going all the way over to column w and column x we need that full name includes column x because it's that full name that we're using in our results so we need to make sure to include up to column x so we can do that right here inside our advanced filter right up to pull that up a little bit so we can see more of it right here w and i mean change that to x our criteria is the same and our results are the same so that doesn't change all right so now that we have that let's pull that up make sure that everything is we also want to make sure we update a little bit more on our student load now we're going to loading from 23 so we're going to change that to column 23 and take a look at this what else do we have student delete everything goes again 23 i'm going to change that to 24 updating all that we're going to actually change it that's exactly right that's what i want to do and everything else looks good show picture everything else so we don't need to make any more updates let's test it out make sure everything's working see if there's any bugs or issues save it every time we make changes to a code we always want to save our work back into the students file we'll have a menu we're concerned and okay so we'll load a student and we'll let's go ahead and update this student make sure that that true gets part of that and going into the student database making sure that we now have this under true so this is now true that's what i want for our student which is tina dam so it's true so we're saving that that's correct we want that now let's go ahead and load in another student to make sure that that works so let's go ahead that didn't work so we've got to update that notice the student name we need to update making sure that when we load it let's take a look in that code because obviously we don't want to code so when we save a student we update we take that name and we bring it over there so student miscellaneous so let's take a look right in here when we save we want to bring that full name over so up here under save update we want to do is here i want to bring that that's got to go to x right and this has got to go to x and we want to make sure this goes to x right because what this is is copying we're going to copy that formula here here in the student database right here we're going to copy that so that formula is now an x and i want to bring that full name over here and i want to bring it there so i want to bring that full name here from x instead of w and i'm bringing that full name inside our students here to bring it there okay so now we update it now works just fine okay all right good i like the way that that looks let's click this active click update and then go back to there tina make sure that that active is there go inactive update and then back into there and just to double check oh we'll fix that one that's got to be changed all right good i like that on student load we also need to make sure that we're taking the full name so let's go into the student load macro right here and take a look here student save update load we want to make sure instead of w the full student name is now located in x now look at an x okay so when you make those updates make sure you check the code so now when we load the student here we're getting the full name everything is proper okay good that's the way i like it now we've got the active here and then we'll just update that making sure they're all active all right good so now we've got the active now we've got the attachments our students really getting billed out now all right before we continue further on this before we start adding things like attachments and exams we really need to focus on the admin screen because the admin screen is going to house where our company folder is and especially for share and sync it's very important that we get that foundation built right now before we continue on with additional features so we're going to do that right now inside the admin we last time what we did is we simply made a list of all the things that we wanted inside our admin screen and this week what we're going to do is we're going to build that out okay so we're going to do just that now the idea is this so what i want to do is i want to have let's say general information here and then in the next section so i'm just going to continue building it out using these columns but i don't really want this merchant centered what i really want to have is making sure that this information here these three information stay at the top always and stay in the same column so no matter when i change the columns hide and show certain columns if i click on general information i want this information to show if i click on application settings then i want to show these columns right so it's basically based on columns so the best way to do that is i want a fixed title right i don't want it fixed i don't want it to move so the best way to do is just holding down the control and i'm going to group this information i want to group it i want to show just like that regardless and then what i'm going to do is i'm going to right click that group here and i'm going to set it going to the size and properties i'm going to set those properties i'm going to make sure that we're actually don't moving or don't sizing with cells that way this title is going to be here regardless of the column so when i hide columns it's going to change no matter what okay great so how do we do that we can clear that out we're going to be using it later i'm going to take this data and i'll be adding it soon data validation and i'll just clear it out now so i'm just going to clear it all click ok but we'll be adding the first name last name in addition different part okay so what do we want in the admin also i don't need this merchant centered as well right because we don't have our admin is now based on it's based on this group here so that's going to stay regardless of whatever column we show so the first section what i really want to do is i want to focus on our general information so general information is the first part of the the first group so how do we get into that well i want to add a title on that general information i also want to probably skip a column here so i'm going to put our admin menu here actually let's take a look at i want to put our admin menu is going to be right here this is our we'll put this is our main dashboard here so let's take a look here so when i click here it's going to show up so let's put this just as a spacer i'm going to keep e blank regardless and i'm keep e-fixed but i'm just going to add this i'm going to go all the way over here zoom out it's easier and i'm just going to color all of this gray everything give it that one color just go all the way to a lot of columns here and then give it that gray background color because we're going to use a lot and i'm going to do the same thing for here and then i'm going to also color it black this one i'm just going to extend the black so you see i'm just doing it for a lot of columns and we're probably going to go all the way to i'm estimating to go right around column c like through ct or something because we're going to go a lot so we've got a lot to cover so let's just do that now go through all of them because i've predicted we're going to go all the way through almost like d almost d a so let's go all the way it's kind of small but you can't see it i'm going all the way to right about d c t d a d a is fine that'll cover it so that'll cover it so just zooming out to do that all right so now we've got everything with the background color and i'll say so i want to start building out the general information so the first thing what i want is i want to put a title onto this general information so we know it's the general information and having that title a little bit closer to something that we have on our ad student screen so let's take a look at what we have here and let's take a look let's go back to students here and um all right so i want to get this basically this color but not this size so that's fine that's what i want keeping consistent so we're going to call this let's say general information and then i'm going to put this in that blue font and then once we get it just the way we like it and i'll put it italicize and calibri let's take a look at the the title we'll put it in columbia maybe 13 we'll go with okay and so the first thing what i want to do and bold okay so we've got this a little bit larger that's exactly what i want and that's going to go in call in row 3 there so then what we're going to start is the form so what do we want first i want the school or company name because we've got we've got a list down here now we put the list down here why don't we do this this is what i want in there so i've got school name logo contact email phone one function so let's put that information inside there inside there so take a look all right so school or company we need that information and then what else do i want i want contact name then down below email i'll be skipping a row and then address and then perhaps city okay and then i also need to know the state so we have the city and then let's just reduce that a little bit and i'm going to bring this according let's say all the way to k so it's going to be it's going to be large and i'll reduce that merge and center this and j is way too big so we don't need that it's going to be a smaller form it won't be a lot going on all right so we've got our general information here bringing those we'll reduce them accordingly okay so good i like that i like that the way it looks company information what i'd like to do is build these forms a little quicker because you can see there's a lot so once i get a good specific style i'm going to save that style and so what else do i want i also want a logo right i want to put the logo let's say in column j and i want to have a space for the logo so we'll put that in k also after our contact name i want to have let's see a phone number so put a phone number here we want we want at least two phone numbers for the company this will go on information it can go on invoices it can go in reports and things like that within emails so we'll be able to send that information all right and also the zip code so and uh we need a state here let's put a state here okay so these are the forms we have built out so let's what we're going to do is we're going to set it up so i'm going to set up for speed so what we'll do is we'll call this white i'll left justify it and i'll put some borders around it format those cells and then we'll put all border all the way around it but the dotted line on the left okay and that's pretty much what i want as far as the left now this company information is going to be a little bit bigger so let's merge and center that and then go to the left and then we'll format those cells once again and basically what i'd like to do is save this as a style so that we can quickly build out these forms so how would we do that so let's just choose a standard a normal one which is like just a single cell like phone one that's a single cell here again what i'm going to do i'll i'll merge and center this down again copy this paste it here so it's a little bit quicker then what we'll do actually i just want to paste paste i want to unmerge instead of this and i only want to copy one single cell and then i want to paste it in then what we're going to do is format those cells and then just put add that right border on now we are ready to save this as a style so what i'm going to do is i'm going to click on here cell styles here new style and i'm going to call this let's call it field style okay so it's basically our field style and we have a label style okay so that's right so what's our label our label is going to be right justified format those cells and then the border is going to be basically around the black border on the left not the dotted line and then solid on the left top and bottom okay so now we have our field style so now what i'm going to do is i'm just going to go into cell styles new style and we're just going to call that label style so now we have both of them so now when i want to add this all i need to do is just simply add this into the cell styles here you can use either one of these so let's go ahead and put in the email here and then the address here i'm gonna hold down the control i'm gonna i'm gonna click on the city the address the email the contact name the state the zip here and the phone too so i'm gonna do all of that there and that's it so we're ready to go and i just need to click on the cell styles and then click the field style okay good so let's see i got that right phone one phone two and zip i'm gonna bring these down a little bit one row give a little more space for the logo all right i like that there so the logo i want to do a little bit bigger and then i'm going to hold down these and this and this and this we got state mixed up there so that's no problem including state and then we're going to go cell styles then we want the label style add this to our of course our field style so it's much quicker there we go now our form split down i want these names these to be a little bit longer all three of these to be a little bit longer because they're general so what i'm going to do is i'm going to merge the center of these and then just double check format those cells and make sure those borders are all the way around it like this this and this okay the logos are slightly different because i want a larger field for the logo something like this here we'll shrink that down a little bit so we can put a smaller logo in here and so we'll just color that white here as well and then put a border around it all the way for and i can merge the set of that as well all right that looks good i like that and then we'll just give this the cell style of the label style okay good so now we've built out the general information relatively quickly we've also given ourselves lots of space to add more fields which is really going to be helpful i'm going to put a border all the way around here like this format those cells sorry it's a little bit off the screen let me scroll up a little bit format those cells just put that solid black border all the way around here okay good so now we've got general information but what about the next screen so we've built that out everything we need is here and then i'm going to bring it so i'm going to skip usually we want to add a few columns right i want to skip a few and just to leave ourselves some space and we'll start at let's say column o here so column o what i'm going to copy this and in o i'm going to paste it in and this way it's going to be put called application settings so i'm going to change that application and now it goes a lot quicker because we have the formats set in the style so what do i want to know i want to know if it's going to be shared in sync i want a yes or no are we sharing and syncing this file we're going to add that ability in but we need to know because it's going to help us make some decisions if it's going to be shared in sync and also i want to know if we're going to sort remember we had that sort names by sort and then we deleted it sort names so we need that by in the admin section area we can put what we wanted so we can just put it down here something like putting the options our last name comma first name and then our first name last name okay that's getting good that's going to be our and then what we'll do is add a data validation sort names by so perfect so now we need to do that we got the named ranges here put in a data validation here data validation going to be a list call it equals sort names by okay very good so now we've got that we can add that and then of course what else do we need on this i want to put in the application sharing folder so let's write here app or shared folder i need that location we're going to be adding that in and also we want to know the currency format i want to know what format we're going to be setting we'll be able to set those formats globally within the application simply by selecting them from a drop down list so the currency format so we've got four formats that we're going to be put in let's just take a look what we're getting in the list let's take a look here so here's what i want to focus on here shared option the shared folder the currency format date format time format percentage format a format table and the sort by options that's all everything we need to cover in the application settings so scrolling up here we're going to do that just right now currency format what else do we need on that i also of course want the date format so we're going to put a date format and then down here i also want to add in the time format and here we're going to putting in the percentage format percentage format okay good that's all this is not going to be as big so we can again unmerge and center that and then we're only going to go here to pretty much r is all we're going to do so merge and center that that's fine we're just going to cover to that so format those cells a little bit less we don't need to cover that area putting in there and then let's take a look at that now all we need to do is get an add our cell style so share and sync here this one's going to be dropboard application shared folder it's going to be larger date format here time format and format okay cell styles that's going to be our field style and then here holding down the control again is going to be our cell styles label style okay i like that just we have to merge and center this make it a little bit bigger and the left and then redo the borders on that and then just add that border here around the okay perfect so now we've got that that looks like perfect what we'll do is we'll be adding an icon i'll be adding an icon here for clicking to browse for that shared folder but i also want to know what are the formats here i also want to add in some formats some cell styles for our again our field so let's do that right now because i've got some that i like here let's merge and center this and one more thing we have to do you see that that icon shouldn't show up why is that showing up let's do let's fix that right now general info notice that this is our general info group general for group but our active needs to be a part of that group because when i switch tabs i don't want this active showing here so all i need to do is click on here the best way is just copy and paste that name ungroup it hold down the control click active regroup it paste in that name paste it in also the last thing every time you regroup right click go to size and property sorry it's off the screen properties and then move but don't size right every time you regroup it we need to change that now we go there we see no it's gone there okay so we have the active now group now it's all grouped together so this group gets hidden or displayed based on the selected tab that's why we always use a single group if we add buttons in here they're going to be grouped here as well so while i have this open what i want to do is i want to save this style and this style i want to save this as perhaps something like a table title and save this as a table header because we're going to be using this style a lot so i'm going to do that cell styles new style we'll call this table title table title and also what i want to do is i want to add in table title okay that's good just the ways and i also want to save this as a table header so i'm going to do cell styles new cell cells and table header this will allow me to create those formats a lot faster here header because i want to use the same style throughout okay we can then merge and send this back to where it was and continue on with our admin now that we have that so let's go ahead and use that newly created format and we're going to call this perhaps we're going to call this common field format so i'm going to do that common field format so i want a list of those formats and i want to create named ranges based on those i'm going to give it this and i'm going to give it four of them right so we have date so we can select from a list date we have time percent and currency okay so there we go so now all i need to do is highlight this added the cell cell this is the table header then of course i need cell style and this is the table title here merge and center that here okay now we've got our table so now what i want to do is just basically add a bunch of different formats that we can do and i've saved some in the sample file i've had it here so it's a little bit easier let's just pull that up because i don't want to re-type them in in front of you so i'm just going to go into the sample style my recent which was right here and i do have them so it's going to make it a little bit easier and i'm going to go in to here admin here and then our application settings and i'm just going to take them see i've got them here and i'm just going to copy that and then what i'm going to do is just go back shift and then just paste paste i guess we can paste it all okay so there we go much easier so these are the styles that i want now we can get rid of that i do want to show you from scratch but sometimes it's a little bit helpful you don't need to watch me type in those okay save our work always save our work all right now let's take a look expand these let's just pull them out double click on them all okay good let's take a look now we can sort let's add the data validation here and we can see we can click sort names by last name first name first name last name okay and also what i want to do is i want to create some dynamic named ranges for these and then place that inside these here so why do we do that right now make it a little bit easier so we can understand so what i'm going to do is i'm just going to highlight that and then i'm going to create in this one we've got a named range so we can just do it let's do it all offset so name manager in the formulas and then we'll create them then so i just want to create a new and then we'll just call this format date we'll do an offset even though this one occupies the entire range offset and also what i want to do is i want to select the first one comma comma comma comma count a we're going to count all of them within that range and then all i'm going to do is just update column one okay tab out tab back in make sure that is i'm going to copy that control c and that's going to let us create the others very quickly click ok and now what we want to do is we want to add under one type in format date okay and click ok next up i want to do the same thing new here and then we're going to do in format and then we'll just call this time tab down and of course time is located in column p so all we need to do is update the column instead of o we're now focused on p so we just change this to p in the three different locations that we have it inside this formula get a hold of that there and the last one here that's it that's all we need to do tab out double check tab in make sure that this dancing ants encompass the data that we have click ok new format percent format percent in the percent of course we are going to make sure we are going to focus on column q so pasting that in changing that o to a q and it's very very quickly to do this one here and now we've got to also do one last one on the currency so new format and then currency pasting that in there also double checking here column r is what we're going to be changing that to just those four is all we need and then what we're going to do is we're going to be able to apply that regardless of every single instance and location of the application click ok tab out make sure that it covers it good click close okay so now we can change the currency formats add that data validation here data validation this one's going to be a list here and that one's going to be our currency format so equals format and then currency under currency click ok so the next one what else do we want to do we also want to add the date format here so inside the data validation inside the list here equals here equals format underscore date okay what's next we also have the percentage format and the time format so selecting on here data validation and then of course you can see to get the hang of it now equals format and then time and lastly we have the percentage so we'll just click data validation oh let's make sure to select that cell first data validation here and then list equals format and then percent clicking okay so that looks good let's take a look we can select a currency format here we can select a time format here let's go with this one here let's go with a date format here something like standard and then a percent format here okay i like that we'll double click on that expand that a little bit perfect okay so this one i want to know if it's yes or no pretty much easy so let's go into data validation this is going to be a list and it's going to be yes or no okay so now that we have that we get a yes or no and then what we'll do is we'll add a browse but we'll add that a little bit later on merge and center these making sure that it's merge and centered and then left justified okay saving our work now we're done with the application settings now i want to go into what's next users and security well let's take on what is on users and security we have a username first name last name password access levels and then access for each of the screens admin students teachers and so on so forth so let's add that and that's going to be a straight table so we've got a lot of screens in that so again what we're going to be doing here is probably starting with column let's say let's go ahead and probably v i'm going to skip three columns and we're going to go to column v so i'm going to copy this here and what i'm going to do inside column v here paste that in but this one we're going to change it to user user settings and writes so i want to know all those and this is going to be expand probably to let's just say it's going to be a lot because it's going to take a lot more so let's update this all the way to here merge and center and merge and center again user settings and right so the what do i want to do though i'll add the borders in just a moment first thing i want to add is a username let's say user probably going to have user detail here right so let's put the capitals user detail that's going to be our main heading main title detail and then under that i want to put user name i want to know the username i want to know the first name the last name and also the one under the password now this password is going to be hidden and it'll just show asterisks so you won't be able to see those and uh we'll probably ask for an admin password to be able to view it we'll ask an admin or maybe we'll just either replace it with an existing password so next up in this screen so now we're going to have kind of a separator so here so user detail here i want all this user detail and here we're going to put in security right so in capital security rights okay so we got security rights there and so that's going to be all the screen so from here we're going to put all the screens down here so let's hold down the control actually what i'm going to do is i'm just going to hold down all the way to like let's say a h here we're going to give it that title that cell style of the table title all the way down here we're going to do give it that cell style of the subheaders which is the table header okay but i want to merge and center this and merge instead of this i've got two distinct sections here so merge and center let's just go here and then i want to put right here i want to put that a white border here format those cells because it's kind of a separator there so i'm going to put that white border you can't see it but it's there hopefully all right now let's add the blackboard around here so this is going to be our security and settings and rights so what we'll do is we'll go all the way to the username so we're putting our screen names here so last name let's see password actually right here let's say i want the it's going to be our first screen so i'm going to redo that merge and center that it's only going to come to here and then this one unmerged it's going to actually come all the way here so we have all the screens here there you go that's what i want now put that white border on there because i want the admins going to be our first screen so that's what i want so what else other than admin i want to put in the students so we're going to put in students here that's the student sheet they're going to have access to that the teacher's sheet are they going to have editable rights of that or scheduling will they have access and editable rights to scheduling or not sketching will have three options for each of those let's spell that right scheduling okay next up i want classes will they be able to create classes or review classes what about exams tests and quizzes i'm going to put that in there and also transactions maybe you want them to create their own transactions or even view existing transactions notifications the ability to send that emails possible sms and also lastly the dashboard will they have access to the dashboard or not okay so those are the main screens that we're going to focus on now i'm just going to highlight all of them and just double click so we have the proper width of those okay so that's good that's what i want and now let's bring it all the way down here let's just add in say all the way to around 25 or something like that either way it doesn't matter okay so i'm going to format those cells and do a bit with them i want the inside dotted line here and then the border all the way around outside border i'll make an adjustment on that and i want to put a fill in maybe like of white and then we'll do a alternating row color there so that's good and now what we'll do is we'll add conditional formatting a new world we're going to use a mod formula that mod formula can help us alternate the color rows i've got that on auto hotkey here to help me create it faster i'm going to put it format those and i'm going to use a fill of probably this light color here because we're using that that's going to color alternating rows okay good i want to do a little bit separate and notice this is the security this is the user detail this is the security detail so i'm just going to put a dotted line on the right side because there's some kind of a separation they're kind of different right they're different so i'm going to put a border we'll use this dotted line left so that we can separate so basically these are all the screens that we're going to give them these are all of the different details so for example they'll put in fred freder's here the first name would be fred last name would be freder's and then the password is going to show just something like this that's you know once again to pass it like that now what about for the admin students what do i what options do i want well i pretty much want three options i want whether it's hidden or editable or visible or something like that so let's give them three different options let's say view edit and hidden so data validation go into we're going to add another data validation list and then first thing edit means they have full rights view means they can see it but not edited and then hidden means they can't even see the screen okay so those are the three options so now we can assign this so for this user we can give them editable writes and things like that so that's kind of going to be really helpful or this hidden so we can do that okay good i like the way that that looks that's going to be good for now so we're done with the user settings for a while we need to add functionality for the password where we can when they enter the password one two three four five automatically changes to answers and that saves this password somewhere secure probably somewhere like right around here something somewhere in somewhere down that they can't see the password somewhere locked up so that's what we're going to probably do okay is but let's continue on after the user settings and writes i want classes and lessons i need to know some information about classes what do i want let's go over here to our notes and see what we have in store for classes i want rooms locations a default teacher class type subjects class type list attendance status list so we need some information so we can help us for when we create those classes so let's go and scroll over here and we'll start it out perhaps right around al again we're going to skip three and then we're going to on a out so i'm going to copy let's this one's not going to be very big so i'm going to copy this one here and then we're going to rename it on al3 paste that in and then rename it and this one we're going to call let's say classes and lessons so capital classes and lessons okay so what do we want for that so i want again i want to default add different cast locations right so let's say we have room one room two or it could be anywhere right you can put any kind of location you want in there so but you may want a default location let's say you want let's say generally you schedule classes in one single location maybe you want to have a default location or maybe we want to have a default subject so we're going to put in that default subject and of course we're going to have a list of subjects in a list of locations that's going to be putting we're going to put that down down below so we have default location maybe you want a default teacher default teacher generally if you only have one teacher or default class type default class type so now that we have that in there and of course let's go ahead and click on here and double click on there bring that out holding down the control again and then setting our field type this one the home screen we're going to go in the cell styles and then we're going to go the field style and then of course this is going to be our label style now i want to create some lists so what what's the first list i want i want rooms and i want a list of rooms and locations so that it's an admin you can add in certain locations or rooms i also want to have class subjects if you want to assign different subjects to class classes that could be all class types maybe it's something like online or in person or group or something like that you can put in different class type and also i want to have attendance status right i want to know different attendance status so we'll put in something like that attendance status okay next up also wanna maybe we'll put some icons in wouldn't it be cool if we could have some icons i ran some tests might be kind of cool i'll show you what i mean in just a second and an icon name let me show you what that would look like in just a second okay so i'm going to give it this our header our default header so cell styles that's going to be our table header double click that here so it's a little bit bigger here okay good that's going to round out what we're looking for so now basically all i want to do is just kind of copy what i've done here just bring that down something like here we can do this copy all those and just bring it down and then bring it down that makes it a little bit easier i'm going to paste that in here then what i'm going to do is i'm going to make this white here the white background and then i'm going to add a border around it and then i'm going to extend that conditional formatting so format the cells adding that border around it here and then we're going to extend the conditional formatting so manage those rules and then we have this if we tab out here we only have those two columns but we need to extend it all we need to do again is just highlight what we want it and then just to click apply and that's going to extend it click ok and i'll add in some borders on this these are going to be distinct fields so except for two of them so we're going to have solid border in the middle except for two of them icons and icon name those are going to go together so what i'm going to do is i'm going to put a dotted line sorry it's off the screen format those cells and then put that dotted line because those are have a relationship together what do we mean by icon what would be kind of cool let's just close this up before i forget uh format that cells and group that all the way together not in white but in black outline that okay good click okay okay i like the way that looks that's fine merged instead of this and so what do i mean by icons well what i would be really cool i ran some kind of test i thought it was really cool for those of you that saw we did a task manager a while ago i'm going to open that up and show what i mean by that so it would be kind of nice if we could assign a specific icon to classes so here's that task manager see how i put a little icon just temporarily it's kind of cool we could do that and what i want to do is i want to run some tests to see if it was fast because now we have to group the shapes it is fast it looks really cool right so it's fast enough so what we can do is we can assign an icon to any class which is not only a color but we can create different icons i thought that would be kind of cool so i did some tests in this and i think it would be really nice so and of course we're going to add just so you know we're going to be adding drag and drop scheduling so we can drag and drop classes and teachers and stuff but the difference is what i want like when i select a class what i want is i want to have a lot of features here i want to have the list of students i want to have to put their attendance in i want to have the teacher's information i want to be able to replace the teacher with a substitute i want to have the teacher to be able to clock in so this right side in our class scheduling is going to be really really fit full feature so we'll be able to add a lot so not only we can have a weak view we can have a day view and a month view each of those views when we select on a class i want that those class details to show up generally on the right side so keep in mind we'll probably have to shrink this up a little bit you know make sure those columns but this is we're going to use shape base so it's really cool we can do so much with it we can edit you know be able to edit classes like this we can edit a class so lots of things we can do with it so we'll be taking some from this task manager this task manager is on youtube if you want to see it okay so that was my idea so that's what we're going to do probably going to be adding icons so i want to have a list of available icons and giving them a name so when we create classes we can assign an icon to a class not and probably a color too so we can add a color to class but i wanted to create a list of icons here and then have those lists of icons available in the drop down list once we create those classes okay super cool super cool all right we got it up we could probably move this a little bit to the left right in the merge and send it those because i don't think we we really need that extra space but we could if it depends on how big the subjects are something like that we'll keep that in mind we've got some extra spacing here location might be a little bit bigger right so we can bring this over and fully use that space allotted to us so i'm going to merge and send these two fields here and then left justify them and then i'm going to bring it over i'm going to do the same thing over with default locations we're going to maximize the space merge and center that and left okay so now holding down the control on all of our screens and then resetting that format that cell style for the field style okay i like that that's pretty good we're good to go okay so i'm going to save the work and now once we've done the classes and lessons we're going to move on to scheduling and settings so what do we want for scheduling and settings let's take a look down at our list and see what we've got here so basically i want the class times whether we're starting the last class time so when when we set up our schedule especially on a day view or week view i want to know the first class time is it 8 am 6 am or whatever so that's going to help us formulate our schedules so we also want to know the last class end time when's the last possible end time what is the interval is it ten minutes five minutes one hour gonna be and the start weekdays monday tuesday or whatever school days holidays and terms terms of dates perhaps okay so this everything i want to add inside that so let's go ahead and put that inside some our cells right now where do we want to start that off probably again we're going to skip three so i'm going to put that starting in a u so let's do that i'm going to copy this and inside au we're going to put that information here so this time it's going to be schedule settings so schedule settings and what do i want in that well the first thing let's just focus on the weekday start week day what day is it monday tuesday week so once a week day that's kind of important day start where do you want your schedule to show up and what is the first class start time first class start time and the last class end time i want to know that the last class end time what else do i want to know well i want to know what is the time interval the scheduling interval is that so that's going to be here call it time or time interval or probably put scheduling interval schedule interval okay is it 10 minutes 15 minutes we'll have a drop down list there okay so good let's just double click on that maximize that let it and also let's hold down the control and then of course set our formats our stell cell our label style here and then our field style here for these four fields here we may add more and we've got space to add more so that's no problem field style uh there was test that's why there's two of them in there okay great so we've got that and let's see now i want to add some lists down there so what kind of list let's start out let's add just in case we're going to be adding more let's drop it down to let's say 12 and i want to add scheduled holidays i want to know let's actually let's put in um let's put it in your scheduled holidays and when we need to know certain things i want to know the name the holiday name i want to know this from day or the start date of the holiday from date until today because sometimes holidays will extend over a period of time so that's better too so i'll go ahead and set the cells out of our table title and then of course this is going to be our header here so cell style and table header here okay so merge and center that but i want to know more right what else do i want to know i want to know what school days right so what is it monday tuesday wednesday so again i want to do school day school let's go ahead and put that down here school days we want to know which days of the week we are actually going to have schools in and we'll start it down here i'm going to skip one because i want it monday and then of course tuesday wednesday so we can just bring that down here all the way to sunday and here so let's bring it down here and here so again i'm going to merge and center this one here and uh also the one below here going to emerge and center that and then give it that cell style this of course will take the table title this of course will take the um taylor header and then we can put um scheduled class days something like a subtitle here it's going to be helpful but inside here i want to know i'm going to like put a check mark so put this this of course going to be in the white font because users are going to be able to select on that and then i'll give it a border around here so format those cells let's go up here scroll up format those cells putting in and since they're related i'm going to surround it with the black and then the dotted lines can be and then i guess i'll put in the black and the blue okay so that's it so i want to put a check mark in here like for example let's say we insert symbol a check mark here insert so that's good that's exactly what i want i'll bring that down here just to give it a look and so we can understand how it's going to look okay so for these fields holiday name from day to date that's what i wanted here i want to know give it some names so we'll drop this down again i'll just take a copy of what i've done here and then i'll just paste it down here something like that that would be nice okay so now again i'll add the conditional formatting manage those rules extend those i know moving quick but we got a whole lot to cover so there we go apply that and then of course we do need to change that to white on the background just like we have done in the previous so white here and then of course format those cells adding the solid border around it and adding the dotted line on the inside because they're related to the related fields all right good i like that so that's going to we have scheduled holidays we'll probably add some more fields in here but we're good on that so merge instead of this i'll make sure that this is all kind of grouped together format those cells here and then adding the border around it here all the way around okay good getting rid of this we don't need that extra additional border here or here all right good so now we're on the scheduling settings i'll merge and center this because it's related and probably the interval can move over to here because it's both small fields i like that and then we can merge and center this one and then right justify it here that looks really nice okay ah let's add the cell style here label style okay good let's save our work okay so after that we have certificates and awards continuing on we are going to move on to certificates and awards so i'm going to copy this here what do we want for certificates in other words let's take a look back at our notes here certificate somewhere list variables browse for word thumbnail open certificate name a file path under certificate type let's put that inside exactly where we need it so what do we want to do so again let's start it out perhaps on skipping three going to bc and pasting that in here giving it a name certificates and awards so what do we want i want to be able to assign automatically assigned certificates and awards it's going to be really cool automatically and then send those an email so let's say we're certificates and we could even put in badges or something like that so that automatically they if they complete a test or a quiz certificate i know it's about that right and then perhaps awards or badges just give me some ideas awards or even badges and badges so we're gonna have some information on that so what do i want on that on that i'm not spelled with j the name we need a name of that i need a type and i also need to know the file name now the file name not the path because the path is going to be combined that file is going to be combined with that and i also want to know the thumbnail i want to see a preview when i select on it i want to see a preview and i also want to be able to open it up so all right i like that that's going to look good so we can then i'll merge and center this around that's going to be bigger and we'll increase this increase all of them of course because we want to increase it so now let's assign this of course our cell style this is going to go let's see just for these three these three and then we'll give it that top header title and then these three of course are actually four let's go ahead four all the way and then assign that our table header all right perfect so now what i want to do is i also want to be able to assign a name and we're going to be able to add it so again i'm just going to simply copy the table that we've created here and then we're going to extend it we can create it quickly and then i'll paste that and then we'll bring it down just a little bit more so that way everything's even on this side okay i like that now we're just going to extend that conditional formatting and then i'm going to put the thumbnail here this shouldn't say file path it should say file name right because the path is going to be put together based on the location of the folder we only want the file name that way this regardless of where it's located users on anywhere in the world can then pull it open because it's going to be based on their local drive so they're local so i'll show you how that's done of course okay great so we have the file name we've got a type we're going to extend this all the way over to here adding the white background here adding the borders around it formatting those cells the round border the solid border on it filling it with the white color which we did inside the border i want a dotted line then all we need to do is extend that conditional formatting manage rules and extend it all the way to be so just highlight that and then just change it to be okay good apply that okay so you see how quickly we can and i also want to extend the borders onto here when we select this file we're going to have whether it's pdf or whether it's a picture or pretty much even a word we could almost do we have a little preview of it it's really really cool we can create those dynamically based on the selection so i'm going to show you how to do that too all right i like that so i'm going to add a button here eventually to add a certificate i think that's going to be important we're going to get to the button soon but let's build out this specific all of them and get those tabs working so you can see that i want to make sure you see that okay good so we've got certificates and award what else do we need i also wanted grades and scoring this is second to last right what's in going to be in our grades and scoring let's take a look at our list our handy list here grades and scoring we're going to have a grade level a percentage table maybe an exam type exam test quizzes certificate and perhaps a grade level so we want to put all that information in so let's take a look how we're going to do that again skipping three so this time we're going to start in bk and bk i'm going to add in our information here actually running us let's i think 2 is enough we're not going to need so much for this so why don't we just put it in bj so i'm going to copy this let's take bj and then this one's going to be called grades and scoring because it's going to be bigger grades and scoring so what do we want for that let's put a g at the end of that that's going to help us grades and scores so i want to put in a list of grades and scores and scores here so the first thing i want to know what grades what can they be grades like a b c d so i want to put that in there grades and scores because you might some work on grades some work on scores so either way so we're going to copy this all the way down here and just paste it over again just like we did it's going to be three columns so we can copy that and then paste all those paste all so what else do we want to know i want to know the minimum percentage perhaps or the percent that we can assign that grade the minimum percentage or maybe it's going to be a minimum score so or minimum score right the score required to get that grade 80 90 100 whatever it is so minimum score or grade could be both it could be either one but we're not sure so we're going to put in both options now give it that table header cell style hold down here double click on that okay so for example it could be a b c so we can get some ideas e and f f e i don't think f is fbcd usually usually i was around the d level okay but don't tell anybody all right so let's just put this in this case uh maybe we better drop this down and make it a sub heading here and we'll just call this click okay what i'll do is i'll add a major title called grades and scores a little bit more descriptive and scores okay i like that a little bit better more descriptive so we have a it's consistent with the theme i'm going to merge and center this there we go okay now we can have a b c d and then f okay so the minimum might be let's say it could be a percentage could be 90 or 80 percent you know something like that 70. but if we're doing percentage we should format it as such so i'm going to hold down the control and then i'm going to redo it add in that percentage and of course 90 would be 0.9 0.8 0.7 and 0.6 and so on and so forth what about the minimum score we'll keep that in general and that's going to be 90 if you if it's on 100 or whatever it gives you you can put in whatever so that way we can base it if we do want to automate and set those defaults but what else do we want in here i also want to know the year or levels right so that's important so let's add that in so here year or level it could be first grade second grade third grade or whatever you want year or level depends on so what kind of would we have we would have maybe something like first grade first grade second grade let's put that in here first second and then we can just continue that on continuing on so what's it going to look like like this we can maybe all the way to 12th grade here and i'm going to highlight let's just copy the formats here and paste special and then formats okay let's continue down one more and then add the border rounded just that single column is all we're going to be focused on here that's going to get our grade level so we'll be allowed to do that and assign that that header color here that's what i want cell style and then the table header here so we got years and grades but what else do i want to know graduation criteria i think that would be really important if we're going to be graduating people what is the criteria so i'm gonna just copy and paste this and then we can fill it out accordingly make it a little quicker so this is going to be graduation criteria and the first thing i want to do is maybe the level right we need to know the level which level the criteria points maybe there's a certain amount of points that we need criteria to in order to graduate you need something like criteria points maybe it's based on the number of points or maybe it's also based on a percentage so let's add in criteria and then measures based on a percentage in order to graduate and also if there's a specific certificate or award are they going to be awarded based on that so we can put that in here too certificate or award let's add in certificate award because they're going to be longer names so we have a space so it's no problem award assigned so we can assign a specific award to a specific level if they reach a specific percentage or specific criteria nice okay we'll merge and center that and then of course we're just going to add in we need four columns so let's just go over here and add in four columns something like this and copy and paste those make it a little bit quicker and pull that up okay so let's take a look how far that extends i'm going to extend it down farther to the bottom so it's consistent and then just add the borders around all of it format those cells and add that black border all the way around in the outline okay so that's the idea so we have all of that graduation so i'm going to extend this merge and center all the way here and then of course add that border around that area too good let's take a look so that's our our grades and scoring last one is transaction settings we don't have too much on transaction settings but we may add to it so the idea is this let's just so the first first grade you may you may need 80 points or you may need an 80 in order to graduate and you may may want to have a first grade award or something like that award so you can have an award assigned to it now the idea is that we'll have a drop down list of awards those awards or certificates are going to be created here so we'll have a drop down of these names we'll be able to create all those certificates and then you can assign that certificate to a specific grade if they've reached a certain criteria certain points nice okay good so we've got admin so now lastly transaction settings i've got just a few ideas for this but we can put in so default date maybe i'm not sure about that it may not be i'll just i'll probably just default to the current date not so important the type of the income expense in the category so why don't we just have a why don't we start it out with just a table of items and then you can create those items can have types so i'm going to put that let's say in cc i'm going to leave a lot of room just in case it's our last column so i'm going to put in a lot of because i don't know if we're going to need more space just in case okay great oh you know what i just forgot something i want to put something more in here i want to put exam types i forgot that let's put insert here and then i'm going to do one more insert here i want to put in exam type so just want to add that it's going to be really easy so what is an exam type let's just put copy that here exam types so an exam type would be let's say an exam maybe a quiz or maybe it could be a test or maybe there's even an assignment because i want to have that that's kind of important so that we can assign those okay so double click there that looks better okay so we got that okay i like that much better almost forgot that don't forget that we wanna we need to have exam types okay now we can put in the transaction so it's gonna be a smaller so i'm just gonna copy let's say this one here and in let's say okay cc we'll go with cc here like that and then i'm going to paste in those what do we want here basically i want to create a table of items transaction items so that we can when we invoice or bill or we have a transaction like we buy something we can we can track that so let's put in a transaction item table transaction items and then what do we want we want an item name we need to know the item name i want to know the item type is it an income or expense item type and maybe even a category so we can assign a category to specific items we run the p l we can know exactly what category what items okay merge and center this here and then of course assign it a cell style here and then also i want to and then center that and then also i'm going to do the same thing for here and give it our title header here all right so again perfect so now again all we're going to be doing is adding three again we'll do just the three he thinks this is good adding these and then i'll just update the format so that's a little bit quicker pasting that in so what would that be what would what kind of item would be let's say we had a school fee school fee maybe you have a school fee and let's type it's an income type so you can put income here and then maybe you want to put in fees or something like that then maybe you have something like enrollment or start fee maybe you start startup fee so some it's a different startup let's just say we have a lesson fee made for specific lesson i think that that would be also an income but maybe you have a teacher's salary so teacher salary that would be an expense so you put and then salaries so you can create it like that it's kind of nice and then this would be fees or something like that so that's the idea i have so then when we create those transactions we can quickly we'll keep that just the way it is right now i'm going to probably expand on that so you may add additional features on that now that we have all the content all we need to do is add in the ability to show and hide these based on the tabs i'm going to show you even easier way to do that than we've been doing it's really cool so the first thing what i want to do is i want to know what is the selected row in this case we're using rows in this case we're using shapes but we can use either one of them so what i want to do is i want to know the menu row so in here menu row i want to know which one we selected so let's say if we select three i want this to change color so i'm going to highlight these here and i'm going to add conditional formatting i'm going to click new rule and i'm going to click use a formula it's going to based on a specific cell what cell is going to be based on b3 right here equals row then what i'm going to do is i'm going to format that differently so then the other one so i'm going to use a fill let me use a fill effect and then what we're going to do is we're going to go in here and i'm going to use this grey color and then i'm going to fade it into white and i'm going to use a vertical from left to right so that's the way we're going to go but i also want to change the font i'm going to use that standard font color that we've been using which is this one i also want to make it bold click ok and i also want to make sure that there's no border on the right side so let's double check that then the border should be we could probably have border but clearing out the border may look good or may look just normal okay good that's pretty much the way i want it so when i select something i want this to change and i want this to change let's highlight it's now an admin so let's highlight that and wrap the borders around it good so we've got that we understand how we're doing that this time last name first name doing the same thing here and so once we do it that's based on selection change so what i'm when i select something i want to take whatever row we've selected and put it in here that's the first thing so why don't we write that now inside the developer sheet i'm going to be based on the admin sheet so we're focused here on the admin sheet and we're going to focus on worksheet but not we're going to focus on selection change we make a selection change we want something to happen and it's going to be based on anywhere if we select anywhere from d 3 all the way through d so that's what we're going to write some code for just that so let's write some code for that that's autohotkey that automated that for me so d3 through d11 that's when we want something to happen so if that happened then what do we want to do well the first thing what i want to do is i want to take whatever row we selected and put it in b3 so range b3 dot value is equal to the target dot row okay so let's take a look at that see what that was so now as we change it perfect so now we can see it so but now we want some action to happen right we need to hide and show these tabs so why don't we do right write some macros for that right now relatively simple so first thing what i want to do is i want to create a module our brand new module so insert module and we're going to call this admin tab so to change the name we're going to click on the properties here i'm going to call it admin and then tabs okay so now that we have the admin tabs the first thing what i want to do is i want to write a single macro that's going to hide everything and it's going to hide all the shapes it's going to make it a lot easier in a lot less code so we don't have any shapes right now that we're going to be hiding but we do have columns that we're going to hide i want to hide all the way from starting with column f all the way to let's say column c i okay from f to c high so let's write that right now so sub admin tab hide all okay so what are we gonna do we can do admin dot range asmr sheet dot range f right so we're focused on f through ci dot entire column dot hidden dot hidden equals true i want to hide everything so that's the first thing i want to hide everything what else do i want to do we're going to hide shapes we don't have any shapes yet so we'll add them in of course but for for now that's fine but as we add in shapes we're going to add in so that's good for now so the first thing i want to do is copy this and add that inside our specific sheet so admin so again after we add that i want to run this macro hide everything hide let's call this hide all columns and shapes because we will have shapes and groups and shapes and buttons and things like that we need to hide too so just put and shapes even though we don't know then what i want to do is i want to run individual macros i want to create individual macros and those individual macros are going to show specific columns so we can write that up right now so let's go ahead and add in those so we've done with that let's close this one out and close this out now and we'll just keep a space there so next up sub admin tab and then we want to have maybe general info we need to add that and then what else do we need we need to add more we can copy this and just continue and paste it in there and okay so let's just create one and then it's going to be relatively easy so what do we want to do perhaps admin let's drop this down so you can see with i'm going to bring this back up here so we can see it we're going to focus on f through l right f through l i'm going to hide f through l so let's go ahead and hide that now admin dot range in this case f through l it's been hidden i should say so now i want to make it visible f through l dot entire column dot hidden equals false right it's no longer hidden equals false good so we've got that perfect so that's all i want to do so now we can just continue on we might as well write them all it's a little bit quicker we don't need to test them out and of course when we want to display certain shapes we'll add in those shapes we will be adding shapes in some of them but for now it's fine okay so what's next i'm going to copy this and i'm going to paste it down here and then i'm going to make it so it's not obviously general we can't have two macros of the same name so this is going to be called let's call it application settings and in this case what do we want to do for application settings i want to show o through let's say s o through s so all i need to do is just change this from f to o and from l to s okay good so what's next so you can see it moves a lot quicker now not general users and securities next so let's call this user's security and in this case we're now we're focused on v right v we're going to be showing v through a i so all i do is changes v through a i relatively simple this repetition it kind of gets a little bit boring but the repetition really helps because you can create these much faster so if you know how to do this already then we're going to focus on speed and shortcuts right sometimes we know what we're doing but we want to be able to create it faster and so knowing these shortcuts is going to do just right obviously you can't have two markers same thing so using security next up is classes and lessons classes and lessons okay and what are the columns so we're going to start with al on classes and lessons we want to display that hidden equals false and i also want to bring it all the way to ar so al all the way through a r okay great so we've got that what else do we need let's make sure that's right and next up what i want to do is i will also want to show the scheduling so let's paste that in here and i'm going to focus on scheduling scheduling scheduling let's take a look all the way from a u we want to display those next up we also want to all the way to let's take a look at here the last one one after would be a z so a z is fine and then next up we have our certificates and awards so let's paste that in here certificate let's call this certificate awards and that's going to be bc so write down b c and then it's going to go all the way to let's take a look here all the way through bh so put that in here bh almost done with this it's been really cool okay next up we want to add in non-general info of course grades and scoring that's going to be our next one so grades call this grades and scoring it's going to be a nice tab so this we can put a ton of features in it very very easily grades and scoring starts out at bj and it goes all the way to let's say bv so let's change that up b j through b v next up what we want to do is lastly i think we have after that we have transactions and that's it so we're going to paste go down here paste that in here and call this transactions you guys want to watch me create these lives so here you go transactions and of course we're going to focus transactions on and that's going to start with cc and we'll go all the way to let's say cj on that okay so cc and then cj now we just have to make these active how do we do that well let's of course save our work before we do anything else back into the admin now what we need to do is determine exactly which row we've selected and based on that row we need to launch that macro so how would we do that well we check based on that so but also what i want to do is i want to make it quicker right i want to do it fast so i want to i don't want any screen flashes so there's a way to to do that and we can add in something called application screen updating equals false and then make it true again so we're going to do that right here in the code application dot screen updating equals false and then before we finish out everything we're going to make it true so application dot screen updating equals true so what that's going to do is keep from flashing so now we're going to based on the target so based on the target so if the user selects row 3 what do we want to happen we want to run that general info macro so let's write it out right now if target dot row equals 3 then what do you want to do then admin tab underscore general info and if you use lowercase letters like this and you change and it changes to capitals you see that i'm gonna do that one more time general info when it changes to capitals you know you've got the right macro right if i had the wrong one if i typed in the wrong one like general general info right typing it incorrectly see it doesn't change so that way you know hey something's wrong maybe i typed it in wrong so it's kind of a shortcut general info typing in the right you get that okay great so that's at the target verticals three but what about now all we need to do is just copy this and then add them and paste it in and then we have 4 5 6 7 8 9 10 like that so all we need to do is add in 4 5 6 7 8 9 we have 10 of them in total 9 and 10. okay and now we need to of course add in the specific macros so of course the next one is going to be for apps and settings app if we can you see application settings so app again i'm going to use lowercase just to make sure that i've got the right name settings if it changes the capitals i know i'm good okay that one's right users and security so users security that was the third one okay that looks good and then classes and lessons let's say not through classes lessons okay i like that no see that's wrong it's missed something here so lessons let's take a look inside our admin because you see okay all of a sudden something's wrong it's not what i remember so go into the admins tab and take a look at that what was it exactly users say it's class lessons right so i didn't remember that's fine class lessons i can change either one no problem class lessons that's fine goes to capitals now we know we got it right see that's quicker and you knew if you know something's wrong right scheduling is next so i like to use those capitals to show me if things are wrong certification awards okay and then also we have our grades and scoring and then what else do we want with last one's transactions okay good very nice i like that again saving our work now let's take a look we've got we can get rid of this extra space and we don't need that here and then general info let's take a look at that bringing it out here that looks good i like that it's way too big here okay we don't need this anymore so let's clear that out that's causing we don't need that okay now we'll double click on here all right that looks much better okay so general information application settings let's take a look at that make sure it encompasses all the data this one's a little bit bigger here so that looks good and classes and lessons that looks good scheduling settings good certificates and awards i like that grades and scoring make sure it encompasses everything yep that looks right transaction settings very good all righty that's pretty good okay one of the most important things that we have to start out with is app shared folder i want to make sure that we have a base folder where application because all the attachments all the pictures all the files are going to be stored inside individual folders inside that folder so that's really important so let's create that right now while i still got you here assuming there i'm just going to copy this button here because that's basically what i want and then what i'm using in the admin section i'm going to paste it right i'm going to keep this real simple so i'm going to get rid of the text here and i just want to shrink that button up and hopefully we'll just i know we don't want to assign a macro at least not yet in a moment we will but i want to shrink this up let's just call this .25 i'm going to square and that's fine i'm just going to keep it like that for now what i'm going to do is i'm going to group this and i'm just going to call this we're going to call this there might be more so i'm going to call this app setting group even though it's just a single button if we want to add more we don't need to change anything in the code it's going to stay this i'm going to call it app setting group and of course i also want to make sure that we're not going to change the size of this so we're going to go into the properties anytime we group something move but don't size i'm assign a macro to this and that macro is going to place that file path right here inside p6 let's write that macro right now so inside i'm going to add a brand new module and we're going to call this admin just miscellaneous because there's going to be a lot of them called it so i'm going to go into the properties i'm going to call this admin miscellaneous okay and then inside that what i want to do is create another one called sub admin browse for app folder so after that i wanted to mention the application folder as a file dialog because we need to create that file dialogue and now we're going to set it set the what kind of file dialog do we want application folder equal to application dot file dialog and then what type do we want we want a folder picker right we're picking the folder i don't want a specific file i want a folder this time so that's what i want now with our application folder we can set some things up what do we want well the first thing what i do is want allow multi-select no i just want a single folder so we're going to make that false next thing i want to give it a title and then we'll just call it choose app or shared folder because it's going to be a shared folder if you're not in shared model we still need a folder where we want the files to be stored we still need to follow whether it's shared or not we still need that okay so actually let's just put please select i like that better please just say please because you have to ask nicely please select an application or shared folder okay at least there's one so that way we know where to put everything else now we just if they don't select something it could cause an error so if dot show does not equal negative one means they've selected something so it does not equal negative one then go to no selection and then we're gonna drop down here no selection and then just put a coil in here assuming that they have what i wanna do well i want to take that folder file path and i want to place it where do i want to place it i want to place it directly inside of p6 so we can write that up admin dot range p6 dot value equals what it's going to equal basically two dot selected items one basically is that one selected that's the integer that you're selecting okay great so let's just take a look at that and get rid of the extra spaces here we don't need that and then admin browser folder we can copy that or use f3 to grab that but that's okay right click on that icon assign the macro for that paste it in click ok now what we do is we click that save before we do any of course before we run any code click ok and it will automatic good now it adds that app chilled flow so now we have a location so now what we can do is we can start building out in the next training we start building out a lot more cool so in the next training what do we got coming up next week we're going to be building out going to add in user security i'm going to be building out this admin screen so it's going to be our foundation our base of the application so everything's going to be built out here because then we can move a lot faster on this we're going to add in students what i'd like to do on the student list of course is i'd like to add some ability some of these for filtering and perhaps sorting on single click sorting and also get a few more i want to add the ability to add attachments some preview attachments create an id we've got a lot of work to do i hope you're sticking with me on these trainings i'm creating this from scratch for you it's going to take longer because everything is from scratch during the videos so you know every step a long way i really appreciate your patience if you like to help us out go ahead there's a mentorship program that i've got available for you in that mentorship program i'm going to teach you how to define design develop and deploy your own excel applications for path of income that's helped me in the past a lot create my own applications and now i'd like to share that with you so that's with myexcel mentor.com you can click on that link or click on the link below and join our program we'd love to see there thank you so much i really appreciate it we'll see you next week for part four of the school manager i can't wait it's going to be an incredible application thanks so much everyone

Channel: Excel For Freelancers

Views: 20,125

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: oCX0bOng3eE

Length: 85min 53sec (5153 seconds)

Published: Tue Mar 09 2021