How to Create Student Database Management System in Excel Using VBA - Full Tutorial

hi guys and welcome to student data management system develop in excel using vba let me show you guys how this beautiful tutorial works now right here we have the students subject and we have the grades for each subject and uh we also have a calendar if you want to change the date of the result you can just double click on it now we have 24 now and today's date is 29 there and right here we have the course depend on the course selected by the student or by the course tutor and here we have the student details and the guidance details and over here we have the either the student is a home student overseas student in one of the universities accommodation if the student is on exchange program or on scholarship there so what i'm going to do is i'm going to add these details straight into the sheet that we have in here and we also expect this very student details to appear right here so let's do that so i'm going to go back in here and let's click on add there once you click on add all our kinsley details should appear right here there we go that's lola king's name details and the list box here also displayed the details of the student lola kinsley now let's go back in here we can always reset or suppose you want to change the grades around all you just need to do is maybe increase the grades or whatever you want to change it to or reduce it the choice is yours and just click on result there we go you can see the total sum up the ranking of the student supposing the student is an average student let's see what's going to happen to the ranking there we go so check out the result now the student is a second class or per student okay so let's reset and see what's going to happen with the reset reset it all so we can then enter another student details and the student name let's assume the student name is james ebail there what about the parents details let's say the brother and brother's name is john and confirm the email again there we go come right in here and there the course that james is on let's assume is into computer science there we go these are the details of the course and right here he happens to be home students is not on any of these is not an overseas student he stays at home he's not an exchange student and is not on scholarship right let's see that is bs is in this let's say final year let's click on this object select the subject event driven object oriented programming system analysis um let's go for data graphics computer games animation math and digital graphic and this call here those are his core let's see what he will wind up with so we just need to do nice to click on result there we go second class lower okay james happens to be an average student so let's come in here now details of james is not right here yet so let's go back in and click on add but but just before that is not also on on the spreadsheet itself so now click on add there go straight into the list box james details is now here and if we go to the spreadsheet that's james details there so guys and i'll see you guys shortly with a full tutorial okay guys let's exit out and start a new project entirely so i'm going to just minimize that now this is my workbook for a new project so first of all i'm going to enter as follows let's select from a rule a let's just select it up to a a n there we go so those are the rows that i the columns that i intend to use for my project so i'm starting with student id first name so name address and so on so i guess you guys can see that there all right the next thing i'm going to do now is i'm going to save this project as a macro enabled so let's go to file save us and i'm going to save it right okay let's save it right inside the student database folder that i have in here i'm going to call it the is going to be the file type will be macro enabled and that is what i'm going to be calling is student database management system okay so i'm just gonna enter one in there oh let's make that two i already have one and that is it click on save there now the next thing is i'm going to go straight to my developer if you don't have a developer on board all you need to do is to go to file click on file select options and right here select the customize ribbon and you also need to make sure you have this option selected all commands then you can look for the developer tool the developer to once it's found will be something like this all you then need to do is to make sure it's checked and you then click on ok right here just click on ok and that we present you with a menu called developer when you click on the developer this is what you get development environment and to your left is a visual basic and you can always select alt f11 okay so click on that and that will bring you straight into this environment if you then click on or you select insert and you don't need to select user form right there that's your user phone okay so let's just define the size of this form the i'm going to make the height about 800 and i'll make the weight 1004 okay 1040. there and that is it the next thing is select the tool the toolbox and we don't need the multi-page i think this is it that is it right there multi-page click on that and just drag it that much all the way okay let's select the very first page of the multi-page you can always right click on it and select rename there we go and that is the name of this is going to be student details click on ok and the second one right click and select rename that is going to be let's say university database there we go now back to my toolbox here i need a frame let's draw one frame here and i'm going to get rid of the text content on that frame delete that bring it down a little bit right i need another frame on this side i select both frames and just bring it down again there and to my right here i'm going to need couple of frames in there as well all right so my frames are in place now so i'm just gonna tidy it up a little bit right okay this is how my frame is looking right now okay and i've added two in here so that's fine so let's run it and you just see how it looks that is how it's looking i guess you guys can see that so i'm going to exit out now let's come back in here now here i'm going to add label let's come in here our label and text box right there so i need couple of those in here so hold on to the control click and drag okay i'm gonna need three more here there and this very one i'm going to chain that to a combo box so undo that i think i'll make that the fifth one students his name okay this very one deleted and grab a combo box and just add it right here there we go there and let's change these details around that's going to be student id this is going to be first name first name oh let's change this one to student txt student id txc student id id the next one here is going to be first name and the text box right in front of it will be first name txt first name we have so name here so i guess you guys get the whole idea of that so that shouldn't be a big deal right and the text box right in front of you that's going to be txt surname now this is going to be address textbox txt address this is gender and we have cbo gender there we go then let's say we have a mobile here lena's mover and this is going to be txt mobile here let's change this to email right here first email and this is going to be txt email there we go there all done now i'm just going to copy these copy everything there and drag it over here this is going to be for the university details and i'm going to move this up here because that would be for for the course why here underneath here that's going to be the guidance details grab all of this and just drag it down as well there now i also need two more here one up here and one down here okay and here i would need one more here and here that would be buttons one two i think we're gonna go for about four there or maybe five even yeah i might have to make that five later all right there i also need a combo no not a combo yeah a combo box here so we need five of those and one more here and we need five there okay we need the same thing here right there looking good so far delete this get rid of that i need a combo here okay let's drag it that much okay looking good guys now the next thing we want to do is i'm going to need label here let's come in here we need a label and that label i want let's change the border so that we can see the lines i need and that is just going to be number i'm going to have to increase the size of that font so something a little bit readable i think that might be too much 12. okay i also need to get a center do get it centered right so i need another one here okay looking good and just one more there all right we don't need text box we might as well just grab one here no we need text box here one here and we also need combo box here right drag i need a label here that's going to be for the subject and this is going to be for the score right so let's just increase the size of this a little bit copy and paste all right it's looking good guys almost there there we go right here that is just going to be the subject anyway so within subject here change this one to score there okay i'm going to now design my calendar there so let's come back in here and just grab the button you need a couple of loose yeah i'm going to have to tidy it up right so i may have to just speed up the whole design of the interface if you guys want to know how to create a a calendar i already have a tutorial on youtube [Applause] i will try and i will actually include the link of that particular tutorial for you guys to have a look at okay so if i run it now this is how it's looking it's a little bit rough and it's gonna take me some time to kind of like put it all looking good so i'm just gonna speed that up and get back to you guys so exit out let's speed that up okay all done guys so let me let me run it and you guys see how the whole interface looks like for now so let's run it there this is how is looking and nothing is happening i've added another button here nothing is happening so fast you can see okay with these very calendar you guys will see there's a tutorial on board of how to create this very calendar so i'm going to include a link of this very calendar for you guys to have a look at now this is how it's all looking now for now okay and nothing is happening all right so let's start work with decoding so let's take care of the exit first that's the easy one okay i'm going to exit out and let's come back in here so let's double click on the exit button and right here let's start by declaring as follows i'm going to say dim i exit as let's say vb message box result msg box result there and i'm going to initialize this variable with the following message so let's say message box it's going to be confirmed if you want to exit okay that would be my very first argument the second argument is going to be question icon there we go plus the third argument that is going to be for vb yes or no and my fourth argument i'm gonna make that my own titles let's just say student management system there that's my fourth argument press enter now let's use an if statement to validate the selection made if i exit equals vbs then i want the system to unload and load me and just end the whole lot there we go that is it done so that is our exit i know some people just prefer to write to enter unload me but i think this is the right way of doing it your end user might just end up making a mistake clicking on exit but this will give them the option to consider if they want to exit or not no i don't want to exit yes i do want to exit okay that's why i prefer it that very way so let's double click on let's take care of this one this easy one as well print for the print i'm just going to enter application dot dialog and that is going to be x x dialog let's look for x dialog printer setup there is one of them here somewhere printer setup that's it there we go dot show there we go and i'm going to say this workbook so the printing is going to be directly from my worksheet and workbook dot sheet and the sheet that i'm going to specify is going to be my shape number one close that without print out let's say print out dots not a dot print out copies and that is going to equals let's say just one copy there that is it and this is my workbook that i'm talking about this other one as you can see sheet number one okay so print is taken care of as well now let's take care of let's take care of the reset double click on reset with the reset guys i'm going to declare two variables let's say dean txc comma and the next one is going to be cbo okay and i'm now going to say 4 using a for loop for each txt in multi let's say multi multi-page one dot page one dot control that'll be controls within s there so you see the multi-page one the page one that is the multi page that i added more or less you can always call it tab control anyway let's get this next then i will show you what i'm talking about if we come in here now this is the multi page that is right there okay so that is the one i'm talking about so i'm going to double click on here okay it is called multipage so double click on my reset and continue so for each txt the variable that i declared in multi-page dot page one so making reference to the text box in there so and i want i'm now going to say if the type let's say type of txt is forms dots text box so that is me now referring to the object then and i want the txt without text i want it to clear whatever content and i have in there there we go that is it and if there so my very first statement to clear whatever content i have in there is taken care of just take a look at these four lines of codes or five lines of codes one two three four five look at that these we actually clear whatever i have inside or on any of the text box let's run it and you guys see what i'm talking about so let's enter some stuff in here let's keep going enter whatever in there and and yes well every single text box will be taken care of using those simple lines of codes that you guys saw earlier so we need to do this entity for the combo box so if i click on reset there we go look at that apart from this this is a combo box so i'm going to exit out now let's go back in here double click on my reset now i want to take care of the combo box okay to take care of the combo box i'm just going to copy this and paste that in there that is going to be for the combo box all i then need to change is you see this variable is going to copy that and replace the txt with the cbo and here as well right here too there but right here the object that we have in there is a text box so we need to delete that and change that to combo box there we go there let's combo box in place now this combo box will actually wipe out everything on every single combo box i have on the system so we need another way around that as well so let's run it first okay whatever i enter in here that will be cleared let's say we have these in here as well and every single data and these are supposed to be my default value okay these are just the default value so if i click on reset here there i've now lost every single default value that i have in here here as well and here i should enter a default value in there now to take care of that this is what i'm going to do i'm going to double click on reset and come back in here clear every single combo box at the same time i would like you to enter some value in there let me show you one thing here you see it is very frame here this is frame number three inside frame number three i have frame five so i need to address frame three point five and this one is going to be frame six i also need to address that so back in here i'm now going to say multipage one dot frame frame three point frame five point that and that will take care of my very first frame and that very well on that very frame five i have let's see what value i have no india and the next one is zero so double click on that and i'll just change the data in here to you know change every single data to know and the other one that is six change every single data in there let's change that to zero there and this one is frame six that that should take care of that now if i run it apart from one and two these two if i run it now let's say i enter whatever i want in here now [Music] and whatever value in here click on reset there default back to zero and no and this one the default value i want it in there and i also want the default value in here and these three here these are labels that need to be cleared so i'm going to take care of them separately now let's go back in here so you guys are i believe you can see how he said put together so let's come back down here first of all i need to know the details that i have in here let's go back in here okay select a course and the name of this is cbo cos right now let's go back into the code and right here let's paste that in a dot text equals select a course there so that should take care of death and the next one that is cbo gender dot text that would be equals let's just say make a selection there we go and that's take care of that too now let's take care of our labels ldl total score dot text no that should be dot caption dots caption equals clear and i'm going to repeat the same thing for the other two the other one is lbl ranking test and finally lbl date there and that take care of every single object that need to be reset on that system there we go have a good look at it guys there we go that is easy for me to take care of now let's come in here you see this gender i'm going to change the value in there let's come in here where is it or text i think yeah just enter that into it yeah that takes care of that now the other thing is i'm going to enter some values in here so let's do that using form load but one thing first let's run it let's come in here click and run run so if i change whatever value i have in here to female and select it will officially default back to the new value so let's say computing here and click on reset there we go look at that let's take care of that good okay now what we need to do is we need to be able to hard let's say months here and years in here and just populate all of these let me exit out first of all i'm going to declare some variables let's double click on the form load and right up there yeah um let's say option explicit and i'm going to declare the following variables d the date as date that's my very first variable like i said earlier if you guys want to know more about how i developed the date time picker there's a tutorial online and i'm going to add the tutorial to the link of the tutorial to the description area of this particular video tutorial that i'm working on right now okay so the next one is the year then i'm going to also declare this month that is this month and that's going to be as date as well now let's declare something to do with boolean let's say create create call we stand for create calendar so that's going to be true or false and finally i'm going to create one integer variable this integer variable will use that to generate the dates the month on the year okay all right that is that done but there's one problem once i generate the month and the year what we happen is anytime i click on reset i will end up deleting this so we're gonna find another way to take care of this but first of all let's double click on the on the form and here i'm going to do one thing let's go for form initialize so come right here and change the the event to form initialize right okay with the form initialize the very first thing i want to do is i'm going to say application dot enable so that'll become false okay dot enable event that will come forth so that is it right there there now that that is taken care of then the variable dates the date that i declare up there i'm going to say equals the this very variable day this this one we say is equals the date bring it right down and change this to date then for this month this month that will be let's say equals let's get it formatted and what are we formatting we actually formatting this the date here i want to format it to months okay we also need the year the year that will be equals let's get it formatted as well to the ear i'm gonna grab all of these and then just paste it right here and we just format it as here okay now that that is done now we're going to use a for loop but first of all let's grab the name of this combo box there okay that is the combo box i just call it cb underscore month and this one is cb underscore here okay so let's go back in here now let's use a follow-up for i i was declared up there equals 1 to 12 there now cb underscore month dot i want you to add as follows and whatever you adding get it formatted first then let's say date series it's going to be year date then we also need a month and that is going to be this there let's say plus i comma zero comma let's go for the months now there and close that all right and let's just get it nested so i'm gonna okay so if i run this now what will happen is we should be able to generate the the month inside that particular combo box so let's run it and you see what i'm talking about there we go see that so and this is the current month okay in here there's nothing yet so let's take care of the years so i'm gonna exit out let's go to form initialize again right inside form initialize what we want to do now is i'm going to say the month itself dots that'll be dots list index so let's grab this dot list index and that is equals format date comma month then i want you to subtract this format of date from the month there we go now let's use a for loop here for i again equals minus 20 to 50. now let's see if if i equals one then c b underscore year dot add add item the item that is added we want that formatted as well so let's just say format this item the date get it formatted comma we also want to format the year there okay now that that is taken care of maybe we should move this down so that you guys will understand it properly now that that is taken care of so what we want to do is let's say else oh we need to close that so let's say else cb here we need to need you to add as follows and we need to get it formatted again grab all of these and paste it here now in this case that is going to be formats date odd let's add data card that will be here that's correct then here we now want to minus i i minus one there then date the date to understand this i have a tutorial on youtube for you guys okay and here we just need to get it nested okay so let's correct this that should be an open bracket there now let's get it nested next okay and that is all there is to it so for us to be able to run it i just want you guys to see how the code is going to look like so now it has to continue okay now the next thing is uh i might as well so it continues so that's going to be else let's bring that there and just get rid of this and that is the year then um i would kind of like those bring that down so that you guys can see the codes so if i run it now what's gonna happen is that should generate the the years for me so let's see what's going to happen oh it's not showing yet it does generate years okay years are generated but let's look at this if i click on reset here that's going to get rid of everything that i've been working on there so i need to find a way to make sure the date and the month and the year stays put so okay now let's come back in the fs and take care of that so right underneath here i'm going to do one thing let's say cb underscore year dot list index and i'm going to say that is equals 21. so that should be able to give us year 2021 then look at it done right so that is taking care of but there's another problem which i showed you guys earlier if i click on this that goes off so let's take care of that before we can continue i'm going to come down here double click on reset and up here i'm going to declare two more variables let's just say say d y comma and m now let's say m and y m comma y as string okay m comma y as string m is going to be m equals this is c b underscore empty the month dot text that's good the next one y which will stand for year that would be cb underscore here dot text there we go so whatever value we have it they are officially stored inside m that's the m is for the month and this one is for the stored inside y so knowing fully well that this will clears it once it's cleared then these two guys will replace the value that we have in there so let's copy that i'm just going to bring it right down here paste and right here i'm going to say this equals m and why this one equals y that is k of the error once i click on my reset there we go there so that should take care of the error so once i run it now and click on reset there we go still there look at that okay let's see there so that takes care of that that's fine all right now that we've written out the lines of code that generates the months and the years we now need a function okay now that very function is kind of like long so i do have my youtube tutorial there for you guys to have a look at so i'm just going to speed that up and right here guys these are the lines of code for the function that will that i will use to create the calendar okay so let's come down a little bit so that you guys can see it's there now what we want to do is we now need to call this very function inside the form initialize okay this is the function have a good look at it like i said there's a tutorial on youtube for that okay bring it down and here inside the form initialize the first thing i want to do is i'm now going to say if you guys can still recall one of the variables declared out there called creates calendar okay that is a boolean so that's going to now be true let's say equals true there now the next thing is i'm now going to call this very function add calendar let's call that very function there okay now in that case application dot enable events that will become true so i'm going to grab that as well come down here and just paste that in there that becomes true there we go and that's all there is to it so but i also need to there we go that is it i would also need to add one single line of code or every single button here but before then we need to be able to populate this and this now let's do that inside form initialize double click on that and write inside form initialize again so have a good look at these lines of code for now the the calendar is finished so right underneath here i'm going to say home student we have as follows yes or no agreed i've already entered a default value for them for the home student so if you want to see the default values so let's come in here there okay you can see all of them have they have their own default value so let's double click on from initialize now right here i'm going to say cbo home from student that i want you to add as follows add item and the item is going to be no there we also need to add yes there we go and that is for yes there okay we also need to repeat exactly the same thing for the other combo box and this time around that is the overseas student so might as well just copy and paste that that is going to now change to oversee and here as well there we also have accommodation let's see accommodation we also have yes oh no yeah i guess that is correct so that is for accommodation now then i have exchange student that is called exchange cpu exchange there we go and finally we have scholarship so come down here is that and scholarship right yeah that takes care of the very first one now the next one is going to be for the gender so the gender will be cv or gender let's say dot add now first of all the text on it the text is officially dead no okay let's just see that odd okay and we just add in male or female right there now we also need one for female male and female i mean okay let's add one for the text this is going to be a text text equals make a selection there we go that is that done we also then need cb or course the text that is equals as follows select a course there now we need to add the following right underneath here that's when we don't add the ad item and the first item that i'm adding is going to be i'm going to add this add that get rid of this now this should be odd item right now let's add the others okay what is do we have we have uh so i'm going to change this one to bsc bsc serious games now that's my first that's the first course the next one is going to be as a bsc computer science then let's say ba animation be a animation and we have couple more so i'm just going to copy this and kind of speed that up paste that in there okay that is it all done for those combo box but still have some more let's run it first and see run and there we go if we drop this down there so we can select whatever we want we have yes or no there okay look at this we want this one to generate some numbers maybe one to twelve no i don't think anyone would spend 12 years in university though so let's use a for loop to take care of this so i need the names of all of these two let's see what is their name that is b-a-b-s-c let me msc okay let's go into form initialize we can use a for loop i'm gonna grab this or we can even put it right in here though it might become confusing but so if we come in here and say ba that's i think it's c i think let's go back in there again let's see yeah that is it cbo grab that double click on that and write in here cpu dot add what are we adding we add an i to it there and that should generate 1 to 12 so if i run it now not this yeah that generates 1 to 12 instead of writing the for loop so i'm just using one single for loop to take care of everything so this is bsc and so on all right so let's repeat the same thing for the others double click on this and in here grab this there are five of those two three four five and this is phd phd this is msc this is plc and now m a m a and here we have d s c b and c there we go that should take care of all of that so here i will just generate 1 to 12 that's all for each of those combo box so check this out and this and so on so that is fine it's more or less like a shortcut okay so that's good now let's take care of this then we can then finish up with this here now let's come right down here right in here so we need the following each of those combo box they all have their own various names so let's see the very first one here is known as cbo subject one so grab that double click on that so inside form initialize backing form initialize again come right down here [Applause] paste cbo dot add item and the item we add in this time around let's just call that data data science there we go that's the very first one and the second one the second subject of go in there let's assume is event driven you can have more than one so i'm just gonna make it to event driven program you can add more than two so that's for the very first one so the second one and so on so let's copy paste drop it down [Music] close that so this one is going to be two and two here so three and three and this will change the name and the subjects to object oriented objects oriented programming oh just okay that's fine now the next one maybe yeah let's just put spreadsheet there oh spreadsheet okay so now this should be three the next one here is going to be system analysis and information technology information technology let's just say info information tip there i just speed up the recipe we have eight of those to do so i'm gonna copy all of these and just speed it up there let's speed up okay guys all done there so if i run it now this is what we get that is good that is very good all right so that's fine we're heading somewhere so all we didn't need to do is if i select this i like this to populate all here and here i should be able to enter numbers and if i enter more than 100 i expect the system to query that okay let's complete work on the form initialize so double click on that now right underneath here what i'm going to do is i'm going to say my multi-page multi-page 1 dot the value equals zero so the very first page will always be the one that we open up once the form once the system opens up now the next thing i'm going to do now is lst student that is the list box that i have in there dot column count no that's a column header i want the heading to to show equals true there we go and in total my column i think i have about 40 so i'm going to say lsd column count equals 40. that count equals 40. you can always change that if that's wrong now where are we going to get all of this information from going to say dot roll source equals that will be from the sheet number one let's say dollar sign from letter a dollar sign take it from to equals dollar sign now and that will be up to a n so that should be a n really so let's look at the workbook itself right a and so i'm going to change that now so let's say dollar sign a n dollar sign up to rule 65 356 and that's all there is to that so if i run it now all of these that you guys are looking at here should be on the heading of my student list box so let's run it and see this very list box here okay i have to add a list i added the list box to the second to my second tab so let's run it to see what i'm talking about there there's the list box there we go that is the headings okay you see that guys so let's do one thing it seems to be too small let's come in here and just increase the font size a little bit so that you can see it i'm going to make it about 12 there now if i run it because i ha ask the form load to always make tab 0 the default and this is what's going to happen look at that change right here and that is it okay that is fine okay what i want to try and achieve now is when i click on this combo box and select any of these calls i would like the system to actually populate all of these text box telling us what's going on on each each of the course or each of the courses and i would also like to be able to enter some value in there and click on my result to calculate that and then populate everything right in here okay let's do that so let's come in here and take care of that for now exit out yes and let's double click on this on the select course day i'm going to start by using an if statement if cbo calls dot text if that is equals let's say bsc serious games you have to get make sure expelling everything is right the way it is okay if that is that then txt course code dot text that would be equals let's say b s c um g s that's a three four three five four okay that's fine now the next one is the faculty faculty dot text that is equals this is cool school of computing school of computer science okay there we go now let's have a look at the dean of faculty pegs and we just make up a name in there let's say it's professor for foreign there we go now let's have a go at the program manager or program leader no program leader dot text he was let's say doctor peter stone right now we also have there's a course tutor dot text and that it will be doctor rolls-royce there we go now the building txt building dot tx that is equals i'm gonna call it kelly's house yep yeah kelly's house in honor of one of my schools okay let's try this out and just select this serious games alone now oh oh no no no no we need to end if let's see what the if ended no it wasn't and if right but maybe i should just get this indented just select it all on top it's indented now okay now let's run that select the serious game boom you see that guys so that's what i want so i'm just going to copy it and repeat exactly the same thing for the others so copy and we then use else eve let's say else if else if if is computer science i'm grab all of these bsc computer science and we change that around as well then so that the course code will be changed let's see this one becomes c and one so i'm going to speed that up okay i guess you guys see that so if i run it now and if it's computer science there we go then we need to change the names we have a nation let's change one or two names in there back in here now the very first name i like to change i say this is professor tony montana and let's call this alan dr alan bry brian and here we have paul parker day okay then let's assume the house is bishop's house bishop's house that's my old house okay and send malachi so run that okay serious games chained to that okay so i'm going to speed that up now guys so just copy and paste and change it around copy paste let's change that right so i'm finished with that so have a good look at the lines of code for that very combo box once you write one you can always copy and paste the rest just change it around so good look at it that is for let's take it from the top again serious game computer science then we have animation there business information system computing computer games and finally we have computer animation play so that is that so let's run it and you see how that works computer animation computing and so on so that is fine that's working well now the next thing we want to do is with now oh you see these buttons when i click on any of these buttons i would actually like it to display the dates right in here okay now let's stop that so to achieve that all we need to do is let's select this very first one i'm going to double click on that so i'm going to say ldl date ldl date dot caption equals d1 dot there we go control t text okay so if i run it now and click on this that should display the date look at that so i'm going to repeat the same thing for this next one okay all you just need to say is ctrl t text now double click on the next one here it's exactly the same lines of code just copy paste that in there and just change the d1 to d2 there we go that's it now if you want you can always select the next one here and that's meant to be d3 and so on so let's go back double click on d3 and just paste that change that to d3 ctrl t go to the next one that's d4 paste changes to d4 there so let's run it and you guys see how that works then i can then speed up the rest d1 d2 3 4. see that it's showing the control tape all right so i'm going to just speed that up and there we go guys that is it taken care of for every single button on the calendar okay that is take it from d one d two three four five six see it's exactly the same lines of code i was just doing it's changing the the object details okay that's up to 11 12 up to 17 and take it from there 23 25 up to 29 and after 34 40 and there that is it up to 40 42 in total okay save that so let's run it and you guys see what i'm talking about so whatever you selected here automatically appears here okay so that is taken care of and that's fine so reset that right we now want to take care of these very one so whatever subject selected here enter the grades in there we should be able to click on these and that should also display the total the ranking and the date so only if we want to change it shall we click on this that's the only time we can click on this if we want if you want to change the date now let's go back to the result double click on result okay right inside the results first of all i'm going to declare the following variable i'm going to call that uh no let's make that an array this array is going to take in nine value comma and we also have that's unit nine total then we also have unit total unit total is just a variable as integer there so i'm going to start by saying if txt mode modal 1 dot text if that is equals nothing then one txt mode model one dots oh no i said oh txt mother one dot text so if it is less than no greater than 100 okay then in that case i would like it to default txt mode one two zero okay we can just copy these and paste that right here and enter zero in there okay so that's fine that's for the very first one the second one we have eight of those in total so just copy and paste we have two three four five six seven eight now changes to two three four five six seven eight and here as well that is eight seven six five four three i should be three two and here we have one two three four five six seven eight there so that is taking care of okay else if none of those happen then the next thing is we say unit already zero oh let's change that spin an arrow there come back in here my unit array zero that will be equals t x t let's grab it txt one here there uh let's say it's an integer center in india there we go and repeat the same thing for the others so grab this all right in total we have eight so there so just change the value here around and this is two three four five six seven eight now we have here one two three four five six seven all right so i've now assigned every single component to the declared array now we're going to add everything up here i'm going to grab this come right down here and that is going to store the whole value so the first one is going to be this one grab this place that's in here plus the next one close the next one and the next one and so on in total we have eight so i'm going to grab this enter a plus sign here paste close that off and just change this one to one two three four five six and seven there we go all of that is taken care of we now need to add the total straight into lbl total lbl totalscore dot text equals no not text line no dot caption dot caption equals that and that is meant to be okay done let's try that out before i use some if statement to check one or two things okay this shouldn't be allowed but for now okay that should default back let's see [Applause] all right there we go that changed to zero so that's fine and the rest is added up that's very good okay exit out and let's double click on the result okay now underneath here i'm going to use an if statement to check to check the total score in here let's come down here let's say if units total is less than an equals to 700. in that case lbl ranking lvl ranking or rank here ranking dot caption that would be equals class there we go else if let's grab this i use lc yeah grab everything [Applause] else if it is 600 then this will be second class open second dot i open that is how second class is written okay now let's go for [Applause] the next one the next one the next one is if is 500 and that will become second class lower okay second class law no i don't want it right and if it's 400 then this one will become third class okay now that state class so let's check these other ones and if it's less than 400 let's enter 300 there 300 in that case that will become certificate of higher education and if it's less than 300 less less than 300 there we go this one is greater than and equals to 300 if this one will be if it's less than and equals to if it's less let's say if it's less than 300 okay then that becomes a fail now let's end that end if there we go i guess you guys see that that is my result let's take care of the following condition if it's empty or if it's greater than 100 do this okay apart from that add up everything straight into the array assign everything into the array then here add up the array and the total score enter that in here then we use this instruction here to check who is first class second class and so on and underneath here let's enter the date lbl date dot caption and that will be equals let's say format date there so now we actually display the day the qualification was issued so let's run it and see save it first run okay and test whatever in there all right decision time there we go that class okay that's fine so that's working fine and these all they need to do is to make sure this object are selected of course you should select the subject before entering the grades all right that is that done okay supposing the end user end up entering something like character like these what should we do okay now we give up nero as you can see so let's end that so in that case what we do is let's prevent this from accepting just accepting whatever character so i'm going to double click on that [Applause] and in here let's use i'm going to change that to on key press okay we can use on key press or on exit so on key press so let's end key press if if not is number numeric actually txt model dots value that should be one dot value then txt module the text equals clay dot text we have to play that then we can also display a message box message box you don't have to but you can do that if you want only numbers are allowed all right only numbers are allowed and let's get it to set focus here txt [Music] or the one dot set focus and end if okay let's let's try this out that's for the very first text box there and i'm gonna come in here now and tell what is right there no it's not allowing any our character there that shouldn't happen [Music] all right let's use exits that is on key press i'm going to grab this copy everything in there and let's cut it off and now i'm going to change the event to exit there and exit instead of key press key press to enter the last value so on exit so let's run that on exit and enter whatever in there on exit and i click here no this that is good so nothing is left in there okay that is fine so i'm going to copy that and use that for the next text box as well so come back in here double click on number two paste and change the text content too has two here and two here as well there the next one is going to be number three okay number three oh it has to be an exit the number two is wrong that's why we have to change that to exit the events should we exit so do this now let's see number two okay i'm right that's wrong cut that off that the event should be an exit there now number four and exit [Applause] fixed chain these two four four and four here four here as well five change first paste and this becomes five five and five six change the event paste changes to six six and the last one as well now number seven the paste and all seven here and finally text box number eight change the event as well paste and this becomes eight and eight here [Applause] eight there that is it all done now run nope there's no allowed no does not allow so that's good so let's enter numbers now careful numbers numbers and numbers here there certificate of our education okay so that is it all taken care of now what would i need to do is i want to be able to click on this and add everything right in here okay guys let's exit out and start work with the add new data so i'm going to scroll right down here double click on this add new button and write in here the first thing i'm going to do now is i'm going to create a following variable i'm going to call it wk which stands for work book that is going to be declared as workbook no worksheets let's get that right as work there we go okay and the next one i'm going to just say them add new as range good now let's set this workbook equals sheets and shape them the worksheet i'm referring to is going to be my sheet number one there we go all right that's taken care of then the next one is i'm going to set the add name that would be equals work wks work sheet dot range and it's going to range from a up to 65 356. close that dot to the end and we can also have to offset that so let's say x up dot offset okay by one day that is taken care of now the next thing i want to do is you see every single component here i'm now going to add them straight onto the system i'll first of all start with the students id which is txt so let's exit out and go back in there so let's grab the name of this one i'm going to just grab student id now double click on the add new button and right underneath here i'm going to now say cd add new dot offset i want you to offset whatever value we have inside this one so the very first one is going to be inside column zero row zero dot value no that should be value not to validation dot value that would be equals txt dot text so that's my very first one so the next one is going to be sore name no first name surname and so on so copy paste paste paste so this one offset it by one to the next one two three and here this is going to be first name followed by surname and followed by address there we go and the next one let's add some more i have 40 in total so this one is going to be 4 5 6 7 and number 4 that will be c b o gender nice then it's gonna be followed by mobile yeah then i have an email there and after the email i'm going to go for let's go for cdo course okay and so on i have 40 of those in total so i'm going to speed that up and get back to you guys and right there guys those are the lines of code that we unload every single data whatever data you enter straight onto the component street onto the work worksheet itself at 0 to 21 let's take it from 21 now to 39 so in total that is 40. all right that is it done right and right underneath here add as follows okay so that is fine so the next thing now that i'm going to do is i'm going to come back in here first of all let's change one or two things here before i test this out i'm going to select this very yeah select that frame and maybe this other one as well so let's put now just this one first and i'm going to change the back color let's change that to background and this other frame in there i'm using that as just like a divider between the textbook between the buttons and repeat the same thing there we go okay so let's run it now decision time all right i'm going to enter some data in here that and the name again i'm going to stick to tony allen and enter so named an address alias number 13 gods as it goes down and it's mail global number okay and email will be tony at gmail.com all right so let's grab this voice guidance details address um let's make this up and it's going to be peter peter allen right here they and here is mobile let's just grab something here to speed up your process paste and work number there okay surname that'll be alan first name peter and who is he is the father there we go um what cause is he taking okay animation he's an overseas student it's an accommodation museum scholarship yes and that would be be a massive final year and let's select the units do that again select that and so on guys there and great there we go okay all they need to do is click on the results that's the result looking good now let's check this out click on add new there we go and let's look at our list box there that is it that is the list box that's the details of tony allen straight on the list box now exit out yeah oh let me show you guys the worksheet right here on the worksheet look at that that's the details of tony allen as well okay guys so with that i'm going to call it the end of this very beautiful tutorial i suppose you guys enjoy enjoyed and please do subscribe to my channel and you can also join to become a member of the channel you all have a nice day now and bye for now

Channel: DJ Oamen

Views: 2,793

Keywords: How to Create Student Database Management System in Excel Using VBA, Create Date Time Picker in Excel VBA, Student Database Management System in Excel Using VBA, Database Management System in Excel Using VBA

Id: HrZBwqS5oIo

Length: 108min 55sec (6535 seconds)

Published: Thu Jul 01 2021