Video instructions and help with filling out and completing Form 2220 User

Instructions and Help about Form 2220 User

Welcome to the spice aisle tutorial in this video we're going to cover creating user forms in Excel VBA so the video is all about designing your own dialog boxes to allow users to interact with your workbooks in a nice efficient way so I'll talk about how you can add a form to a VBA project and then how you can add control so the form to allow the users to interact with it will mention a few of the useful drawing tools to help you layout your controls neatly so including aligning and distributing them and then we'll talk about how you can actually make the form interactive so I'll talk about how you work with the properties of a form and the controls on it and also how you can add code to the form events to respond to what users do inside the form the final part of the video we'll talk about validating forms to make sure that users always enter sensible values so it's quite a long video with quite a lot to do let's get started designing user form in VBA provides a really convenient way for your users to interact with a workbook so in this video we're going to design a simple form that allows user to add a new film to the end of our list and the forms going to end up looking something roughly like this one so it's fairly simple but it's also nice and intuitive for the user to interact with we've got clearly labeled controls so easy should know that they can type in a film name into the film main box and then we'll save done that they can see that they need to select a date and they can do that from a nice date/time picker control so they have to worry about what they'd format to use they can then either type in a film length or they can use a nice spin block control to increase and decrease the value and they can also choose a film genre from a predefined list of options clicking the sensibly label to save this film button will then add the film to the current list and then the form closes down now even though the form is quite simple we can add some nice touches to it in code to make sure that the user always have to provide valid details so if I display the form again and try to save a film without having added to film name it's clearly indicated to the user that that's not a valid option so if they do type in and film name then the the validation text or the validation coloring disappears and they can try to save this film again and again they can see that they've not chosen another option with a nice red highlight that extends even to things like testing whether the values of what options are the correct data type so type in some text into a numerical textbox then the users are clearly indicated they can't do that as soon as they replace it with a valid value then everything's fine and they can save the film again so that's a sort of form we're going to try to create in this video and the first step is to insert the user form into our VBA project inserting a form into a project is a lot like inserting a module so in the project Explorer you can right click somewhere inside the project you want your form to appear in choose insert and then choose user form and as soon as you do that your form will appear in the code window by default you'll also see an extra toolbar appear called the toolbox which I'm just going to click and drag out of the way for the time being you can actually turn the toolbox on and off using this button on the toolbar it looks like a little hammer and a spanner crossed over if you click on the button that turns the toolbox off click it again brings the toolbox back before we start worrying about what the toolbox allows us to do the first thing we should do to our form is give it a sensible name because as names go userform one is a bit rubbish so once you've got the object selected you can rename it in exactly the same way you would rename a module using the properties window the first property that will be listed for a forum will be his name so I'm going to replace user form one with the name frm new film hit enter and formulas renamed the name of the forum should always be the very first property that you can change but you can immediately see from the properties window just how many other properties are available far more them for a module of course which only has a single name property so basically any changes you that you would like to make to the design of the forum you can choose from the properties window so for example if you didn't like the bog standard boring Microsoft grey for the background color there's a back color property for the forum you can click on that and then click on the drop-down arrow and choose from a slightly wider selection of boring Microsoft Gray's and there's actually an extra tab here called palette which allows you to choose from a wider selection of colors and even if there you can't find the color that you want you can actually add your own custom colors by right-clicking on one of these blank boxes at the bottom and choosing the color from the color picker so you've got the full RGB color palette available so for example if you wanted a beautiful bright pink form pinky purple then you can select the color that you want