 Hello everyone and welcome back to another video in this tutorial. We're going to be looking at user forms So we'll be creating our first basic user form and we'll be using VBA So some of the scripting that we've looked at in past videos to one help us open that user form and to submit some basic Information so we're basically use this form to capture some information and then paste it into our Excel sheet And obviously we want this to be obviously of use to you So we're going to actually use our dynamic row finder that we previously used so that when we submit new data from our user form Obviously, it's going to put it onto a new row within our spreadsheet. So without further delay We'll jump straight into it. So the first thing we need to do is to design our user form From all I've done on here is I've created a new file And actually I'll save it as the wrong format. I'm just going to go file save as Go browse desktop and we'll save this as an Excel SM So this is a macro enabled workbook. So that's all we need to do there Yes, so we can now create our user form So you can do all f11 or obviously open your macros Developer window and all we're going to do is when we're in his window We're going to go into insert as we do with adding a module, but this time we're going to go to user form So you click that and you'll see that we have a new folder added for us So this is form. So any user forms that you add into your Excel will be Nicely stored in this little folder. That's expandable on the side here And you'll have your basic user form here as well And what we need is our little toolbox so that we can start to design our user form As you'll see down the left hand side There are a number of options that we can use for the various parameters within our user form We're not going to jump into those too much But by all means you can have a look at those when you're working through and they'll just give you a bit more through say Flexibility and sort of personalization when you're working with your user form But we won't be going into that today. Maybe in a future video So all we want to do is add simply two fields to this user form And we're going to have first name and last name and we'll also have a submit button So that obviously once you've populated that information you can submit it to the Excel sheet To do that we're only going to be using these two options here You've got the label. What's the second one in in our controls and we've got the text box So we'll just click label and then you'll see once selected You've got this cursor or this crosshair now available click anywhere on your form and it'll put a label in for you I just double-click there so ignore that So if I just click my label and you can see I can now go in and type what I want to call it So we're going to call this one first name and I'm going to do a colon and let's just reduce the size down on this If I click off that, you know, I'm then going to go into another label and this one I will call Last name It's another colon and let's just reduce this in size again Okay, so now we've got the labels we just now need to do our Our text boxes. So if we do let's go into here and we're going to call this one No, actually I didn't I'm not going to change the name on that obviously I changed previously when we did the labels All I'm going to do is just maybe make this a bit tiny bit bigger Just so it's a bit easier to work with and then we'll do exactly the same here So click on text box click on our sheet and you'll see I'll put one in there for us And as I'm doing on the screen now if you want to make that any bigger All you need to do is just use obviously the variable grab options around Around the box and you'll see very helpfully as default our user form comes with this dotted grid So that enables us to obviously work with various sizing and obviously the layout of our user form The last one we're going to do is if we go underneath the label you see we have command button Just going to select that come back into my user form again and just paste that somewhere here Obviously not being too precious about the layout and if it's in the center And then all I'm going to do from this one is if I now look down these options down the side here You see I have the name so the name is and it's probably why I was worth I'll cover this off in a second Actually, so the name is obviously what this button refers to and that's going to be important to us when we're actually writing our BVA script as that's going to be the the name is what we're going to need to refer to If you go into the caption, then this is where you can just change obviously the in the caption was displayed in this button What I want to do is put the word submit And if I was going into one these text box you'll see the name you can see at the moment is defined as text box one and The second one is text box to also just to carry off if I was going to labels You can see my first name is a label is label number one and the second name is label number two So that's how you find out which each Part within your user form is named and obviously you need to know those names if you want to refer to it in your VBA script So that is the extent of our form. We're going to be using in this Demo so the first thing we need to do is to be able to open this form So to do that, I'm going to insert a button into our excel sheet. So I'm going to go into the developer tab Insert and then select this top left hand button here, and you'll see another crosshair click select And there's no macro there to do at the moment. So it's going to click ok and Just make that button a bit bigger so we can actually see that on the screen and I'm going to just call this We'll call it open form And you don't need to put it in capitals, but it has just what happened. So we've got our open form button here If I right-click this button Okay, so what we need to do if I was to right-click this button and do a sign macro Obviously, we've got no macros available to us in this sheet And we need to have a macro assigned to this button just so that it does something So I'm going to go cancel that for the time being I'm going to come back over to the this pane on the left here and this time go insert and open a module So we'll call this so we're going to put a sub routine in here Basically just to open the form when that button is clicked and then obviously we can assign this macro to the button So we'll call this sub open Form something very basic and all we need to do in here is simply put user form one dot show as Easy as that. So and actually might help if I type user form correctly. So user form one They go a little bit better now with the formatting and we know that's the name because if you go into our user form We can see that it is named user form one You can see obviously in this part here underneath forms and also when we went into the little name section there of these Are the properties for that user form You can obviously change your user form if you wanted to obviously as in what it's called But I often prefer we just leave it as user form one just because it just makes it a bit easier I personally find the forms in the gap navigating them if you have more than one form So now that we've got this macro created all we need to do is go back to our button Right-click and go assign macro and you can now see we have the open form macro available to us Select it hit okay And if you click off the button every time we hit the open form button now You'll see it's opened our user form up for us And there is our user form and obviously at the moment if we to type something in here someone's name Let's go Harry Potter first one comes to mind and hit submit you'll see obviously nothing's happening It allows us to type values in here, but obviously it's not actually going to let us do anything with that information So let's just delete those out and close the form and obviously you can see it was the here Then it'll close button obviously that's just effective in that form for closing the form So what we now need to do is we need to assign a macro to this button which tells well Basically what executes from code so that when this button is pressed it captures the information within these two text boxes And it places it places it into our worksheet here And what I'm going to do just to tidy this up before we preserve press is go first name So we just put some column headers here Just so you can see where the information is going to go and let's just make this 12 in width and just going to move that there. So just to move that button you can Obviously click on the button and move it But my preference often is just to right-click and hold drag the button to where you want it And then you can just do move here once you're happy with the location move here And let's just do a tiny bit of formatting not that it's really needed But I've obviously just very particular about doing that So what button will open our form so we now need to code this submit button to do that I'm just going to double-click on this submit button in the user form here, and you'll see it's Open up some code to us So this is all the code that's assigned to the various aspects of our form and you'll see when I mistakenly clicked Double-click label one earlier. It's also created a sub routine for that as well We don't need that so we'll just remove that one and all we're interested in is this command button one click here So this is going to be executed as soon as that command button one is clicked So the first thing was a couple of things we need to do So we want our form or we want this code to obviously capture the data and paste it into the sheet But we also want it to identify obviously the each new available row So if you had information in row two, we wouldn't want to overwrite that information We want to paste our new data into row three and if you've seen our previous videos You'd have seen us use this particular line of code a couple of times So if you want to go over it in more detail, obviously how strong you suggest you check out those previous videos Particularly the one focused on pasting data using VVA But alternatively we will be covering off very quickly here So you will probably get the grasp of how it works from this video So the first thing we're going to do is we need to define a couple of variables So first it's going to be an integer for our row number or our new row number And we're also going to be defining a reference to this worksheet. So the first thing we'll do is go dim I Last row and again, you can obviously call these what you want. It's just obviously this is the choices I'm going for as divine as integer Next one. I'm going to dim sheet one as worksheet Okay, so we'll go set sheet one equals This goes sheets sheet one and in the second part is I last row So this is obviously defining or finding and defining what our next available row is. So we're going to go sheet one dot cells Brackets and then again sheet one because obviously you want to count the rows within this sheet So rows dot count and do that within column a at the end here what dot end Excel dot row Plus one and as I've probably touched on in my previous videos in the game for this one You don't you can you should hopefully rather get a rough grasp of what this means. You're basically saying Within this sheet number one that we're defined you want to do you want to look at the cells and obviously particular cells being Count how many are available or how many have got information in them So it's a moment to be one within column a and you want to obviously go to the end that range The reason we're doing the plus one at the end here is because at the moment this piece of code on this Own would tell us the last row that has information in it. So that being obviously row one I'm just doing the plus one at the end here So we can find our first last row of data in it add one number to it to then or add a row to it Should I say to find the next empty row? So that's what that piece of code is doing for us Having done that we now just simply just now need to store our data into the sheet And to do that we're going to go sheet one dot cells So the first one we're going to publish or populate is this one in column a here So as we look at it row number two so our row index so we want to use the row number obtained using our last row and Then we go go and this column index is going to be column number one because we want to put the first name into column a Dot value so the value of this cell is going to equal text box one dot value and This next part is going to be very much the same. So we've got sheet one dot cells I Last row comma this time a column two so column B dot value equals text box to dot value and That's it. So that's now going to take that information and Store what we've got in our text boxes and store it into these two respective cells or columns and the last thing we're going to do is this one single line of code is going to be unload user user form one enter and all that's going to do is basically it'll just clear the form So just take all the information what's currently in our form So when this obviously when these two lines of code read what will happen is it's basically just copying the value What you see in text box one to obviously the corresponding Cell within our sheet therefore the information is still retained within the user form at that point But by using unload user form It just means that data is then going to be cleared from the user form So the next person who uses it is obviously going to have a clear form or an empty form for them to use And they're going to highlight this section and use tab just so it moves in so it's bit clear clearer and easy To read our code and that is the extent of what we need So just to go over that off we first created our user form obviously by going insert and do user form And using our toolbox over here to get labels and obviously our text boxes and our button as required We've then gone into our module one by inserting it and we created a basic macro that just basically shows or goes user form One dot show and that's triggered by clicking our open form button Once that form has been loaded and you've entered our data or your required data We've got this simple macro or a few lines of code here Should we say what allows us to understand or define the next Row that has no data in it and then take the data stored in our text or user forms text boxes And paste it into the correct corresponding columns within our sheet So let's give it a run and see if it works. So we go open form And bring the form over so first line we'll go Harry And because our text boxes are in order you can use the tab button to move to the next one rather than clicking harry potter Click submit and you can see that name harry potter has been added to row number two This time if we go open form again, so we go this time we'll go home Simpson Submit and you can see it's now added to that next available row and maybe just go one last name um I kind of think let's go first hit first and second I really can't think of any options to go there first and second submit And you can see again the more names we add on to this the more this continually add rows to our data set in column a and b And you probably know it's obviously when you hit open form You'll notice that first name and last name obviously are now blank because we did the unload User form one at the end of that script And we're not even fair to end the script obviously this command button one And one last thing I just need to talk to touch on because I happen to notice it It's obviously as you moment you'll see we've got user form one written at the top of our user form at the moment In order just to tidy that up a bit more what we can do is if you go into our user form And we'll just go into the caption here. You see user form one. We'll put it in here. Uh, please enter And that way obviously if you want to change it to anything else Or you need to do is update this caption here, but you can see it's now updated on our user form So if we do open form again, it's now going to have enter Please enter name here and obviously first name last name What just tidies that up a bit more as well So we've got a couple more videos coming up using user forms So you're going a bit more technical with how to work with these And obviously we've only got two pieces of data here But obviously if you've got more columns information And you wanted to say be able to double click into one of these options and open a form Pre-populated with that information. Uh, like I say, that's going to be a subject We'll be covering off in one of those videos that are going to be coming up very shortly To make sure you do not miss that video Please do ensure you are subscribed to the channel and hit that bell notification button as well So you're notified as soon as those videos do come out If you've enjoyed this video, please do give the video a like as it's greatly appreciate by myself And also shows the videos that you potentially like to see more of If you do have any questions at all, please just leave a comment below this video And I will get back to you as soon as I can So thank you very much for watching and we'll see you in the next video