How to Create an Excel VBA Data Entry Form with Update and Search Function - Full Tutorial

hi and welcome to Excel VBA data entry form with sash on update function let me show you guys how this works right here let's start with the spin button navigate using the spin button okay we can print if we intend to do that click on print I'll select the digital one I'm gonna save it straight on my desktop and there we go that's my PDF formats because I don't have a printer on my system just close that and we can decide to enter new data or even search let's search for any better in here let's look for yeah let's look for 7 to 3 and search for that particular customer oh yeah delgo does it Tommy Lee Jones okay I let's search for someone else let's just poof or maybe 1 9 0 0 d he the search button they told us larry roye okay we can exit if you want switched I don't intend to now are we can even were set so let's reset all the always let me show you guys wanting if I double click on any of these little click on dynamic alter they will go dynamic on that we should be able to spot that right you look at it dynamic on theis officially selected on your on your worksheet for let's double click on any other one let's say 14 - that's 14 Johnny there okay I select Paul Parker Tony Park okay and let's see Tony Montana and so on that's how the double-clicking of the list box works now let's reset click on research you see the reset actually wipe out all of these information there wipe out all of the selection we can now come in here let's enter new reference number I'm just gonna call us and it means these Danny they say Danny Boy okay address it's called Arjun Singh right telephone number date registration date and form of identification I remember okay then and poor sport they're all just need to do is to click on hard there is already it's right away or transported trial develop that is its and it's also added Danny Boy straight onto my system if you double-click there so this is just an overview of my excel data entry form with a search and of that function ok guys let us go straight into excel and start a completely new tutorial so I'm gonna exit out yes and close all of these now I have you sheet here that I've never used okay for those of you who might be new to excel development environment using use a phone the first thing you need to do is you need a data I have a link to this data on YouTube and Owen just the raw data so that you can practice now what you need to do is to make sure you have your Developer tab if you don't have your Developer tab right up here the menu all you need to do is to go to father select options right inside the options come straight to where we have customize ribbon so like that you need your Developer tab you should be able to see it right it you can see just search for it yet but it's always here anyway once you have the popular command make sure the developer Todd is checked and just click on ok there once you click on located developer type should be displayed out there now if the system would be something like this click on developer tab and the next thing you want to do is to select the visual basic menu here click on that okay now the next thing is you see the insert click on them set and you select user form and you should use the phone I like you to change the size the spoof of the width we move this up so that you can see what I'm about to do and in here and we'll check my weight to 1014 for there and that is my width right there 1044 then the heart and come in here we change Heights to about now the background is coming here you see we all have the background to change our background cheese sent in a little bit greenish all of these it all looks a bit darker now we go straight up here make sure your farm is selected because you need your toolbox if your toolbox is not there does it right here just click on two loves I'm now gonna select frame the first frame of the Buddha yeah drop another frame okay I'm gonna need one up here and there's gonna be one in the middle yes about that those are all the frames I intend to use and these frames would then change the color let's change the fabric around is too so hold on to the controls the light time go straight to the background here just wanna say like something a little bit lighter for the background of those 10 frames they just wanna pick under that's good enough for me right time the other thing I'll do is just get rid of the text content here you see where we have frame I wanna make sure it's selected I'm just gonna try and delete that so that is it right here you see the caption and this is now on this very first frame up there and we'll add as follows I need four texts for labels there so let's go back to my toolbox drop one label there then I also need a text box so just gonna enhance them and use them to populate the rest of the frame display belt I'm gonna change the size of that to 16 bold not just gonna bother change the font attack detects the text box now the board 14 there we go and I'll just populate it just use this to to populate my frame hold on to your control click and drag okay also need about I made a gamete tree somewhere here and I'll need an order free here and I'll have a label here somewhere I'll label I'm gonna call that mine let's just copy one needle or use that for dates you're right so you need to the strength is that much I see yeah right okay now that that is done let's name all of those complaints this one is going to be known as reference number and this is going to be gay let's name this one oh there's no room for that so let's move all of this here so we have enough room for all the reference number and the others yeah that's more like it and the next or this one face so that should be txt a reference number so I'm just gonna call it is txt Ref yeah the next one is going to be so our first name will come to the actual chamber the first name and the text beside each is gonna be THC the first name so name text blocks CFCs Selena they will and this last one has going to be my address so let's just drag yourself up to deer and this has been to be impress there we'll go guys so I'm gonna repeat the same thing for the rest and oh this one let's just change this I'm gonna change is to put the box where's Prabhu box yeah chamber 2 combo box because that straight to be for proof of ID yep and what else and these all are to here I'm gonna delete those and just copy these across okay I'll just speed up the development of this friend walk and get down to you guys okay let's let's speed up what I'm just doing is giving them a name okay this is going to be let's say that is telephone and so on okay I've finished be just the design of this very first frame there so this other one here let's Ahmad the listbox should click on the list box and just drag it all the way yeah that's fine and I'm gonna give it a name and just call it tell HD display okay and we'll add some urgency I think I'm gonna need about so let's do four seven yeah there's gonna be one and let's see maybe settle for about 26 little baby heights 26 and which I wanna make - maybe yeah let's do 400 as the booth they're suppose you guys can see that yeah 100 yeah that's fine so the font I'm going to make that 14 bold and this very first one that is going to be my let's just call that D and it turns magenta it is going to be me so I'm gonna copy this whole don't you control click and drag copy the next one this is going to be Prince and the Dame now I need this both stretch in here we left is that will be a devil my toolbox you see I need this this paint let them drag that and that's gonna be about hundred as well that's my spin button and a couple of this one here as well to be updates research let's call that updates here and is very one I'm going to call that reset let's go get this one with deletes this will be known as fish and one more so with a sesh we don't have a taxi I just go oh let's buy the text box let's copy this it should be exit I move this here that's beside exits chain doubts exits right there's been to be a text box let's go get text box paste it right in here nice Melanie my sesh just beside a search button okay so we can change that that's before in the 24 and 25 okay now here I want to add a label here so that very label let's just copy wanna just leave a copy and Dumpty true I did and I will chained about lon - yeah green right up there I only text I need I'm gonna change that - okay let's change the font size it 26 and text and air text column which ain't dancing yeah that's fine I let's just change the contents while we're here now change out to Excel data entry form with search and update function okay let's open that up there will prove us all right that's taking care of now here let's take care of this I'm not gonna do anything just yet but let's come straight to the spreadsheet you see these various spreadsheets okay I'm gonna select from here roll number two all I just want to do is to give it a name so let's roll number two okay all the way and I've selected to the last row that I have in here okay just from number 202 the last row you can also do that yet coming to format and select manage name but you can also do easier ok so right there there's been attacking records that is the name for this rows that have selected press ENTER so I've given anything min so the name is call records okay now if I select it all again let's just select that and come right down here we should be able to see that named a look at that now let's go back to our visual okay now in here make sure this is selected once is selected come right down here the Faustine let's go straight you see where we have and when you expand this a little bit see where we have enclose this right now where we have Coulomb count because I have 10 rules here if you count it I have 10 in total right I'm now going to 8 Coulomb count is 10 allow heading okay Coulomb header instead of force I'm gonna make that tree dead okay now you can see my head in this showing now the next thing I want to do is you see we have column width I'm going to just enter in there 1990 1.6 because of the size of my system so I'm gonna say comma so you can also write night one point six pixels but if you write night one point six which is semicolon that's fine because I have ten of these I'm just gonna copy these and just paste it let's copy again and paste so I have the little last column so I've set the size of each column then the size that I'm gonna change be the column number four which is for the address you will see the reason why now okay that is that done I'm going to scroll right down you see where we have row see here roll sauce okay we must enter this very name the name without any records see I've just copied our name so I'm gonna go back to my okay now that I'm back in my visual basic development environments you see we have the row source piece that in there you can just type it now right above it right about the road sauce don't ever forget that we have multi select I wanna change that - multi-select eyes if I hear there we go you see now like I told you guys say later on look at the a choice if I run the program now this is what you guys are gonna see you look at that beautiful but look at the address that's not to rock the now home for the address I will still have plenty of rooms here so I'm going to change when it changed the address addresses number four one two three four select your list box come right down here see column width one two three four so I'm gonna change that to something maybe hundred and fifty and let's see that there do you see hundred fifty so we don't run the program again and let's just see how it's gonna look like you say we can even add maybe five or ten more you see that's funny I like that let me order let me make it 260 and see what's been happy now run it yeah 160 is now bad so we can see the address pretty much so that's its that's why so we then need to now do is to use talked with some coatings so the whole interface development is ready so we just need to do is a few lines of code and get everything rolling now let's start work with the coding so the first one I'd like to take care for BD exit let's come right in there and dads and I'm going to double click on the exit button the name of the exit button is C and B exit and before I even do anything else let's go up here and hit enter option explicit there so which means any of the variable that I enter in there it's expected to whoever's using it so they're now inside my exit button right here first of all right underneath yet let me just declare the following variable I'm going to call update roll okay let's say us integer right and the next there if I'm gonna declare underneath there I'm going to say them I exit as dalip I think this one is meant to be bb-baby message box message box results this is Excel VBA working on all right so right inside my exit button as the exit button again double click on data declare two variables so the first one that I'm going to make use of is the exit one that I've just declared in here so just copy that because that's in here unless say the variable I exit equals message box message box and the message box I'm just gonna enter on a fan if you want to exit it [Music] close that up that's my very first argument so the second argument is going to be baby question so for questioning and just add another argument let's say baby yes or no that is true we have one two three arguments comma the foot arguments would be let me just call it data data entry form so they'll be the title so now let's come in here and just come right underneath and say I exist equals VB yes so if I exactically be less then this is what I want the system to do unload me there we go then let's just say end if and that is already exits so the good thing about this method is just when you click on it the system actually prank you confirm if you want to make that you see those are the argument DBS or no one right appeared as the title and that's what I entered in there confirm if you want to exit with the question mark the question mark is right here that is where we have let's open that up and that's where we'll have VB question so that's taken care of now let's go back here the next one I like to take off right away his new girlie prints that's a very easy one to you so double-click on the print and right here for the print I'm jasmine enter application yep the Daleks open up the bracket unless selects X dollar printer setup X dialogue inside is various metadata its dialogue intercept oh yeah extol of dialogue printer settle close that dot sure so what will happen is when you click on the bottom this dialog box we should actually show up now you can then say workbook that sheets with a mess and that will be my one yeah that's the one I want the system to print out for me haha Oh No first I'll actually dot frames print out copies okay then uh Coulomb equals one and that's all for the prints so if I run the prints this is what you guys you see let's run the program when you see that click on print there we go I can just select whatever you want to me I'm going to close it up okay let's take care of the Magnum button so I mean two exits unless double click on the add new button the first thing I'm going to do there is a man declare the following variable deal let's just say WK test so that will be for worksheet ours work let's just go wash it put an s there yep now I will also declare another one for the range of data that I would like to also let's give that a name I'm going to call it odd me a strange right now the next thing is to set the worksheet wanna say the worksheets are sheet 1 okay when I say sheet 1 I'm talking of these very sheets here this very one so let's the crystal agency does it this very sheet here so I'm gonna set wk as ours sheet number one so minimize that come right in here now say wks this one the wks is just searching for worksheet anyway it was the set need to set it face equals number one they would now and I want to save our deal does everyone here I've new copy that Vista to you and I want to set that against ships one is WK s dot range and that range I'm going to just go all the way I will say about a along a up to 65,000 356 okay I should do dot and open a bracket unless length it's L up that offsets okay that means when I enter any data just get it off straight to the next row alright now add me yourself dot offset what do I want inside the very first column the very first column is zero comma zero what I want in there is the following value and say the value for that is going to be txt reference dot txt so that's the very first one and I have ten in totals and Jazmyn copy this and just paste it alright copy paste paste so at the latest to have chain in total so the number here is a to the 1 2 3 and so on we have 8 and 9 so now let's change every single data we have on this so that's gonna be first name okay and the next one is gonna be so name so let's speed up of us well okay that is the taking care of that is for the odd but I've saved let's just increase it form so that you guys can have a good look at its I'm gonna make that maybe about 12 yeah should be able to see what I'm up to now okay so that's what print art and right below it is exits there now before I go any further to be any on the safe side let me say this so I'm gonna just call it baby and oh let's say think so underscore sash underscore update yeah yeah underscore function so that I know which one I'm dealing with desperate now to save that you see the save type we'll have to change that to save Excel macro enable macro and ever walk book make sure that is selected we are going to save that in my own directory which is on my desktop here so let's say it's right in here safe yeah okay that's taken care of okay we can even test it out but the thing is the combo box has no data on the combo box with us that's no problem let's test it out yeah let's test it out kaitlin's palm right down here and here so I'm gonna give enter some data India let's just say reference there's a 3 0 4 right and the name is going to be Tony Harlan okay and the address is 79 and fallujah says 79 Afrobeat yet Afrobeat Road okay and I'm gonna call in the air telephone on goddess eyes and tried following and right you have a fabulous entire CTV and certainly this yeah that's fine and the date let's make up a date this week all that 3204 2020 right here is nothing so I'm just not giving me title there so let's just say pilot yeah pilots and membership type we can call him and he's paying as follows it was good there we go all right as you can see that's my last record there which is duplicated with this so I'm gonna enter this but when I click on our this is now when I appeared it will appear on my worksheets here so let's click on art ok I've already and tied it in there so I'm going to now takes it out so let's just be sure is there anyway so look on that twice yes exit but the problem is it's now here you will see why it's not here now so exit so let's come into my walk book there SPRO right down we should be able to see Tony Allen there with your finger can see Tony Allen look great that's Tony Allen right there a why don't we have Tony Allen on day on this very sheet here under list box we don't have to Neverland day you know why because in the name right here where we have four months you see name manager the name manager is only up to rule 45 and Tony Allen happens to be down rule 46 so what I'm gonna do is I'm gonna increase the role because I'm going to have more so let's just change the whole chin to 60 all I can make it let's say about 2000 right that's fine okay 2000 yeah all right so Kavya click on save so we'll have it up to 2000 so if I click on text look at it okay it's going to the old all the way up to 2000 see that still going okay so that's is that done we can now close this close you wanna save save that now let's go back to our interface designer run the program now and if we screw right down will she be able to see Tony Allen details here let's take it back up this road gently that is it does look at Tony Allen thatõs a cat so that's is the solution to that when I was taking care of listing of deletes reset update and these other ones so am I have to try and speed up most of this so double click on delete now inside elite I'm going to declare the following so let's say Dean integer I'm gonna deems something as integer there copy this just change the variable name around is that since I delete so the variable name I'm just gonna call that I shame that too later I okay oh that's one gender - I okay let's use a for loop for I equals 0 to range and the range okay Linda she will change it to yeah yeah I'm going to change your whole lot 260 17,000 let's make the range 6 to 7,000 okay so let's say for I equals 1 to 65,000 plus okay so instead of 1,000 or 2,000 can always be six to seven thousand and and US base as well appeal of those just dump it in there Unchained is to roll roll minus 1 now if LSD display dot select dots selected I so if I is selected so I'm now talking to the list box if I select anything from the list box this is what I want the system to do okay then I want whatever that is selected I want it deleted roll let's plan like indent this so we can just see roles I plus 1 dot select ok and whatever is selected which is we will refer to that as selection dot delete and if and we must next I as well so that is taking care of X I and let's indent this and then this that is that taking care of does the delete ok ok let's take care of the reset button double click on reset I mean to have to speed things all the cost of Tom now that is the reset these other lines of code for the reset okay now one important team if you guys notice when I run the program the other time I don't have any data in here so let's get those sulfate so I mean to enter those as form initialize now but I need the name of each of them so let's come right down here so right here let's come up here and choose a different select a different event form initialize that is a Chris table so that you guys can see it and in here okay right in here the following code should be included now take a good look at this lines of codes this includes this now this we are the following data straight into the member time okay and this will are the following for the proof of ID if we come right down here these lines approach here we are the following price for the memoir feelings okay why this you just displays the time can write above all of these three components of them now right below it this is the very wide declarative they're called or debt road this will updates the road street way as soon as we log on because remember the verbal the the event features and is initialize is form initialized okay so as you can see roll was initialized as 10 so whatever we have will be updated straightaway inside each of these when we click on updates whatever so far running now that's is what you guys to see they all have data in there okay as soon as I log on to the system run the system it actually initialize straightaway okay so as you can see the the data that is initialized in here is actually from here that is the John Davis if you look at it because I said 10 another code so what is done is is added the headings 1 2 3 4 5 6 7 8 9 10 so I should actually change that to 2 instead of 10 so you can get a very fast data here called dynamic hunter so I'm gonna ads it out let's go back inside form and initialize and scroll right down here okay instead of 10 I'm gonna change that to 2 okay so we now go run the program look at that dynamic on today if we change it to whatever let's go back in there if I change it to something like 7 let's see they're running 7 is turn Jonas let's eat and Jonas devil to be number 6 yet if you can't 1 2 3 4 5 6 7 ok let's take it back the 2 2 is logic correct I want to put two in there so that is to do with the form initialize okay save that back in here now let's take care of the spin button and they will produce are the lines of code for the spin button if you look at this code properly studied you will see that it is logically correct greater than zero okay the value of the spin button will be what I have whatever have inside seaching okay and c1 will be close to a and C to write the same thing applies to the orders roughly this if you look this this is for the reference number first name so name okay and we have address and so on okay so let's check it out so that you can see the rest of the codes I have to speed this up okay that's it now let's run the program and see how that would work there we go run it yeah see that that's how the spin button works okay let's have a boards one two a thing that the leads I'm now gonna select anything that will be deleted I might come in here and that okay now let's take care of update double click on the update I'm right here are the lines of code for the update okay that is quite simple is not a difficult line of code orbital doing is updating whatever value we've just entered straight in on today especially Google does it okay that is not difficult so have a good look at the lines of codes now take care of search now this are the lines of code for the search button take a good look at it from here up to here a little bit I had to speed things up because there's a lot of cordoning off there we'll go up to here take it from the top and bring it down a little bit or maybe just move this up a little bit now you can see everything the way it is [Music] maybe indent all of this right here are the lines of code for the session from the darling of today okay one more thing from the resets if you guys notice the research I've also included this this lines of code we actually once I click on research if there's any selection made that will be deselected okay why this one actually place all of the data inside every single text box and this one should be set to play as well let's go back in here you see the list box itself I'm gonna double click on that now underplate that's what I want to use I don't want to use split so many changes to double pick when I double click I want you to be able to select so let's run it and see so i double-click any any data in a rule that is double-click I will be automatically selected on the spreadsheets itself called a worksheet or workbook so let's run this program so now you guys see it then it would look at that let me take it ups here are the lines of codes I declare the following variables print to bring you down odd data take it down one more time deletes we'll have exit here and we have reset from here up to here reset they will have the sesh there's a sesh updates kunal his policies tend to envelope sesh updates double-click on the list box yes peen bathin and finally initialize the form with the full name as a farm in Asia lies and me down initialize it in and there that's all there is to it so I'm gonna grind the program now so I can call it any of these projects yet first in first that we haven't tried out is the swish let's click on that and let's enter 9i as vocalist say seven seven nine sesh they would go look at it Danny boy's is checked for another data and me to go for a zit Tony Parker I'm everyone let's search for four five six eight ah okay as a mistake let's see what will happen data does not exist okay that's 45 68 there we go dynamic hunter okay that is how the whole system works now we double-click for the double click this is how the double clips walks so if are coming here now and I don't click on any data let's see what's gonna happen that is gray small double click and gray small that is it gray small is automatically selected on the spreadsheet itself let's check out 1414 journey they will go 14 to journey and let's go back down Tony Parker you can see it right there Johnny Davis or John Davis so that is how the Excel data entry form with search and update function works so without guys I'm a quality end of this tutorial suppose you were st. George we all have a nice day now I'm fine for now

Channel: DJ Oamen

Views: 73,324

Keywords: How to Create an Excel Data Entry Form With Search System, Create an Excel Data Entry Form With Search System, Excel Data Entry Form With Search System, Create an Excel VBA Data Entry Form with Update and Search Function, Excel VBA Data Entry Form with Update and Search Function, Excel VBA Tutorial of Data Entry Form with Update and Search Function, Excel VBA Tutorial of Update and Search Function, Excel VBA Tutorial of Search Function, Excel VBA Tutorial

Id: r2M2H_akGZk

Length: 54min 23sec (3263 seconds)

Published: Fri May 01 2020