Excel VBA UserForm | Add Data | Reset | Exit | Search | Update | Delete |

namaste friends welcome to this channel er.pdr in this video we will learn how to create employee details dynamic form using excel vba as you can see in my computer screen i have created this beautiful form where i am capturing information of employee id employee name gender their address contact number email in which department they will work and their salary now i will show you how it works when i fill the details and hit on the save button it will automatically save data to the excel sheet and the list box now take a take a look now i'll write employee id 102 employee name ram khanal gender ismail address pohara contact number i'll put the dummy number email address ram khanna gmail.com and department is an electrical engineer and salary is 80 000 when i click on the save button now you can see the data have been saved in our excel sheet and even our list box now if i want to update some data like i want to update this employee one and a two is address a is from bootle now when i click on the update button now you can see address has been are updated now if i want to delete this data one and a two employee id 1 and a 2 then click on the delete button then ok now the data has been deleted this is the beauty of an excel vba now without any delay let's get started now i need a new excel sheet so press ctrl n new excel sheet has been added first i want to delete this excel sheet don't save now save this excel file control yes yes browser and save this as a macro enable workbook just save it yes the seat has been saved first we will design this seat we need to design soap on button and table to record save data change the name as worksheet now make a table to record data we'll write here employee id employee name a gender an address contact number after contact number email department and last one is salary now change the size of this column my employee name yes gender address this one will be okay contact number the size will be okay email it site and department this is and last one is your salary now we'll send this format go to home and all border okay this this will be okay now change the background color of this header now this will be okay and bold we need a button here so go to insert we'll select a rounded rectangle rounded rectangle now write name so form yes you go here we'll change here this font size 20 and this one will be okay now this is our show form button now here if you don't have developer tool then go to the file click on the option and here on the customize ribbon the developer then hit ok button now you get ssd developer tool you can see it in it on customize ribbon now we completed the designing of the worksheet let's design our form so go to developer visual basic insert user form now increase the size of this form first we will design the header section so click on the image and draw the image box and now click on the label we need to write something on this header section so change the caption of the label employee details dynamic employee details dynamic form then here we'll change the font it is lagging bold and since the size of the phone 20 i think 20 will be okay okay employee details dynamic form size employee details time before here we also change this alignment text alignment center now first we'll change this size increase this size now here it is finger now change this color and then the background color background color which one will be okay this one yes i think this suits goods now for this we need to change background style background style to zero now it looks perfect yes now now we need to frame one for writing the details of the employee and next one for to keep the data this is our two frame now change the caption of the frame your details and this one is for the our list box yes now we need some level in the text box click on the label and draw some label and this one is the text box now we'll size this label in the text box we need to change their height their height i'll put 20 20 will be okay it's done now we need some more level in this text box so copy and paste it paste and paste now copy all this label in the text box and paste here now change the caption of this label now this is our employee id and the second one is our employee name and this this third one is our gender this is for address this is for contact number and this one is for email this one is for department and this last one is for this salary in the sender we don't need a text box we need a combo box here so we'll delete this and in department section we also need to come box delete now select this combo box and draw done now i'll change this height height will keep 20 done now copy and paste here this one is done now i'll change the name so that it will be easy while writing code i'll give the name is txt id txt name this one is so this is my combo box so i'll give cmb gender this one is my text box so txt address this is also my text so txt contact and this one is also text so txt email this is my combo box so cmb department and last one is my text box so txt salary done now we need a save button exit button for that we'll click on the button command button then draw a command button here this is my save button so first i'll change first i will copy this button and paste and it's six more button five more buttons sorry one two three and this will be for my search button and this one is for update and last one is for my delete and yes i'll first rename this one is cm d cmd this one is for save command button yes now this one sorry for i also change the name here caption sorry i change the caption this one is for save done this one is command cmd reset reset and caption reset this is cmd exit this is exit button exit this is source button so cmd source and caption search this is my update button cmd update and caption update and this last one is my delete button so cmd delete and caption delete done i need a text for searching the details so click on the text box and draw the text box here now change the name of the text boxes txt search done then even i'll change this size done now i'll change the color of everything this one sends the color now i'll put this no this is not good this one is okay reset for exit will be okay update and last one for delete done now i need to draw a list box here so click on the list box and make a list box here and change the name of list boxes list the designing of user form has been completed now we will do coding first of all we will put some code for this user from activation in this combo box you can see there is no drop down list we'll activate this list first so double click on this user form it will take us to this blank page and we'll put code here now from here select this user form and here slate activate now delete this to now we will write code within this user from activate name of the combo box cmb gender that list we need a list so dot list equals we'll put it on array mail female now second drop second combo box name is cmb department department dot list equals array now write the different name of the department mechanical electrical accountant done now can't save this and now let's take a trial and run this now in this drop down list you can see male and female and in the drop down list of department you can see the name of different department this one is done now close this form and we'll put code for this a button so double click on the save button first we'll set some variable dim ss is worksheet set yes equal this workbook dot seeds now we need to put the name of our excel sheet and the name of rx as it is a worksheet so right here worksheet we'll declare another variable this dim lrs long a realer is the last row and yellow equals sits the name of our seed that is worksheets that range will count our first column row of the first column all the rules of the first column so here this a denotes the column and it will count rows dot count and it will count all the rows of first column that in excel up that row here in the beginning we'll put some validation here just like our text names will not be empty and our text id it will be of memory for that we'll put some validation now if me dot text name that value equals empty then message box please enter the employee name it's baby critical now exit sub and in if done now second validation is if is numeric me dot txt id that value equals false then message box please enter the current employee id and baby critical exit serve in if done now we'll take a trial we'll check this code whether our code is running properly or not so run this code now first of all we'll write employee id 1081 and and keep this employee name blank then save here the message box will pop up please enter them our validation is working properly now we'll put this number here and something like this in the employee name and when you click on the save button again message will simply because our employee id is of word and it must be of only of numeric that's why the message box now our validation is working properly close this one now double click on this f button now we'll add data on the excel sheet for this with yeses here vtss indicates our worksheet the name of our xlc that is worksheet that cells in the first cell and we our row is a lr plus one because this clr indicates this first initially the lr indicates the header section and we'll put the employee id here implied is a starting from the implied is here so this one is the lr plus one and our column section is one or we can write a also area a is the column first column first indicates that value equals and this is our me dot txt id section s id dot value here we give here a space in that value done now copy this and paste here we need eight more such code and our second column is the column number b change the name here this one is c d a f z and this one is yes and this second column is of of our text name name and third column is our combo box so cmb gender and third one is our address fourth one is our contact now email here and this one is our department combo department and the last one is our txt salary and now now close this in this width and with done ctrl save now run this code when you type the id181 here ram josie agenda is mail and address kathmandu and contact number put some dummy number now email ramadrid gmail.com and department this one mechanical salaries sixty thousand and when you save click on the save button here you can see how that data has been saved in our worksheet excel sheet but the data is here also now we need to clear this data from these boxes for that double click on the save button now we need to clear our data so we'll add code for clearing our data clear boxes for that copy this section media txt added value copy and after copying this here we just type empty here done then copy this so done until let's run this code now employee 192 employee name sita gender female and address pokhara when you click on this save button now you can see there's no data in this boxes our code is working properly now we will focus our cursor in this employee id and display some message when we save our data so first cancel this double click on the show button for displaying message we write some message here message box data has been added in the worksheet this is vb information done for focusing our question we write txt id dot set focus done save this run this code now we write employee num id 3 and name here sam sam when you click on the save button data has been added in the worksheet message box message has been seen and even you can see the cursor blink on the text id now it is ready for ready for the next entry now we will write code to display data in this list box we can see the data in this worksheet but we also want our data in this list box as soon as we click on the save button the data will be displayed on this list box close this now go to the coding page write soft refresh data now from here copy this variable this one done in this section we'll count our column and set our width so with me that list box dot column count and there are eight columns dot column heads column it's true dot column width column width i put employee for employee id 40 130 we'll take it as random 90 130 100 8080 80 done then row source equals and resource is our worksheet and in our worksheet and this one is our sm starting from the a7 and it will indicate yeah so a7 yes and last row now close this with end with invite done now run this code but still there is no list works here once we complete the coding of the refresh we must call it otherwise it won't so the list box we must call in the two areas one in this save button areas and another is in the activation now i'll show you the differences what happens when you call on the save button then what will happens when you call on the user from activity first we'll call here call refresh data done save run this code now still we are nothing but wait a second output 104 rita when you save now you can see this is our list box we can see here now close this now copy this call refresh data and paste here when you paste here and dot this run this code you can you can see initially this is the difference between calling it on the save button areas and on the initialize areas but what will happen just close this form close this form go to the worksheet and delete all the data done now run this code here your header section comes below it is because it will take the last row so we have to add some code here first close this form now we have to hear this if last row equals 6 then last row equals 7 save and run this code now it is ok close this form now we write code for the reset button double click on the reset here type unload me then user form one user from one this is our user from one dot so done now run this code when you click on the reset now you can see our form has been listed the code is working properly now close this form now we will move towards the exit bottom double click on this exit here if message box do you want to exit this form you want to exist this form this one is viv equation plus pv yes or no then confirmation equals bb yes then unload me and if and insert now run this code when you click on the exit if you if you click on the no then you list here and if you click on this the form will close now we will write code for the source button so double click on the source we'll set some variable first dim x is long and dim y is long and here we copy this row cop control that copy paste here and this is eleanor this is x here our row is start from for y equals r we started from row 7 y 7 to x as you can see or we started from the seven reacts if sits our name of our city is worksheet worksheet this w is capital here dot dot cell row 7 ru is our y where is our row and column first column so type one here that value equals txt search dot text then this is our txt source and this one is our txt source you can see dxt source if our worksheet cell y1 first cell equals txt so this is id number equals dxt source then the txt id equals we'll copy this ctrl c done then i can copy this ctrl v here txt name no exceed this is y2 now first run this code and i'll show you how it works first fill up i will put some data that is because sing gender male address kathmandu and this one is a dummy number done also when you click on this the data will be saved here we'll save another data one two this is row singh gender mail address pokhara contact number save now i want to source the employee id 101 then type 101 when you click on the search compile end if i have to close the if so end if end if here and also i have to insert next y now run this code now you can see when i type 101 and click on this source information id and the name has been display now i want to display this gender address contact contactor all these things so close this form first double click on the search now copy this text and paste it now change the name this one is cmb gender text address this is txt contact and txt email this one is combo department cmb department this one is txt salary and also change here distance three column number three four five six seven and here it is eight now run this code type here 101 click on this source then you can see all the data has been displayed in now exit this form coding for the search button has been completed now we'll code for this update button all the code is same is that of source button so we'll copy the code of source button and we'll paste here we need to change interchange this yellow and rhs here so we'll curd this and paste here we'll do same things for rest all the rows cut and paste cut this and paste card this in your paste cut this and your paste cut this in our paste now run this code type your one ended one click on the search i i want to add email here because they're there gmail.com when you click on the update now here you can see email has been updated now now here the details is showing yeah now i want to clear all these details from the boxes when i click on the update so double click on this update i'll copy this clear boxes and where is my update here now paste here delete this call refresh and that has been updated updated in the worksheet set force controllers and i'll put the validations also so copy and where is my update here is my update and paste here done run this code when i type 102 search and i'll add email here rosan editor gmail.com when i click on the update that has been added in the worksheet and my text boxes all the text boxes has become blank now the coding for update has been completed now close this form now we will call for delete button so double click on the delete from search copy this code and paste here now we don't this so cut this now here type rows y dot delete ctrl s and we'll clear the boxes also ctrl c here ctrl v or this now that has been deleted deleted from the worksheet now run this code and type your one ended to search when you click on the delete button that has now you can delete the data from the worksheet here our search text box has not been cleared so we'll clear it first exist this form double click on the delete here we have to add copy and paste txt dot search this copy even must put here in the update section this one is the save sort yes here also now copy this thing has been completed now when we double click on this list we want our data to be displayed here for that close this form now double click on this list here select double click now delete this now we'll write code here here we have to write text search dot text equals list box dot column zero on our list box the column first is indicated by zero now copy this if paste then txt id dot text equals copy me dot paste list box dot column zero now copy this code and paste here now change the number one two three four five six seven and change the name also this one is txt name this is cmb gender and this one is txt address this one is txt contact this is txt email this one is combo department and last one is txt salary now end if and if run this code now when you double click on this let's see arcs yes here also yeah yes yes yes that looks up now i think it is okay now no now you can see all the details if you want to change the department yet sir then salary 60 000 and you can click update and data will be updated the only thing remaining is the coding for this so form button this button when you click on this button we want our form to be displayed for that we have to assign macro close this form then click on insert module and we'll write code here sub user from then type here user from our user from name is user from one dot so save it now go to the excel file now now right click on this form click on assign macro then click on this user form then click on ok it is taking time don't worry it will work now when you click on this show form now you can see if you want to add the data 1 102 here we write ram chandra gender mail address pokhara then save we can easily save if you want to close click on the exit this is the complete tutorial of the excel vba now keep learning have a good day thank you

Channel: Er. Pedia

Views: 42,064

Rating: 4.8857141 out of 5

Keywords: excel vba programming, excel vba userform, excel vba form, excel vba projects, excel vba tutorial, excel vba code, excel vba button, excel vba course, Userform add data, userform search, userform update, excel vba full tutorial, easy way to learn excel vba userform, excel vba, microsoft excel (software), excel project, userform reset, userform delete, vba programming tutorial for beginners, user form

Id: M5Zcsz8zPVM

Length: 57min 21sec (3441 seconds)

Published: Tue Nov 10 2020