Excel VBA Forms Part 1 - Drawing Forms

welcome to this wise ell tutorial this is part one of a new series all about Excel VBA user forms now we have covered user forms as part of the main excel vba tutorial previously the idea behind this series is we're going to break things down into smaller sections and go into a bit more detail in each one so although inevitably we will cover a few of the things we've already seen in the previous video we're also going to get into some about exciting advanced topics too so this is part one as I say and we're going to start with the absolute basics of drawing forms in your video projects so we're going to start with a quick look at how you can insert a user form and then how you can edit some of its basic properties such as its caption and its name and some of the basic formatting properties as well we'll then talk about how you can draw controls on the form and again how you modify the properties of those controls we'll talk about a naming convention you can use for for naming the controls in your forms and whether you should use out or not and then towards the end of the video we're going to discuss some of the useful layout tools to help you to quickly give your forms that are slightly more professional looking layout so let's get started okay so the basic aim of this video is to show you how to create a basic form which looks roughly like the one shown on screen here it's not particularly sophisticated or complicated and it doesn't even do anything yet although I can type into their text boxes and so on my couldn't click buttons but nothing will actually happen so the idea here is to show you the basic techniques you will need to use in order to draw forms in the first place now although this form is quite basic the same techniques will apply to any of the more advanced controls that you'll draw later on so that's game we're going to start by showing you how to insert a form into the project in the first place so to start with when we head into the visual basic editor and as usual you can do that by either heading to the Developer tab in the ribbon and using visual basic or pressing Dalton f11 on the keyboard once you've done that you will hopefully be looking at your VBA project and in order to insert a form it's a lot like inserting a module into a project simple way to do it is to right-click somewhere in the project Explorer and choose insert user form and that will be enough to generate your form for you so that will appear in the main code area of the window it should also open up this extra toolbar called the toolbox which will come back to you very shortly and you should see that ever just click away from the form just to clear the toolbox should see that your user form appears in its own separate folder in the project Explorer now before we get some of the process of actually designing the form I want to very quickly mention form views and how you can switch between them and the main reason I want to do this now is because it's really easy to accidentally switch between different views of a form without knowing what you've done and once make sure you know how to get back to this view which is the object for you all the design views is sometimes evolved so the easy way to accidentally switch out of this view is when you're trying to select something on a form and you just double click by accident if I double-click on the background in the form it immediately switches to what's called the code view the form and it generates an event handler and we're gonna come back to the year the events of a form a bit later on in a different video when we talk about how to code forms so let's just ignore this code for now the main thing you'll want to know how to do is how to get back to the design view or the object for you so there are several different ways to do this simple ways to just close down the code you all together so if we click the cross and the lower of the two crosses in this all right hand corner and that will see you much of the form alternatively you could have just double click back on the form object in the project Explorer if I double click on the user form one object here that was doing about the design view or the object view as well it's also possible to right-click that's object and choose to view the object rather than viewing its code you can see you can easily switch between the two views there by right clicking on it and choosing two views code or the object and there are also keyboard shortcuts to do the same thing so while you're looking at the form in design view you can press the f7 key on the keyboard and I'll take you to the code view if I put a shift in f7 that'll tell you back to the object view so now we can start modifying the forms design and pretty much all the changes you might want to be able to make to a form you can achieve by the properties window which should be sitting at the bottom left-hand corner of your screen by default if it's not there head up to the View menu at the top and then from about 2/3 of the way down choose properties window or just press the f4 key on your keyboard instead when you've got the form selected the properties window will show you a list of all the properties you modify about this form bear in mind that if you selected a different object in the project Explorer of course it will show you properties of a different object so make sure you've got the user forum itself selected either by collecting it in the project Explorer or just clicking on it in the design view the object view now by default properties are listed alphabetically but if you prefer you can view them in a categorized list instead daddy behind it if I click on the categorized tap is you get exactly the same list of properties just organized into groups related to their their function so I've got an appearance group for making change to of course the forms appearance and a separate group for behavior and so on and so on now perhaps the most important property to change to begin with is the forms name this is how we're going to reference the forming code later on so we certainly wanted have a slightly more descriptive name than just user form 1 so to find a name property probably the easiest way is to use the alphabetic list now I know technically it's not alphabetically listed but the name property will shoot right to the top of the list making it much easier to find so rather than calling this one user form 1 I'm going to call this one film details naming rules and conventions for the same rules and conventions as for other vba names so to use spaces don't use punctuation characters and so on if I hit enter I'll find that the form gets renamed in the properties window now you notice that this doesn't actually change the forms title this is set by a different property all together called caption so this display text we can type into here whatever we want we haven't got to follow any rules in particular so we can use spaces and punctuation characters and you'll find the caption property again I'm looking a bit alphabetically listed caption property just down here we can modify this I'm gonna call this one film details as well but I'm gonna stick a space in between and the word of film and the word details so you'll see that change as I'm typing in fact in the title bar at the top there we go the name and the caption the form changed for the next section I'm going to think the properties window back into categorize view this have put all the appearance properties together into a nice single group because I want to start talking about changing the colors of the form I'm gonna start with the background color property of the back color properties it's called mainly because there's something the most obvious visual has changed at the forum away from this boring old Microsoft gray problem is if I go to the back color property and I click on the drop down arrow what I'm presented with to begin with it's just a slightly longer list of boring Gray's these colors come from the the the windows theme that you have applied so you can modify these colors via the window settings but it probably makes more sense rather than using any other system colors just to demonstrate to the palette table there which gives you a slightly wider wider range of colors to choose from including a bunch of boring grades of course but some slightly more interesting colors add to now in the real world it's likely that you want to reproduce a very specific color for use in your forms and maybe even other parts of your workbook so you wanted to get the full range of colors available for form even wider than the palette shows you have a look at the blank rows of whites boxes at the bottom of this of the pallet grid if you were to right-click on any of these boxes that will launch the defined colors dialog box and from here you can select any of the 16,777,216 colors that can be reproduced via the RGB system so the idea is that you either click and drag to select a color and then use a slider bar to change its intensity alternatively to get a very accurate color what you can do is enter the RGB or red green blue values but a specific color that you want to generate so each of these numbers here compare value between 0 and 255 and the idea is that you enter a specific value to get a specific color really really quick example and you may well have access to your own brand guidelines somewhere within your own company but very very quickly if I go to a quick google search for and the NHS this is just one that I know comes to light very very quickly so just search for the NHS blue color and then go to the initial price and it's just brand guidelines page there's the NHS blues colors definition is red green and blue search for at zero green hundred 14 298 there's a slightly wider range of colors there as well and each one of these cars has its own very specific definition with RGB values so to reproduce any of those colors all I would have to do is head back to this grid and enter the numbers into these boxes I'm usually happy with a boring old pale blue color that I've selected there so I'm gonna click the add color button and that will add it into the palette and also changed the background color of my form when you've added a custom color to the palette you'll find that that not only appears for the property you originally selected it in the back color in this case but also for things like the border color and for the font color and so on so you'll find that reproduces itself elsewhere to make it easily available for you I'm just going to go back and change the back color of my form back one more time I'm gonna go back to a plain old boring white color actually and then I'm going to change the for color of the form so that it's some kind of I don't know some dark blue sort of color instead now you may notice it changing the for color didn't really do anything to the form at this point there isn't actually anything displayed on the form at this point though though can be changed to be that color the point of changing this property here at this point is that any subsequent controls I draw on the form things I labels and command buttons which have text displayed on them they will automatically inherit that color now I can still change out for the individual controls later on but it can save a lot of time with formatting if you set up some default to formatting properties for the entire form which other controls will inherit at this stage another set of properties that will be inherited by other controls our properties related to the font now it doesn't look like there's much you can really do with a font property at this point but if you still let the font property you'll see a little ellipsis button appears and if you click on the ellipses you'll find a separate dialog box exposing way more properties about the font than you originally think so I'm just going to make a couple of quick changes here I'm gonna change from the default to Homer font to Calibri font I think and I'm gonna change the font size if I think that's a tiny little bit too small Mather point let's go for a 10 point font size instead so once I've done nothing like okay I'm going that what have any any actual immediate impact on the form because there's nothing this currently displaying tax button the forms caption of course but that doesn't up to you inherit the forms the font properties but any subsequent controls I've drawn they're like labels and command buttons again we'll use these font properties for properties as their default values which you can then change should unity the next set of properties I wanted to quickly look at or three with this form size and position so again you can find these in the categorized list of your properties you know fairly easily you scroll down and find the position category there's a height with a top and a left property there's also start position which we'll get back to fairly shortly so the item with a fairly obvious these are measured in points actually the measurement unit for height and width and of course you can modified these manually by typing into the boxes you could also of course just do it by clicking and dragging the selection handles around the outside of the form when you do this it changes the other the height and width of course and in doing so will automatically update these properties here now it's probably easier and certainly easier to create more accurate Heights and widths just by manually keying bodies in so what I'm gonna do is it's just manually key in something like hundred and sixty for their height and let's say I'd know one hundred and eighty for the width somehow that should be enough the remaining three properties than in this section are the left the top in the starter position and left on top a relatively easy to describe left is the distance in points from the left-hand edge of the screen to the left hand edge of the form and top fairly obviously is the distant same distance but from the top of the screen to the top of the form these only have an effect if you change the start up position property so by default the start up position will will set the forms to open up right in the middle of the Excel window only refers the the owner of the form which is Microsoft Excel so the other settings there are center of the screen so regardless of where Excel with positioned on the screen your format open up in the middle of your screen windows default is well what if your windows default setting is a manual allows you to manually set this by changing the left and the top properties I think it makes more sense for my particular form so this starts up in the center of the Excel window so I'm going to revert back to the center owner and we'll see that when we run the form later on it should appear right in the middle of the Excel window so I think that's all the properties I want to change about the form itself at the time being the next thing to do is move on and start drawing objects or controls on the form now in order to start drawing controls on the form you'll need to display the tool box and the simplest way to make that appear is to clicks once on the background of the form so I were to click somewhere on the background of the form the tool box should pop up automatically now if it doesn't it probably means that it was closed down last time you were using it so for instance if I were to click on the cross at the top right hand corner of the tool box the next time I click on the background of the form the top box didn't appear let's bring it back then the simplest way to do it is to click on the toolbox icon on the toolbar alternatively I could go to the View menu and choose toolbox sadly there's no keyboard shortcut to make the tool box appear which is a bit annoying but there we go and then the tool box pops up and it shows you all the various different types of controls that you can draw on the form it's a fairly limited list to begin with you can always add more to it later on but for now we're gonna stick with the basic default set of controls the first type of control we're going to draw is something called a label and that's represented by the big capital letter a tool on the toolbox now a label represents a descriptive text on your form so this is usually drawn next to another control like a text box or a drop-down list and it describes what to do what to type in what value to enter we're gonna have three of these in our form so one way to draw a label is to click once on the label tool and then move the mouse cursor somewhere over the form you'll see the cursors changed to indicate you're about to draw a label and if I click once with the mouse button it will drop a label of a preset size another way to draw a label will be to click and drag with the label tool so if I click and hold the mouse button on the label tool on the tool box I can interact across under the form and I'll see an outline of the label that's about to be drawn so I just release the mouse button anywhere that will draw the label at the decision I've indicated you can also choose to draw a label of a size you want by clicking on the label tool and then clicking and dragging on the form so I click and drag that will draw a label of the size I want personally I tend to draw labels of a default size and then I know that everything is got a set pre defined size it's always possible to resize things like tirana they need to be increased or decreased I'm going to delete label 3 in that case I'm going to replace it with a label of a default size so if I select label 3 and already is selected of course you can see the selection handles and then press the Delete key on the keyboard that will remove it I can then either click and drag or just click once and draw another label on the form it's also possible worthwhile mentioning that you can copy and paste existing controls so over to right click on label three I can choose to copy it I could also press ctrl C to do that then of course I can paste it in elsewhere on the form so we want to duplicate labels or any other controls copying amazing is a simplest way to do it now if you have to draw lots of the same type of control a quick simple technique you use is to lock the control to the mouse cursor so the next thing we'll do is draw three text boxes so you'll find the text box tool by default right next door to the label and there simply seems to do here is to double click on the text box tool to lock it to the cursor so if I double click with mouse I can then and draw as many of these as I like you'll see that this I'm about draw a text box by clicking it doesn't detach the text box tool from the cursor so you can second just click again and again and as many times as I like it can draw as many text boxes without having to go back to the tool box each time the easy thing to forget there is that of course you've got the tool still locked to the cursor so to remove it or to unlock it you may have back to the tool box and click on these select objects the black arrow there so that will release the the toolbox officer at the text box from the cursor and you can go back to either selecting things or drawing color types of controls two more controls - I don't gonna use the same technique we're going to add 2 command buttons so I'm going to double click the command button tool and I'm going to command buttons towards the bottom in the form just like so and then back to the Select objects cursor and then those are all the controls for now that we want to add to our form now just like the forum itself every single control that you draw on the forum has its own set of properties and you'll see if you click on two different controls you'll see the properties window changes to reflect the properties of that type of object so kunti have got command button two selected if I slapped text box I got a different set of properties and a label the different set of properties yet again hopefully you'll notice that some of the properties have already been picked up based on the things we changed with the forum earlier on so every controllers on the forum so far the label text box command buttons have all picked up on the font properties they're all set to Calibri rather than the original Tahoma mastery for the label for the command button and even for the text box although slightly less obvious to see for the text box to begin with I scroll down far enough I'll find the Calibri font the labels have also picked up on the color changes the back color and the for color by certain label one you'll see that back the for color has been such as this dark blue color his back color matches that of the form as well so it's this white rot van the original grey so it was worthwhile setting some properties earlier on on the form a sages a bit of time now but of course there's nothing to stop you subsequently changing the properties of different objects so for instance the command buttons I'd kind of like to see the font of those in that dark blue color so I select the first command button I can also set the second command button to change both controls at the same time you'll see the properties window changes to show you common properties so if I select command button one remember one command button - command button - if I hold down the control key on the keyboard and select a different control so it's like a number and Walmart commands you selected you see a limited list of properties I can't show the name property because each control has a different name but they have a common back color command for color so I'm going to change these just quickly using the property window and I go for this pale blue background color I'm gonna go for the dark blue font color as we had earlier on there we go I think I'll probably do in terms of formatting changes coloring changes the next really important thing to do for all of our controls is set their names now when it comes to naming controls in a forum there's a couple of different conventions you might choose to adopt the first one I'm going to talk about is slightly old-fashioned it's a technique called Hungarian notation or is at least a modified form of it it's not quite the original intention of the technique but it's often described as Hungarian notation so the idea behind this technique is that you prefix the name of each object with little code that indicates what type of item it is so for instance for a label if I select label one this is gonna have the word film name or the word title in it it describes a text box next door to it to informs you to enter the film's name so to name this label I would change its name in the properties window so it's called L B L film name so the idea is lb L indicates that the type of control is a label while I'm here I'm also going to change the caption and the caption doesn't really matter what you were UN's in that it's just descriptive text it appears on the form so I'm gonna talking to a title for that one so again for the label two I go to the label two and I change its name this wasn't gonna kind of refer to the film's gross takings in dollars so I'm gonna say lb l film gross and then I can I'm gonna change its caption so it's called tomb-like I'll just put the word gross and then put in parentheses a dollar sign too in case it meant to be in US dollars likewise for label three and go back there and choose lb l this one's the film's release date so I'm gonna call this one film date and then the caption can be something similar like release date okay so that's that's the mention for labels text boxes begin with txt so if I select the first text box and then go to the name property I'll change this to txt film name and then the next Xbox will be txt film on gross and then third one will be txt film date last of all the command buttons if I select the first commandment and I'm gonna call this one CMD short the command that's the convention CMD add to list so the idea is that clicking this button would add the current details that the user has entered into the existing list of data on the worksheet then I'll change captions that's just says something like add to list nice and simple then the second command button will be the one that cancels the form just closes it down without doing anything so I'm going to call this on C MD cancel and then change its caption so that's called cancel as well now obviously we've only drawn three different types of controls here so we've got lvl the labels txt for text boxes and CMD for Commandments there there are lots more control types you can add to a form of course if you're interested in the definitive list I suppose of garion notation conventions then I'm not bad place to start would be with a quick Google search perhaps for Excel VBA forms or in fact VBA forms Hungarian notation so there will be a link there to Microsoft knowledgebase article maybe you select it you'll see several different tables on here which indicates the standardized conventions for types of things so one thing I didn't do I didn't add Canaria notation to my form itself so ideally if I was following this convention for everything I would have used frm in front of my form name so you'll be able to see there of course all the different types of controls and the different prefixes or yes prefixes you're supposed to use okay so what sadly 100 go in it the idea behind doing this in the first place is when you're writing code that references these objects if I go back to my VB editor and I just double click on the background of my form again just as a quick simple way to demonstrate there that the technique if I double click on the form to go back to this code view we saw earlier on if I look in the intellisense list for objects in my form if I just press ctrl + space on the keyboard and I fell starts I mean letters LBL I find that all my labels and neatly grouped together so regards what they're that descriptive name is they all begin with LD l so it's nice and easy to find them in the list likewise every txt I find on my text boxes every one for CMD there's all my I'm sorry beg pardon CMD there's all my command buttons so that's the idea and this is it the Hungarian notation has kind of fallen out of favor in in recent years and you'll find lots and lots of articles about this about describing why Hungarian notation is bad and I'm not bad place to start actually with Hungarian it and their pros and cons is the Wikipedia entry you'll find a advantages and disadvantages section on there and if you really really interested please do have a quick read I'm not going to cause describe all of these things and let me show you the alternative technique slightly more modern convention forum for naming controls on forms I'm not gonna recommend one or the other here by the way this is purely to show you what's available is very much up to you to make your own choice here so the the other type of company I don't show you actually has a name actually it's just not an gary notation rather than prefixing each controls name with a little prefix like this LVL what I would do is father the label I would call this film name label and then for the text box within which the user enters the film name I would either call this film name text box which in the real world I don't tend to do what I would tend to do with this is I would just call this one film name this describes the the object which is going to actually hold the name of the film that the users going to enter so rather than describe what type of object it is I want to reference that as a film name almost like the variable name it's just a container to hold a piece of data so the idea behind this noun is if I went back to the code viewer for my vegetable click rather than seeing items grouped by type as the items grouped by the value entered into them so if I press ctrl + space again and look for film name I see the film name and film name label both together in the list and there might be other controls I add to the form related to the film name textbox so I might have maybe a hidden error message in a label as well so again I would call that film name error label something along those lines so that's a ID behind that approach and that's what I'm asking I'm going to take for this particular video as well and I'm not gonna use Hungarian notation I'm just gonna go back to my form in design view or object for you I'm going to rename each of my other control so they're not called there are these little prefixes so rather than LBL film gross as when we film grossed label and then the text box itself will just be called film gross it describes what the piece of information actually is likewise back to a release date and I'll call that film date label and the text box will just be called film date and then Superman buttons again I could call this add to list button if you prefer I probably wouldn't bother with the button that that's not gonna be related to any any value that the user can enter there'll be no corresponding text box with that personally to keep things nice and short and clean and easy I'll call that add to list cancel potentially I would actually add the word button cancel can be used in on your find cancel used as a keyword in various other parts of VBA for instance it exists as a parameter for certain event procedures you can cancel events so for this one I would probably tend to call this one cancel button I guess I'm kind of mixing and matching conventions a little bit of a lack of consistency personally that's the approach I take in the real world as with all conventions it's not really massively important which one you adopt it's just that you choose to use it consistently so for me this is their this is the style that I go with if you're more comfortable with Hungarian notation please by all means use it I'm really genuinely not trying to discourage you from using it whatever you think makes more sense to you just be prepared for the snobby advanced level programmers to turn their noses about you if you do you can carry your notation from time to time I've been in that situation myself but you can feel free to ignore them okay so now that we've got all of our controls labels and named and formatted appropriately let's do something to sort out their layout now really when I kind of deliberately drew the controls a little bit too haphazardly I'm starting to feel a little bit uncomfortable because I've never really in there and in the real world draw controls this disorganized what I'd like to do is align them and distribute them so they have a nice sensible professional-looking neat layout so I guess the simplest way to achieve this is just to click and drag on controls to move them around on the form so I've picked the the gross text the gross labels or even rather than sex what's the gross label first just start to click and drag to move it around if I click on hold the mouse button on it I can start to move it around it's relatively easy to achieve a neat layout by clicking and dragging you probably see hopefully who make out there that the controls moving in little jumps little steps it's kind of snapping to the divisions in the grid in the macro and the form now the steps are quite large to begin with by default so just very quickly before we continue this I want to show you how you modify the grid settings to modify the grid settings you can go to the Tools menu and choose options and then on the general page of the dialog box you'll find the little section to the form grid settings so you can turn the grid off altogether if you like you can just display don't check the show grid box that where we stop controls snapping to the grid I want to do that I can just uncheck for your line controls to grid checkbox and that will stop controls actually snapping to it all I want to do here is just I just want to modify the the divisions the increments in the greatest values changed from 6 down to 2 points just to give myself a little bit of a little bit finer control and then click OK and you'll see it changes how much the control jumps by each time I move it so lets me gives me that a little bit finer control now you may prefer not to use this at all you can turn the grid off altogether I've just seen I'm gonna stick with this add this 2 step increments in the grid and that means I can click and drag to move things around reasonably easily ish makes it a little bit fiddly but it's still possible to achieve so there we go now in the real world again doing this by hand is really really tedious particularly for much larger forms with with many many controls on them so I'm gonna do is sort of deliberately mix these up a little bit again and talk about a couple of really useful tools that you can use to help pull out your controls on a form most of the techniques were about to cover in this section and much easier to achieve if you've despite the extra userforms toolbar and probably the easiest way to make it appear is smooth mouse cursor up towards the top menu bar and then right-click and simply choose user form the toolbar will appear somewhere on the screen and I'm gonna click and drag to move mine up and nest it at the top with all the existing tool bus most of the controls on this toolbar rely on you having multiple items selected so for instance the first thing I'd like to be able to do is align the title gross and release state labels to the left-hand edge of the title label so I need to select title gross and release date one way to achieve this is if I select the title label and then hold down the control key I can click on other individual controls one by one and add them into the selection that's one way to make it work not necessarily the quickest way though if I click in the background the form again to deselect everything this time I'm gonna click on the title text but I'm sorry title label not text box titled label that's up and this time I'm gonna hold down the shift key if I hold down the shift key I can click on the release date label and that will select everything in between the first and the last one that I've selected probably the easiest way the second guy tend to use more often is to just click and drag a selection box around the items I want to select so the trick to this is starting with the mouse cursor in a blank part of the form now the box you draw does not have to enclose the entire control in order for it to be selected so I can safely start with the mouse cursor somewhere here in a blank part of the form click and hold the mouse button as I as I drag a box which at least partially encloses all the items I want to select so if I release the mouse button at this point all those items will be selected before we align the controls together to the title label you got to make sure that the control you want to align items to is the dominant control so you'll hopefully see that from the three items I got selected at this point there's one with a set of white box around the outside and two with black boxes the one with the white selection handles is the dominant control so if I apply any of these layout or alignment tools to the set of controls they'll all take their cues from the release date label which isn't quite what I want to make the title label the dominant one all I've got to do is click somewhere on it you'll see that that one now has the white selection boxes around it I leave this you have the black so now that I've done that I can find the appropriate tool to align all the other controls to their left-hand edges so they're the tool that I want is this form the align tool if I click on the drop down arrow just to the right of that I'll see a list of different options probably the I guess little icons are probably more helpful than the descriptions descriptions there so I'm gonna put a left option although in this case it doesn't really matter left centers or rights because all the all the labels are exactly the same size anyway don't that you matter which of these three that I select I'm gonna have the left and you'll see that everything now comes in line with the other title label now I'd like to do exactly the same thing with the three text box as I'd like them all to be aligned to the left hand edge of the top text box so again if I select all three by clicking and dragging selection box around them and then making sure the top text box is their dominant control this time I don't need to click on the drop down arrow to select align left's because it was the last option I selected all I need to do now is click on the button and once again those three objects will be aligned to the left hand edge of the Domino mom the next thing I'd like to do is make the spacing between the controls equal now before I do that I'd like to move the bottom text box up quite a long way so I'm gonna click away from the group of controls and then just click and drag to move the bottoms X Box all the way up here next thing to do is select all three text boxes again you need at least three items selected to make this technique work I know you can't select this tool from the from the userform toolbar there's be a really nice addition to the toolbar well I can do is headed up to the format menu at the top of the screen and then choose the says a vertical spacing I can see there's a couple of options in here I can nudge them all up so that there's no space between them at all by using the remove option I can increase and decrease it but what I want to do here is make the spacing between them equal to distribute them evenly so I choose make equal that'll set all three text boxes as an exact even amount of space between the three now the next thing we're going to do is align each label with this corresponding text box because it's close that isn't the case at the moment before I do that I'm also going to reduce the height of each label because I don't even see the moment the text in each label is aligned to the top of the label means if I put it alongside the text box it looks as though it's offset which looks a bit messy now as far as I can tell there's no property available as you change the vertical alignment of text and label there's a one for the horizontal alignment it's called text align and I can change it to the left right or centered but nothing for the vertical alignment so the simplest thing to do here is to reduce the height of the label and then align it align Center is middle with their corresponding text box so I'm gonna reduce the height of this label just by clicking and dragging on its resizing handle now I've reduced that to pretty much exactly the right size I don't want a that's each other individual label so while I'm here with the title label selected I'm going to hold down the shift key and then click on the release date label they're all selected the title label is the dominant control what I'm gonna do now is change everything so their height is exactly the same so another tool on the on the userforms toolbar allows you to change the height so that everything's the same height as the dominant control so that saves me having to do exactly the same thing to each of the other labels the last step then involves selecting each pair of text box and label individually like so you want to make sure that you've got the text box of the dominant control this time because we don't remove those ones actual position and then we're going to use a align tool and a line in the middles so once you do that one text box and label second text box on the label and the third pair and that's a reasonably neat looking the layout so hopefully you can agree that doing all that by hand is a bit would be a bit tedious having done it using the user forms toolbar things are a lot simpler one last technique to talk about in terms of automatically laying out controls is specifically to do with command buttons there's a couple of sort of defaults of range mint you're likely to see Wichmann buttons on a form one has some arrangement along the bottom just like we've got them here another arrangement has an aligned along the right-hand side so you'd have tend to find them more like so now the cool thing about these default layouts is there's a couple of options in the menu that allow you to create them automatically as long as you have multiple buttons selected you can achieve this really quickly if I select the the absol Estanza buttons like so I've got the add to list button is the dominant control if I then go to the format menu and choose arrange buttons I can choose to put them along the bottom of the form like that and then if I go back and I go to the format tool and I choose arrange buttons and make them a right aligned they go to the right you see the dominant controls the one that sits in last if I change that around and I use there the cancel button first so the cancel button was the dominant control like so if I go to the same tools format arrange buttons bottom which is now the add to Lisbon which comes along on the left hand side and again if I go to the format tool and use your arrange buttons and go to the right cancel sits at the bottom of that I'm just gonna rearrange them one more time so they sit along the bottom of the form so arrange buttons to the bottom and then resize my form by changing its width property again so rather than clicking and dragging in sort of trying to judge it it's much easier I think oh there you go got it 180 it's much easier just to type in the number into the width property at the foot of the form properties window but there we go now that we've spent so much time arranging our control so neatly it would be a real shame if we were to accidentally knock them out of alignment and just me I am just about clumsy enough to do that more times than I care to admit so we're going to do is put these controls into a group simplest thing to do here is select all the controls you want to group together and then you can either right-click on all the controls and choose to group them or you can use the toolbar as we added in earlier on and just choose a group tool on there so choose group you'll see that you get one single selection box around the entire set of controls to give myself a bit more space you'll find that if I select the group now putting on one single control selects the entire group you move the entire thing around in one go without the risk of moving them out of alignment you can still change properties of single controls inside the group just by selecting them so you can see when you've got the entire group selected you've got the common properties of all of the items in their side the group so you won't see the name for instance or the caption but if I were to select a single item inside the group just by clicking on it again you'll see that now I get the specific properties for that single object so the film they've label selected I can I can subsidize other items inside the group like so so it's the sort of the best of both worlds kind of thing really grouping it allows you to keep together all the items inside the group but still individually modify each one should you need to the only minor downside to grouping controls is there's no real visible indicator that they're grouped together you mean I spend the forms running that the user could see that those items are part of a specific group so one way to achieve that is using the basic tool called a frame now ordinarily if you decided you were going to use a frame you would draw the frame first and then draw your controls inside it because sadly can't draw a frame on top of existing controls well technically you can but the frame will just sit on top of the controls and just mask them so what were going to do instead is you gonna modify our form mates with quite a lot of wider and then we're going to draw a frame control inside the form I might need to increase its height a little bit there as well with the default size let me just increase the height of my form there okay so that in now is I can drag existing controls inside the frame so to simulate drawing new controls inside it's okay click to select the group of controls I can drag the entire set inside the frame they don't necessarily need to be groups here by the way I could have have an ungroup set I forgot to mention in fact that if you did want to ungroup your controls you know they just right click on them and choose ungroup or use the same tool on the toolbar if I choose to ungroup them that they now behave as individual controls again so there's my frame created and I've got my controls inside it and we're gonna resize the frame and then position that somewhere a little bit neater on the form so I'll drag it back over to the left there and then just a resize the form one more time so everything is reasonably neat I'd spend a bit more time doing this in the real world of course and probably end up typing in my values over there they're accurate and precise I should give the frame a name and I should give it a caption as well so if I select the frame and then just use the properties window to change let's see its name first of all let's call this one at film details frame I've already got a form called film details so I can't recall it film details I'll call it film details frame instead and then the caption will simply be I guess once again I call this on basic information again this is free text you can tell me whatever you like now again frames work in a similar way ish so grouping controls if wanted to move all the controls around as you saw and then select the frame and move the entire frame and everything that sits inside the frame goes along with it but once again I've still got the ability to modify the individual controls within it by selecting them so these are the only long part of a group everyone grouped them but I can still move them around together as part the frame or modify them individually the single advantage really the frame is that it gives you a nice visual indicator as well so when the form is actually running then you actually CV that the the outline around there the group of controls and it's a nice way to visually split up a large complex form having spent all this time designing the former making it look as professional as we can it'd be nice to see what it looks like when it's running in Excel so just as a quick sort of preview for the things we'll be covering in the next video what we're going to do is click once on the background of the form and then just as you would with a subroutine or another procedure you can either click on the green triangle button on the toolbar or press the f5 key on your keyboard when you do that you ought to be taken back into Excel with your form appearing on-screen in basically starter position as we talked about earlier on it's interactive after a fashion you can click on the button certainly although nothing will happen and you into text boxes and you can type things in of course nothing will happen when you put the buttons again but it's a nice visual indicator as to what your formal look like when it's actually running if it's not quite what you expected or not up to your standards it's not too bad an effort really um it's better than most the forms i designed in the real world i have to admit and what you can use close down the form and then make further design change to it and hopefully have enough tools at your disposal now and you know how to use them to make your forms look exactly the way you want i guess the rest is pretty much up to you if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 106,976

Rating: 4.9224377 out of 5

Keywords: vba, microsoft excel, excel vba, forms, user forms, wise owl

Id: O1L5cPQXv1Q

Length: 42min 37sec (2557 seconds)

Published: Thu Mar 31 2016