How To Create An Excel Data Entry Form With A UserForm - Full Tutorial

hi and welcome to Excel data entry form so let me show you guys how this works if I click on add or add record this is what you guys receive all of this information that I've just added in here she will display it right underneath here right there you see that verse okay and we can also reset your system or delete whatever we want let's say we want to delete let's do for something to delete assuming I want to delete this now okay I can just click on delete a message that is gone see that's let's say I want to delete this okay there and that is done so I can reset to reset all I need to do is click on reset and enter new data in there I say enter for the reference and the name of this let's say the name is repeat it's an address artists can see right so that is the address they are nipples cordless EDD and proof ID member time and feed so just need to do is to add a very rare card if you look in here now before God take a good look at this quake and there we go come right down dead as fits out eatos day okay so we also have exit if I click on exit and exit or do whatever like okay the other chain about this excel data entry form is all of these information that we entry on this form itself can be directly stored on to the Excel workbook itself so if I'll click on this now right so see that's coming here see that information okay that's the 14 Johnny so let's assume I click on it again look at that you see that is officially stored on to the Excel spreadsheet itself so let's come back to the form itself and we can delete some of those exits record as well so just select I'm just delete okay so what I'm gonna do right now is I'm going to take you guys straight into Excel development environment I will put one of these together so let's do that now guys I am welcome to Excel data entry form I'm going to start by clicking on the developer tool right here but if you don't have the developer tool or if you are new to developing the system using Excel what I would advise you to do is to get your developer to you go to file then you select options once you run on options you need to click on the customize ribbon right here and once that is enabled you see the developer tool you must make sure that is checked it must be checked once is checked you then click on OK but if you can't find the developer to all you need to do is maybe go to popular commands and there you should be able to see a developer tool here just click and add to this customize ribbon area and then click on OK today and you should be able to see your developer menu up there and click on developer menu now you want to select visual basic or you can always click on at f11 as you can see here called earth 11 so click on visual basic once you click on visual basic you need to select inset and use a form click on user form now your form will appear or now that your form appears the next thing you want to do is depend on the size that you want your form to be my case I want the height of my form to be approximately 700 and the width I'll make that maybe one thousand and Teddy and just click on enter yeah that is it done now the next thing that you will need is as follows I need some frames so click on your form and the toolbox we have PA the pro toolbox is not there that is your toolbox there click on your toolbox I will now select frame click on the frame just drag it that much if you want to be specific you can always take off your design know the length of your frame and so on so suppose in my frame the top frame let's speed up our product Li 54 and the width I'm gonna make that 996 right there then so that is that taking care of the next one I would like to take care of would be another frame I might as well just click hold my control and copy this there so these very one that I've just copied I'm gonna make this size the width let's go for the hike let's change the height to 234 okay now let's come down here I'm gonna copy this again because I need another one copy drop they're okay in the case of this one let's change the I'm gonna change the height or down to approximately 180 so I have three frames there in my system yeah that's it all done now those three components of my system will change our color to select them individually the first two the first two will be of the same column so you can go to back column right there and select that will go to pallet and I think I'm gonna set you for this background color now this is very one on top change back color as well palette and maybe I will set you for something green just like that now for the girl one right on top I'm now going to add label click test label there and this very label I'm going to enter in there Excel data entry form okay I have Excel data entry form in there but then do you need to customize the size so let's go to form all fonts click on font run in there make that bold maybe let's go fool okay 48 that might be too much I'm going to see if I can reduce it to 43 right still need to like open it up so that you guys can see it okay that looks fine all right now the next thing I want to do is to change the font color clothing to where we have fonts or four color here click on that and let's change color to that that is sorted now I need more levels here and these are levels let's change the size of the available to don't 16 our copy that across and I also need a text box that is text box there click paste the text box there let's change the font of data to both 16 as well right and the size of this very text box is coming to height exchange height to 30 and the width I'm going to change out to 330 as well right so I can now copy it across so grab both and let's position them properly okay just like that so I will now hold on to the control and just copy hold on to the control copy again and one more time yeah okay this is going to be for my address the Lord last Roma let me appeal of this rockin just copied and pasted won't let si copy the right thing yeah copy and drag that is it now this very one dragged it right down the size of the a better one maybe I should leave it like that for now and this one I'm going to need about six of these years I need two more so let's copy one and just drag no hold on to the control and do that okay let's move this from fest so I will have enough room let's do the same thing here and the center and select it all click and drag that is it all is ready down so all I just need to do now is to starts changing the name so the label name is coming here let's look for the caption for the label in de the caption for the label will be reference number they does they'll label one it's known as reference number and the other one here is going to be change the caption of this ver want to press Sony and this this change is to as first name and this won't be sorry this should be a dress just like that and I'm going to just just fit it all up and get back to you guys okay guys this is how my system looks like but the background let me click on the background chain the call of that background would make that black so that it will look more pronounced as you can see now that is it okay if I click wrong this is what you guys you see you see that to sell beautiful details but nothing is happening I've also added buttons nothing is happening so the whole idea is whatever data enter any I would like you to be displayed on on my workbook here or worksheet whatever is called right so that is done and each of these components as you can see I've given them all email all right they all have a name but there's one important thing left for us to do let's go to file and save this face save okay I'm gonna save it right inside my data entry form there so we give it a name let's just say data on the score entry in sport form okay but instead of using this excel save type I'm gonna change to Excel macro and able workbook to select that they will go until unsaved yeah does that done so the next thing we want to do now is write down let me start with exit don't click on the exit button and I'm gonna declare as follows I say D I exit as three big message box will be message box result now I'm gonna press ENTER then less severe blue I exit equals message box okay and now we'll enter as follows so all I'm just gonna say is confirmed if you want to exit as it does the question I'm asking the end user now , svb question and plus VB yes no those are the method and when you use comma and I'm going to call this so let's give it a name data entry from clues that get rid of this day unless just close that as well now if the selection made by end users if my exit equals baby yes then we need to say is unload me and end your if statement and if that is they done okay so if I come in here now let's try this out so what's happening is you can always write just unload me but supposing the end user end up making a mistake this is just given them an option to check tour just to confirm if they read true you want to exit the system so if I click on exit confirm if you want to exit or no I made a mistake I'm fine if you want to exit yes see how it works so the next thing I'd like to do is let me take care of these okay this order 3 so let's exit out of the system now those should take care of reset phone double click on reset in the case of research instead of using instead of saying text box name or let's just group all of the text box together so this one is call T XT reference so if I double click and paste that one in the adult text you can always use this Specter but as it's kind of like that would be too long for the component that I have indelicate works then what I don't want to do that I'm gonna exit out so what I want to do is to use a for loop okay get rid of that because they all have txt as they are conventional name so I'm gonna say then I control okay as controls as control then so that's my variable for every single text box controls there so now use a for loop let's say for each I control in me dot control needed controls if I control dot name like txt when I put a star there if I control that name like txt then I'd like you to do this just clear anything we have in there so we can say I control equals B be non string okay I'm just press ENTER and they'll be all I'll say next okay that's it I'm going to look at these lines of codes okay if I run it now as ain't our Avaya click on reset there we go all gone go back to exit and exit okay the next thing for us to do is let's take care of this add record before I take care detail it's a double click on nuts in the case of add records I'm going to declare the following dim worksheet W K S stand for worksheet pass work there's no gonna do what book I'm gonna choose worksheets right there then then I say game add I'll say are deemed as range then the next thing is set worksheet equals 1 there so the variable W KS is now class ass 1 and right here let's move this down that's my wonder yeah I believe you guys can see sheet 1 here okay so that's now variable W K s alright let's punch email now the next thing I like to do is to set admin [Music] equals sheets a workbook touch range okay open up the range I believe a mighty one was the range as far as I know it was meant to be approximately ted-ed sixty-five thousand three hundred and fifty-six it might be more than that now I was a dot and I will be the end of the workbook itself up to the top okay dot less offset it open the bracket 1 comma 0 now what I'm trying to achieve is whatever data enter inside these text box and I had click on either I want them to be added straight on to my workbook here or worksheet that's what I'm trying to achieve now go back to my code now let's say our name let's add names dot offset for each components so the first one is going to occupy column 0 row 0 dot value and was the value that goes inside that would be equals to CX T reference dot Tex so that's the very first one and that div txt reference does text that is it writing the next one is going to be name first name so name address and so on I'm just gonna copy that across and change the names around copy I have about 10 or 12 something like that let's see I'm gonna do our full thing so I have AIDS Day 92 more okay so I will then change this numbers so you got to be 1/2 three and so on this and now let's change the names so this one will become first name this becomes sore name I will have address here them have post sport followed by Teufel then it's registered and this will be proof of ID remember time mm no fees then at is it all done now the other thing I like to achieve is I would like every single data that is entered in here I like it displayed on these very list box this is a list box then you can see it responds but before our continuing what I want to do is try this out and see if it's going to display on my list box sorry on my walk book so first of all let's enter the following headiness the next one is going to be so FS name this was good okay so I'll finish with a title so I'm going to go back to my visual basic or video so let's come in here now let's run it and see what those lines of code to do and let's say we came to that and the name is Paul open and the address is say 1515 then okay and postcode just make up something dates as in today's date as today's date I'm proof of ID I say pilot pilot Alliance has been the talk is going to be on one page one four five yeah okay so the next thing is let me move this aside you see all of the data that I've just entered someone move this aside and let's click on this odd record you see that dance level see it is nice okay but guess what I also want those information to appear on this list box I'm going to exit well first of all let's see try out the reset again yeah that's fine so let's exit and take care of the tallit and this as well so I'm going to take care of this first then before we take care of the delete all right let's go back into add record double click on a bad record right underneath here let's call our list box tell st display dot columns I will be column pounds this very one okay it was ten because I have 10 data in total and say display LST display at media dot root sauce equals I'm going to ask it to come from let's say B 1 up 2 J 3 no J's 6065 thousand-plus is that day right and that's all there is to it okay let's run that and see what's wrong and toss some data in here let's say that the name this time around is Johnny Johnny good luck a dress of good men love the three Kingsland who's right and possible whatever telephone number dates of good men yes there's a date and thus a good man is a student okay still hiding and here I know I'm VP is that so let's add good minds teachers on to our spreadsheet here let's see that and make sure the data also a PhD a click yeah you see that guys that's maintained all of the information are displayed here if I click on Jenny Goodman again day you see an outhouse Jenny Goodman twice so I should be able to just select and delete so that's what I need to achieve now okay so close that and come right down here double click on delete and right there inside the leads right here inside delete I will now enter the following first of all declare a variable call that I as integer and gives the follow for I equals 0 to range [Music] and in this case my range start from a as anti quote a 265 thousand plus lose that dot till the end X up who's that Dutch rule -1 now say if display dot select or selected I then I want the following to happen so bro i dot slate and selection dot delete ND and next i right okay does the glyphs kept this indented so that we can understand what's going on here integers ended oh this one should be rose it looks a bit rough right that's much better okay have a good look at those lines of codes then we'll try it out that is for the delete and this very one is for exit as for exits and this one as well okay so let's run it now right out okay and thousand meters in here and the name Miriam we are a bully we Chris let's see Johnny hi alright we'll scoop and tell remember dates 15 then pilots nonsense then the time she is is monthly payment Amash is duck Leslie that's about 45 no that's it 25 right all they need to do is as pom in here so that we can see everything they move these are you see the details of miriam holy we'll look at that that is a that is fine so now to try out the delete button selects where it's like johnny and just split there we go you see one Johnny's left now okay then now we can just delete all this content of having them polish everywhere you see that so you see how to delete button works and it's also the leeching up here inside in October so guys what I'm going to now do is to Kali the end of this tutorial I suppose you guys in joints we all have now subscribe and like thanks for watching and bye for now

Channel: DJ Oamen

Views: 721,713

Keywords: How To Create An Excel Data Entry Form With A UserForm, How To Create An Excel Data Entry Form, Create An Excel Data Entry Form, Excel Data Entry Form

Id: w-yQf2Kiu6Y

Length: 36min 11sec (2171 seconds)

Published: Sat Dec 15 2018