Fully Automated Data Entry User Form in Excel - Step By Step Tutorial

hello Varun welcome back you are watching the data lapse my name is Dilip in this tutorial we will learn about creating an automated data entry form in Excel so let's move to excel windows and see the demo file first so here you can see that I have already developed and automated and very simple dynamic form with the help of some textbox control option button command button sorry come on combo box and command button and this is the list box control which is showing or whatever the data is available in our database so let's make some entry over here so see the entry which I have made let's click on Save button and here it's asking do you want to see the data let's click on yes so once you click on yes now you can notice that this there are only 70 cores available in our database and once you click on yes and that the eighth record will be added over here in I meaning database and at this particular list boxes you know one time i'm a click connected with a database it will start showing in the next record the recent record which we are going to enter so let us click on yes now I can see that this is the record in which we have entered here right so let's click on the close button and go to the database and now you can see that all of the details have been captured here right so this is very simple and dynamic form and with the help of this you can make entry and you can see the live data entry like whatever the data has been entered by user and this is the reset button with the help of region button II you can reset the form like suppose you have updated couple of fields and now you are thinking okay you do not need to or make entry we need to you need to make some other end to write then you can click on yes and include ha whatever the field has been updated by you it will be clear so let's click on closes and press ctrl W to close this particular automated form and let's start developing this form of form from scratch so to create a new file press ctrl n and go to file press click on save as and browse and you give the designated path over here so I am just going to select the same path where I have kept the no demo file let's move to excel and we give the path over here click on save and make sure you have you should select the save as type macro enabled file otherwise you will not be able to retain you know the code we whatever the code you will write right so let us give the name click on sale now you can see that this hasn't successfully saved simple and dynamic automated form in Excel if your window like if Excel has no Developer tab then you can go to file and then click on option and in Excel option click on select customize ribbon and customize ribbon in the next group just take the Developer tab and once you like check this click on OK and after that you can see the Developer tab is visible over here right so here we need to separate sheet one is for whom and one is for database so let's rename this first sheet as home and go to view and remove the gridlines like unto the gridlines and now you can see that this is the gridlines is not visible now click on insert and under illustration group and just click on shape and under shape and just select the rounded rectangle and draw a rounded rectangle over here in the middle of the screen right just select the shape and go to format shape effect preset and select this one the preset number 4 right click own shape and edit text enter launch form okay just select this form this shape then go to Home tab and click on center and then middle and the size should be 40 ok so now you can see that this particular button is ready and we will utilize this button to launch the form now we need a one mood worksheet over here for database so let us click on new sheet and give the name as database let's quickly a-type the header so we have entered the column header over here let's make the resize the column okay now let's assign the color back color font color is white font side little bit bigger like 12 select this range maybe even 221 and apply the border all bottle now go to view tab and hunting gridlines okay so our the database or sheet and the home shape both are ready let's quickly undeveloped design the form and then start the coding so let's go to developer tab and under code group just click on Visual Basic so once you click on Visual Basic you will be jumped to Microsoft Visual Basic for application here we need to insert in the form so let's click on insert menu then usual form so here you can see that one user firm has been started reflecting over here let's resize the user form so the height should be 325 so change the height till 25 and the weight should be 578 so here we would be 578 okay just move on top of the properties and keep the name as f RM and the caption should be caption is basically this one so we are going to change this so let's give the cop caption as automated okay so now you can see that the form and width and height and name and caps on each hadn't stood for this particular form so let's add to frame one is for the filled textbox control and others and one is full list box control to show the data so let's this is the form in tool box you can see that this is the frame basically frame and just click on frame and start growing the frame over here okay now change the frame caption as and details okay and the border color should be water style select single this one and the border color so let me select the blue one okay this is blue one now again we need to enter one more frame so let's click on frame and just draw this frame from here to hit okay and just give the caption as database right and change the border style should be single this one the second one and maybe the border color should be select the any of the border color like maybe this one okay any of the color which you want so we have created two frame one is for enter details like this is a group and one is for database and that is for list control so let's quickly start adding the label first of level so so label we need six different labels so this is first level make it copy this best it over here and okay let's change the name caption off-label so this is for employee ID this is for employee name and this is for gender okay now we need to add text box control for employee ID employee name so let's click on text box and draw this make it a little bit more in this one yeah just copy this text box move it to here paste it over here and move it to the aligned like this one right maybe okay now we need to insert to option button one is for male and second we need for female so let's click on option button and then click on form and here just make it a little bit smaller and again copy this and paste it and now adjust this here okay so we have two options bottom one is for male one is for female now we need to insert the label and control box basically for Department City and you know country so let's copy this control control fee and move it over here okay now give the level as Department and here in this city and this is will for country and remove the this one the option button because we need to apply the text box control okay just text box paste over here and just keep it over here okay now we need to command button so let's click on command button over here again again one more button for reset just align this and this language let it make it little bit the frame should be smaller and this frame should be little bit more bigger right okay so you can see that we have successfully created all the labels control you know control box basically the text box option button and Department command button over let's quickly change the name and the caption for each and every one pole here so let's say this is for employee ID so let's leave that txt I this will employ name so this txt name this is for mail so this is for males and opt and may LA and the OP caption is male right this is for female so opt female and caption should be female this is so basically here we are not going to utilize text box for Department so let's remove this Department text box and we need to utilize the combo box so left click on combo box and just draw this combo box over here okay so now you can see that we have successfully created the combo box let's see in the name as CMB li BR T Department right and C T should be or T X T C T and this is for country so txt country and this is for come on for save so CMD save and accelerated you should be you know s and the back color should be maybe this one and the caption should be save the next come on button is for reset so let's keep the name as the see MD are easily reset and the back color should be red and capsule should be re-entering SEC and the accelerator is our okay so we have successfully assigned the name for each and every control and caption for that let's give the tab order for each and every field so let's select this text box go to down and here you can see that tab index is 3 so let's change from 3 to 1 and then assign this should be 2 this should be 3 & 4 5 6 7 and this is for eat this is for 9 now we have successfully given the tab order in everything's now we need to up to add 1 list box control over here in database section basically in the frame so let's click on this one list box and quickly create the list box over here okay now give the name as LSD database little is right and here we need to assign the tab order for this database as well so let's select the list and the tab order so the last tab order was you know I think 9 then you can see that this is for 9 so let's give the time index as 10 so you can see that we have successfully and design the form where we have 6 different types of fields like employee ID employee name gender Department city country and we have two different or no button that one is for safe one is for reset there is a list box for the damage to show the data basically so let's quickly start writing the code now we need to insert one module so let's click on insert menu and click on module so insert module we need to write the code so let's start the writing to writing code so let's start the writing code for he says so the name should be reset sir we should start bracket and close back and give some room over here and start writing so first of all we need to declare one variable so the team I hello as long and the idea is basically to contain the last column and so last row value so I row is equal to and we need to use excel function that is counter function so so in square bracket we will give the count a and the formula that is database worksheet and then we have E and E right and then bracket close so let me give the commentary over here so identifying the last row so basically this particular line of a segment is to identify the last row of the database so suppose we have two entry so it will return to like the row will be three one is 402 is 400 K now let's start the writing code so read frm for frm form is 4 we are going we are referring this form and enter and wait so it's txt ID dot value is equal to blank dot txt name no other value is equal to blank dot opt male this one the Auto value is equal to false opt female the auto value is equal to false okay basically we are you know initializing the all those controls like we are resetting the controls so that we can use this code or while initializing the window as well as so whenever user will click on reset button we will use this code right so Neil is equal to false and female now we need to clear the combo box through the dot c mb department dot figure so whatever the the items which will be available in combo box that will be clear first of all and then we will start adding CMB we part in north add item and the first department will be a char in again CMB department dot add item and operation cm CMB dot add item and then training c.m CMD department or add item quality so we we have added four different types of departments if you want to add more departments just you need to replicate the same line of code like again and again and change them on the value of whatever the values is available in double quote right so as of now we are going to consider only four department okay now we need to clear the txt city dot value is equal to blank and then the extra stacked country dot value is equal to blank okay so and like we have already received all this value the value of level the top section of this form now we need to initialize the list box which is available in the bottom frame basically so first of all we need to set the column count so as we have six different of values interval user and one is CL number that is automatically generated and two different values like submitted submitted by and submitted one so all those fields are nine fields basically the nine columns in our database so let's think the LSD and database dot column count is equal to 9 so we have 9 different new problems so that's why for this list box we are giving column kind count as 9 and again don't LSD on database dot our column like in our database worksheet we have a header section so let's click on let's like the column headers is equal to true ok now we need to give the column widths of this particular list box if you want to ignore this particular line of course then you can ignore but this is for learning purpose I am providing over here providing here okay so let's LSD database taught column of widths okay and then equal to and in double quote we need to give the width of each and every column so let's start with 30 30 is for serial numbers 60s for employee ID and 75 for employee name then again 40 60 45 50 pipe and this is submitted on 73 and submitted by 70 so you can count here we have provided the width of all those nine columns 1 2 3 4 5 6 7 8 9 right now we need to give the reference of a database like we need to give the reference or assign the data row source basically to list box beause we have only one ro that is you know the column header and there is no records so in that case we need to assign some different source and if we have more multiple rows like database data then we need to assign some different row source so here we are going to marinate if I row is equal to one if I fo is greater than one okay so in this case we need to assign a LST or database torque row source and double quote just give them data database is worksheet right so database and then exclamation sign and then the range we start from 8 to 2 I and here we need to give the row number L 0 else not less elastic little database torque row source is equal to beta database a two column I 2 and if clear all the space extra space oh yeah the reset code is done here let's go to debug and compile the beauty budget now we need to add the code for a submit I mean save button so let's click down here and give the name as sub submit right and open bracket pull back give this some space over here okay let's declare a variable for worksheet to be message as walk shake and one variable to hold the last row number basically the blank row name I row as long okay and let's assign the worksheet name to a such variable subset as such as such is equal to this move dog and the name is database right and I row should be and then UV will utilize the count a function over here so let's start I know square bracket and under like in a square bracket we need to write the code like excel formula County and then database object a colony so this will return the last row where the data is available right the count of that no we need to add one to identify the next blank row so we will add plus one over here now we have like worksheet name as well as the next blank row so let's start writing the code with with as such an end so dot cells so cell this particular function or cells as you know two parameter one is row index and column index so we have already I row so let's I you know and the column index is first so first of all like you we have to enter serial number let's start writing equal to then I row minus 1 because see I row will be the serial number suppose we are on third number room it means we have only two like we have only a one entry one is for header first row one is for record that is second row and we are in third row it means this is the second record text I am deducting one from idle right let's copy this line pool paste over here and change the reference like from 1 to 2 give this space and now this so frm form dot txt text ID and dorking value okay let's copy this code this is for idea this is for name so let's column number will be 3 txt name and again copy this line of code and the column name column number should be 4 here 4 is basically for gender so we will utilize double iya function and frm form dot o PT female dot value is equal to true then the value will be female otherwise it would be made okay so let's copy this whole line of code and here this is column number five okay and here we need to give the combo box name like CMD DET är the department dot value okay let's copy this five six seven eight so let's change the six seven and eight and copy one more line of code this and this is four nine column number nine okay so we have department value four five six is four txt city and this is for T X T country okay now here this is for submitted by so for submitted by V will implies application dot username so it will give the username of other application who is running this application here we need to again give you the excel function in the square bracket so the function will be text and then Open bracket and now we need to give the time now now function then common and under enable coma let's start DD mmm why-why-why hh:mm:ss okay so this is done and it is like remove the extra space over here now we have successfully written down the code for submit now let's add one more suppose Earth sub procedure for to launch the form so so under school form right and here frm formed or show okay so we have written down three different codes in our procedures opposite basically in module one is a reset one is submit and one is show the phone let's move to move to our form window and let's start writing the code for some save here we need to declare one more variable so dim MSD value value as VV message box wizard MSD value is equal to the message box do you want to save in the data and Vivi here's no plus V information and here the heading should be confirmation like title confirmation right close the bracket if MSG value is equal to v v no then GTA else like if that user will select yes then this particular line of code will not run basically and it will start running from here so we need to call call submit and then call reset okay so this particular of supposition will submit the data and this supposedly will reset the form and i refresh the form basically so let's copy this line of code all these line of code like here let's copy all the code of level in the sub CMD save go to form and here click on reset and under this and just paste the code I'll remove the sub called submit here do you want to save so we need to change the reset deform so here if we have successfully written down the code for that he said and see so let's debug compile VV project now you can see that there is no error now we need to write the code for initialize the form basically and slide the form so let's copy this call reset and I can go to form double click on form right and here like select the initialize this one i remove the double click on the click event here and just peace the coldest it now go to debug and compile now click on a jump to with Microsoft Excel let's assign the code assign macro and select the show form click on OK so we have successfully assigned the macro on this particular shape that is button let's click on launch form and here you can see that this particular database like list box has been successfully added you know including to with the database worksheet and we have all those fields over here right so let's make some entry and see so as of now we do not have any records so any record available here right so let's enter 2 5 5 and maybe my name Dilip Kumar in gender male and the Department is you know quality and city gzn and the country and yeah let's click on save yeah it's asking do you want to see the data yes I think there is some error right okay so we forgot to close the bracket here just puts bracket over here and just click on here so now you can see that in this particular line of code I started working let's add one more pipe it to and yeah let's click on save yes now it's working that's one two let's click on save so now you can see that it's working properly and whatever the data is available in database it has already started reflecting here and the serial number one two three you showing correctly so let's close this form go to database worksheet and now you can see that whatever the data we have transferred with the help of form all the data are available here right and the same data and the same data all those three cores are available in this particular form so you can so there is no need to go to database and see you are whether the data has been captured and whatever the data has been submitted by user right so either the while submitting the data you can see whatever the data you have submitted so you can take for this on particular table to to like check whether whatever the data you have entered here has been transferred or not so as of now we have only save and reset if you think that you need more like some additional features in this particular form like editing and deleting the records so just leave a comment in comment section I will definitely add those features and upload a next video so hope this video will be useful for you thanks for watching please like share subscribe and comment

Channel: TheDataLabs

Views: 1,775,129

Rating: 4.85532 out of 5

Keywords: Automated forms in excel on UserForm, How to make an automated form in excel with listbox control, excel data entry form template free download, how to create an excel data entry form with a userform, excel data entry form free software for windows, employee data entry in excel, how to create a data entry form in excel 2016, excel data entry form with drop down and list box, Step by Step Tutorial to create an automated form in Excel, Step by step guide to create a form in Excel

Id: P53T6oxgUVA

Length: 35min 41sec (2141 seconds)

Published: Sat Nov 16 2019