Excel Userforms for Beginners (2/10) – Use Excel VBA to Create A Userform and Manage a Database

even if you've never tried a user forum before this video is going to help you get started we're going to have a look at how to create a user forum we're going to have a look at how to make a user forum up here we're going to look at how to make a user forum disappear and we're going to put a button on a user forum and make a very simple operation happen and these are the basic elements the basic process is we've use the forms we're going to do something very simple in this video but to do something more sophisticated you're really just working through the same processes with the same elements so hopefully this video going to get you started with user forms let's get into the spreadsheet file there's two spreadsheet files available with this series you can go to the website to download them there will be a link in the description below this video one of the files that begins with the word ends and that file is the completed file so it's a good idea if you haven't done so already - just click through the file now click on some of the buttons have a play with the user forms try to understand what the user forms it doing my database has got 250 rows in about 10 columns so even a database of this size to have to find somebody's name even scrolling to the bottom scrolling to the top moving around the database just using the cursor keys that's going to take a lot of time so the idea of the user form speeding up that process literally at the click of a button I can add somebody at the click of a button I can edit an existing entry in the database so that's what we're doing with user forms so with that said let's get into the start file just clicked into the start file here obviously the database element looks exactly the same so I've just randomly generated this data if you would like to see how to randomly generate data in Excel quickly you can look at our video on that topic on the channel so I've got some randomly generated data here we're looking to create a user form to help us quickly add entries to the bottom and also to help us edit existing entries so let's get started that process the first step is to open the visual basic editor so develop a tab and then just click the visual basic or if you're on a PC you can use the keyboard shortcut which is alt f11 so now I'm in the visual basic editor now I've got a couple of files open and that always complicates matters in the project Explorer it's important to make sure you're working on the right file in the bold type we can see the names of the files so I want to be working on the start file so I'm just going to click on this minus sign next to the file I'm not interested in and we can see now I'm in the area that relates to the file I am working on which is the start file so that's just something to keep an eye on if you've got multiple files open while you're working with the visual basic editor so we're definitely in the right area of the project Explorer so I'm going to go up to the top just bring this into your screen shot I'm going to hit the insert menu and then user forum so inserts user forum and immediately we can see the user forum appearing and also this toolbox appears now this toolbox will allow us to edit manipulate user form add controls buttons text boxes menus to the user form so it's very useful to us and let's just get straight into it let's get started so we've got our user form here and let's just add something very simple so I'm going to add a button so I'm hovering the mouse over command button just click on command button you can see that the mouse pointer is now a cross shape that's showing me that it's ready to add something to the user form and there we go we've got command button one there and this is now on the user form we can see in the bottom left corner down here lots of useful information about this control useful information about this command button and let's let's do some editing straightaway so this caption controls the text that appears on the button so in the caption area here I'm just going to type in continue let's go for uppercase there we go so we've added a control we've added in this case we've added a button and then the area in the bottom left allows us to edit allows us to adjust how that control appears how that control behaves so we've got our user form we've got a button so that's as far as we're going to go for now in subsequent videos are going to add more controls obviously so we've got a user form in the visual basic editor editor but we want it to appear in the spreadsheet so how do we make the user form appear in the spreadsheet now to do this we have to get a few different things interacting what we want and let's have a quick look at the other file we want a couple of buttons here and specifically a button that we can click that will make the user form appear now if you've looked at our other coding videos you'll know how to create a button but we just go to the Developer tab then inserts and then top left just click on button and again the cursor has changed to a cross symbol so it's ready to add something hold down the Alt key on the PC hold down the Alt key that will lock the button to the grid make sure it aligns neatly with the grid and all I did there was position size and release the button going to do that one more time Developer tab insert button hold down the Alt key hold down the left mouse button and then just sizing the button I could make it really big if I wanted to but this size will be fine releasing the mouse button and then immediately Excel is saying what macro do you want to run when the user click this clicks this button now we haven't prepared a macro yet so I'm just going to hit cancel here we've got our button here there's no macro signs to it I can't click on it yet it's not running any code so I'm just going to right click here edit text and then type in add so we've got a button there with the text ad on it so the user wants to be able to click on that button and then I use a form is going to appear so somewhere we're going to need some code to make that happen to create code we're going to have to go back to the visual basic editor an area and again you've got to be careful which file you're working in I'm going to just shut down the modules and the user forms from the other file so this this is a file work we're interested in we've got our user form here so we need to input another elements in the project Explorer insert something else to accommodate some code to store some code what we put in well we can insert a module insert a module we can see module 1 has appeared here so now we've got somewhere where we can put some code so let's create a new routine so and let's call it show let's call it show show user form keep it nice and simple there we go so we've got a new routine with an informative name we can tell from the name of the routine what it does now at this point I'm going to edit the name of the user form because clearly if you had lots of user forms user for one user form two user form 3 that's not really telling you what the user forms are doing so let's give the user form an informative name so we can understand later what it does and let's just call it I'm just double clicking in the name area here so clicked on the user form double clicking in the name area and then let's just call it data underscore you I data underscore you are it seems to be a suitable name we can see the name of the user form has changed now so I'm going to go back to the module to edit the routine begin the routine that we've created very simple now we're going to type in the name of the userform data UI I did mean data us that us for user form UI for user interface but UF is better clearly because this is a user form data us that makes sense data us dots show so a very simple line of code the name of the object the name of the user form and dot show so that's just going to make the user form display what's the name of this macro the name of the subroutine is show user form so now what do we have to do to kind of complete this process we've got to go back to the spreadsheet and assign the macro to the button we created so right click assign macro and then again important not to get confused here because I've got multiple files open the macros that are in this file they will display just showing the names of the macros you can see the other macros they have the name of the file first so they're actually in another file so we're not interested in those we are interested in the macro that we just created it's called show user form so I'm clicking on that clicking ok so now if I click on the button we can see I'm just hovering over the button we can see the cursor the mouse pointer has become a little handler with a pointing finger so it's going to do something when we click on the button so let's just try clicking on the button and we can see user form one is displaying that now clearly this is not looking too good yet but we're just getting started getting to know the basic the basic processes to go to to go through so we've made a user form display but clearly with one the user form to do something and we want to be able to make the user form disappear as well as the user form won't disappear automatically we can get rid of it just clicking on the cross in the top in the top right hand corner but we want to be able to control it with some precision so how do we make something happen then how do we make the user form disappear well let's say let's click on this continue button it let's say if we click on the continue button we want a message box to flash up and then we want the userform to disappear so how might we do that I'm going to close the user forum for now back to the visual basic editor now we're going to create a piece of code that we want to be triggered when the user clicks on this continue button that's specifically what we're trying to do so in order to create some code that's triggered when that happens we can in the visual basic editor double click on the continue button that we created and the VBA editor automatically creates a new routine that will run when the user clicks on that button I'm just going to do that one more time so we've got the user form if we double click on the button we go into the code and this routine will what will run when command button one is clicked so what do what do we want to happen well let's just do something very simple trying to keep it simple in this first video let's make a message box appear and let's say the user forum will be closed there we Lancers going to resize this a little zero and then let's just say message okay so some basic message box code the message box the first component is what will display in the message box the zero controls the type of message box and then message will be the title at the top of the message box so that's one that's the first line of code that will execute when the user clicks on the continue button but we want to do more than that we want this message box to flush up we want something to happen then we want the user forum to disappear to close to go away to do that we use a line of code which is unload and then the name of the object the name of the user for our user forum is of course called eita underscore us that's the name of user form we created so these two lines of code now we're going to see the message box same user form is going to be closed and then the next line of code unload data underscore us that's going to make the user form disappear so let's give it a go back to the spreadsheet so at clicked on the button we got our user form here then we're going to click on the continue button we can see the message box is displaying we can hit OK and then the user form disappears so let's just go through that whole process we've got an Add button here when we click the add button the macro assigned to that button runs and we have a module in the visual basic editor with a very simple routine that so this is the routine that runs when we click the Add button it just shows the user form and then we have some more code which runs when we click the continue button now we have to make sure that's in the right place and we click double click on the continue button in the visual basic editor that took us to this area where we can put some code in that will run when this commands button is clicked so we've got a few different elements there we've got the user four we created we've got the new module we created we've got two shorts coding routines all of these elements working together to create the desired effects and let's just demonstrate that one more time click the Add button userform flashing up clearly this is very simple but we're going to build on this user form in the subsequent videos to get it doing the things we need to do then we can click on the user form do something in this case we've just flashed flashed up a message in subsequent videos we're going to take some data from the user form and put it in the database so we can add a button or anything else to the user form click on it and it will do something and then finally a line of code to close down the user form so we haven't done anything yet but we works through the main elements of creating a user form creating it in the visual basic editor first putting a button in the spreadsheet to make it appear putting something on the user form that the user can click to make something happen and then making sure the user form disappears so regardless of how sophisticated or complicated looking a user form is it will be working off of these basic processes so these basic process is creating the user form make it up making it appear and disappear if you can master those you are well on the way to making user forms work for you so that's as far as we're going to go in this first introductory video we're going to move on in the future videos to get this user form working for us we're going to add some text boxes for the user to input some information and then we're going to think about what code we need to get that information from the user form into the database and by that time we'll really be making some progress with this user form task I'll see you in the next video [Music]

Channel: Tiger Spreadsheet Solutions

Views: 163,073

Rating: 4.9126048 out of 5

Keywords: Excel Userform, Tutorial, How To, Userform, Excel VBA, Excel Userform Beginner, Excel VBA Beginner

Id: TxQU8qj4K_Y

Length: 16min 45sec (1005 seconds)

Published: Wed Apr 12 2017