 back to another video. So this video is going to follow on from our last one, what I looked at using or creating a very first user form for you to use in that form to do some data entry. So you remember we created this simple process. So ignore on the left here, this is obviously the VBA window or our actual form we've developed. By the moment, if you click open form, we get this pop up, not pop up, but this user form, what allows us to put in names, let's go Nicky Nels. So you can see I've got a first name and a last name for you. When we get submit, they are then added to this list of names that we have here in column A and B. What we want to do is we're going to build upon this so that actually it's a working or it's kind of like a mini database in the sense that we can obviously add new records. But also if we've got an existing record, we're able to go into that record by simply double clicking on the first name, which will then open the record up for us so that we can make the edits that we require. So there's a few things we need to do to this and we'll try and cover them off in separate stages as we go through. The first thing we need to do to our data really is to add a unique identifier. And the reason we need that is we want to have a unique idea or a unique reference for each record in this list so that we can use that ID when it comes to identifying if we're adding a new record or if that record already exists such as when we're trying to edit one obviously what's in already in that list. So the first thing we're going to do is we're just going to move our button out of the way just to give us a bit more space. And I'm going to create another field so I'm going to do the formatting there, paste it into here and we're going to call this employee ID. And because we've already got some existing data here let's just remove some of this. So let's get rid of these ones here. Let's move Mickey Mouse up to that one there and move our button out of the way, move here and just delete that just to make sure there is nothing contained in those fields. And for our employee ID we're going to follow the format of having the letter E standing for employee and then we're going to start our IDs from 1000 so we'll incrementally go up. So the first record we have here will be 1001. The next one will be E1002, E1003. So I'm going to literally just done that for those first three there just so we have a starting point to obviously start from. We could obviously get rid of all these names and actually maybe we should do that maybe that probably make more sense. Let's just get rid of these names that we've got here, delete, share sales up just to get rid of all that so we can start a complete fresh. And as I said we've obviously just added that employee ID there just so we've got a unique identifier for each of our records. I'm also just going to change this text so rather than open form we're going to call this new record because we're only going to be using this button when we want to add a new record to our sheet. So right there's a couple of changes that we now need to make. So at the moment we know if we hit this button it runs, it's assigned to this button so let's go into here. We can see we've got the open form macro so if we then go into our module where that's stored just double click. You can see at the moment we just have the simple instruction user form show once that button is pressed. So we need to build up on this now just so it'll obviously do the detail that we require. So we'll leave user form show there but I'm just going to add some rows in here to get us started. What we now need to do is we need to identify first obviously what is the next available row or how many rows of data are in our sheet and that's going to enable us to be able to identify what reference number should be assigned to them because obviously we want to incrementally increase each time. So to do that I'm going to go dim I last row as integer so that allows me obviously it's going to store an integer value there and lastly because we're in the module we need to be referencing to sheet number one in this instance. Obviously we're just going to stick with sheet number one but obviously you might want to be cool and that's something a bit more descriptive if you're doing this as a real world example. Dim sheet one as worksheet. So we've got them done for us so we now just need to define these two variables. So the first one is going to be set sheet one with equals sheets sheet one cool so that's that part then and then I last row is simply going to be our friend that we use quite a lot and that is going to be sheet one because we now just define that in that previous one there and we're going to do cells sheet one dot range on sorry sheet one dot rows dot count comma and then we'll do this in column A close brackets dot end in brackets excel dot row. So what I'm going to do is obviously give us how many rows basically of data we have the moment this will just give us the value of number one because obviously all we've got there in column A is one value which is obviously the header for us in this instance so once we've done that we've got the detail that we require and we can now get rid of this user form one dot show and not completely get rid of I'm just going to literally tidy this up by doing it within a with statement so we can do with user form one and we have to have end with at the end here so the first thing we now need to do is ah and actually we can't do this just yet I'm jumped ahead myself what we need to do is if we jump back into the user form and we'll go double click on there so we as you'll notice what we haven't done is we haven't added a third text box what is going to capture this employee ID so what I'm going to do is just pull this down ever so slightly pull our submit button down as well so move that here so let's go into a view toolbox there we go we'll have what we need so I just need to do a first one I'm going to create is going to be employee ID and let's just make that a bit smaller we don't need all that space taken up and that looks about in the right spots and again a toolbox popped over there let's now add another text box fields so I think it's about that sort of size yep perfect and let's get through the toolbox have a look how that looks yeah it looks about right and then what we can do is also with this text box I'm going to make this so it's um when we're looking for enabled I'm just going to just make that unenabled and I think there's another one I want as well and I'm going to lock it so basically all that means is to the user when they open this form they're unable to do anything with this field and that's because we're obviously going to define what that ID is in our code and we don't want the user to be able to change what that number is so we'll now add that field so we can go back into this module because we now have something to refer to um yeah so with user form one so the first thing we're going to do is we're going to go text box three you say it's not available there dot text equals and this is where we're going to define our um ID so we're going to define it's going to be e it's going to start with the letter e and it's going to be 1000 plus our i last row so what that basically means is in our i last row here we found that how many values are in our data and for us there's no data here at the moment so all it's going to do is say there's only one row of information at the moment therefore this is what is our head is so we know row number one is our head is therefore the first item we add to our list is going to be i last row so it's going to be the number of one so once we've done that and obviously it's going to have the e at the fun and be a thousand plus that number uh what will give us obviously the employee number and then the next part we need to do with the user form is go simply go dot show so it's going to show the user form so the first thing we'll do is our user form it'll set this text box three with our required id and then it will show the user form to us as well and that is it so having done that we can now quickly give that a test so module one if we do new records you can see it's giving us our user form it's popped up and you can see we've got our first id populated for us so it's e 1001 and as you can see we're unable to do anything with that field so we're unable to change it or yeah we just basically have to leave it as it is so this time we can go with our adding a record so just go home uh simpson and then when you go to tab because we've unable to disable that tab obviously it won't go to this field it goes straight to the submit button so once you hit now submit it'll add homo simpson to our data and you can see there's something we forgot to do obviously we haven't captured and stored the employee id therefore what we're going to do is just delete this one more time because obviously that's wrong go delete shelf cell so we now need to go into our user form so go to user form and go view code and simply the reason it didn't work is because we haven't defined obviously how to populate column three or column c shall I say all we need to do here is go sheet one dot cells and it's going to be i last row comma uh three so this is the cell reference that we want to populate so we're now looking at column c the what value of that equals text box three dot value and it is as simple as that and what we could do here as well just to tie this up as we've done it the last one is go with sheet one and then we go end with and then we can get rid of sheet one sheet one sheet one and it just tidies our code up and it just gets us looking a bit more structured with our information and also what we want to do is just tidy this up ever so much bring that back there and bring that one back there as well well didn't want to do that I want to do that there you go do that so you can now see it looks a bit more tidy there as well so this time if we go to add a new record here's our user form it's giving us our employee id comma simpson submit and you can see our id is now stored there as well and let's just try another one so this time let's go uh mcdonald duck and you can see that it's identified that we've already got one record there so the next record to add in there is going to be employee id 1002 and there we go and we can see we've basically got our information as required so that is all working perfectly we've got our new field added and we're able to add new records with a employee id so the next thing we now want to do is to be able to edit our records so say we've got Donald Duck here I can see that it's got uh hasn't got a capital D and I know you can go and change that here just by doing that but let's say you want to use the form so if you want to double click here and open the record within the user form how do we do that well in order to do that we need to now create another way of launching the form so I'm going to double click on sheet one and you can see at the moment we have no code entered in here so we're going to be looking at another change event so previously we looked at uh change events for when you first open save or close a workbook well this one is going to be called what is called before double click so it basically means that this event will launch every time you double click in a defined range that we provide so it looked quite familiar um in the first row of this sub routine as you've seen those previous change event videos but obviously it's going to be different uh because it's slightly different so we're going to go private sub worksheet and scroll before double click open brackets so we've got five our target as range so target is obviously the area that we're going to be double clicking in and then cancel as boolean so obviously if you're going to click into that range hit enter and you can see it's done the end sub routine for us as well and let's just move them down so we're going to need to use our last row finder again so we're going to just bring that into the equation so we're going to dim i last row as integer not interior integer cool and we've got that important for us so we can now do i last row equals cells so we don't need to refer to the sheet that's why i'm not defining the sheet in this one because we're actually coding within the sheet itself so it knows that everything is being referenced reference to such as cells is going to be contained within this sheet therefore that's why we don't need to define it just in case you're wondering there so we've got cells rows dot count a so and excel dot row so we've got that informed for us as well okay so we've got that information ready for us and this is literally just going to help us understand well not understand so what we want to do about double click is we're going to only have it working in column a and within column a we're only going to have it working within the number of rows that we have information for so if someone was to double click in row five nothing would happen because we have no no data available to us in row five but if they double clicked in row three then it is in a range that we want to obviously work with so what we're going to do is come down to here and we're going to type if not intersect intersect open brackets and we're going to target and we've got to define what our target is so that is going to be range a to to a so this is a column a part and i last row so it's only going to go the range of a to so from row two down to how many rows of data we have in that range obviously what's going to continue to grow over time is nothing then right cool so if not perfect perfect and we're just doing end if here as well and we can tap in so okay so if we do if there is a double click that happens and it is within this defined row so two and three as you see here what we want to do is we want to hear with user form one and with dot text box so what we're now going to do here is so if someone double clicks within this defined range we want to open our user form and not only do we want to open the user form we want the user form to be pre-populated with the information available to us on that row that we double clicked so we're going to do is go text box one dot value equals cells and it's going to be our active cell dot row and one so what that means is obviously i say we double clicked on download here this cell here so a three is now our active cell so what we want to do when we're populating text box one is we want to say okay we want to populate text box one based on the value in this cell and this cell is going to be active cell dot row so it's going to say okay it's row three and it's within column number one so column a so that's all we're going to do when we get any information the next one we want to populate is go text box text box two dot value equals cells that's going to be the same active cell dot row but this time we want to put it from column two and then the last one we've got text box three dot value before expel value value equals cells and it's going active cell dot row is going to be from column three and then the last thing we now need to do having populated the user form is to show it to the user so that they can see the user form with that information ready to be populated once that's all done we can then remove these and the last thing we want to do is just enter a row in here as cancel equals true so do that and what that just basically means is when you obviously double click into a cell obviously now you don't want to like it's the only stuck in the cell obviously it just moves away for us so what we can do is we do that so you can see at the moment you can double click and actually be in the cell to edit whereas if we do this cancel is true obviously you can't actually double click into a cell that's all it basically means and it just makes it easier using so say you said double click into a cell and there's some information and then come to move away from it you have to then click away from the cell before it's usable again and I probably made that quite hard to understand but the benefit of using this is obviously this single line of code here but if you still want your users to double click into the cell all you need to do is just delete or comment out this row here so let's have a look at what we've now got available to us here so if you want to do a new record we can do a new record it's going to pick up that the next ID should be 1003 and we can call this one a Mickey Mouse six emit and you can see that the record is there if we now go and double click in row six seven or eight or five nothing happens but if we double click Mickey Mouse you can see that it's opened up our user form for us and pre-populated the information available to us for Mickey Mouse we close that and then go home to Simpson you can see that it's now done the same for us again so we're now really getting into the dynamic use of our file the only problem that we have now is if I was to change this so let's go home to Simpson number one and hit submit you can see that rather than overriding or updating this existing record for home to Simpson it's added a new row so that's where we just now need to do a final little update to our the user form in the terms of when we hit the submit button so that it can identify if that record already exists or if it should be added as a new record so we now need to update that code in the user form so it was going to go into user form view code and you can see this is the existing code we have as it stands so that when the submit button is pressed on the user form all the information is gathered it will find the next empty row what's obviously going to be very five if we're looking at it now and paste the information in for us what we want to do is just apply a bit of logic that will say okay well if it's already existing in our range then therefore we want to take or we want to paste into the row number of that existing record or obviously if it's not we want to go to continue on with this new row so in order to do that we're going to add another integer so dim i row as integer and so this will make sense as we step along here so we've got sheet modified and we've got the last row here and this is obviously where the information is populated into our excel file so this intersection here we now need to do we need to have some logic so we want to say logic to determine where to add record so there we go this is the bit where we now need to add some additional code in here that will do this logic for us so the first thing we're going to do or basically what we are going to do is do an if statement so we're going to do an if statement that includes a match formula so you might be familiar with the normal match formula available in excel to basically try and find our if our record already exists and it's also going to use if error so this is where i can get not confusing in terms it's too technical just because we're going to be dealing with if error so obviously that can some obviously is going to give us the inverse of actually what we're looking for and i think that makes sense but we'll step through it so the first thing we need to do is go if is error open our brackets so we're going to do application dot match so this is how we can do a match function within vba and we're going to say okay what's the text box three dot text so this is the value that we want to search for we want to search for this in the range of sheet one dot range c2 to c and our last row and i may not just type this and then talk through what it's doing once complete that might be easier and then cool it might help if i actually do the ensemble rather than the number seven and then go enter perfect and then go down to here and if we'll tidy all these space up in a second so what this is saying is we're going to do this match formula here and the match formula is this section you see here what's going to happen is going to take the value within text box three which is our id and obviously this is the value in that text box it's then going to do a match formula in this range here so from c2 down to obviously how many rows so at the moment to row four and obviously we want to use we've got zero here to do an exact match what that's going to do is going to say well the match formula returns a number so it would tell us the number or the placement of the value if it existed in this row so let's say it was one zero zero or e one zero zero one the return of the match function would give us the number one however if it doesn't exist in that range then the match function will return an error message so it'll because the value is not available and that's why we've used is error so because we've used is error what this will now do is if our value is not available in that range so it doesn't exist and therefore it's a new record what's going to happen is this is error is going to return a true statement because what it's saying is if there's an error is there an error in this formula if it doesn't exist there is going to be an error therefore it's going to return a true statement so based on that we have two options if it is a true statement so it's not available then we want to add a new record however if it's not an error and the value is available there then we obviously want to make sure we update the existing row so how can we just do that so let's go into if and go let's just do a message box in msg box and we're going new record so this is the new record scenario so this is saying if there is a true and so there is an error here what we're going to do is i row is going to equal um i last row so let's bring that back in there and it might make more sense to do this so let's go i update this i row i row i row so previously with our our formula here or not formula code we were always adding our record into the next available row which is i last row but because we now have two options it could either be a new record or it could be an existing record this is why i've added i row so i row can then obviously default to either i last row or it can default to the active cell row if it's an existing record that we want to update so this is what we're using that if statement we're basically using the if statement to define what i row is and then we'll see i can just refer to it in this code down the bottom here so if it is a new record then we're obviously what a little message box at the moment will say oh it's a new record however and if it is a new record we want to place it into the next last row however if it's not a new record then we want to make sure we update the existing row where that uh record is currently occupying therefore we can do message box and then the existing record by so and therefore i row is going to equal uh active cell row like so and then we can just remove this information here like that we can remove that information from here that's about tidy so hopefully that now seems logical and let's just move that out perfect so what's going to happen is when we add a new or when a record is when we're basically in the user form section when we hit the submit button what will happen is we'll do this quick validation to see if our id already exists in this range if it does already exist it's an existing record therefore we want to update the data contained for that record else if it doesn't exist in that range then we want to add it as a new row so let's go through those two steps so add new record and we can see because we've done the add new record it's going to populate a new id for us so this is quite simple we know it's a new record and let's call this one uh mini mouse and then go submit and we can see we've got a message box here so it's done the validation for us it said okay take this id does it exist in this range was highlighted here no it doesn't therefore it must be a new record okay so it's going to submit it for us however if we double click on donald duck and obviously we've got our information populate for us including the id and let's say we change this to capital donald or donald like that uh donald duck one or donald one and duck one what it will now or should do this time is now hit submit yet you can see it's done the test it's identified that this id or because it already exists in this range and therefore it's identified as an existing record and because of that what it's going to do is say okay well this is the active cell here in a three what is the row number it's row three therefore populate this information into row three rather than add a new row and you can see it's done just that so that is how we can obviously build upon our last video to create this workable mini database for adding and amending existing data so i hope you enjoyed that video it was obviously quite a bit longer than our normal videos but obviously it was quite an in-depth one to talk you through that process i will make this file available to you so if you want to download this exact file and use it um basically either just to understand it better or if you want to build upon this for your own uses then obviously feel free to do so you'll find the link to this download in the description to this video if you did enjoy the video please do make sure you give the video a like uh it's obviously not only greatly appreciated by me but does help that all important youtube algorithm and if it's the first time finding the channel or you've watched our videos before please if you haven't already do subscribe to the channel and hit that bell notification button so you are notified of all of our future videos so lastly thank you very much for watching and we'll see you in the next video