How to Create Registration Form With Image in Excel and VBA - Step By Step Tutorial

hello everyone welcome back you're watching the data labs my name is lip in this tutorial we are going to learn how to develop a student registration form with image control in Excel in VBA to make this tutorial simple and easy we will utilize the same excel file for data base as well as a data entry form to store the students image we will dynamically create a folder for the images at the same location where we will save our excel file and create a copy of selected image in this folder and store the part of the selected image in database worksheet so this tutorial has a lot to learn and a lot to do I would request you to watch this tutorial till and so that you can't miss any code or logic after watching this tutorial you will be able to develop a complex form as per your requirement I have provided the link of excel file in description box a please follow the link and download the excel file so that you can practice along with this tutorial so let's move to the folder where we will create this data entry form and understand the support files which we will be utilizing so this is the support files let's open the support files and here you can see that there are three different objects of level one is you know calendar dot gif this is icon which we will utilize to launch the calendar control in our form and the second one is custom calendar control which has been developed in Visual Basic application and that is fully compatible with any version of Excel so you can use it and the third one is the regular expression pattern to validate the structure of email id provided by the user in data entry form okay so this is these are the basic details which we will utilize you can download the support files from the same link which has been provided in the description box let's open the Excel application and create a blank workbook let's click on a blank workbook and press ctrl s to save this file so here we need to save the file at the same location where we have kept support file so let's move to the folder copy the part let's copy this part press ctrl C go back press the part over here save and let's give the name as a students registration form okay and make sure you have selected the type s dot XLS M click on save now this file has been saved let's add three walks it over here so 1 MUX it is already available let's click on this plus icon the second one and this is the third one okay let's move to first worksheet and change the name from sheet 1 to home Joey okay press ctrl s to save the changes now we need to add a launch form button over here let's design this home our shape [Music] [Music] [Music] okay and slick the bottle so this is outline okay click on okay so now we can see that the home worksheet is ready press ctrl s to save the changes let's move to the next worksheet that is a sheet 2 let's see in the name from sheet 2 to database just double click on that and database ok so this is the database worksheet which we will utilize to transfer the data from form to this data and database right let's add the column header so the first column would be serial number and then student student's name father's name date of birth and gender course mobile number/email ID address picture submitted by submitted on so these are the columns which are required for database worksheet let's explain the column header okay so let's format the header okay so let's move to worksheet three and change the name to support okay and let's create a column header that would be courses and mention the course that is 10th ten plus two bachelors master DV master and PhD so this these are the sample course which we are utilizing over here you can mention your course and we are adding support worksheet because if we will make the course drop-down totally dynamic so it means in future suppose you need to add some course then you do not need to move to the code window and add some certain line line of codes you just need to come to the support worksheet and just update make the changes in this course column if you delete any of the course from here it will be automatically updated in course combo box as well as if you add any of the costs that will be automatically added over there okay you now all the worksheets are ready let's move to the visual basic application window to move through the visual basic application you can do you just need to click on a developer tab and under code group just click on visual basic click on maximize so here you can see that you are on visual basic application when you do so first of all let's change the name of each and every worksheet in properties window so let's select the form the homework sheet and change the name from sheet 1 to a such home okay select the name of the sheet - that is database - SS database okay and the third one that is shoot three so nuts Sh support okay so we have changed the name of sheet1 sheet2 and sheet3 and we will utilize this naming our coding okay so let's add a user form so just click on insert and click on user form so this is the user form which we will be utilizing to transfer the data from this form to database so let's a design the user form we'll just select this user form and go to the properties window that is this is available here so let's expand this you your properties you can do a little bit more ok now you can see that or see all those details so let's change the name so then the name would be frm data entry frm ok and the back color should be white so let's change the back color so this one white color okay and the caption should be here you can see that captions so that would be students registration form okay let's explain it okay so this is students registration form let's see the height and weight to height will be 483 maybe 484 and which should be 573 okay let's run this form and see the size so this size is perfectly on appearing on our screen let's close this form press ctrl s to save the changes now we need to insert all those controls and you know the frame so we will divide our form into section one will be for data entry and one will for database so for that we need to insert frame so let's insert it frame so click on frame this is the frame okay and click enjoy frame okay let's see the name soul name would be frame 1 okay and the back color would be again let's change the back layer to white so simple white and border color so let's border style would be single and the border color should be good too maybe this one water color okay let's see the height and weight so height would be its like this frame and height would be 252 and width would be 534 so 534 okay let's make it center so it's a little bit right side let's change that caption so it's like this frame go to up caption would be enter details and the font color so font should be it's a Toma and a regular and the side should be twelve so let's select this twelve click on OK and the color would be the same color which we are utilizing so maybe in this one so the first frame is done and now we need to insert all those controls like the input field so let's quickly insert all those required labels so we need a eight level so let's start adding all those levels first you so first of all let's arrange all these labels so let's move this level a little bit upside okay select all those levels you go to format align left and again format and the vertical spacing and that would be equal okay let's select this level again and increase the width okay let's control as to save this let's change the caption for each and every label so the label one the caption would be student's name and the second would be father's name third is data birth okay and the fourth is gender and the fifth one is course applied then mobile number okay and email ID okay press ctrl s to save this form so let's add the input controls for each every label against each and every level so first of all the Estonian name ah that would be text box draw the text box over here okay and again the text box throw father name so that's like this father name text box and for data birth of believed text box as well and text box for gender we need to option button so this is option one and again [Music] okay and course applied we need combo box right and for mobile number and email ID we need text box so let's add two textbooks so this is for mobile number and again this is for email id okay now we need a image control for image so let's insert emails control and that is available here click over here draw any means control over here okay let's move little bit yeah and let's okay and let's add it command button to upload the image switch click on command button let's add this and we need one more text box to store the path of image so let's click on this text box and in 30 let's reduce the size and then place this over here okay and now we need a one label that is for address so we will place address over here so let's this is for address and select the caption I see the caption that would be address and we need it's this little bit upper side right and let's insert a text box so just insert a text box over here let's move this okay okay and let's insert a command button one is for submit and one is for easy so let's this is for submit okay and press control C control V and that would be for is it okay so we have inserted all those required controls we need to add the icon for little birds so first of all we will make the changes of choosing properties and then we will add it icon for that okay so let's add one more text box that would be hidden like we will make this hidden as well as one more text box to contain the rule so let's click over here text box and please see so now we are done with each and every control this is required so let's change the properties of each and every input controls and format all those controls so it will look beautiful okay so let's start with the student the text box first and see in the name that the name would be txt a student named okay and the back color would be again back color would be white and the border style that would be single and the border color should be a simpler which we are utilizing and height would be 20 and the width should be 192 okay and the tab order should be 1 okay select the father's moon and change the cap name from text to two.txt father name and again the border style that should be finger back color that would be white and Bordeaux color the same pattern which we are using okay and the height would be 20 and which would be 192 select the date of birth again see the name that would be txt D will be no true style single border color this one back color Wyatt and height should be 20 and which would be 192 okay I think we need to make this form a little bit wider so that each and every control will get placed perfectly so let's increase the size of this frame let's move this to right side now that's looking good okay that's okay great let's change the caption like change the properties for gender like option one and option two so let's that it be Whoopi opt female and the caption should be female and that would be opt means the caption should be male ok Go's applied for so let's see the name so that would be CMB course and the border should be be in the single the border color would be the same color which we are utilizing for others and back color would be white and the height should be 20 and the weight should be 192 okay let's select the text box for mobile number so name would be txt mobile and back color would be white single and border be the same curve okay hi it should be 20 and which would be 192 okay let's select the text box for email id saying the name from text box 5 2 T extinct email again border should be single border color would be same color and back a little bit white height should be 20 and the wit should be 192 okay now we need to change the name of this text box that is hidden so let's txt roll number okay and so visible is equal to false let's change the properties for image controls so it's like this and go to the properties window and name would be IMG to student okay and again the border color border style should be single border color should be the same color which we're utilizing battler would be blank and height would be 80 let's see in the height that would be 80 and again the weight should be 80 okay so we are considering a passport size photo for that let's change the picture alignment and other picture Pro put a image property so let's picture alignment should be Center and picture size should be a stretch in the Sizemore let's this one and let's change the name for this the hidden text box name would be txt in this part we will be utilizing this textbook to store the image the path of that image okay and let's change the visible the visibility should be false let's click on visual pause now let's change the properties of this command button just like this so CMD load image okay and the back color would be white again and the caption would be upload image okay let's decrease the size I think let's change the properties of text box that is for address so that would be txt address and the border style should be single border color will be the same color and back to little P this one and you just make it multi lines with multi-line property that would be true let's increase the size okay and let's let's add the scroll bar so let's make it vertical let's change the properties of command the come on come on button - and come on button three so let's select this command button - and let's change the name that would be CMD submit okay and the celery you should be s back color like the palate and maybe this one submit okay let's just like this come on button three C in the name CMD reset okay and that color would be maybe this one and s literally should be our and the caption would be reset let's move to the right side press ctrl s let's run this form to see how it looks like okay so it looking good let's close this form press ctrl s again and now the inter details section is ready let's add a one frame that is for database let's select this frame from this toolbox okay and draw the frame here okay we go to the properties and the border style should be single and the color the same color which we are utilizing and the font the color would be the same color and the font would be yeah that would be twelve and give the caption and that would be database okay just control s so let's quickly add all those controls in this particular section so first of all we need to button over here one is for edit and one is for delete so let's insert two buttons over here this is for edit and again this is for delete and the third control that is required that is a list box so let's select this list box and draw a list box over here okay press ctrl s let's change the properties of each and every controls in the database frame okay so let's select this command button and that is command button one for this particular of frame go to the properties window and let's see the name and that would be CMD and it and accelerator cue should be e okay and see the caption and it and the height would be a 20 and which would be 60 so that's 60 and let's change the font size that should be 10 okay let's see in the back line as well so what Eddie died Bakula should be maybe we can pick this one and it let's change the name and caption of command button too so that would be CMD delete and that's Diaz is a little tricky and the caption delayed okay and the back color would be again maybe this one let's select some different color maybe this one okay and the height should be 20 and the font size should be 10 okay and the wit should be 60 okay let's move to the side okay so edit and delete buttons are ready let's change the properties of list box so select this list box go to properties window and change the name from list box 1 to ls3 database okay and select the border style that would be single border color the same color okay the back color would be white so everything is done let's run this form and see how it looks like so it's looking good right okay let's close this form again so everything the designing part is done what we need to do we need to set the tab order of each and every control let's quickly add the tab order for each and every control so go to properties window and select this and here tab index twirl 11:12 and for this group and that would be 1 0 & 1 & 2 good so we have successfully set the tab order one thing is missing we need to insert the icon over here so let's insert the image for that icon and click the insert image over here okay so let's set the height and width for that image so the height and weight would be 20 so that's 20 and the width would be again 20 right and let's change the name of this icon so from image 1 to IMG D or B data birth right and the name would be IMG see a calendar okay because we are going to utilize this icon to launch the current of it current reform right go to the picture let's add the picture on this image and select the calendar this one calendar on gif and here the size should be a stretch and border style should be none and the back color should be just on track this icon in place over here okay that's done let's run this form now we can see that this particular icon is perfectly fitted under the text box that is for that little birth let's close this form one thing we are missing we need to protect this editor birth so that user will not directly enter any of the date in this particular uh no Xbox which are either usually utilize this no icon or double-click on this text box to launch the calendar button so let's go and select the protection yeah here it comes that she locked I love to be true ok so now we have done with the form created required controls and set all those properties as you can see on the screen let's import the my calendar custom custom calendar form so that we can utilize that calendar on the click event of date of birth double click on event of data birds text box as well as this particular icon so just right click on the form and import file and from input file you can see that we are in support file so just select this my calendar dot frm click on open now you can see that an additional user form that is my calendar which is available here s so it has been created in Visual Basic application let me show you the code which we have written to develop this form in a calendar basically so it's a very simple let's go to the frm data entry and now let's utilize this my calendar so before proceeding and utilizing the my calendar and right in the core let's insert a module so we just go to insert and click on module let's change the names like this module one and change the name from module 1 to M D data entry ok let's double click on your foreign calendar and let's write the code on double click event on this particular text books the date of birth so just double click on that and here you can see that the event is change soon we need to write the code on double click so let's change the event and here you can see that it's double click and let's delete that this particular line of course which is for change event and now we need to write the code are within this sub procedure like the double click events for txt DOB so let's start writing the code so first of all we will make the application has created a screen updating that would be false and let's declare a variable to store the date so as late as string and let's write the code to resume the next line if any error will occur so on resume next and let's assign the value on to a state variable a state is equal to my calendar the calendar which we have already imported so I am referring this Mike Calendar dot date because to date picker is a function which we have which I have already created in this my calendar form you can go and like go and explore all those functionality or the goals which are available in my calendar so I am NOT going to discuss all those goals here right we are directly utilizing the function which is available in this particular my calendar that is date picker ok they trigger an Open bracket and me dot txt do be ok simple very simple right and here me dot txt do B is equal to and now we need to assign so value is equal to format and we need to format the date which we have already assigned in s date so as a date and the format would be DD may be in DD mmm okay and then for time way okay now let's release the onerror so on terror go go to zero an application dot screen updating is equal to true so this is all about the code of launch the calendar calendar control and select the date and assign the date to txt DOB let's copy this code entire code press ctrl C again go to frm data entry now we need to assign the same code on click event of this image control okay so just double click on that and here you can see that one privates or procedure has been added and the event is click event let's mix create some room between private and sub an answer and paste the code which we copied from this procedure okay let's end this now then press ctrl s go to the forum data entry click on run and let's double click on that now you can see that once you click on the double click on this particular text box you can like the calendar calendar is showing and you can select any of the date right and even you can select or see the date on this particular one click event of this image and that is icon and let's select the 19th now I can see that it's working so let's close this form press ctrl s ok let's minimize the size of properties window because we are not going to utilize it more because we have already created and said all those properties let's double click on MD data entry module and now we need to write the code to India to reset the form and to initialize the window right so let's give the comment code to reset reset n form ok let's start writing the code so the sub procedure name would be reset underscore form create some room let's declare a variable team I row as long and let's start with with frm data entry and and with so we are going to reset the frm data entry this this form and that's why we are utilizing with frm data entry right let's write the code within with an N with block so dot txt student name dot value is equal to blank and dot txt student name dot back color will be V V white okay and dot txt father name dot value is equal to blank dot txt father name dot back color is equal to V be white so basically what we are doing we are just resetting all those fields where user can make the entry and assign the default color okay dot txt do big dot value is equal to blank dot txt d will be door back color is equal to VB white okay now that a button let's reset the value of gender so dot opt female dot value is equal to false and opt main male dot value is equal to false here we are not going to set the back color of option button because we have not utilized the back color for option button right txt mobile number dot value is equal to blank dot txt file dot back color is equal to VB white dot txt email dot value is equal to blank dot txt email dot back color is equal to VB white ok ok so remember we have inserted two hidden rule number text box one is for rule number one is to store the image so let's txt txt roll number dot value is equal to blank we do not need to set the color because this is hidden okay and the next text box that is reason is txt image dot part or value is equal to blank that's T next txt address total value is equal to blank and dot txt and restore back color is equal to VB white so let's reset the image control so dot I am the student dot picture is equal to load load picture and the picture name it would be because you BR are no reseting so we are not going to assign any of the pictures who the value would be Vivi null string so here we are utilizing load picture function and to assign the picture ok and dot CMD submit CMD submit dot caption and the caption would make submit okay now the pending control is combo box so first of all let's change the color back color of combo box or CMB course dot back color that would be VV Wyatt and now as let's move to the excel worksheet and here you can see that supporting support worksheet and the support sheet we have treated the course okay and the reason for creation the course is here because we will utilize this particular column and the row source for that combo box and it will work dynamically right so let's move to the visual basic and let's enter a comment over here dynamic range based on support shape okay so first of all we will create the name for the given range so let's start so that's there for the worksheet names assist support dot range and the range is starting from a - okay and then and it ends two offices support dot range and here e ampersand rose dot count God and axle up dot names and the name would be so here we are creating a dynamic name which is starting from a two to the cell where a data is available right so what it will do it will select the range starting from a two to a six or maybe if you add some more cores over here then it will consider all those course all those cells right and then created dynamic name and the name would be dynamic and once the name will be created and then it will assign the name to Rose properties of combo box okay so here we have created a dynamic a name basis the value of level in support sheet so let's assign this so dear to CMB course daughter row source and there also should be the name let's utilize the name here the name which we have already created in dynamic okay and let's see mb course dot value and that will be blank okay so we have successfully reset all those input controls let's write the code to assign the table value to list box so let's give the comment over here assigning row source to LST database this box okay so let's move to excel worksheet first we go to database and here you can see that we have total 12 columns so we need 12 columns in our list box so let's move to Col window and LSD LSD database dot column count is equal to 12 okay and let's our show the headers or LST on database dot column heads is equal to true because in our database we have column header and let's assign the column width so we have considered 12 columns so we need to assign the points the column points the weight basically for each and every column so let's LST Alice still database nor column width is equal to and we need to pass all these column widths in inverted commas so let's first would be 13 comma 17 again comma 70 40 45 and then 70 60 70 and 0 0 you okay so we do not want to do not want to show the last three columns and that's why I have mention 0 okay and let's identify the last row in last non blank row in database worksheet so until find the last known blank rowing data is okay as we have already declared a variable and that is I rule let's utilize that variable so I row in equal to SS database plot range and here II am person Rose dot count dot end and then Excel up total row okay so it will give it the output as the last known blank row let's utilize this row first of all we need to validate whether the IO is greater than one or not because here on row number one we have mentioned the column headers so let's if I row is greater than one then else and if so if I row the value of I 2 is greater than one then we need to LST database daughter row source is equal to and here and data base okay and a tutu I hope okay let's copy this code and if there's only one the collimator available then we need to make it I to l2 basically so that's done the code for reset is done go to debug and compile now there is no compiler error for this code that's create room here so let's let's add a sub procedure to check whether the images folder is available or not if it's not available then it will create the folder okay and if that image folder is available then it will skip it will not do anything and we will utilize the same folder to keep all those you know selected emails we will utilize and the email folder as a repository of images okay so it means whenever user will browse any of the experience image and flicked image the code will repeat the dot image from the actual location and paste it in the image folder and change the name the name would be a student name right and then after that we will utilize the same image path while assigning the image to the image control as well as keeping on the image path in database worksheet right so let's start writing the code create folder do some room over here it's City clear dim s STR Street folder as a string okay and STR folder is equal to this workbook dot hot ampersand application dot path separator and then again impassioned and the folder name would be images ok and let's check whether these forgeries exist at the same location note so we will utilize directory function here and we will pass here you can see that the path name so STR folder and the directory that is V V directory so VB directory ok it was bracket and equal to if that is equal to blank it means that particular folder is not available or the folder is not of levels and the given location then what we need to do we need to call a function that is called make dir and create the folder that is STR awkwardly here folder and and if so it's a very simple function we have assigned the folder path over here pull their name basically and with the help of director function we are well reading whether this folder is exists or not if the folder would not be available at the given location then it will it will be blank okay so if folder is missing or probably not available then it will be blank in that case we need to create on the folder that is STR folder so with the help of make directory mkdir we are creating the directory now folder and if that folder is available then it will it will not do anything it will escape the the code ok so this is done for create folder now let's write a function or to browse the image and select the image ok so lets function and the function name would be get image path ok as a stream that's okay first of all let's initialize the gate image part with blank so great it means part is equal to blank because the function is a string type right and let's read application dot file dialog and the file dialog would be MSO file picker okay and with let's allow multi-select we do not need to allow the multis like that would be false and the filters we need to and filters so it's filters dot add and the let me images coma and let's give the extension over here so start dot gif okay semicolon star dot jpg semicolon and I can start dot jpg okay that's if dot show is equal to is not equal to 0 it means user has selected some of the image image basically then and if we need to here gate image path and the path would be selected items one so what it will do suppose usually sleeping any of the image it will assign the path of selected image to get image path that is the function name okay so that's done it's very simple let's delete the extra Bank lines now we have a function and with the help of that function we can get the path of selected emails so let's write is supposed to create the copy of selected image and paste it to the folder then assign that the path of that he means image controls as well as text box okay so let's write load image let's create some room over here okay let's start writing the code so first of all we will declare a variable that is IMG source path and the string so let's again IMG source path and the string let's put a comment over here so that whenever you refer this code you could identify the purpose of and that particular variable okay so to store in the path of selected image okay that's one key one more variable that is dim I am the destination string and here to store the destination part of but to create the copy of selected image okay now let's assign the source part so i am lee s why your source path and the source path would be because we have already declared a function and that is let me go there so this this particular function we are you going to utilize so let's copy this and here let's give you the trim function and okay so what it will do it will get the emails the path of selected emails and assign it to I am the sore spot okay let's up called create folder function so if the folder will not available at the given location and then it will create otherwise it will skip and the destination path would be so IMG the destination and that would be this workbook dot path okay and ambition application dot art separator and then ampersand and here we are going to look like the student's name for that image so the file name would be frm data entry dot txt students name okay and then impression let's break the line and we need to give the extension over here so extension so after name we need to insert dot okay then ten percent and I mean it a split and we need to retrieve the extension from the selected image okay so I am the source path and that is the this one we have already sore spot comma delimiter that would be dot okay and then again s plate and then IMG source sore spot okay so I think we have forgot to add one open bracket over here now it's done you can see that it's working there is no error basically let's debug this compile no this is no compiler error okay now let's copy this this particular I am the source path from this to IMD destination okay so to copy that image we need to utilize file copy function so that's fine composition and a music source spot comma IMG destination okay okay let's assign the image to our students image the image is controlled which we are utilizing on forum so first of all let's set the properties of emails that would be a stage mode so frm beta dot I am the student dot picture size the picture size mode would be frm stretch the frm picture size moon stretch right let's load the picture frm data entry dot IMG student dot picture is equal to and the function would be load picture I am destination okay and let's assign the destination on the path of that image to the hidden text box which we are utilizing for image so let's frm entry door txt image path you don't value is equal to the destination okay so that's done let's delete the unwanted space let's align this code more press ctrl s we go to debug compile so now you can see that we have created a sub procedure to load the image now let's write the code to validate the email id so okay so let's function valid email email as string is the type would be boolean okay let's move let's declare a variable for regular expression so dim particular expression as object and set Oh R is X is equal to we need to create a object so we will utilize a function create object and the object wouldn't be a let's double quote and the name would be V V s will be a script dot r EG exp racket let's start with o ra NC e x and with okay let's write the core here let's uh set the pattern pattern and the pattern would be we have already mentioned the pattern in our support file so let's open the support file and the regular expression here and copy this pattern from here and just visit to Col window and pissed okay that's valid email is equal to dot T in yes T test you know it should be test not text basically test okay let's set the DX is equal to nothing that's format let's go to debug compiled now you can see that there is no error okay so let's write a function to validate all the entries made by user so let's quickly add a function over here so the name would be function valid entry as boolean we'll treat room okay let's start with valid and three is equal to two and let's read the forum data entry and with okay and let's write the code assign the default color for each and every or to each and every control basically so dot txt student back color is equal to V V white dot txt father name dot background is equal to V be white dot txt do B dot back color is equal to V be white dot txt in mobile not back color is equal to white txt email back color is equal to V V white dot txt and restore that color is equal to V V white dot CMB host dot back color is equal to white okay so now we have done with the default color okay so let's write the code to validate each and every entry so first of all validating a student's name ok if lets you play stream function dot txt txt students name dot value is equal to blank then and if let's write the code over here so the master box will be please enter a student's name plus VB information a student's name okay and txt mr. Dean's name not bad color is equal to VB all right now we are highlighting with red color txt is students name dot set focus okay and valid entry it will false exit function so this is for a student name let's copy the score and replicate this code for father's name and here txt father name with inter father's name father's and let's go please yeah that would be father okay this is done for father's let's start writing the code to validate data part will be if dream dot txt do B dot value is equal to blank then and live and the message box would be the Obi's blank please select B will be 4 clearly calendars control ok we will be only plus the read information will be little bird dot txt 3 will be dark black color will be V be red because there is error on get a bird field and we need to set focus so be eckstein be will be Leo be doctor set focus and then valid and 3 is equal to false exit function let's write the code to valid gender so here validating validating gender okay so if dot opt female dot value is equal to false and dot o PT male dot value is equal to false then and if if nothing has been selected by user then we need to mess show MS box please select gender okay we'll be okay only plus may be information gender okay and valid entry is equal to false exit function okay let's write the code to validate the course details so let's copy this or maybe I think let's copy the code which is for father's name and replicate this what course melody course and here let's see in the name dot t-- c mb course dot value if the value is blank it means user has not selected any of the course so we need to pop up my messes please select course name drop-down and here cautioning okay let's see in the tht father to CMB course again cm equals let's write the code to validate the mobile number stone validating mobile number so to validate and while number we need to check three different condition and pop up message if like user has not entered any value it means totally blank or if the length of mobile number is less than ten or if user has entered some low a string value rather than numeric value so in that case we need to show a masses to use it at you have entered incorrect mobile number okay so I start writing the core if dream dot txt while dot value is equal to blank or length off dot txt mobile number dot value is less than 10 or not is numeric and dot txt mobile number dot value okay then and if that's comedy score it schools and replicate here please enter enter a valid mobile number okay I'm here while number th t-mobile back there would be red and setfocus it's done from a while number let's write the code to validate email ID so let's put a comment over here my little email and remember we have already created a function to validate email in email ID so we will call that function here so if valid email valid email is the function name and if you need to pass a parameter that is email ID so dot txt emailed dot value is equal to true is equal to false basically if if the pattern is will not match then we need to pop up masses over here then and if here we need to copy the score and replicate it for email ideas girl just copy this text email list ok please enter your valid email ID okay and the caption would be email ID right so email ID validation is done let's validate the address so just copy the code maybe the code is for father's name replicate and this for address ok and see in the comments so address and here dot txt address note value is equal to blank then please enter address and here Andres replace the father's name with Andres th-th-this so validating address is then one last validation is spending that is image control that's validating image okay that's if dot I am a student dot picture is nothing then and here we need to pass a message stating that you have not selected in your team is so let's message box please upload the passport size photo okay okay only plus information comma picture so valid and three is equal to false and get the function so that's a ten validation code is ready let's remove unwanted space so now you can see that the valid entry function is ready let's put the bug click on compile now I can see that there is no compiler error press ctrl s to save the changes okay so let's write it supposed to say to submit at the enter data to the database worksheet ok let's start writing the code so the name would be a start with some and submit all the school data create room over here let's move okay so let's declare team I row as long we will utilize this to identify a non blank row the last non blank row okay so we have already created a a textbox to keep the row number okay so if that remember the value of that text box is blank then we will utilize the idle variable okay otherwise we will utilize that text box okay so let's first of all check if frm vector entry dot txt row number dot value is equal to blank then twist we need to identify the row number so I row is equal to this is litter base dot range e and person rows dot count dot end Excel up dot Rho plus one okay so plus one means we are just moving first of all we are identifying the last non blank row and then we are adding one one value to that that particular rule to identify the next blank row okay suppose if the txt rule number dot value is not blank it means some values are over there so in that case we need to consider in a row is equal to frm data entry dot txt roll number dot value so we will utilize that room number to update the records okay and live so let's start writing the code to transfer the data from frm return entry to SS database worksheet trade so with SS data base dot range and the range would be e and the row will be I wrote and with let's delete this is yes and let's write the code within with an end with block so here we will utilize the offset function to move a particular column basically so let's offset and here we need to keep the cursor at the same I mean II column okay total value value is equal to and the the first column is our serial number so let's utilize a row function here and because he we have utilized the first row as a header so we need to minus one to identify the exact serial number okay and this serial number is totally dynamic it means whenever we add any of the line of the Uni records from the datasheet database then rule number the serial number will be automatically adjusted okay so as we have already seen that we have total 12 columns of level in our database worksheet so depth let's replicate this line of code the offset function 12 times so let's first of all make this 1 2 3 4 5 6 7 8 9 10 11 12 ok now we need to move the column basically so here this is the first column so this is 2nd 2 3 4 5 6 7 it means to be around 8 : 8 9 10 and 11 is the last column okay and let's start writing the code so second column would be frm data entry dot txt student name dot value and hey this is father's name frm data dot txt father knew not value and this is date of birth to amp our emulator entry dot txt D will be down to value and this is for gender so let's utilize if function iif frm data entry dot opt female dot value is equal to true then female else okay okay so let's add the code for course so frm data entry dot c NB scores dot value and let's write the code for mobile number so let's select this replicate the code here and change txt no well okay then email ID so let's txt email okay now address so let's replicate the same goal for address and well and here txt address and this would be for image so let's replicate the same code and change txt image path okay and here we need to store the username application got username okay and the date we need to enter the date in last column so let's use format function and now reading MMN yyy hh:mm:ss that's done so we have done with the data transfer code let's call the reset function so once data will get transferred from the data entry form then data entry form would be blank okay so all reset underscore form okay now we need to clean the application screen everything to the screen ability is equal to true and then we need to show a message data submitted successfully that's done so we have done with the data transfer now let's move to the form frm data entry and now we need to write the code to identify the selection basically the beach record has been selected by user so let's move to modules and here we need to write a function and here we need to write a function to identify the selected row number in the ls3 database list box right so let's start function and the function name would be selected list and on the data type for this function would be long basically right so let's as long and create some room so that we can write the code that's done okay let's write the core so let's declare your variable so first of all a team I as long and selected selected underscore list that is long data type that would be 0 so first of all over here in his initializing and this particular variable and the function variable basically and the data type is long so we are initializing selected under school list with 0 right if your forum data entry dot a list database dot list count is less than 1 then it sub naught H so basically this is a function so that would be exist function so here basically if user has not selected any of the recording list box then it will not do anything it will directly come out from this this function right let's start writing the for loop for I is equal to 0 to frm data entry not a list database total list count - one so we will do till the list count the last list item right and here next I let's put an addition over here if frm Delta and three dot still database not selected I is equal to true and then and if so suppose user has selected any of the records then this portable line of code will identify that that particular row number in that list box right and it will assign the number to a selected list so let's write the poor selected underscore list list is equal to ie plus one so once we will identify the selected row then we need to come out from this loop so the code is exit 4 let's deal and delete the unwanted space so we have done with the code which we will utilize to identify the selected row in list box let's write it suppose you to show the form so so show underscore formed here we need to write the code frm data entry dot show okay that's done so we have done with all the procedures and functions required for this particular tool let's move to data entry form and call all those pushes on different events like maybe initialize and double click or click events so let's double click on frm data entry and first of all we need to call the reset function on form initialization so just double click on the form and here you can see that a it's showing the click event so we need to change the click event to initialize so let's go to the even drop-down and select the initialize and get the previous code ok let's create some boom so let's call the reset underscore performed so what it will do whenever this form will get initialize entire all those you know the controls which are of level will have blank value and the default color right let's move to the frm data entry form and double click on let's double click on upload image button here and let's write the code to load the image basically the Browse features which we have already written the code so before uploading the image we need to identify whether a user has input the students name or not so if a student naming blank then we can't upload the image because here we are utilizing the students name as the image name okay so in that case we need to evaluate whether user has input a student's name or not so let's do this so if me dot txt students name dot value is equal to blank then msgbox please enter a student's name first okay maybe okay only plus critical in this the caption would be the title basically error if everything is okay it means user has entered the strange name then meeting to write the code basically here so here we just need to call the procedure that is load for image so call that's done okay let's move to the form we have done with the this button let's assign the current submit so let's double click on submit button and here we need to write the code okay so first of all we need to get the confirmation from user whether user wants to transfer the data from form to database so to do that first of all let's declare a variable dim is waving message box result I is equal to message box and we need to do you one two maybe submit the data okay we just know plus question submit a turn okay if I is equal to V we know then it's up so if user will select no then it will not do anything okay and suppose user has selected years then further we need to validate whether the input is correct or not and for that we have already created a function and the function is valid entry so let's look like that valid and three is equal to true then okay let's call the procedures submit under school data that's done so we have done with the submit code now let's move to the frm data entry and let's assign the code on reset click event of reset button so let's double click on reset and create some room okay let's copy the code which are of label on submit under scope click and just replicate the same code over here and we just need to change the message box data basically so do you want to reset the form and the title would be reset form and if you J will select yes then we need to call a reset underscore form that's it so it's done okay so we have assigned the code for submitting reset now the pending code is for edit and delete so let's double click on edit button and here we need to assign the code to edit the selected records basically the selected records in list box so let's start writing the code so first of all we will validate whether user has selected any recourse or not and for that we have already created a function that is elected under school list so let's utilize that function okay so let's start writing code if selected underscore list is equal to 0 0 then okay then how many to pop up message msgbox note a row is selected baby okay only Plus maybe information and here error edit and it's up and if okay so if you just a not selected any any of the recording list this box basically then it will prompt a message that Norway's selected and it will come out from this this procedure that is CMD edit if user has selected a record then we need to proceed with the code and the code will be so first of all let's declare a variable dim yes gender has string and let's assign the value or the row number basically so we will utilize the txt row number that is hidden so male dot txt row number dot value is equal to me and dot LST database dot list and me dot LST database dot list index plus one so basically here we are assigning the selected row on to txt row number right and let's so let's assign all those value to the controls available in the first section shine okay so frm Delta n trade or txt students name dot value is equal to mail dot LST database dot list and again me dot LST database not list index comma one so this is for a strange name let's create some room over here and replicate this line of code for father's name so this is for father's name txt father no THD father name okay and the column should be to replicate this line of code to date of birth dot txt DOB and column would be three let's format DD mmm why why why so this is for father name let's assign the value of gender so we have already declared a variable that is s gender and me dot this database dot list me dot this database dot list index and for okay and if s gender is equal to female then in the forum data entry dot o PT female dot value is equal to true else frm data entry dot opt male dot value is welcome truth and live so this is for gender okay so let's write the code for course okay so frm data and three dot C and the course dot value let's copy this line of code to this one replicate here and the column number will be five let's copy this line of code and replicate this for a while number txt mobile and that would be the column will be six let's compute this line of code and replicate this for email id to txt email and here the value the column will be seven let's write the code for address so txt address and the column number will be eight now we need to write the code or to load the picture so frm data entry dot IMG student dot picture and the picture will be the function load load picture and here close bracket let's copy this line of code this one and pass a parameter to load picture and change the column to nine okay and let's replicate this line of code forty txt image path so here txt image path and column number would be nine okay let's change the caption of frm vodka country dot CMD submit dot C ap caption and the caption would be update okay now everything has been successfully assigned to the respective controls so let's pop up a message and the message would be please make the required changes changes and click on object two to save to edit the data code is ready let's go to deburr and click on compile now you can see that there is no compiler error press ctrl s to save this file now we need to write the code to delete selected records so let's move to frm data entry and double click on delete here and we need to write the code here okay so here we again need to identify whether any row is selected or not right so let's copy the code here under CMD heading I'll just copy this and here and see in the title delete okay so Norway's selected and exit sub okay now if user has selected a row then we need to pick a confirmation from user whether user wants to delete or not right so first of all let's declare variable died I has maybe message box result I is equal to message box do you want to delete selected record we we yes no plus very question and the title would be confirmation they relate basically okay and if I is equal to V V no then again so if usable select yes then in that case we need to delete the records so to delete the records we need to identify the row number so let's declare a variable deem row as long okay and the value of row number would be row me dot and list bar this database basically dot list and again me dot this ste database dot list index c'mon zero plus one okay so it will provide the correct row number and assign it to row variable okay now we need to delete the recourse so let's write the code in this move dot shoots database okay dr. Rose Road you got the later now once the record will get deleted then we need to call the function reset and then we need to pass a message to use the dot selector a record has been deleted successfully maybe okay only plus the information delete so the code for delete is done so now we have successfully assigned the code on click events of button and form initialization let's move to excel window and assign the macro on the launch of form button so that whenever user will click on a launch of form button then form will appear on the screen let's go to move right click on that launch form and assign macro and here show form and click on OK so everything is done let's all move to Visual Basic and debug now you can see that there is no error so let's sum up Excel click on launch ok now you can see that this form is ready let's start submitting a data so that's the lip hard K and let's like the date of birth so maybe 9th male 10 plus 2 or maybe master degree mobile number 1 2 3 4 5 6 7 8 9 0 email ID in 408 labs dot watching upload image let's select any of the image basically oh let's ask like this this email click on ok now I can see that this image is available here now right the cool address so New Delhi and click on submit click on yes hope everything is perfect let's click on close go to database now you can see that everything is available here and email ID and trace the path of the polder coming to the visual way shake run this form and let's add it now I can see that whenever you click on edit it has started showing the details in relevant no input controls basically ok let's see the emails maybe maybe this one ok let's update date 1 years now it has in abated let's slide this so it's working now reset this form yes let's add one more code she wants decay maybe this one mail goes apply it maybe 10th 2003 4 5 6 7 8 9 0 and info at the rate lapsed or to wall she implored image select any of the image maybe I'm picking the same image here and again New Delhi click on submit yes now we have two records right you can make the like adjust the size of form accordingly right and can see that there are two recourse available here let's delete the first record that is on the Left selected I have already selected we can delete yes okay so here you can see that there is only one record that is called 4c once and here the serial number has been Auto existed earlier the serial number for she once was - but now it's it is showing one okay let's keep on edit click on okay so this is all about you know creating a student registration form in Excel and Visual Basic you can utilize all this code to generate is a complex form as per your requirement hope this video will be useful for you thank you for watching please like share subscribe and comment have a great day bye bye

Channel: TheDataLabs

Views: 175,372

Keywords: How to Create Registration Form With Image in Excel and VBA, Data Entry form with Image, Add Image control in User Form, Step By Step Tutorial on VBA Form, Create Employee Registration Form in Excel, How to use Image Control in VBA, Listbox control in VBA and Excel, Automation by TheDataLabs

Id: e-v5XdS9yw4

Length: 116min 39sec (6999 seconds)

Published: Wed Mar 18 2020