How to create an Employee Payroll Data Entry UserForm with VBA in Excel - Full Tutorial

hi and welcome to excel vba data entry form for employee payroll system with user interface now let me show you guys how this works right here we can search this algae money search for any of the family let's reset the whole lot I'm coming here one good thing about this program is you can use any of these criteria anyone to say for whatever candidate you want let us say we want to use it in a city with and so I'm going to just select any one here randomly I'm going to go for three 345 pounds and nothing does it right here every one of the selected I know we have two now let's see and the one we just selected that belongs to Natalie bills okay we can just change it just from the inner city let's assume on select from the postcode let's how to do at any of those people school when you just select maybe and 0 9 nice I see a sidewalk any of this color this is actually as a request after request of one of mine you are she would actually want to be able to search these again she's actually a member of the channel so we have to put something like this together okay now one other thing that we can do is we can add as you guys can see this as you change the details of this Larry Coleman let's change it to what's the Santana Montana let's check the post of some tournament label and let's say the fam he's working for is some tournament a nice male okay let's check the wages here all right as the city waiting allowance [Music] so I just need to do is to come right here we will see odd ladies click on that and you see you see the changes are taking place and if you go straight down we should be able to see the details of Santana Montana but if it's not there does it does Santana Montana if it's not all just need to do this click on add new but something Hamilton is already there anyway that's fine we can delete whatever I wanted to make it seems to have a lot of Santana mature that's my left we have there okay so we can also print everyone's okay and exit the choice is yours so what I'm going to do now is I'm going to take that straight into Excel development environment I'll put something like this together for information is going to take some time or for those of you was killed at designing the interface how you want you can always keep that it's going to be a table of content for you guys to click on at the end the description area so let's have a go at it now and welcome back to excel development environment now let's keep this short okay okay busy over that is it all right now I'm gonna save this lets beautiful I'm saved and I'm going to be saving it as I'm right here - where does the director intend to save my walks changes you see we will have save time hours and then change to Excel macro a neighbor walk okay make sure - Mooji select see they do that the previous ones are they so now if we go to the Developer tab here and that's one thing because I do have some new we need to do is to go straight to file now you select your options right there now go to customize ribbon sorry customizing Developer tab is right here okay it must be checked to make sure it is checked okay confine Developer tab here come to popular command all the select any of these maybe all command you should be able to see them develop the search tree here you see it and once that is checked you click on OK they will go Developer tab is right there now the next thing you want to do is once you have your Developer tab click on the tab that you just click on shoot open up all of this menu select baby oh you can always click on out f11 so I'm going to click on that and that's it there's nothing that you can see okay now to get to our forum in place I'm gonna go to insert look at it right here click on insert are the phone use a phone and you see a user forum automatically appears here look lead now that's your phone we now want to modify your phone to how you want that is the properties just I'm gonna click on the properties that the properties are I did before I want to do is to take care of this phone to change the size of our forum to 700 the height to send now the width I'm gonna change let's see I'm going to change that to 1000 and now that's the way that is done this Dargis idea which was before right and that is it you see that is nothing [Music] now now [Music] now we can change the size of that combo box to allow for Tina's well made that food and it takes 20 time going to that combo box I'm going to enter let's see check I believe the assistance I'm gonna enter female and here you see the level here we'll change that to gender so we need to construct the caption now done for us to complete the the data that would intend to our today's combo box let me give it a name that sometimes means changes of CV gender okay so I'm going to now double click on my phone why the phone initializes I want you to complete the rest of the beta double click on that that is the form now but I don't want to plan out using the event click I want to use initialize now on the form initialize I'm gonna say see me oh I want you to add the following to this ladies first there so if I run the program this is what you guys get so you have a choice of choosing whatever you want but that let's continue now here in there the caption will then change the caption to employee the reason one showing this is because you guys need to be a pro - yeah VBA programming so I just want to give you the opportunity of understanding how to put together an interface but I'm now going to be able to complete it analysis we annotate too long so let's change the next one to address and this one is a change to that's a bit cold and now the name of each components the very first one okay I'm gonna call that CXC employees names changes to t FC the txt that represents textbox CXC employee name and this is going to be t extreme and dress and this forget exp was good they this already has me them now the next thing I want to do is since of taking care of this one I will now phrase let's create something for my using for the dates that I intend to create this one side first I'm going to add the frame and in there I'm gonna get rid of this text content now in here I need a label label and I need to warn to you see this is the one the second one I'm gonna change it look everything of that I will change it to label the text so it's now white and the background is white text contents let's get rid of that okay they will go I'm the next thing is you see the border I'm going to change that to single day Louise now we don't have a black line around eight okay then the next one component let's copy this text and just drag it to you there now this is unchanged move to CB cm cm game search widest Xbox should be known as txt text box session done so this very one is going to be for date so I'm gonna change that to LPL date and the content in a down paid dates this is meant to be that's not it is the name like day yeah that's taken care of all right forty did for us to be able to see the date what I'm gonna do is go back to the initializer area so first infest let's come up here I'm going to the class on variables but let's declare it the first thing I'm gonna do is I'm gonna say option explicit they're my very first variable I intend to declare I'm gonna call it team this state that's it once that is the player the next thing we want to do is to now enter the datum then when you say tell detail the data entering that inside my initialize it don't function equals format you have to format it else you will end up with date on time so for mom's date they will that's that's all there is to it if I run the program now should give me today's date that is it okay now that is how I'm gonna put together those then the next I want to do now we have to speed up the development of this interface else it's gonna take forever okay so right now it's taking down long now the next thing I want to do is I'm gonna come in here less odd frame I'm afraid they're on this very frame I'm just gonna get rid of this I'm inside this room I would need three of these coffee like this see that ice and then underneath I'm gonna need just papi disappear that's what they change it all around and select all of this reduce the size of that talk that much now copy this again and I need one more this year right now the next note to be is a little coffee let's copy this one I'm sure and increase the size of this and I'm gonna have to reduce this because just now gonna be enough room for my daughters that much and in here I need one more what if I may the leads right and one here okay increase the size of this yes right yeah there is some buttons so I'm just you know copy it is very but in there let's get rid of go to the property where entities drop it is very bottom grab I need five more of those all my business in place now let's change data these products all the odd wages this is now for each of these nothing's I'm gonna give them and look at the exit I'm gonna call it CMD exits so we repeat the same thing for the revs now let's see right underneath here I'm going to add okay that's how it's looking now okay let's do one thing I mean to select these the list box let's give the name so I've changed up okay almost II display okay look for this list box I just wanna show you one thing before I then speed up before design of the interface so when I run it I want all of these data that I have on my spreadsheet I want to display look at it just looking beautiful ready so let's end that so I want all of these dated here displayed on my list box let's just new a two lines of code so let's click on the form itself right on the video maybe my list box dot columns let's a column comes in okay I just come down we have [Music] this box again dot we want to say which source - we go south equals ready stuff from column a 1 up to let's see this where our ends should be I'm gonna go for W that will be W make that 60 5356 and that's it so if I run this now we should be able to see those data have then I'm now going to just speed up with the design of the holding of the case and I'll get back to you guys shortly and there we go guys I've actually finished up with a design of the whole interface then we can start work with proper programming there's nothing happening here as you can see right there's nothing happened here so come right here if I let's start work with exits don't you see a lot of people out they always prefer exit to be unload me you can just enter I supposed to say you just lost everything that's why I prefer using a prompt something I'll prompt you and ask you to come from do you really wanna exit so let's enter it prompt in then first of all I'm going to declare the variable call it I exit even coreos were dividing like I exit us I'll then call it I would be a data the type is the type is called leading message VB message box results writing the next now I'm going to do is I would now ask I exit very cool on this you guys should be able to see it I ate it equals message box and this error message box I want the swell enter mind prompt that we questioned you if you really want to heads it confront if you want to exit yeah close that this takes about four argument zero album after first argument and my second argument is going to be something let's say a method are we actually ask you to confirm or a question Mac sorry that will be the icon enter applause sign man this time around I want to enter close or count enter VB yes or no then enter a comedy rights the next one here is range be mine is going to be the name of my project so does employee pay room then if you look at it in total I have for argument argument one two three now I'll then use an if statement to validated option mate if I exit yes if I really program this watch in genesee click on that you see is asking me to confirm confirmed if confirmed if you want exit so I need to correct that yes I do of depth now let's go back in here this one is going to be for delete don't click on delete and inside it delete I will also declare a very good at the delete button all day so this variable is going to be an integer that we actually check my my worksheet to validate wish let's just call that I then I us integer because what is counted as 1 2 3 and so on skill set was counted as 1 2 3 so I'm gonna say as integer and I'm gonna say I equals 1 to the range and the range that I'm talking of less meter from a sixty five thousand three hundred and fifty cents and open the bracket and stop start and I'm going to say minus one now let's say if ls3 display God's elect we selected that is I then Rouge plus minus one select an eye condenser selection dot delete and if so that is that full to delete there okay that is fine so I'm going to try this out and let's make sure it works properly but one thing is if you guys notice I'll start from one if it's zero that means the title you can delete the title and that's why some bronies let's see I have some duplicated takes a look at that look Steve let's see have about three less oh alright okay next one yeah another one now let's even see I have 3d okay I have Luke from 10 11 12 now let's delete some more minutes my deletes that is Mythili do I believe you guys can see it now delete they're going to see that now the last one let's delete another one look at that look to come in here does the delete button this is the exit that's it I wanna delete there we go see the delete is working so that's fun and that's one of the reason I'm going to write protects this work boots okay that is fine now let's take care of resets so research is to take your follow this okay with the reset and we can tie the following lines of codes so that very first one is going to be THD I say address but X equals then I will do the same thing for the employee so let's let's take off now thanks course and so on vice just pick that up and right there those are the lines of clothes for my recent if you look underneath here this is meant to be for my own dates and these are for all the other text box that I already initialized with while using them and that's all there is to it here I initialize the name of these were cocoa puffs go ahead he said Chris new now say CB tax period so let's double click on the form itself and inside we we have initialized let's take it right underneath there I just want to enter the the period I've got odd the very first item is going to be let's make that one and the next one is going to be two since we have 12 months in a year so I'm just going to copy and paste can't change it around that is five six and just copy paste them now change the numbers and those are meant to be the tax period period that's correct this is eggs and dev'reaux so that's fine so if I'm running this is what you will see there okay let's assume I select a value in there and this very one mother can't let's hope our chain that as well I haven't say the valley the anti near them instead of it aunty I will change that to female so let's go to gender sorry under resets the conductor let's change that to female that is the default the default input right that's fine so let's go to the next one here let's take care of this easy one double click on trend so with the Prince our entering the application dots dialogue and what is supposed to be text download and there we go expel dialogue print setup they would then up City Showdown - you show the dialogue for us and I'm going to say I want you to clean this workbook drop the sheet and open up specify the number so he's going to fish it one close that dot print out okay we want you to print out how many copies is good for one day that's my prince taking him now let's take care of one - these aren't new and always copy dad's yeah okay we do add new let me enter decidir dimmed with my variables on to declare now I'm gonna call that wks and I was also going to declare something for the range I'm gonna call that add meal range now then set the workgroup set worksheets equals that I'm working on which is cheat one I'm also going to set the admin because worksheets dot range now what is my range I'm gonna start from cell a2 dead 65,000 356 then close that end and let's get it off stage now that that is done let me make my components play add the following components here I do dot sets and where will I get those information from the very first one is going to be C value and that is the name of that one is going to be txt employee thanks now that's very small what is the name of the next one the way up arrange to see I'm going to go okay address so let's go back to our new dog click on that the next one is going to be address copy I have 23 it goes five I'm just changing okay us wanna trade to talk now let's change the mains around this one is known as address and that is going to be 0 1 the next one is known as postcode and that is 2 this is 3 and 3 is gender of see gender number for that is going to be lvl Behram no pain reference does option right so another place so we just pick that up and get back to you guys okay those are the lines of codes for the odd news so which means if I click on a very button whatever that I happen on any of these components will be valid straight onto the spreadsheet so I'll good look at it take it from up here so that is it so now let's take care of our wages so double click on the add wages now with the odd wages we need to declare some variables so let's go right up here I'm gonna start by declaring as follows there's a Dean in a city ask about the next one this basic salary or basic fee there will go over time tax and so on I'm dead okay now let's go back to that very bottom and double click on it okay so what we wanna do is first in first I'm going to let's say LBL pay reference dot caption and that I'm just going to use that for my reference so the reference has been randomized they say evaluate between the surrender Rand between let's pick that one town - so that's fine so which means when I click on the Add button I should give me back you dare look at that click on it again then okay let's go back to our code exit out now the next one is going to be that variable called in a city in a city takes values should be lowercase K not city this game takes in what everybody have inside in a city next one is going to be over time equals GFC thing is just call over time DuckTales yeah that's fine now the next one is all the pay as equals two txt auto pay right now now austere comma let's go for two and that takes care of my decimal that's fine no worries what about the tax tax equals we need to add up all of these from this paste it right under the tail of the bracket paste and I'm going to just make up whatever P go for other times so let's say multiply that by nine and then we divided by 100 always left to double tops now that is good Alice take care of pension pension equals let's grab everything in here equals taste and in the case of pension let's multiply that by 12 pick in my pension so that G we talk money actual retirement age okay that's fine now supposing if you have a student loan so later student love me wait a mintue hi okay student loan all right it was all of these as well we multiply that by the seller 5% of your Muji's divided by 100 come right down right and then let me know that would be your an item and a number is like your social security in some other country held and I'm not sure insurance number - for your house which is very good what a plant about three so I just made up this feel as you could be copy alone okay all of these percentages meet them all now those are all your deductions okay now let's get everything straight in today different types box right soon be lvl 2 halves don't caption and that is going to be equals all of this and because it's the types that want to attain them and grab this and paste it right inside the times they - well copied is a locator sent in for my pension as pension student loan ni so let's grab this copy as patient and here the next one is student taste I missed that and today okay so this should be walking now for now so let's see if I'll be able to okay coming here inte R whatever value all right I'm click on odd wages there we'll go look at that that's fine yeah it'll be good now we need to work out the deduction and all of these deduction will be subtracted from this one to get the nest net with net B so let's go back in here we are almost there now press ENTER now deduction so that will be lvl deduction dot caption he goes as follows let's see if we can take a shortcut she'll be able to but who knows I say equals would not the brackets now if this year all of my deduction I'll carry out here records let's see his true walk in the world so tax plus pension Plus so Dutch now all of that I'm a Buddha in a project okay that's what I think now let's be good I need to delete no now need to deduct what about we have insider deduction but let's see let me rule this is my deduction let me run it yes enter somebody has three meter for my death yeah good deduction in there now let's take up this from this okay that will be met be right underneath movie that caption equals let's grab all of this now we're dealing with where is my gross grab them now replace that with this and you don't need to - this will teach you okay let's run it and see let's just enter some value right that is fine now I need to work this out so that is going to be the period come right down here this declare a variable is called period and this CB oh right so we want to use that to walk work out the taxes and so on pensionable pay adults caption equals penis as equals these ver 1 multiplied by myself is that this whatever we have it here they want to multiply by period by period what goes here to plug it that's fine now what is when I need to do these possible Hey so let's write now you see this this calculation depends on how the tension on it the tax is calculated in your country I just me down I don't know might be wrong so make up your own thing do your own research okay that is my odd wages so let's come right down here that is it let's run it and see now we enter whatever India does anything all right click on add there we go we have 0 here because the amount is 0 if I click on this and enter the 7th month which is July they would work so since the beginning of the year the tax you've paid is 3000 plus your pension so far it's five thousand rows that's good and so on that is AIDS that's fine now we can always click on add words we just need to take care of little you 2 things here and finally we need to take care of these they're important so let's come in here I may have to speed that up double click on okay time is against me so I'm gonna double click on that and right in here those are the lines of codes for my stash now what are the component onto sesh I will ask you to leave trim spreadsheet one dot cells that is the eye for the folder that I have in there from 1 is not equals to what device trained check on my txt otherwise in dirty X's fish tanks ok then let's do that up here right so we have to specify they will want to search from so right here - a txt employee name things equals what if I have a my sheet dots cells hi thankee so that means I'm only using I'm only using just mine and play name to sash what I want to be able to search any of the columns so what I'm going to do some just gonna copy these and change the names around okay alright and that is it for these sesh I just have to speed this up because of time if you notice I ended up and trained not equals two data stream is meant to be equals so that is the lines of code from here because I have 23 components on board and does it and here those are the components that are meant to be searched then you have to run ugly functions use there okay so let's take it back up so that you guys can see it dead the names of every single components and how this components are going to be searched using massage function massage object component like I said I have 23 items all right that is it now I just need to do now is just to tidy up you see when I click here I want that to disappear so less in here text the text and supposing adding a tiny T if I decide to leave come in here if this tracks empty that is that's done I should be point zero all right so I just need to do that for the others that's not good all right so I'm gonna repeat exactly the same thing for the other so you know come in here double click on the next one and I'm just gonna copy this copy change this one too and change the events to enter Moscow and change okay that is for event enter event exits reads if you exited there's nothing there MZ does it I just change everything to zero then I'll just repeat the same thing for the others so what we have when you go in here there click I condemn enter something if I leave there click elsewhere and so on okay so I'm gonna speed up do they last you and get back to you guys that is the guys all down so we will now try it out and I've also repeated the same thing for the search function here just one just one in vain for that okay so let's run it and now anything [Music] anything 860 that's what is all about [Music] [Music] things change okay folks okay and thank you guys so here you can add so let's assume we want to add something in there in here we'll have red fox day so I'm just going to change this red fox let's change that name to exchangeable scott around what else do we need to change this we automatically change okay they're walking the same thing petroleum I mean in the period two seventy seven and let's increase the wages here to five hundred changes as well they'll go then so we just need to click on total and look up here rule eleven so let me click on toto before odd odd wages they will do now let's go into we now need to add it straight on to the spreadsheet so this is the odd look at it I'm gonna click on add row you can see that here to the Montana's right there as well now the last part of this project is let's make sure nobody can add any data on to the system okay - to achieve that what I'm going to do is I will first of all go straight today wawk wawk boom here see this one just hard one here they're one big motive as big enough for you see that so let's come straight to the properties and just change that one so I'm gonna call it CD okay that is fine now we also need to change its font in to run the chess selector let's come in here and change sure I'm gonna close that don't click on it when you don't click on it all just need to do that start taking job now let's go into now go to the tools right inside tools you see where we have VBA project properties click on that and you see these two tabs the general one and the protection one you can enter whatever you want you need the description and so on but I'm gonna go straight to the second tab you see on the second tab protection you see we have this checkbox not proved yet for viewing click on that and here whatever you intend to enter your password so I'm just going to enter employee now without come into this workbook we need to change the events drop this down change it to workbook get inside workbook enter the following lines of codes sheets under sheet is number one and that is my password and low enough and that's all there is to it the file let's save that and also exceeds when I open the program my function open up this one so let's close this and try to paint some data into anything in there [Music] but unfortunately you will be able to access this form straight away so to be able to be able to access the form come in there and just enter your codes so this prevents anyone who just want to mess around with your system open it face there we go okay that guy's eye McAuley the end of this tutorial I know it's a little bit long but believe me is an interesting one I like it myself so with that I'm just gonna say bye for now but twice you subscribe to my channel and you can also join to become a member on the channel I always say there's a lot of benefit for you you get access to all of the codes y'all have a nice day now bye for now

Channel: DJ Oamen

Views: 3,857

Keywords: Overview of an Excel Data Entry Form for Employee Payroll System with a Userform, Excel Data Entry Form for Employee Payroll System with a Userform, create an Excel Data Entry Form for Employee Payroll System with a Userform, Excel Data Entry Form for Employee Payroll System, Employee Payroll System created in Excel VBA, Employee Payroll Tutorial created with Excel VBA, How to create an Excel Data Entry UserForm for Employee Payroll System using VBA

Id: XBfEA6GZA2A

Length: 77min 10sec (4630 seconds)

Published: Fri Jul 17 2020