Become a member of our Patreon. The link is in our video description. If you have any questions you want answered by one of our instructors, please join our offsite community. The link is in the description as well as always. If this course has exercise files, you'll find them in the video description below. Now we're gonna get started with lesson six, working with forms and controls. We have several topics to cover in this lesson. You're gonna start learning about the user form object, and then it's.
Properties, events, and methods. There's a toolbox we'll be using to add controls to the user form. We'll go over. What kind of controls will be able to access from the toolbox? We'll be adding controls to the form and modifying the control proper. Formatting the controls. And then we are gonna apply code to the controls to determine what their use is going to be. Finally, you'll learn how to launch the form via code. We're gonna be using our sales fiscal year file as well as accessing another.
Text file named coding controls during this lesson so far in this course you've created message boxes and input boxes. You've learned that message boxes, display messages, and accepts input from the user via buttons. In our case, it was okay in cancel buttons. And action is then taken on that input via coding input boxes, display messages to the. But also allow a user to type in one input or have it default, just.
Like a message box input action is taken on the input box via coding. The user form objects allow for multiple inputs, which can then be coded to perform specific actions. These inputs can be text check boxes or dropdown lists, for example, and they're known as controls, user forms have properties, events, and methods that can be accessed these next few slides or for your reference..
And again, I will be walking you through these during the exercises in this lesson. The one that I do wanna cover with you now are form control prefixes. We've used lowercase mod as a prefix for a module. And so if you're looking at them in a list, you'll see. All begin with Mo D we have prefixes for form controls, and I'll guide you through these as we used them. But for example, C K would be a check box..
CBO is a combo box, and we'll be utilizing these throughout the lesson. Each form control has its own set of properties and they differ depending on the type of control. But this slide has a list of common control properties that most controls have. So you have like a height property, a with property, that type of thing. And again, this slide deck is in the video description. So you'll have access to these reference slides..
We're ready to get hands on now with the user forum object. Before we insert a user form object into our sales fiscal year Excel file. Let's talk about the different types of modules. Again, we did a brief review early in the course, your standard code is stored in modules. Like in this project, we have module one. We could have renamed it. We didn't, we've also in a different file created event procedures..
And if you expand your Microsoft Excel objects folder in the project, Explorer window, you'll see one for each sheet and all the way at the bottom of all of the sheets, you. One called this workbook. And in our vehicle's macro enabled file, we did an event procedure based off of the new vehicle's sheet tab, which was known as sheet three in the system, your sheet modules, and this workbook module are where you store event, procedure codes..
And I'm gonna just go back up and collapse. The Excel objects folder. So I don't have to look at all of those sheets. So we have our regular module where you store your function procedures, your sub procedures, and then you have your event, procedure modules, your worksheets, and this workbook. Now we also talked about early in the course, a class module, the modules we've been using, like module one, that's a standard module class modules. We're not gonna use in this course. However, those are used to create your own objects..
Posts Related:
And so now we're gonna create a user form and that actually sets up a forms folder.
And no matter how many forms you create in this particular file, they will all go into that forms folder. So it's almost like another module just for form user form objects. And you'll see that now. As we go to insert our user form. So we're gonna just go up to the insert menu and choose user form. And now a couple of things happened. You have the blank grid, a framework for your user form object on your.Screen and attached to it is a toolbox which you'll use to access the controls that you're going to put on the form. Now, I'll give you a heads up here. Sometimes if you have double monitors, your toolbox may show up on another monitor and it could be behind some other open windows. So if you need it, you may have to search it out. If I click on a blank area here or try to deselect this user form, it'll disappear, but then it'll reappear. When I click back on the user form typically, and we can always get.
It back by using the view menu. Now I'm gonna close my toolbox for a minute. It has an X and its upper right hand corner so that I can see in the project Explorer window that I now have a forms folder with one form in it, which it names by default user form one. Now we're gonna use the properties window to change some of the properties for our user form object. And again, if your property's window is not showing on your left hand pain, you can go to your view menu..
And selected, or you can press F four to bring it up. We are gonna give the form a name using the appropriate prefix. We're gonna give it a caption, which will show in the title bar of the form. And we're gonna give it a height and width measurement. So in the properties window, we're gonna start with the name property. We're gonna double click name and parent. And we're gonna type lowercase, F R M capital G generate capital R reports. So that's the system name of the form FRM generate reports..
The next property we're gonna do is the caption property. And again, that shows in the title bar and we're gonna type just in plain English. And as you're typing it, you can look at the title bar and see it populating generate sales reports, and I'm pressing enter. After I change a property, we're gonna double click height and we're gonna make it 300 and we're gonna scroll down if necessary, double click with, and we're gonna change that value to 400. And by the way, the height and width values would change. If we manually resize the form by dragging its sizing handles..
In the next exercises, we're gonna add controls to the form and set properties for those controls. So the first thing we need to do is display our toolbox again, and we can get that from the view menu because we closed it by using its X. So we're gonna just go to view toolbox and the toolbox shows up again and we can move it so you can see it clearly. And if you look at the controls in the toolbox, as you hover over them..
So the first one is your mouse pointer. It's the select objects control. The next one is a label. Third one is a text box. You have a combo box list, box, so on and so forth. So we'll be using several of those controls, placing them on our form, and you'll learn also how to size and align your controls a little bit later in these exercises. So let's get started. We're gonna add, start date and end date label controls to the form..
So in my toolbox, I'm gonna look for the label control. It's the capital letter a and I'm gonna click on it on a toolbox. And now if I move my mouse over the form grid, it looks like a crosshair. I can click and hold and draw the label control. Or I'm gonna just click in the lower left corner of the form and it places the label control there for me. And so now with that label control selected, we're gonna change some of its properties. So typically you don't name labels, unless they're gonna be some.
Kind of code attached to them. So we're not gonna use the name property. We want to look at the auto size property. So the auto size property is set to false. If we double click auto size, it will change it to true and we want it to be true. And then the caption property is the text that shows on the label. So I'm gonna double click caption. and I'm gonna type end date in plain English, followed by a colon and press enter. And now you see the end date, text populating the label, and.
Then you notice how it wrapped end date within that label control. We don't want it to do word wrap.
So the last property is word wrap and it's set to true. We're gonna double click it to set it to false. So it doesn't wrap the words in the label and I'm gonna just manually move. So I want my mouse pointer to look like a four headed arrow on that end date label. And I'm gonna just drag it down just a little bit..We also want a start date label. So instead of starting from scratch, I'm gonna just right click on the end date label, and I'm gonna choose copy. And then I'm gonna right click in a blank area of my form grid and choose paste. So now I'm gonna move the copy of the end date label above the existing end date label. And now it's just a matter of changing some of its properties, couple of things, we didn't give the label a name. So we don't have a label name here. Our other property auto size is set to true already because we copied it and.
Word wrap is set to false and it's the caption property we're gonna change. So I'm gonna double click caption, and I want it to say start date in plain English with the colon and press enter. So copying it actually saves you time because it copies some of the pertinent property information as well. Now we're gonna add two text box controls. We'll do the first one and then copy it and modify the.
Properties for the second one. We're gonna find in the toolbox, our text box control. And it's the one that has the lowercase AB to the right of your label, going to select it. And again, when my mouse is on top of the form grid, it looks like a crosshair and I'm gonna click to the right of the end date label and let it place the text box there. I'm gonna move mine up just a little bit. So it's more in line like that. And with that text box control selected, we are gonna give it a name..
So in the properties window, we're gonna double click name. We're gonna use lowercase T X, T as the prefix for a text box, and we're gonna type capital E and capital D date. So T X T end date, and the other property that we're gonna change for the text box is it's width property, and we're gonna make the width 50, and then we're gonna copy that text box, paste it on the grid and move it down. So it's to the right of the start date label, and on the copy, we're gonna just.
Change the name of it to T X T start date and notice the name property didn't come over with the copy because you can't have the same name for controls. On a form object. So TXT capital T capital S start capital D. Date is what we're naming that text box. When this form is completed and totally coded, it will generate sales reports.
Based on user choices in the form. And as such, we wanna add two buttons to the form. We wanna add a display button and a cancel button. So we're gonna use the command button control in your toolbox. The command button control is in the second row. It's the second one on my screen in the second row. And it looks like a lowercase B as well. When you hover over it, of course the screen tip will let you know it is command button. So we're gonna go ahead and select it in the toolbox..
And we're gonna place a command button like in the upper right hand corner. Of the form in the property's pain, we're gonna name it using C M D as the prefix for a command button, capital D display. So CMD display is the name and the caption. We're gonna go down to the caption property and that's the text that shows on the button and it's just gonna be display in plain English. And the last property we're gonna change is the default property. Default property is currently set to false..
We're gonna double click it to make it true. So the default property, when it comes to a command button is you can press enter to make that button happen instead of having to click on it. That's what the default property does. And you can only have one default. Property on a button on your object, your user forum object. So we have CMD display and we gave it a caption of display and we changed a default property to true. Now we're gonna copy that display button, paste it on the grid and.
Drag the pasted version underneath your original display button. And again, don't worry about alignment. We'll fix that later. So on the copy, notice the name didn't carry over. Right? We're gonna name this one. C MD capital C cancel. We're going to give. We're gonna change the cancel property here from false to true. And that means you can use the escape key on your keyboard to make that button happen..
And so we set cancel the true, the caption property. We're gonna change it to cancel and a default property. We need to change default because you can only have one default command button. And that is the display button. If you click on a blank area of your grid, you'll see the display button has like that dark border around it. Whenever you see a button like that and a dialogue box, it means you can press your inter key to make that button happen..
This would be a good time for us to go ahead and save right from the toolbar before we continue adding controls to this form. Now we're gonna add a combo box control to our form. So the combo find a combo box control in your toolbox and select it. And we're gonna put it in the, like the lower right hand corner of the form in the properties pain. We're gonna change its name to prefix of C B O, and then capital M month..
We're gonna use its list rows property. And that just says how many rows will be in the dropdown list. So we're gonna change list rows to 12, and then the match required property. We're gonna double click that to change it from false to true in visual basic control. A combo box by default says no matches required, which means that the end user can type in their own entries and use them from the list..
We don't want that to happen. We want them to pull just from the list that we're gonna populate later. So we have our combo box in the lower right hand corner, and now we're gonna click in a blank area of the grid and you can copy your start date or end date label because we're gonna want another label and we're gonna paste it onto the grid. And then we're gonna move that label to the left of the combo box. I'm gonna just move mine down a little bit. And again, we'll do some alignment stuff in a little..
So now we just have to change the label properties. Now we're gonna use this label later and code some stuff for it. So we want to give it a name. So we're gonna name this L B L prefix for a label month, capital M month. And we're gonna give it a caption of four, which month question mark. And then I need to move my label over cuz it auto expanded. So it's not overhanging my combo box. So your form should look similar to mine at this point..
Now we're gonna add two frame controls to the form and we'll add the first one. Of course, copy it to create the second one. But the frame control is utilized to group related other controls together. So we're gonna start by adding our first frame control on my toolbox. The frame control is the first one in the second row. It has an X Y at the top of it. We're gonna click that and I'm gonna click like in the upper.
Left corner of the form grid. And now in order to make space for the other frame, I'm gonna move this frame over as far left as I can. Where I can actually see the borders of it. And then I'm going to decrease its width manually by just clicking on the right center sizing handle. So my mouse looks like a double-headed arrow and dragging it backwards about that much. And so we're gonna give the frame a name and it's gonna be F R a is the prefix.
For frame control capital P period. And we're also gonna give it a caption of period. And the caption is what shows up the upper left hand corner of the frame. Now we're gonna copy that frame, paste it onto your grid and drag the pasted frame to the right of your period frame. And for this one, we're gonna change the name property. To F R a sales and we're gonna give it a caption of sales..
So the next thing we're gonna do is we're gonna add three option button controls into each frame. We're gonna find the option button in the toolbox, and it looks like the little circle with a.in the middle of it, the next to the last one in my first row. And I'm gonna select the option button, and now I'm gonna click inside the period frame, and then I'm gonna copy that option button, and I'm gonna paste it twice inside the period frame..
So you'll have three option buttons inside your period frame. And if you click on the top one, you'll see in the name property it's option button one second. One is two second. One is three. You kind of want to have them in the right order for tab order purposes a little bit later. And again, we'll deal with the alignment a little bit later, and I'm gonna show you how to get three option buttons in a sales frame. Now, too. Another efficiency tip here, what we can do inside the period frame, just click in a blank area inside the period frame and do control a, and it selects.
All three option buttons, right? And then you can just do control C to copy them and then right. Click inside your sales. And paste let's do the properties for these option buttons. So the first one inside the period frame should say, option button one as its name, property, its name, property is going to be O P T as the prefix capital M month. And we're gonna give it a caption of month and you can see the caption shows.
Instead of option button one, the second option button in the period frame, which name is currently option button two. We're gonna change it to O P T all with a caption of all and the last option button. Option button three in the period frame, its name is going to be O P T other with a caption of other. So we have month all and other option buttons in the period frame, let's go to the sales frame. So the first one in the sales frame, since we copied it, it's now option button four..
And we're gonna give that a name of O P T salesperson with a caption of salesperson. And I'm making that caption plain English two words. And then the next one in a sales frame, option button five, that one is gonna be O P T model with a caption of model. And last but not least option button six, its name is going to be O P T classification with a caption of classification..
So now we're ready to do some formatting of our form controls and we'll start with alignment in grouping. We're gonna start by aligning the option buttons in the period frame. So just click in the period frame and do control a like we did before to select the option buttons. And we can go up to the format tab, the format menu, hover over a line. And we're gonna click on lefts. So those three option buttons are aligned by their left edge..
Let's do the same thing in the sales frame. Also align those three option buttons by their lefts. Now we wanna align our two frames by their tops. So I'm gonna select the period frame, hold down my control key and select the sales frame. That's one way of getting them both selected, gonna go back to format, menu align, and this time we're gonna choose tops and we can click away and see their perfectly aligned by their top edges. Now, now we wanna group the options buttons in each frame so that if we move.
Any one of them, all of them will move. They'll retain their alignment and everything. So I'm gonna just click in the period frame and do control a, to select all three option buttons. I'm gonna go to the format menu and I'm gonna click on group. And now you'll see there's one border around all three option buttons instead of three separate borders. They're grouped together. Now do the same in your sales frame. Now I'm gonna show you how to use a selection net also known as a selection marque..
It has a lot of different names to select different objects. So we want to align the period frame and the start date and end date labels by the left edges. So I'm gonna click slightly above until the left of the period frame, click and hold and drag down until you start and end. Date labels are within that little guideline. So now when you let go, it's selected the period frame and the start and end date labels. That's called a selection net, and we're gonna go to format, align lefts..
Let's use a selection net to select our display and cancel buttons as well as our combo box. And let's align those by their left edges as well. At this point, we haven't coded any of our form controls yet, but we want to test our form, make sure it displays properly. And specifically we wanna test a tab order on the form, just make sure your form is selected and you can use the run button on the toolbar run sub user form button, or.
You can just press F five to run the form. And the form should pop up on your screen. It's on top of some other module. So we wanna test the tab order on the form. Now, when I bring up the form, notice that it's flashing in the end date field. That's where it's starting. If I press the tab key on my keyboard, it then goes to start date. If I press tab again, it looks like it has the display button and then the cancel button..