I think you would love that means 175 of my best workbooks it's on sale now for just 66 dollars so it's got a complete library a single click to open the workbook single click to go to the youtube video really convenient that's really low cost right now so i hope you get that we've got a lot to cover like i said i'm going to create this school manager from scratch don't worry we're going to go over every step but it's going to look something like this hopefully when we get it we're going to go so every step you're going to see every line of code every format every mistake every bug i'm going to walk you through it on my thought process so let's get started we've.
Got a lot to cover in this training i don't want to waste much time what i'm going to do is i'm going to close this one out this is a sample so no need to keep this we'll save it any changes we made and then what we'll do is we'll create a brand new file this is going to be great for any level of excel because we're going to go over it so i'm going to create a new file first thing i'm going to do is save it i always want to save it and we're going to save it as what we want to do we want to save it as an xlsm file that's going to be for macros xlsm file i'm going to choose the folder here i've got a school manager part one let's just go ahead and get rid of this it's not going to be a sample file this will be the actual one this is going to be a free download so i'll include the links down in the description below yes it's part one we're gonna be a multi-part okay so the first thing i want to do is i'm gonna save these first two columns it's gonna be sheet.
One let's go ahead and add some sheets in right now so we do want an admin sheet so i'm gonna call that sheet1 admin we're not gonna work on that too much today we're going to work on this sheet too that's going to be students so i'm going to the second one i'm going to go with students and this is the one we're going to focus on today but we're also going to have teachers i want to hold teachers and then of course we are going to have a schedule and then classes schedule we want we want to also have classes right i want to organize classes and the schedule is going to be great i've got a day view week view month view plan so really going to be cool with drag and drop scheduling you won't want to miss any video in this series okay we also want to create exams quizzes tests got to have that and be able to do that and also i want.
To make sure we have transactions too transactions would be really cool to have transactions could be fees from students it could be also expenses it could be payroll from teachers so having a whole transaction is going to allow us to do that so we'll call that transactions and also i want notifications i want to be able to notify students teachers etc notifications so we can have that so we can notify them by email and of course a dashboard right i want a dashboard all right dashboards are going to combine all those reports gonna be cool and then of course i wanna we'll start with we're gonna have a lot of databases but it's gonna hold all of our information so we'll call the students database okay or student let's just call it student database okay so we have student database our we're primarily going to focus today on our students okay so students is what we want to do.
Second color black here that looks good it's not entirely black but gray and then we'll go with a we'll go with the font of bold and then white on that so we'll that'll be good if we get this stand out okay i'll create a title on this and we're going to make it a lot bigger i'm going to call it school manager caps school manager okay hopefully not too many misspellings but i'm sure that'll happen i'm going to drop this down we're going to be doing a lot of formatting in the beginning i like the font let's go with something like sans maybe i think i have like comic sans comic sans is kind of a cool little font not vomit comet sands there we go that's the one i want okay i like the size maybe about 28 make sure it's big and it's already bold i think so we'll take a look not 238. 28 good that's nice but i want to center that maybe between let's say.
C and l or d and l something like that i want to have c i want to actually have c as our this is going to be for our menu here i'm probably going to not make a so let's actually move this over i'm going to copy that and put it right about here okay yeah that's perfect that's what i want there because i'm going to put the menu here so we can delete the font there so basically i want this column to be for our menu and so we'll center this perhaps between d and l and then i'll make these call okay so we got some icons due we're going to guide those a little bit later on so here i want the menu now this menu is going to be consistent for every single sheet so every single.
Sheet that we're going to have a visual is going to have that same menu and so we're going to make it black but i'm not going to use i'm not going to use a cell based menu i'm going to use perhaps a shape based menu because that way we have a lot more flexibility with our rows and columns so this is going to have our menu column c so every sheet is going to have a column c and then we're going to menu for the rest i'm just going to simply color this maybe the a gray color so we can get a nice background something a little bit we'll call this a flat design and i'm going to go with here and the rest of this we can actually color everything with this.
But this is going to be black i'm going to cover this black right but uh so i want to give it that black color which is going to be the same consistent color throughout this one okay however our menu is going to be shape based menu so basically this is going to be our title of the so this is our title the application while this is the title of our sheet so let's put in a sheet title here in d and we're going to call this let's say called students alright this is going to be our student section so students i'm going to go with the same font and i'm going to use an accent color so let's let's add this a little bit bigger font but so we'll do about 22 on this one.
22 and i'm going to merge and center this across also through l all the way through l and we'll make those bigger a little bit okay also i'm going to add again comic sans is going to be our comic for our titles and then i want to give this an accent color so something uh let's say probably like blue maybe not this blue actually the font color we'll go with i want i've got some icons that are pretty much the similar so but i want to use something like this blue here or this.
Posts Related:
- SharePoint Basics Beginner Tutorial
- How To Create A Dynamic Employee Skills Matrix With Projects In Excel Masterclass + Free Download
- VBA to add data to SharePoint. ADO and SQL. SharePoint Automation using VBA - 3
Blue here that look blue okay good so i've got that bold so now we've got our students and what i want to do here basically i want to be able to select a student so i'm going to keep column d d
Is going to be a spacer column right we don't want our we don't want our forms we're hugging this left menu just like that we always want to have a nice little spacer that way we can always control exactly where it's going to be so we'll use column d as our spacer so we're going to start it out in i guess here we'll probably put select students so select here we're going to select the students so we want this above it so that way in column f they will actually be able to select which student they.Have and then it'll load here then i want several tabs here i also want a spacer now here's the beauty of having a shape based shape based menu here because i can then make this like this one i want to make this one smaller maybe about 6.75 just a little spacer this one can be standard at i believe what's 15. okay so then i want to have some tabs i want general info these are going to be cell based tabs that means when you click on a cell it's going to switch so the first tab or section it's going to be general info then i want fees and payments i want to know.
What fees or payments the student has made or what fees so i want to help that could change technically as far as the name but pretty much i want all the transactions based on a student there i want to know their attendance so attendance i want to know what classes they're attending when and also so attendance i want to put that there learn how to spell that next up i also want to know any exams quizzes or tests that they've taken recently so exams and then i'll i want to create an id card so i want to put an id card here a d card there we go so we'll make those all about the same width so they should all be probably right around 14 so i'll do that right now give or take you know if you so we'll make that a little bit bigger it'll spread it out there we go.
That gives us some space so we know what we're doing okay continuing on let's see this one's going to need to be a little bit bigger all right so what do i want to do i want to give all of these a single color and then what i want to do is based on selection i want to change that color so we're going to format those cells and then we'll go into the fill and then fill effects and then i'll do is i'll select two colors very similar to our our color here so i'll do a fade from maybe a medium to a little bit darker black just something very very slight and i want it to be dark enough so that when i have the font i want the font to be in white then based on conditional formatting.
It will change so i'm going to make it probably just actually not bold we'll just go with regular and white but we will bold the selected and what i mean by that okay click ok and i guess we can center that okay all right so that's what i want so the idea is here when you select the tab the color changes and we can add actually add a border around that too so why don't we add a border so that we can separate those easily i might add a white border so the color is going to be white and we'll go with an inside border on all those and click ok good that's what we got now again we'll center that here and we get an idea of what the tabs are going to look like all right and then.
We'll put in on the general information we'll put first name and information here and then we can have that okay great so now what i want to do is i want to create this general tab but i want to have that tab based displayed based on a conditional formatting so we want to know the selected tab so i'm going to put that right here i'm going to put in the selected call tab column okay because i want to know what column has been selected this is column 5 this is column 5 6 7 8. so basically i want that column number to go in here so if i put in 5 here now what i want to do is i want to base.
Some conditional format i'm going to left justify these and i'm going to right justify these we're going to use those as admins those are going to be hidden right so okay so basically what i want to do is i want to develop conditional finding when a user clicks here five is going to go here because this is column five what do i mean by that column right five column six seven eight so when i bring this out right i wanna know whatever column has been selected i wanna put it here then what we're going to do is we're going to base conditional formatting based on this column it's very easy to code so all i have to do is highlight this all of those cells that i want conditional formatting i'm going to go to conditional formatting and i'm at a new rule that rule is going to be based on the column that was selected so equals and then we're going to do is b5 which.
Is that this equals again and then column and then double parentheses like that now i want to give it a specific format what kind of format do i want to get well basically very similar to the background that's going on so the first thing what i'm going to do is i'm going to give it a fill effects now i'm going to go for our background is this color so i'm going to go a little bit darker and then a little bit lighter right i want that bottom to match our background exactly also i don't want any borders on the bottom i want to make sure that there's no borders on the bottom part okay clear that out also i want to change the font color right if it's going to be light i need to change the font color to black and i also want to make it bold so click ok.
Now click ok ok so you see how that changes but we do need a border around there so that's important so why don't we add the border around here format those cells and then what we'll put do is we'll put a black border around those and we'll see how that looks okay so i'm going to put a black border on the outline of get and then we can remove the bottom one we don't need the bottom one but we could put it like either way it wouldn't wouldn't show up okay that's nice that's what i want so now as the user selects on a different and it changes right so see how we can get that.
Really nice tab effect just like that okay perfect that's what i want all the way from five to nine
So good so we've got the tab so what we'll do is i'm gonna have this section for our students then what we'll do is we'll hide certain rows and then we'll make the fees and payments just like that okay great so we we kind of understand that we'll get into the coding of how we're going to flip these but what i want to do is i want to design the general info now i'd like to have let's say i want to have a spacer here so let's put this back to five so it's visually normal so in the general information i want to have a spacer here spacer row nothing in.This row and then i want to put something in here so here i'm going to put let's say first name we need to track the student first name i'm going to tab it over and do last name and then i want the student id here so student id we need to keep track of that id that's important we may go with an id you've always asked me how do we combine letters and numbers and have it so maybe i'll we'll do that in this case but we'll start out with this and i'll show you how we'll start with a standard id and then we'll if you want it i'll show you how to do different types of ids okay all right so and then probably in this section here so i have a student id and then this section k and l i'll put a picture in here so we'll increase this about to 14 as well something like that probably a.
Little bit bigger so i want a picture here and so i want to skip a row and then i want to put in the address so address is going to go here and then i'll probably put in the birth date here birth date i want to know the birth date of them and skipping a row again and put the city here and then skipping i don't put the state we need to know state or region that they're into based on the country and then also the zip code i want to know that zip code there okay next up i want to have the i want to have the parent name because if this is a kid we need to have the parent we'll just call it parent one name we might have multiple parents so let's add that in put that in here and then i also want the actually let's put in before we put in the parent let's put in the level or the grade we need to know what level of the grade and then also i want to put in email.
We got to have the student email in case they're older email and then the phone okay now we can put in the parent information and then i want to put in parent one and then their email and then parent one email see how it's like the same all right we have emails in the same column then parent one phone number then skip down also this time we're gonna do parent two so all we need to do is just copy this and then paste it down here and then this one's gonna be parent two okay we've got some more and and what we're gonna do is this these rows that we're skipping in between.
We're gonna actually make them smaller so it fits better and so next up what i want to have is the admission date and miss what date are they admit admission date and then also perhaps we'll do the tuition fee we need to know what tuition fee that should be billed and i think we have to know how how often are we going to build that to the student every month you know every six months so we want to put a frequency in here and then lastly i want to put in some uneditable fields like i want to know the total fees that have been billed for that student i also want to know the total paid how much have they paid total paid and maybe if they have a balance we need to know there's a balance so these are going to be calculated field balance okay we've got that perfect so this let's.
Shrink it up so what i want to do is i want to have create this little spacer and that spacer basically the row is going to be smaller so let's go ahead and hold down the control and then every other column on those columns that are simply used as space in 14 16 18 we have and 20 we have i'm going to make those lower so i'm going to right click probably make them row height i'm going to make them around 8 points i'm going to drag this up here let's call this probably in half so 8.5 there or less a little bit more than half let's take a look at that yeah that's nice okay so you see now how everything is and now what i'm going to do is i'm just going to simply right justify these because i like them right justified and then what i want to do is i'm going to left justify.
These things so we actually we can hold down the control and i want to format all of these labels exactly the same here so pretty much the same color and then so i'm holding down the control as i'm selecting them individually so that our form comes to life so right click format those cells and i'll do is i'll border all the way around and then i want to do a dotted line on the right down the line on the right okay good so that's it now let's do the fields and address we're going to make it a lot larger so i'm holding down the control again we'll just give this a border as.
Well and we're also going to give it some color here and then we'll format it accordingly to have different formats so right click we need to make column j bigger because that's got acceptance data format those cells so this is going to take the white color and then a border it will go all the way around again let's do this and this and this so it's top right and bottom borders okay good that's good that's exactly what i want to see perfect it's getting we'll add some icons we've got buttons to add in we also need to do the same thing for this so i'm going to copy and paste this just get that format here all right i'm going to copy this and then i'm going to paste special and then paste the format in here okay here we go so we've got that that looks good now our forms come.
Into life we can see it just the way i like it but this i like this to be all together you see how it kind of doesn't so what i want to do is i want to put a border all the way around here so the first thing i want to do is put a border format those cells and put a board on the left side because i want that tab structure to be consistent okay so you see how it's nice and consistent on the left side we'll do the same thing on here so all the way through l what we're going to do i'm going to put a border on the bottom because i want that tab group to be consistent and we'll do that with every single tab and again the same thing here all the way down here all the way to let's say 21.
Here we'll do the same thing here so we'll format those cells here and then put the border here and here okay good i like that and then what we'll do is we'll put the picture it's really starting to come so as we click here we'll be able to see different perfect okay so what else do we need to do a lot well i'm going to take this address i'm going to merge and center this and then justify it to left because that's going to be a larger field redo the borders on that one after doing that and do the solid border on the top and the bottom okay good i like that i like the way it looks all right so our picture is going to go in here right so we want to make sure that we have that completed so let's put in i'm going to put in a border here i want a button set maybe.
Down here so let's put in a button set i think right about here format those cells again i'm putting in the border here so there we go i want to border and i want to put a button set down here we're going to put something like add picture clear picture so let's add that now we've got some we've got everything else once we have the buttons then we're going to add the icons then we're going to build our menu and then we're going to be able to select those tabs so cool we're moving along really well oh don't forget to save your work let me save our work okay continuing on let's add some buttons now so i'm going to insert shape we're going to go with a square design right we got a kind of a flat look here so let's do that let's add this and we want the same look.
And feel so the shape fill is going to be this same color we'll take no outline no outline on that all right that's what i like let's format the size 0.25 looks about good let's just make it a little bit bigger here okay nice and then what we want to do is get the font just the way we like it and then we can duplicate it so we've got lots of different buttons let's start out with add picture and then i'm going to right justify it make it in the center i'm going gonna probably bold that font.
And let's at least make it a little bit bigger i like that okay well we might be able to stick with 11 because we've got a lot of text on there and then it's going to get too big because we don't have a lot of space so add picture we go right here so we'll put that in and then i want clear picture i want another button for clear picture so clear picture very good so we'll put those there are going to make them a little bit bigger here not the button itself but just the cell itself so all right moving these over we'll get those out of the way let's see we probably about two of the 14 again we'll make them consistent 14.29 putting in those buttons we'll be adding icons here so i'm going to put add picture and then clear the picture we want to do both of those things there.
Next up what else do we want to do i want to be able to add save and delete of course students so to do that we need to control d let's add a menu bar up here so what do i want to do well the first thing i also want to save the student and then i want to also in case we're in a new we need to cancel the student so that's going to be another one so let's cancel that let's add a cancel button onto that okay copy and paste that and then we'll just call it cancel new so these are for new students cancel new that means when you create a new student we have two options we.
Have save the student and we have cancel new that's one button set but we have more than that so i'm going to duplicate that for existing student students that have already been saved i want to give it an update i want to actually make that we can give the user the ability to update so we want three buttons on that the first of ones is going to be called new student in case we want to add a new student then we're going to so new student then i also want to click update so in case they've made a change we can update it so let's do that update we'll just call it update.
And then also what i want to do is i want to have the ability to delete a student so let's type that in delete student okay very good we'll make that one a little bit bigger not quite big enough 1.3 on that that should be good so we've got button sets here so these are going to be the same location up here new student update which is too big and then point eight and then what we wanna do is delete student that's all the buttons we're gonna need that'll take care of it for us all right so let's get some icons going here so that we can actually see that a little bit clear so i think 1.15 should be fine for that i don't want to make it too big all right so let's add some icons we also need a menu here so what kind of menu do we need let's duplicate this and.
Create a button so i want a menu menu not going to give us a nice structure so let's do 1.4 on this so what kind of menu items are menu is going to go right down here it's going to go all the way here so what kind of items do we want for the menu well i want a dashboard so let's type in dashboard and uh but i don't want them i want them i'll say put them left justified but i want to leave a space for those icons so maybe two three four five six something like that that's going to leave space for the icon all right good i like that but they're going to be a little bit bigger right we don't want i don't want the font i'm going to put it a big font let's say maybe.
13 so it's going to be got to stand out so 13 so that's going to be our font all right so now that we have that kind of size just the way we want it to we can do the rest so let's duplicate it ctrl d and we're going to call this let's click on it again ctrl d there we go ctrl d and then we'll i also want students that's the current one one students this one's going to look different though actually because it's selected then i also want to have another one that's called teachers so teachers i've got some icons here so that's going to help us as soon as we do the buttons then we do the icons so the icons are going to be on top automatically then also after teachers i'm going to want to have a schedule we.
Need to know the schedule that would be monthly daily weekly schedule so we can schedule all those okay so what else do we want this one needs to be formatted the same way we have the res left and then space it i got to put space here so one two three four five we can also if you don't want the spaces another thing you can do is kind of add this so we can do that so let's do that instead of the spaces right so it's easier to understand they're all going to be the same we can format those basically using this one right here using the increase indent so after the schedule what do we want to have i also want to have classes so i'm going to duplicate that and then put in classes classes okay and also classes we need exams tests quizzes.
So duplicate that again exams we'll just call it example keep it short exam and i'll put an icon for that exams and next up transactions notifications and admin so transactions and then notifications we also want notifications because we want to be able to notify students so let's call this and note it notifications okay and last mean admin right so an admin okay duplicate that let's select outside duplicate it we'll just call this admin okay i like that again but let's.
Let's put the space in let's see how we do with um with this increment let's see if we can get it just though we want to gonna have to select inside the text again not too much right it's too much so one single is too much so again we'll go with the five spaces that saves enough so i think we're good there we'll make sure one two three four five and then five spaces on each so it's consistent all right so now what i want to do is i'll space those accordingly and then i'll place them here so basically pretty much here here here and here so i want the first one to be on the top so i.
Want that first one basically to be even with the top how can i size those accordingly well let's just put them at the top i can add a border here and a border here so wherever i want format those cells i'm just going to line it up so that i can see where i want to line up and then i'll remove the border just so we can see exactly where i want to end up putting it up there okay so i want to line it put start put that dashboard right up here and another thing we can do to make sure is let's let's add change the background color on this one not the black because it's harder to see so we'll temporarily we're going to change that background to the gray which is consistent with the theme now we can see to make sure that they line up accordingly.
So i'm going to bring it up here and then until that there we go that's what i want and then so dashboard we've got students next student's going to be colored differently remember it's not going to be black in this case i'm going to make it white with a gray and then teachers here will be our next and we'll get those lined up we're going to have after the teachers we want our schedule and then we want our classes here then we want our exams transactions here and then notifications will get everything lined up notifications and the admin okay we've got some great tools that we can use to help us so again let's space these out accordingly.
Making sure they're all exactly start at the same location the same number of spaces which is five spaces so making sure they're all exactly even and then the schedule will do then what we're going to do is we're going to all right there looks like they're almost lined up pretty well now we're just going to bring this up here and line them all up accordingly so i'm going to use my selection tool here here's the selection tool you can add that tool just by going into more commands if you don't have it looking under all commands and searching for selection tool okay and then so to have that so i'm going to use that selection tool i'm going to select them all and then what i'm going to do is i'm going to go into the format and i'm going to align them to the left and then i'm going to distribute them vertically then i'm going to take a look to see how it looks looks pretty good i want to make sure that they're not size 2 so i'm going to.
Move it down until i see some kind of separation then up that's what i want so i wanted them just one up because i don't want to see any separation see this is how we have the different background and we can take off our selection tool so we can see it until we get there so until we don't see anything between them perfect that's exactly what i want that would be spaced exactly the same and then what we can do is we can add the icons over here okay good i like that the way it looks now it's looking good that's a little bit too low so we just play with a little bit and space it out accordingly because you can have the menu see now when we have a cell based menu cell based.
Meaning click on sales we don't have the luxury of hiding we're going to be hiding these rows but we wouldn't have that luxury if we used a cell base that's why we're using a shape based menu so that we can do whatever we want and then we can have the same one all we need to do is copy and paste it on other sheets gonna be much easier to function and use it so it's very very easy all we need to do again as i mentioned we're gonna be taking this i don't think they're spaced all accordingly i do need to set that up it looks a little bit funny there okay all right let's take a look here okay there we go there's no beginning okay i like that so dashboard students teachers schedule classes exams transaction notifications and admin we have those nine and we're gonna then what we wanna do is let's just move them all up a little bit here so that blends in now we can add that background back to and then i think they're a little bit off.
But perfect there we go a little bit to the right so we want to line up really nice let's add some icons i've already done it i've i'll include a link but i've used a great website called adioma adioma to get those icons it's absolutely free let's add some icons so i'm going to insert and i'm going to insert the pictures and i've got some icons here now most of them are going to be uh white icons except for the students so i've got calendar i'm going to hold down the control i want the white cancel i need check mark i need um the call the conference is we're going to be using for the teachers uh i need a confirmation to save and update i need a dashboard icon payment.
Icon uh user profile we're going to need that i need the teacher we're going to use this for the add picture button and then again like i said the students i do want the blue one it's going to be a little bit different i'm using this for students so and i also want this for actually the main icon okay so we want the admin here we're going to need that but i want that in white i've got it in blue we'll show you how that works i want the recycle bin to clear the pictures i want the plus to basically new add a new student we're going to use this as the school manager icon the white one.
And then envelope let's take we need this for notifications good so i've got everything now i'm just going to click insert and they're all going to be large size so i'm going to reduce the size down to 0.2 for most of them and then we'll change them accordingly okay take off our selection tool and i'm going to move these up here to where we can see them and now we can start to work with them so this is our school icon here so we're going to add that here that's our main icon let me move that here so this is our school icon i'm going to move this up here bring it all the way up here and then we'll just size it accordingly so that it looks better okay so that's the school manager i also want one for students now notice the students is the blue.
