Fully Automated Data Entry Form in Excel - Part 4 (with Validation and Print Features)

[Music] hello everyone welcome back you're watching the data labs my name is the lip this is part four of fully automated data entry form in Excel and in this tutorial we are going to learn how to make a dynamic serial number in existing form and how to validate the data entries made by user before submitting the data from form to database and we will also validate the duplicate entries if user will try to submit the duplicate entries it means the same employee details again then it will prompt a message that ok you have already entered this particular employee details and we will update the reset function reset code basically sub procedures which we have already created so we will add some certain life line of codes to assign the default color to each and every controls and we will add another validation code to validate the employee form details before printing ok and the last and the most important feature that is printing so they will add sub procedures so that with the help of that procedure we will be able to print the selected employee details through printer or if a printer will be not available on your system then you can export that employ details into PDF file so this is all about adding several features in our existing data entry form so without wasting our time let's move to Excel and let's add all those required features in existing form so here you can see that this is the fully automated data entry form and let's click on launch form so in our part 1 of this tutorial we learned how to create this user form and all those controls so that user can make entries and how to transfer the data from this form to this particular database a worksheet and also how to reset the form and again how to connect the data base worksheet with the list box the table which we are using here right and in the second part we learned how to edit and delete an existing God okay and in third part we learned how to add a feature like search by so that we can filter the database work the database details and we can only show the matching details in this particular list box right so this is all about part 1 part 2 and part 3 if you have not watched from part 1 - part 3 I would recommend you to watch the tutorial from beginning till part 3 and then please come to this tutorial then you will be able to relate all those existing logics and code I have already provided the link of each and every tutorial in the description box let's close this form and make the required changes in this fully automatic data entry form so just closest so let's add a new worksheet to print the selected employee details we just click on new sheet and rename this sheet 1 to print and let's move to the last ok now go to view and gridlines ok so until the gridlines so let's quickly create the table you you [Music] [Music] you okay so we have treated the tables and we will utilize this particular range to print other selected employee details so this is done so let's move to the Developer tab and click on visual basic and now you can see that we are individual basic application window okay so let's some modification first of all we need to add one button here that we'll use for a print so let's move this same and they said left right and just and copy the reset button and just press control 3 and let's move align this here let's make the adjustment let us select the last command button over here and just expand this give the name as CMD PR print okay and the oscillator you should be P and the caption should be print let's select the back color so back color may be on this one green so we have done with the modification we need only one button that is for print so first of all let's make the modification in existing code so let's start with making on dynamic serial number so that moved to module 1 and go to submit S sub submit procedure and here you can see that we are considering I you as a static okay so we need to make it dynamic so that whenever user will delete any recalls all the serial signal number will be automatically adjusted basis on the roll number so let's start replace the iro -1 with new code ok so here we are going to apply 0 function and that is minus 1 that's it let me put comment over here dynamic serial number okay press control test ok and now let's add ie function to validate the entry so as of now we have not added any function to validate the entries made by user so let's move below so first of all let's start our function and then validate and trace as boolean and just create some room over here that's okay so now we have sufficient space now let's start the coding so to validate increase is equal to true and then let's declare a variable I employ ID as invariant so we are declaring this variable to store employee ID that will be you entered by user and we will use this variable to check the to proceed in our data database worksheet all right let's move let's declare a variable name as such as worksheet and let's set this worksheet set as such is equal to this moved on shoots and then print okay and let's assign the employee ID details to this particular variable that is I employee ID just copy this test over here and equal to frm form dot txt ID total value okay now let's system with a forum form and with okay let's create some room and we will write all those codes within with an end with block so first of all let's write the code to assign the default color to each and every controls which are available on user forum so [Music] default color log txt ID dot back color is equal to V we can white dot txt a name without back color is equal to will be white dot txt city dot back color is equal to V be white dot txt country back color integral to lady white dots CMB department got back color is equal to VB white so this is all about assigning the default color to each and every controls okay let's start validating each and every user input so first of all we will validate the employee ID so if dream dot txt ID dot value is equal to blank then message box please enter employ ID okay maybe okay only plus the information and then employee idle and now here we need to assign the validate entries to false so let's fil sea walls and then the text dot txt ID dot back color is equal to VB red and then dot txt XT ID don't check focus and then exit function because we need to come out from this motion right then and if so this is all about validating the employee ID so if user will not enter any information in txt employee or ticks txt ID then it will prompt a message that a please enter employee details let's start validating the duplicate entry so validating entries okay let's write if not SH dot range and here we are utilizing B column because we are using B column to store employee ID details so the SS da trains BB dot find and we need to find the I employee ID so basically the variable which we have already declared here this one I employee ID so what employee ID and we need to match the whole content so to look at Excel hole okay is nothing then and if let's copy the entire code this one and just paste over here and make the changes in message so this would be duplicate in blow I I did found so this is done for duplicate entries let's write the code to validate employee name basically so let's copy the first one this one and just paste over here and change the txt I do too txt rename please enter the employee name and here that would be employing in and just copy the text in it and replace the txt ID with txt neighbor okay now let's validate on the gender so let's create validating gender that's great if dot opt female dot value is equal to false and not opt male dog value is equal to false then and leave okay let's copy this line of codes test over here and make the changes in message so please select gender and the caption should be gender validate and let's remove the the color backward code because we do not need to assign the color to option button and let's again remove the straight focus so that will be very simple so this is all about the validating in gender code let's write the code to validate Department so let's copy the code in this one and paste over here and make changes replace the txt to CMB Department not CMB Department let's copy this code and the CMB department and paste is this txt name now make the modification please select in Department name from drop and that would be in Department okay so that's done let's copy the code of name and replace the same for PT so that would be replace this dot txt state a total value and please enter city name and that would be again 50 copy the txt City and replace the txt name with txt City don't force it let's copy this code and replicate the same for country that would be our txt country and just copy the txt country and replace the txt city we tht country okay let's make some modification in message box so that would be country name yeah containing okay so this is all about evaluating each and every controls which are AB level on user form let's debug this code to find out any compiler so you can see that there is no compiler error let's copy the entire code in this particular function and replicate this the same function for validating print details okay so now we can see that I have replicated the same code and we need to make some modification in existing code okay so first of all let's change the name function validate we validate print details okay now just copy this function the well read print details names and replace all those validate and trace wherever that is available here you can see that again this one validate entries that should be validated in details I mean validate clean details different details so now we have replaced all those validate entries with welded print details let's check it again okay so basically if we are going to utilize this function to validate the details which are available on forms if form has no value then it there is no benefit of printing all those details right and in that case we will utilize validating details so if any of the film will be blank then equal promptly error message that okay this particular field is blind please enter the details and then click on print so in that case here we need each and every validation except the duplicate entry because we are not going to transfer the data from form to database we are just utilizing this particular function to validate the entry so that we can get the printout of this particular employee details so let's just read this and go to debug compile now you can see that there is no error in this particular validate print details function now let's copy this default color code and paste it to reset code because we have not used color coding in reset so let's move to the reset here so here you can state this is a sub preset and we just need to paste the code in the color default color code over here that's debug okay so everything is working there is no error basically now let's write a sub procedure to print the selected employee details so let's go to the below of coding the bottom side and add a new procedure that would be print underscore form okay and here we will write the code to print out get the print out of selected employee details okay so let's start with coding so application the screen updating is equal to false application dot display LS is equal to false okay let's get Lyra one worksheet variable D message and walk through how that's said the SH worksheet variable so s step s H is equal to these sheets and the sheet name is print okay let's write the code so we will we will use the frm form so let's start with F frm with form okay so let's move to the Excel Excel here and you can see that this is the print works it which we are going to utilize so we need to update employee ID employee name gentle department city and country code from frm form okay so here consider this is starting with E 5 let's move to Visual Basic and let's start writing code so SS thought arranged and that starting cell is e 5 ok dot Valley is equal to the dot txt ID dot value okay let's copy the code and replicate it for employee ID employee name so this is this would be 7 and here click X team name let's copy this code and this replicate is for gender me 7 would be e 9 its so IIF dot Papa T female dot value is equal to true then the value would be female otherwise it will be male okay so this is for gender let's copy this code this line of code and replicate this for Department so CMD Department in the reference from 7 to 11 and then just pop its code replicate this for city so 11 will become 13 and here txt city complete this code on this line of code and the placate this for country so 13 would become 15 and replace this theta city with txt country so this is all about assigning the form values to respective or not filled so here we this is for employee ID name gender Department city and country now let's write the code print the form or export to PDF so code to print the form or export to PDF okay so first of all let's our set print area so SH dot page setup both print area and the print area would be let let me move let's move to the excel sheet and the range would be starting from b2i 17 ok let's come to here and so the range would be our b2 - I 17 so this is the print area so let's write the code to get the printout so Sh dot print out and copies copies would be only one copy so if you want to get multiple complete and you just need to change the number okay comma areas in holes so this this code will help us in getting the printout suppose your system has do printer installed like my system so I have no printer so in that case it will prompt a error and this is the reason I am just going to write an additional line of code so that we can get the the the details employ details in a PDF format so let me put a comment over here and okay if your system has a printer then you just uncomment this particular line of code and comment this the next line of code that is for exporting the details into a PDF file okay let's write the code to export the details so let's start export as fixed format and the type would be Excel type PDF coma and we need to give the file name so file koume and here we need to give the file name so we need to save this PDF file at the same location where this for this excel file has been saved so application dot path and % application both path separator again ampersand so here we need to give the file name so let's consider the employee name as a file name so frm form dot name dot value okay let's make it a little bit shorter yeah ampersand and then the extension would be dot PDF okay so this is all about exporting the details to PDF format so let's prompt the message stating that employee details has been printed so MSE Box employee details have been printed maybe okay and this is very information common print now let's application dot a screen ablating that would be true application dot display alerts then that would be true and press ctrl s to save all those changes whatever the changes to be made in this particular code okay let's move to debug and compile now you can see that there is an error so basically we are considering frm form dot name however the correct name is frm txt name okay go to debug compile now I can see that there is no compiler error now let's call the functions and sub procedure on click event of save and print so let's move to frm form and first of all we need to make modification on this the click event of save that just double click on that and here you can see that we have not you know use the validation code so just we need to write the code over here if validate and trees is equal to true then we need to turn this procedure that is sub and reset and if okay now just copy the entire code which are available on this CMD save under scope with just copy this go to the frm form let's double click on print and just create some room over here and just paste all the scores so let's make some modifications so here the method box would be do you want to print the employee details okay and this is confirmation that would be print and it's the function would be this one validate validating details and let's remove this reset function and here we need to print underscore form that's it so now we have successfully assigned all those goals to unclick event of CMD print as well as click event of CMD save let's go to debug compile now you can see that there is no error press ctrl s move to excel window and go to home let's click on home so first of all let's submit the record so let's I'm 2 5 6 this is the employee ID and maybe the lip a male and the department that would be quality and city that would be New Delhi country dear let's click on save click on yes now you can see that this particular record has been transferred to database let's move to database thus closed form database and here you can see that this is totally dynamic so first of all let's make the changes in this one one two that way three and let me pour because earlier we are utilized a static single number and that's the region okay let's click on move click on launch form now let's get the print details so just select the delete and click on edit and click on OK and just click on print and do you want to bring the employee details click on yes so now you can see that it's a publish there is an error so I think we have made some mistake over here okay so only we should use this workbook dot part because we are not going to utilize the application part so let's replace this application Road part with this this working got part right and click on run now you can see that employee details have been printed okay let's reset it form yes let's select another name like maybe Danish click on edit let's take a printout click on it yes okay now let's move to the folder and here you can see that there's our two separate files one is for the lip and one is for Denise let's double click on any other file and this is all about the employee details ok let's move to Excel so friends it was all about how to make a serial number dynamic how to add the code to validate the entries before submitting the data as well as how to evaluate the Coe the details before getting the printout and again we added the code to get the printouts and if printer is missing on your system then you can utilize the export to PDF feature right so hope this particular tutorial would be useful for you thank you for watching please like share subscribe and comment have a great day bye bye

Channel: TheDataLabs

Views: 56,682

Rating: 4.9128203 out of 5

Keywords: UserForm with Add Edit Delete Search and Print Features, Part 4 of Data Entry tutorial, Adding Print features in form, Print Selected Records in VBA, VBA code to update dynamic serial numbers, Validating User Inputs through VBA Code, VBA code to Print Form or Selected Range

Id: 0bFOKpaSIBg

Length: 31min 44sec (1904 seconds)

Published: Sat Feb 01 2020