How to Create a Data Entry Form in Excel With Add, Modify, Delete and Reset (Step-by-step Guide)

hello and welcome back here wasn't the little apps my name it lip in this tutorial we are going to learn about creating and employ data entry form with add modify delete and reset features so without wasting our time let's move to excel window and develop this automatic tracker quickly this tutorial will be little bit longer so be with me till and to learn this automatic tracker right I have divided this tutorial into three sections first is designing user interface second one is writing a visual basic application code and the third one is testing the form so let's start the designing form so before designing the form let's as you can see that there is no Developer tab and I deliberately make it hidden so that's enable this Developer tab let's click on file menu and then option and Excel option window just click on customize ribbon and in customize even the right side section you just take the Developer tab like developer option here and click on OK now you can see that as the rigid Developer tab is available over here now let's save this file so let's click on file then save as and then just give the desire like the location which you want so I am just giving this location okay just give location over here and and do not forget to select the actual macro enabled workbook the extension is dot XLS M give the name as employee data entry form enjoy now click on save so here you can see that if we have already successfully saved this employee data entry form in Excel with the extension of dot XLS M right do not forget to select this particular H nation otherwise you will not be able to retain the macro which will you will write right so let's design the user interface so first of all let's double click on this sheet one and just keep the name as form now click on add new worksheet and double click on it and give the name as a database ok let's move to form and so here we will utilize the form sheet for creating user interface like form and the data built database work sheet basically to utilize the this particular sheet as a data base like so from form user will enter some information and that will get transferred to database and database will keep on maintaining the repository of each and every transaction whatever the transaction will perform by user may be our user will add maybe modify or delete or no really externally that user will take that action ok so let's let's create the form first and then we will move to database so to create the form first we need to select the particular range like we are not utilizing or advanced feature of you know form ActiveX control we will utilize the only couple of ActiveX control features and this will be Excel features basically right so let's select on D 3 and 2 and D 3 M 3 and me just like deep 3 2 m4 and click on merge and center right click on the color and select the colorize gold accent and give the name as employee data entry form ok increase the size to 26 and the color should be or it should be remain blank like black and let's increase the side column size here little bit more yeah so let's select this DT 2 D 2 to M 22 D 3 to m23 basically okay and select the border again stick the border right and again make the size hide the column column a and column B and again to hide the column oh and so basically just select the column column and press control shift and right arrow to select an entire column and right click on that selected columns and click on hide okay same goes for the below row so just go down leave one blank row here and select all to select this row number 25 and press control shift and down key now you can see that all the rows have been selected now right-click on the selected row and click on hide okay so you can see that all those rows and columns on the unwanted rows and columns are hidden over here so let's delete the whole number one as we are with you okay and [Music] reduce the size of column see then column rule number one same goes for column n and for 23 as well okay hide the bottom row nine hundred so okay now fill the color the border color should be in blue now click this this particular area where we need to add label and textbox basically just go to the fill color and give the color adds light green so this is no formatting is done what we need to do we need to keep the label as well as the entry you know text field basically so select the e6 six to g6 basically click on merge and fill the colors Y and give the name as employee ID ID make it left aligned copy this let this one blank row and just over here give the name as label as basically my name again copy this and leave the one row blank like this one and so we need to leave a alternate blank row and then we need to give the bar keep the label so the third option will be gender okay seems like this and the department okay and then copy this test over here and then Salvi I can compute this and now we need to the last value that is and this so C including this form I am utilizing only two three four five six so six periods so this is for learning pop purpose you can add or more know text field and label as per your requirement so let's focus on only only these fields basically and now we need to give the border here so just like this and press control one maybe you can select all those fields basically this one so let's click on this again this one this one while pressing holding control key and clicking left click on each and every cell now press control one for format cells and go to border select this one this and click the bottom border okay so now you can see that there is a border apply it to the bottom side of each and every label here now let's reduce the size of each column okay and now we have to create the text field where user can make the entry okay so that should be we need to merge I 6 2 l6 basically so just click over here and click on merge and center okay fill color should be white and now press ctrl one while selecting this particular merged area first controller 1 and select this border click over here bottom and right water so you can see that this particular cell formatting has been done so what we need to do we need to apply the left formatting as well here so just click on a left alignment basically now we have applied border alignment and back color and we have already merged the sails so let's copy this and make the duplicate entry for each and every field respectable license for employee ID this is for employee and name this will be for you a gender this will be for Department this is for Salvi and this is for you address but enter should be a little bit no extra space basically so we need to merge a couple of rows as well so let's select all these roles like from a 16 17 18 and 19 so I 16 to 19 and click on merge and center right and again select this filled the address and press control 1 and apply the bottom border and the right side border that's it so we have created the text fill basically here so this is done by now we need to create a list drop-down list for gender and Department so to create a list just select this gender and go to data and data validation so alternatively you can press alt d ll so once you press LDL or click on data validation on this data valuation window will appear so in allow section and how often do we need to select list and the source we just need to give on the name so first one should be a female and then second one will should be ok click on OK now you can see that there are two options available one is female and Sigmund is male in the Dept section we have to provide a several departments like you know HR operation training and quality so we will perform the same activity which we will done for gender so again select the department go to data tab and click on data validation alt you can press alt d ll to launch the nikah validation window so let's press alt d ll okay so now you can see that data validation window is available here on our screen so in allow section allow field basically let's select a select the list and here in source we need to provide the several option like only four options so first of all I will I'm going to enter HR then operation then training and then quality so only four fields I am going to know like folder Department I am have entered here you can add more departments according to your requirements okay now click on OK so here you can select that this is a drop-down list of level when you select this particular audience so click on drop down now you can see that HR operation training and quality all these four options like lists are available in this particular drop down now we have successfully created the like the labels and the textbox where user will make some entry now we need to add four different types of button control over here so that user can add add-on like to see if the super entire entry and modify the whatever they made by user again delete the existing entries or records and reset the form so we need four different types of button over here command button basically so to insert the command button loads click on Developer tab and Developer tab you can find it control groups here okay so in control groups click on insert and insert you will find form controls and ActiveX controls so see if we do not need to utilize the form controls because this is these are the basic controls which we can apply of course but we need to focus on some new active advanced control so we are going to utilize this one the command button ActiveX control the very first one just click on that and click here okay now you can see that this is available press control-c and make a duplicate copy of these particular buttons and gain control V I have copied the the one control button and I'm testing it again and again okay so let's make it alignment and make the alignment here let's select all those buttons like while holding ctrl you control key and press left click on each and every button so now we have selected all those you know command button and then go to format and in alignment click on top lineman and distribute horizontally okay so it has it equally distributed basically there is a piece among each and every button like you know is equal now we have to provide the name like make the changes even buttons and formattings and we have to provide some hotkey over here so let's start with to save button so let's click on you know I'm go to develop a selector command button go to Developer tab here and this is a properties just click on properties under control groups you will find the properties window like to put this just click on that and once you click on that then properties window will appear so there are a lot of options available here you can make it easy size change the color and all the things so let's first of all let's select the Select scene the name of this common button so CMD + sa ve this is CMD shape so CMD means command button and save is this is for you know the purpose of saving the data like transferring the data from this form to database right and this is for basically through coding proper so that whenever you will do some coding you can easily you know identify ok the purpose of this particular command button is this write an accelerator I know the accelerator here is a hotkey basically source if you will press Enter then s will we get highlighted like they will underline on that yes yes and if you will press Alt + s then the active the the the function which has been assigned or the function which we will assign on this particular button that will be get triggered right it will work like no hotkey basically okay and here bad colors will select the battlers battlers just select the green and come on the caption should be just keep the caption s C okay now you can see that s it has been underlined it means you can utilize the s as a hot key while pressing alt and then s you can perform the same activity the activity which we will perform while clicking on this Save button right now quickly let's modify this second button as well so the second button will before modify so let's see in the name as CMD that's a prefix to read I modify so let's stretch in the back cover of this particular command button that is for modify so select the back color and then click on and then click on palette color it should be this one now keep the accelerator as you know m and change the caption to modify modify right now select this one the third third button that is for delete so just give the name as CMD we delete okay and accelerator should be D and back color should be red one this one okay and change the caption that is delete right now we need to change the color and formatting for these the last those like this come on button the last button and keep the name as reset so c md r es ET CMD reset and keep the hotkey like accelerator keys are and then change the caption to our ESC TV set and then color backward should be let's select this one the pink one maybe a little bit uh let's select this one the yellow one okay so now you can see that we have successfully created the form this is the user interface basically we will applies this form to mix some entries and all these buttons to you know transfer the data from this form to database and then again facing the data basis on some you know search field and modify them and again saving with the help of this and deleting as well as and also that he said the form here we will utilize the l1 and m1 this particular cell store the roll number and and the serial number of a particular particular decors particular record here you are suppose you are going to modify and if you click on modify then it will ask you to enter the serial number and once you enter the serial number it will save the serial number in m1 cell and the respective row number in l1 so that after modification once you click on save and it will get stored on that particular record it means that previous record will get replaced by the new records right so these are the two cell which we will appliance later on while doing coding so let's move to database worksheet and create the header of the database so let's add the require column header here that first one will be a serial number then second one employee ID so let's like this okay employee named employee name and fourth is gender fifth one is Department sixth one is salary and the seventh one is address so you have notice that I have added one extra field that is that will get automatically generated and all these columns are the the field available in that particular form apart from all these you know fields we have to add two additional field one is for submitted by so that we can capture the username who is feeding the information right and capture Alex submitted on on what which date or which time user has submitted so let's give the new column header is submitted by submitted on ok so these are the columns like if we have total nine columns which we are going to utilize three are auto-generated that is serial number submitted by and submitted on and the rest of six are coming from this form right so let's make the column size little bit larger so that you can easily see the header section over here okay just get some room and just go to Home tab you know and the center alignment now like this fill color should be maybe this one dark the blue cooler and font color should be white click on ball maybe you can increase the size a little bit more so you can see that we have successfully created the column header to required column header let's go to view tab and remove the gridlines okay now select the couple of rows over here and make it tunnel and keep the border color so to do that you know either you can utilize with the help of this borrow section or you can press control one so here and form itself click on border and then just select this one first and leave the color as green select this so now you can see that we have a possibly Cuban in the border color of each and every cell here and now our design part of this form is ready we have treated the form section and the database section over here now the next part is visual basic code so let's start doing the visual basic code so to do that we have to jump over Visual Basic application window so let's click on developer tab and in developer tab you can see the code group here so in core group just click on visual basic this particular button alternatively you can press Alt + f11 key to jump from excel to visual basic application so let's utilize this feature like the button click on this now we need to do all the coatings here so let's before starting and the coding let me tell you that in coding section we are going to write several codes so basically and we will write some functions and some sub procedure and then we will assign all those function and sub procedure on on so basically on event like button click so basically the up we will create no five two different types of fun functions or sub procedure to handle the handled all those tasks first one is validate and the second one is the reset value it means validating the entire entry which will be done by user reset reset will work like you know once we will utilize that particular procedure then it will reset the data we should the data available in that particular form and save same will visa procedure which we will utilize to transfer the data from form to database and modify so modify will be also a procedure we will utilize that for sub procedure to modify existing course write fetching the existing records and then we will make some modifications and then we will click on save right the last is our delete basically so delete is the procedure so that once you will click on delete it will ask the record number and you will provide the serial number and it will delete the records so these basically there are five different functions and procedure we are going to utilize here so B so let's start window coding so to do that we have to insert the module so click on insert and click on module okay so module is nothing but a say code container where you can write in the code and you can easily access all those code code of level here on like any of the events like which we are performing or we can call all those procedures and function in sheet1 sheet2 and this workbook okay so first of all let's start the coding for validate so for validation we will utilize the function basically and not the sub procedure because function returns some value okay and we need boolean value either true or false if entire data will be valid according to our validation then it will return true otherwise it will return false and on the basis of true and false or we will know and give the masses to user data okay you have made some entries that is that is incorrect and then user will be able to know that okay user has done some mistake and he will or he or she will make some modification and then click on Save button it will validate the data with the help of validate function and if everything will be successful like as per our validation then it will transfer the data from form to database so let's start writing the code for function so functional didn't start with function okay and the name should be I am just giving the name as we validate like it start and like it closed and the data types of this validate function should be a boolean because I already told you that it will return either true or false right press enter and just create some room over here so that you can easily write the code and make some alignment and that will be visible okay so here we need to declare a variable that is called what should shoot variable so name frm as the worksheet okay so this is this particular variable is to refer the form on this particular worksheet sheet one right and sit the form as set frm is equal to this now as I already told you that validity is also a variable so initially we need we are we are just assuming that okay valid it is true it means whatever the data has been interval user is true so let's initiate this variable with true okay and then after like on the validation on the criteria we will make it false okay so let's start with block with frm frm is you know worksheet name that is form it means we are referring the form and make and width so we need to write the code in with block within the wing with block so let's move to excel or worksheet basically excel here and here you can see that the range is i6i 8 I 10 I 12 I 14 and I sixteen so we need to value to own those up entries like all those fields available here okay so we will keep the reference and we will apply some validation and if that validation will okay then we will pass okay we will keep the value of validate the function has a true otherwise we will make it fall so let's move to know which other basic window again and start the coding so rains I told you that the employee ID range is i6 so I six dot make it first of all so basically we need to assign the color like if we need to assign the color as you know white basically it means we do not know we need to remove the colors of level in that particular cells and that's why we are here we are going to utilize the with this particular section so we mean i6 dot interior doc color is equal to XL none so suppose you have value to data and it has been highlighted with red and later on you made some correct entries and once then again you are clicking on no say button then again equal validate it means first of all we need to remove the previous highlighted color okay and that's why we are utilizing this this particular section so let's copy this and quickly make you know six times five and six so we have six let's give the value as eight because we have left one alternate row over there ten twelve fourteen and the last that is address is 16 or 16 okay now we need to evaluate each and every entry it means we have to evaluate the employee ID employee name and each and every field over here right so let's start validating the first the first field that is employee ID so it put comment over here validating employee ID okay so let's start the coding infrastructure if dream and frm dot means i6i sixth or tooth value is equal to blank then then message box employ ID is blank so I am I'm applying simple validation you can add more validation on this like checking with the employee ID is number or any alphanumeric characters or even limitation like size or anything else here we be okay only plus VB information and employer employee ID yeah here we need to select this particular cell this one I six so FR FR m dot range i 6i6 dot value dot select basically so we need to select so that user can easily okay this particular field has been selected it means you don't need to enter the information over there now we need to highlight this particular cell with red color so FR m dot range i six dot interior door color is equal to XL red okay sorry not accelerated which is VB red right okay now we have successfully you know highlighted the this particular cell we need to assign because evaluation is not like you know it is no longer true basically you know well listen is getting you know not meeting the target basically the criteria so let's copy this and paste over here and make it to false okay and what we need to do we need to get from this compatible function then you can write exit function now enter end so this is this is for no validity employee-id let's copy this and paste it for validating employee name as well so we will apply this code for validating employee name and just see the comments replace the ID with name and reference I six should be I ate and ID again here employee ID should be employing name and here employ name okay six replace the six with eight and again here so this is done for validating employee name now we need to validate gender or so let's again copy this particular section of code and just make enter make leave some room for you paste it over here validating gender and now here we do not need to check or whether this blank or not the genders should be either male or female so first of all let's select this change the reference like gender should be on ten and then value is not equal to is not equal to female again copy this okay and and and this particular clean for male so here we can see that okay dream the fr membranes I tend up value if that value is not female and again the dad well is not male it means either it is not female or male it means we are not meeting the target criteria we have entered or selected some other values and that's why in that case we need to pass a message so you think please please select Valley gender from drop-down okay and we okay only information in gender gender here we need to select this so just change in reference I tend to I eat white N and again fr mi-28n and then everything goes on so we have completed the validation coding for gender gender right but now we need to do for you know department name let's copy this import for gender and replicate the same for Department as well so paste over here and change the comment section like validating gender to Department so see in department we have considered you know HR operation training in quality so we have already two section over here the male and female so we can we need to replicate these fields name we should have you know for basically for validation here we have two validation so let us copy this and line break enter it based over here okay and pressed pink and enter and so now we need to give the reference just change the reference I ten should be I twelve again here I ten should be I twelve again here I tend should be I've twelve and then okay and the values that are female so let's replace all those four fills with relevant this relevant and department name so operation not operation basically HR and then operation okay then training and last one is quality so this is for our department which we have mentioned you know which we have added in our drop-down list now we keep the message if that particular condition will not meet then we will have a passing message to users so message will be please select valid the department name from drop-down and okay only information and that is in Department Department and now we need to select that particular range so cell I twelve we need to make the saying the color of that particular cell so I again if that will be twelve validate it is equal to false because we are not meeting the condition and a digit function we are exiting the function as we are will not be able to proceed in case and I user has already entered an incorrect value so now it's done for Department now we need to do the validation for salary so let's select this again welded valuating gender here in this particular port and copy this and paste over here and validating salary salary right let's move to a little bit Tom's right okay so validating cell D so cell 3 is available in row number 14 so first let's make the changes in 14 first and then in first 14 okay so here we need to validate whether the row number like I 14 is blank is not equal to blank so if that will be blank or here we will put the condition all and then we need to pass like no not is numeric yes if that will the value user has been entered by user is not Marik okay and then it means we are not meeting the criteria while entering the value is like salary in our print so that should be either that's not be blank or whatever the input has been done by user and that should be numeric okay so there are two criteria that is only blank not equal to blank and not equal to no it should be no is numeric right his number basically hmm now we need to give the message as you know please and turn valid staggering and this gender should be salary and here 14 should be like I 10 because we are well-written salary so cell 3 is available in 1400 14 again change the code it so we have done with 2 meditating salary now the last variation is spending that is no address validation so for address validation we can copy this field as well it will work at this particular code for validating solving and best over here okay and just click the change the comment reevaluating address okay so now we need to check whether address is blank so we need to remove this particular second validation and trim the ranges you know I 16 if that is if that is Bank and again the same is know here here we need to give the equal to sign because he if LT is blank or is numeric I'm sorry I should have change the like not equal to sign to equal design but we have modified now so it should be equal to sign again same goes for if the i-16 that address is blank it means user has not entered you know address so please and play and valid address okay here address a forum range yeah 16 and 16 okay so we have done with address validation and the code is done for validating the entry so let's compile this compile here you can see that we have done some no there is a typo so basically the spelling of false I have written say s LD fals see I think this mistake is available every in every section of one because we just copied and pasted it so replace the false okay so we have successfully replaced the incorrect spelling of false now let's again go to debug and compiling so now you can see that there is no error in the in that particular what about the code we have written as of now so we we covered the phone's like we have completed the function validate now we need to write the code for that is it let's come down and click here like enter some line break and start writing the code so as from allegation we utilize the function now for reset we will use sub procedure which is always start with some so and then give the name as whatever the name you want so I am just giving the name as reset bracket open bracket close and press Enter so once you enter press ENTER you can see that there is end sub and this own additional line has been added by a visual basic application so now we need to write the all the statement like lines of code over here in this middle of this so let's create some room over here okay so let's start writing the code so we will utilize a big block so with kids and the sheet name is form right and it now own reset we need to reset the color as well as value so whatever the color has been highlighted or may be available on each and every field we need to remove those colors as well as whatever the values has been selected or enter by user we need to remove that at one as well okay so let's give the range i6 door color is equal to XL none again means i6 dot value is equal to blank so this is for employee ID again we need to replicate the same thing for other section light employee name salary Department address and gender as well so let's replicate this this particular line of code five times so let's break over you then paste it again get language that's it 305 6 so we have 6 different filled over there and we have replicated this line of code six times so first is i6 then we need to change the room number I ate okay then I 10 I 10 then again is to I 12 basically there is one row gap by 14 and I 16 so now the code for the set is done let's set above this compile now you can see that there is no error now we need to write the code first save okay so let's start writing the code for save so again in the same way we need to give the reference on write the sub and keep the name as save back at the start back it closed and press Enter and you just get some room over here so here as I already told you that let me go to the Excel window and I already told you that we are utilizing l1 and you know m 1 cell 2 s 2 roll number as well as no serial number so that we can use all to all these 2 values while modifying the records the same well we will keep in mind while writing the code so let us move to visual basic window and start the writing so let's declare some variables okay and now we need to declare two variables more to be my throw as long in eye eye serum as okay that's a set of both the worksheet so step frm is equal to this later this is equal to this book shoots database very simple so we have successfully set both these know this form like this box it and this box it with form and database right you can see that the database and from here now we need to check some condition like if the value like if there is no value over here like em on there is no row number and dunno see Ellen Burstyn we need to identify the row number as well as the next serial number from this particular database okay so let's move to be Turkish it and start the coding dream if dream that form which is the form arranged m dot the value is equal to blank it means no there is no number no serial number available we are just going to enter some new request a new record we're not modifying the records in that case we need to find the row number and serial number from database okay so in that case our eye on the go should be new data base database dot range and the range our ambition application dot rose dot count right and then end Excel up to plus one okay okay and then I cereal I see it is equal to IC is equal to database dot cells then I row the rule number the previous row number basically means and one dot value plus one it means whatever the value is of level in previous Oh in Colombia we are adding one plus in that particular value and that will become our next serial number okay so if else we need to else and if that m1 has some value it means we are going to modify then we need to keep the extent the reference to I row will be FR m dot range L 1 dot value and I serial will be FR m dot range m1 dot value that means value not validation okay no that's ending so we have successfully Nana far like we have assigned the value for our you and I cereal so that we can paste the data in next row and we can like now use the serial number as well okay so now we need to move the data from the form to database so let's start using the width data now dot cells pyro and then one this dog value is equal to this is for serial number so if we will use I serial here I have CRI here cereal okay now same I just copy this particular you know this copy and paste over here this is for employee employee ID so I row plus and the column will be to one value is equal to Fr m dot range i 6.2 value okay complete this particular our lineup over can replicate five times more two three four five six okay so let's uh I to make thing I 3 I 4 5 i5 like sorry wrong number the column on 5 6 & 7 for number 7 ok and here 6x6 i8i 10 by 12 I 14 high 16 ok now we need to assign the value for submitted on and submitted by so first is submitted by and then submit it on so let's copy this paste over here and this would be Aizen is this is it column 8 and then we need to assign the username application user again this copy this particular in a port and then for submitted on so this is the column should be 9 and here we need to keep the trains as you know not ring here we need to give the value of like if you apply is formula so let's text then now now function and then here TV mmm YY YY space hh:mm:ss the bracket okay now and with now here once we will submit the data we need to remove the value which is available in this particular cell like l1 and m1 okay so let's clear the value so FR m dot range one dot value is equal to blank now copy this line of code you can also write L 1 to M 1 like this one but just for reading on so that you can easily read this and understand this I am dedicating this code and just make this M 1 so it means we are removing the data or whatever the data is available in L okay so the code for save is done now we need to write the code for modifying so let's go more this case over here and start writing the code first modify to sub and modify start so basically we need to declare two variables here one is I cereal and one is I row so that it will hold the serial number as well as the row number so that whenever we will make modification post modification once you will click on save button save function will utilize both the numbers to update the data existing data basically so let us declare it beam I row as long then I see your as long okay now we need to identify the serial number okay so not basically we need to UM get the input from user so that user will input the serial number and then with the help of that serial number we will identify the I row and we will make the modification right so first of all let's son let's get the input from the user I serial is equal to application dot input box okay just a prompt as a string should be place and serial number to make modification okay or make or modify the decors basically comma title should be modified okay default let keep it blank left you should be blank top blank help file plant help contact ID blank and type should be one one is for number so these are application what input will accept only numeric whether it will not accept any text value or anything else that's why I am entering I am giving the last you know parameter as one so here we have come let's make it over here so that you can easily see the screen on your screen the entire coding now we need to write the code let's assume in that if there would be any error so let's write the code next okay now we need to identify the row number with the helpful we know our serial number so whatever the serial numbers have been entered by user so let's I row is equal to we will utilize the function that is called a new worksheet function match function basically so let's start writing application dot worksheet function dot match right and the first argument you know that you know and that is I CL this one I fail and where we need to find we need to find in the column e e column in database so let's start writing the code sheets data base dot range equal only okay entire problem we need to find comma 0 so what will you do it will provide the rule number of that serial number but suppose if the serial number will not be available in database then in that case it will give or it will prompt an error so to handle there we will utilize one more function that is if error so lets you capsulate this particular function with again if there are two application dot if application log worksheet application worksheet function dot if error and then back it start and then just go here and then credit towards okay okay so let me make it into line so that you can easily see this so we utilize you know underscore and then break the line to continue to make it things like it will consider compiler with a compiler will consider the same line okay so this is done so now we have I 2 and I serial number for modification okay so what we need to do we need to assign basically so here suppose you know if I Rou is 0 basically if there is no value here application comma comma 0 right so if there would be any error or the value will not be found over here right then it will return the 0 right so in that case we have to prompt a message to user okay stating that no records found so in that case we need to write the I row is equal to 0 then message box know found okay plus critical know record and then and if it means there is no record found it means we do not need to proceed ahead right now we need to assign the suppose you are like we like row number found and we have serial numbers so we need to assign those roles that room number and the serial number in range Elwin its ll 1 and M 1 in form worksheet so let's write the code so let us assign the I serial number to respect itself so here shit's for dot range L 1 dot value is equal to zero and sheets form dot range m1 m1 dot value is equal to I cereal so here we are restoring my row and I serial number in l1 and m17 on Form okay now we need to a phase respective record from database to form so let's write the code for that so first of all shape form dot range i6w is equal to ok sheets database database dot cells and the census we need to pass a row here and the column number should be to cop value so this is for you know I employ ID let's copy let's copy our district line of code and replicate for are no other fields like employee name gender Department salving address so we have six one two three four five six let's sing the reference ice six should be I ate and here six should be then ten and then 12 14 and 16 okay here we need to change the column number so 2 then 3 4 5 6 7 ok that's done so basically we have successfully written down the code for modify now let's go to debug and compile so you can see that there is no error in whatever the code we have written add up now now we need to write the code to delete the record specifically okay so let's start writing the code again serve to be late start writing the code so here are so here we need to declare the variable pyro and ICL number as it again so beam i0 as long beam is serial as right and again we need to get the input from user so that we can now we can find the serial number and delete that particular record from our database so let's again utilize so I I CL is equal to application dot input box and then prompt plays serial number - delete delete the record and pong should be delayed and the default blank left blank top blank health I drank help context ID blank and type is equal to 1 okay so we will get input from user and again let's write the code on on error resume next and now start writing the code to find out the row i row is equal to again we will apply the same you know same function or same line of code which we have already written in in modifying so let's copy this and here we forget to write the code on go to 0 right and let's copy this code and because see the same line of code we need to write over here in in delete section so let's go based over here ok and now again we need to identify if I row is equal to 0 then if I is equal to 0 then we need to pump and if message box here message box no record found right no record found maybe ok only plus critical and no record and like that's okay if they suppose there is no to court then it will exit from this until eight otherwise it will know the other line of code will run so here we need to give the line of we need to write a code so that we can delete that particular record from the database so let's write the code it's theta these dot cells a row and then one we need to then like I do is row number and the phone is column basically so we are just selecting a particular a cell of dad but in the first cell of on that particular row and then we are just deleting like selecting entire row row then dot delete and then shift axle up this is done so blatant part is done let's click on debug compile we have subtly written down the code for it let's go to beginning of the code like validate then reset then save then modify then delete okay so what we have completed all the coding is basically functional procedures now we need to assign all this you know we need to call all these all these functions and sub procedure on click events of a particular button so let's move to excel window and click on design mode and double click on click on the design moon and double click on save so here you can see that on private sub CMD save underscore click on click events we need to call that function so so what we need to do we need to first of all we need to validate so we have already a function that is welded so if the validate is equal to true then and if then we need to call or the sub function so here we need to declare a variable team msg value as we message box result right msg we value is equal to Sigma says box do you want to save the data right here we need to get the two option vve yes or no I here we need to give the plus B question and we question and title should be safe okay so suppose if usually saying that okay if MSG we value is equal to V V yes then then and if handed and now we need to call call basically we are validating like if that the validation is true then we are declaring you variable to store the message box result and if the user is confirming okay you just wants to transfer the data if that is yes click on yes okay then it will call this procedure so procedure like save and it will transfer the data right and once it will transfer we need to or reset the value of that performance of call our ESET disabled okay simple again move jump to excel window we have successfully assigned the code on Save button we need to click on modify right here we need to again create a variable image value value as VB mrs. box it'll MSG value is equal to msg previous no plus VV question and here modify okay in packet close if MSD we value is equal to VV yes then and if user will click on you know we we know then it will not perform any of the activity so here we need to call mo bi fi 25 okay now let's move to our Excel window again and we need to click on ready set so just double click on reset again copy the code whatever the code we have written over in CMD modify and just paste over here and do you want to delete delete the record right vs no here community changes change it to be late and message box value V vs then call will be e LD delete simple now again move to excel window and we have successfully assigned the code on delete now the pending is reset this is just double click on reset and again we need to copy the same code the modifying and paste it in CMD reset underscore clip do you want to reset reset the form form and capture should be a reset yes then music reset okay so you can see that we have a signed code on each and every button so let's go to debug and compile media project now you can see that it has no error just click on save and jump to Visual Basic sorry Microsoft Excel now let's start start making some entry and see whether it's working or not so let's start two five five and my name engenders like the gender as in male Department you can maybe quality cell D one two three four right any of the numbers don't go by this number and address make it turn line back wrap text okay now we have no also so first of all we need to validate like we need to test this properly so let's I am I'm deleting this particular record and just now clicking on Save button so come out from design mode and clicking on save so here you can see that and so I deliberately leave this employee name blank now it's it's saying that employee name is blank clicking okay so once you click on OK it has been highlighted with no red color so let's delete mail and now enter well for the name so what next time whenever I will click on this new save it will clear the formatting because it has value and now it will highlight this and pop up a message saying that gender you need to select a value from drop-down so lets you can see you can see you can see here you know it's saying please select valid gender from drop-down okay and now you can you can observe that the formatting has been restored basically okay now you can see that deciding highlighted with red color so let's select the mail okay and assuming that our validation will just work in the same way let's click on save and it is asking basically it's asking a confirmation do you want to save the data yes now you can see that there is an error so okay so in in this case basically so suppose I do is equal to 2 here we are game so i row is equal to 2 then then we need to else else this line of code will work right basically and see we are on the very first row and they knew the previous cell value has no no more serial number and that's why it's not you know performance showing error so here we need to be real number here is equal to 1 now stop this right let's move now you can say we want to say that it is now you can see that entire form has been reset and let's move to database and you can see that and this is the serial number one to 55 delayed male quality sale real New Delhi and the user name is the data labs and you submit it on the time and date so let's move to again let's enter some more information 1 4 5 - now it's ask yes go to database you can see that secondary code has been entered let's move to form make more entry yes so now we have three decodes let's go to form and let's click on modify to modify the instinct or so do you want to modify the record yes so we need to I just want to modify the the one first record basically which has serial number one click on ok now you can see that you can see the value over here one and two and we will this particular module utilize this code ok so let's see in the name like maybe I just want to change from Dilip Kumar divided to delay man you know let's make it some room sell it like this and click on save I can it will ask do you want to save a yes let's go to database you can see that the first record has been changed and having the same serial number but the time has been changed accordingly okay let's go to form you'd like the motive and delete record so let's click on yes we want to have delicious cancel ok that's who and I think we have while calling the function on it when we just call the delete so delete records so let's see the name delete our record right and copy this and just to give the name change the name here to delete records because I think there is inbuilt function okay let's go to excel now click on delete yes enter the serial number I just want to delete the first record click on OK and now you can see that the first record has been deleted successfully ok click on Form and let's make some entry maybe this one like employee ID this one main department should be operations savvy like this one and you just want to reset the form so let's click on reset it will get a confirmation do you want to reset the form yes now you can see that this has been reset so this particular form is working perfectly and we have successfully completed the coding designing basically designing coding and testing part so in this way you can design a lot of flow of employee designed to form maybe quality node or it forms some training transaction or operational transaction form and this is a basic example let in our next video we will learn some advanced techniques on creating the form form basically form control not on excel sheet and there is a one drawback I just want to highlight over here is you can't set tab order over here so there is no option to set a tab order on Excel worksheet basically the worksheet based form if you go for form based form then you can definitely assign the tab order so that's it for today hope you liked this video and that video will be very helpful thanks for watching please like share subscribe and comment have a great day bye bye you

Channel: TheDataLabs

Views: 1,357,342

Keywords: automated forms in excel sheet, how to make an automated form in excel sheet, create automated forms in excel sheet, excel data entry form template free download, how to create an excel data entry form without a userform, excel data entry form software, employee data entry in excel using worksheet, how to create a data entry form in excel 2016, excel data entry form with drop down list template, Simple Data Entry form for beginners, how to make a fillable form in excel

Id: I-Ze6bX7ous

Length: 85min 35sec (5135 seconds)

Published: Sun Nov 10 2019