1 00:00:01,360 --> 00:00:08,140 In the controls in Excel files video we discussed how to put controls into your excel file and the different 2 00:00:08,140 --> 00:00:10,720 types of controls that are available. 3 00:00:10,720 --> 00:00:15,880 For example I've inserted a button control into this particular excel file and I can tie some code to 4 00:00:15,880 --> 00:00:20,980 this button that will run when we click on it and you can see a name of this button add employee and 5 00:00:20,980 --> 00:00:25,810 over on the right here I have a table that is just an example of a table you might use to track all 6 00:00:25,810 --> 00:00:32,440 of your employees so we can leave this Excel file just like this and let each individual user come in 7 00:00:32,440 --> 00:00:35,290 and add a row to this table and fill it out manually. 8 00:00:35,680 --> 00:00:42,640 Alternatively we could create a form that will actually pop up on the screen when we click on Add employee 9 00:00:43,060 --> 00:00:47,820 and we will fill out the form and the form add that information to the table for us. 10 00:00:47,830 --> 00:00:49,900 And there are a couple of reasons we might do this. 11 00:00:49,930 --> 00:00:55,630 One is to eliminate any human error we can build some check some error checks into the form that'll 12 00:00:55,630 --> 00:01:02,230 make sure we actually did enter a date for example or one of the roles that got entered is actually 13 00:01:02,230 --> 00:01:09,610 a valid role and we're only selecting full a part time if we wanted to generate an employee I.D. for 14 00:01:09,610 --> 00:01:13,480 example that's something that we could do encode into there's a couple different reasons that we might 15 00:01:13,480 --> 00:01:15,430 use code for something like this. 16 00:01:15,430 --> 00:01:20,480 This is a pretty simple example of what we might want to build a user form for. 17 00:01:20,560 --> 00:01:22,720 And so that's what we're going to be looking at in this video. 18 00:01:23,320 --> 00:01:28,630 So the first thing that we're gonna need to do is actually insert a form into our excel file so we'll 19 00:01:28,630 --> 00:01:36,260 go up to the developer tab and open up our visual basic and now up here and insert where we usually 20 00:01:36,260 --> 00:01:42,720 select module we're going to click on user form instead and then we can talk about this screen for a 21 00:01:42,720 --> 00:01:43,850 second. 22 00:01:43,950 --> 00:01:49,170 So this block you see here this is the actual form that you've inserted. 23 00:01:49,170 --> 00:01:51,180 This is the window that will pop up on the screen. 24 00:01:51,180 --> 00:01:53,670 This is what your users will see and interact with. 25 00:01:53,670 --> 00:01:58,920 So this is what we're gonna be working on and what we're going to be building this toolbox over on the 26 00:01:58,920 --> 00:02:00,490 right side of my screen. 27 00:02:00,510 --> 00:02:04,770 This contains all of the different controls that we'll be talking about in this video and we'll go through 28 00:02:04,770 --> 00:02:06,030 these one by one. 29 00:02:06,210 --> 00:02:11,520 If your tool box doesn't pop up automatically or if it ever disappears you can get it back by clicking 30 00:02:11,520 --> 00:02:12,290 on this tool box. 31 00:02:12,290 --> 00:02:14,930 Button up at the top of your screen. 32 00:02:15,070 --> 00:02:20,230 The other thing to pay attention to is the properties window over on the left which will cover in more 33 00:02:20,230 --> 00:02:24,100 depth since as a lot of really important properties that you might want to adjust. 34 00:02:24,250 --> 00:02:25,930 And we'll do that in a later video. 35 00:02:25,930 --> 00:02:29,830 So first let's expand our form here. 36 00:02:29,830 --> 00:02:31,630 I'll just click on the corner and drag it out. 37 00:02:31,630 --> 00:02:36,490 So we have a little bit more room to work with and we can take a look at all of these different controls 38 00:02:36,550 --> 00:02:38,620 in our tool box. 39 00:02:38,620 --> 00:02:41,500 So the first one to look at is this little A. 40 00:02:41,860 --> 00:02:43,210 And this just means label. 41 00:02:43,210 --> 00:02:44,260 So we'll click on it. 42 00:02:44,440 --> 00:02:50,230 Go ahead draw it on our form across the top like this and we can see it shows up and it says label 1 43 00:02:50,770 --> 00:02:54,910 if we double click into this box if you just click a second time into this box you'll see you'll be 44 00:02:54,910 --> 00:02:56,420 able to actually type in here. 45 00:02:56,530 --> 00:03:06,900 So we'll just type this is our example form something like this and then we'll click out of this we 46 00:03:06,900 --> 00:03:08,490 can see our tool box will pop back up. 47 00:03:08,520 --> 00:03:13,350 I'm going to move it over to the side of my screen do some rearranging so I don't have to keep finding 48 00:03:13,350 --> 00:03:14,150 it again. 49 00:03:14,190 --> 00:03:15,770 And so this is just the label. 50 00:03:15,780 --> 00:03:20,430 And what this means is this actually doesn't perform any purpose on our form. 51 00:03:20,430 --> 00:03:24,610 This isn't something that the user will be able to change or interact with it's just text that's going 52 00:03:24,610 --> 00:03:25,370 to show up. 53 00:03:25,420 --> 00:03:32,340 That will help us give instructions to the user or maybe clarify what this particular box on the form 54 00:03:32,340 --> 00:03:33,240 is supposed to be. 55 00:03:33,240 --> 00:03:35,490 So we definitely use labels a lot. 56 00:03:35,610 --> 00:03:39,530 And again we'll talk about this property window down here later but there are some things in here that 57 00:03:39,530 --> 00:03:41,180 you'll probably want to adjust. 58 00:03:41,310 --> 00:03:44,370 So for now we'll just leave our little example label up there. 59 00:03:44,400 --> 00:03:47,490 I'm going to click back onto the form to get out of the example label. 60 00:03:47,640 --> 00:03:53,860 Next I'm going to click on this which is a text box and I'll just click somewhere on the form drag it 61 00:03:53,860 --> 00:03:55,510 out create a little text box. 62 00:03:55,510 --> 00:03:58,480 Now this is something that the user actually will interact with. 63 00:03:58,480 --> 00:04:02,980 This is an empty text box that they're going to be able to type into and again and will cover the properties 64 00:04:02,980 --> 00:04:03,640 later. 65 00:04:03,640 --> 00:04:06,530 But just as an example we have this text box. 66 00:04:06,700 --> 00:04:12,070 Now we have lists box and combo box which are these two right here and there's a little bit of a difference 67 00:04:12,070 --> 00:04:20,620 between the two so combo box and we'll go ahead and just draw a combo box onto our form and we can just 68 00:04:20,620 --> 00:04:25,240 make it about the same size as our text box and we can see it has this little dropdown arrow right here. 69 00:04:25,240 --> 00:04:30,370 So with a combo box we can preset this list to be maybe on our form. 70 00:04:30,370 --> 00:04:37,870 This is like our role so maybe we have manager designer product support something like that and we can 71 00:04:37,870 --> 00:04:42,420 select this dropdown and we'll see that list populate and we can select from this list. 72 00:04:42,460 --> 00:04:47,680 Now on a combo box you can also type your own answer into the box itself and there's a setting that 73 00:04:47,680 --> 00:04:53,080 we can turn off to control that but that's the primary difference between combo box and a list box so 74 00:04:53,080 --> 00:04:59,150 if we draw a list box onto the form now and we'll draw this one a little bit bigger the list box you'll 75 00:04:59,150 --> 00:05:03,410 see doesn't actually have this dropdown arrow and that's because we're going to have a whole bunch of 76 00:05:03,410 --> 00:05:09,050 options that appear in this box if we have too many options they can scroll down using the vertical 77 00:05:09,080 --> 00:05:11,390 scroll bar that'll show up on the right here. 78 00:05:11,450 --> 00:05:15,860 But generally speaking a list box takes up a lot more space on your screen. 79 00:05:15,860 --> 00:05:21,200 And so a lot of people will just use combo boxes and we can control the property that allows the user 80 00:05:21,200 --> 00:05:23,240 to custom enter their own information. 81 00:05:23,240 --> 00:05:30,740 If we don't want them to be able to do that next we have a checkbox which is pretty self-explanatory. 82 00:05:30,980 --> 00:05:37,930 You can double click into this control here to change whatever the checkbox is so we'll say maybe full 83 00:05:37,930 --> 00:05:41,720 time or something like that and we'll click back out of it next. 84 00:05:41,790 --> 00:05:47,220 We have this option button and the difference between a checkbox and an option button is that you typically 85 00:05:47,220 --> 00:05:49,370 have multiple option buttons on here. 86 00:05:49,500 --> 00:06:01,620 So maybe this time we have one that says full time and one that says part time and in our code we can 87 00:06:01,620 --> 00:06:06,440 actually set this up so that these options are tied to one another. 88 00:06:06,440 --> 00:06:12,690 So if we select full time part time automatically is un selected or if we select part time full time 89 00:06:12,690 --> 00:06:14,640 is automatically on selected. 90 00:06:14,670 --> 00:06:19,590 So if you have a couple of different options and you don't want to use something like a combo box to 91 00:06:19,590 --> 00:06:22,110 select from them you might use an option group. 92 00:06:22,110 --> 00:06:26,790 And again this is all something that you would want to group together whereas the checkbox is something 93 00:06:26,790 --> 00:06:31,650 that is a standalone item you can have multiple checkboxes on your form and you can interact with them 94 00:06:31,680 --> 00:06:34,400 individually and get different results. 95 00:06:34,440 --> 00:06:41,810 Next we have this toggle button and what the toggle button does is you can either click it and it will 96 00:06:41,810 --> 00:06:47,300 stay clicked or you can click on it again and it will actually on click on this button. 97 00:06:47,300 --> 00:06:52,040 So this might be something where you have two different versions of the form maybe one version of the 98 00:06:52,040 --> 00:06:56,900 form is for managers and one version of the form is for regular employees. 99 00:06:56,900 --> 00:07:01,400 So you might click this toggle button to toggle your manager side of the form. 100 00:07:01,550 --> 00:07:06,740 And at that point we can go into code and we can say we want you to hide this field in this field and 101 00:07:06,740 --> 00:07:09,440 we want you show these new fields over here. 102 00:07:09,440 --> 00:07:12,270 And that's typically what you would use a toggle button for. 103 00:07:12,410 --> 00:07:18,200 It's a little more complicated than you really need in Excel VBA so you won't see this very often but 104 00:07:18,200 --> 00:07:25,620 it does have a unique application that might fit a specific purpose for you next we have this frame 105 00:07:26,310 --> 00:07:31,500 and this can be good for organizing different things so if I draw this frame on here you can see it 106 00:07:31,500 --> 00:07:37,080 shows up with the text frame one and we can change this in the property section of our form over here 107 00:07:37,110 --> 00:07:43,530 and caption and again we'll cover the properties later on that just as an example we'll say full or 108 00:07:43,530 --> 00:07:51,570 part time something like this and maybe we want to put our option groups into this frame to help organize 109 00:07:51,570 --> 00:07:54,150 our form a little bit so we can drag them on there. 110 00:07:54,180 --> 00:07:56,340 And now these option groups are part of this frame. 111 00:07:57,530 --> 00:08:02,630 So we can see if I drag this frame it's actually going to pull my option groups with it. 112 00:08:02,630 --> 00:08:08,720 So this is a super helpful tool for organizing your form makes it easy to shift things around later. 113 00:08:08,870 --> 00:08:13,880 And it also makes it easy for the user to identify all of this information is grouped together. 114 00:08:13,880 --> 00:08:19,910 This is one subsection of my form so it's useful for cleaning up your form as well. 115 00:08:19,910 --> 00:08:24,860 So I'll pull my tool box back over here and the next thing we're going to look at is a regular command 116 00:08:24,860 --> 00:08:29,830 button so I'll go ahead and insert one of these on the form and we can see right off the bat. 117 00:08:29,840 --> 00:08:35,780 It looks very similar to the toggle button and also to the command buttons that we can insert onto our 118 00:08:35,780 --> 00:08:37,140 regular excel file. 119 00:08:37,310 --> 00:08:39,530 And this serves a regular same sort of purpose. 120 00:08:39,530 --> 00:08:44,350 So if we click on this button we can tie code to it that's going to run after we click on this. 121 00:08:44,350 --> 00:08:51,290 So we'll go ahead and create a closed button and then I'll insert another one and I will call this button 122 00:08:51,950 --> 00:08:59,280 add and I found it's best to generally go ahead and give your users a close button even though they 123 00:08:59,280 --> 00:09:02,900 could also exit out of the form by clicking on the actual exe itself. 124 00:09:03,030 --> 00:09:07,440 And that's just because it gives them something that they feel is an alternative to the actual Add button. 125 00:09:07,440 --> 00:09:11,210 So if they don't want to add this information they don't want to save this information. 126 00:09:11,310 --> 00:09:17,670 They can use the closed button instead and know that none of this is going to get saved so these next 127 00:09:17,670 --> 00:09:25,110 four controls here the scroll bar spin button image and reference edit controls aren't used as often 128 00:09:25,170 --> 00:09:30,510 as the rest of these controls are you probably won't find yourself ever in a situation where you need 129 00:09:30,510 --> 00:09:35,700 to use these for that we'll go ahead and go through them so we'll click on scroll bar and click onto 130 00:09:35,700 --> 00:09:42,600 our screen and you can see the scroll bar shows up and this looks more like a slider than it does really 131 00:09:42,630 --> 00:09:45,420 a control or a scroll bar. 132 00:09:45,420 --> 00:09:50,910 So this is a kind of situation where maybe you have an interest rate or something and you can drag this 133 00:09:51,330 --> 00:09:54,550 bar up or down to change that interest rate. 134 00:09:54,600 --> 00:10:00,270 So there's a lot of code that goes into making this operate properly and it's not used as often because 135 00:10:00,270 --> 00:10:05,640 it's just as easy for the user to manually type in the interest rate in something like a text box. 136 00:10:05,640 --> 00:10:10,110 So you won't see the scroll bar used very often. 137 00:10:10,110 --> 00:10:14,240 You can also have a spin button which will go ahead and insert over here as well. 138 00:10:15,030 --> 00:10:17,760 And the spin button you can see are just up and down arrows. 139 00:10:17,760 --> 00:10:20,610 So these are just used to increase or decrease the value. 140 00:10:20,610 --> 00:10:25,410 So it's good for fields like age for example where you might just want to increase or decrease your 141 00:10:25,410 --> 00:10:26,520 value by one. 142 00:10:26,550 --> 00:10:29,190 But again you could just use a text box for this. 143 00:10:29,190 --> 00:10:34,240 So not very common to see the spin button on a form. 144 00:10:34,260 --> 00:10:37,730 Now the images control I'm not even going to insert on the form. 145 00:10:37,770 --> 00:10:42,750 They get a little messy because you have to make sure the image you're pointing to is on a shared network 146 00:10:42,810 --> 00:10:43,740 drive. 147 00:10:43,730 --> 00:10:49,320 If the image is on your local computer storage drive and someone opens this form on a different computer 148 00:10:49,680 --> 00:10:51,750 the form will actually enter a loop. 149 00:10:51,750 --> 00:10:54,210 Error trying to locate that file. 150 00:10:54,240 --> 00:10:59,400 So generally the image control isn't used very often and it's not usual that you would need to have 151 00:10:59,640 --> 00:11:03,480 an image on your form in the first place. 152 00:11:03,490 --> 00:11:10,330 Now the reference edit control lets the user select a range of cells in the workbook and this can be 153 00:11:10,330 --> 00:11:15,610 helpful if your code does something like automatically formatting arrange yourselves or if you're searching 154 00:11:15,610 --> 00:11:21,040 through data for a particular value but nine times out of ten your range is going to be predetermined 155 00:11:21,040 --> 00:11:22,690 using something like a table. 156 00:11:22,720 --> 00:11:25,810 So this won't be used very often. 157 00:11:25,810 --> 00:11:31,810 So the last control that we haven't talked about yet is this multi-page and the TAB strip and these 158 00:11:31,810 --> 00:11:33,790 both do similar things. 159 00:11:33,850 --> 00:11:40,330 So we'll talk about the multi-page since this is more common to see and forms. 160 00:11:40,330 --> 00:11:46,960 So I'm actually going to expand my form and give us some room over here to insert this new control and 161 00:11:46,960 --> 00:11:51,840 I'll click on the multi page and go ahead and drag it out. 162 00:11:51,940 --> 00:11:54,480 Now the tab strip does something very similar. 163 00:11:54,490 --> 00:11:59,560 It doesn't have this kind of frame that you see on the multi-page so most people will just go ahead 164 00:11:59,560 --> 00:12:01,460 and use the multi page. 165 00:12:01,510 --> 00:12:06,670 So this is where we were talking about earlier with our toggle button for example you might be toggling 166 00:12:06,670 --> 00:12:07,860 back and forth between. 167 00:12:07,870 --> 00:12:10,720 I'm adding a manager versus I'm adding an employee. 168 00:12:10,810 --> 00:12:15,580 This is a separate way that you might accomplish that this is also good for breaking out if you have 169 00:12:15,580 --> 00:12:18,880 lots of information that's kind of too much for one form. 170 00:12:18,880 --> 00:12:21,400 Maybe you don't want to use a whole bunch frames like this. 171 00:12:21,400 --> 00:12:26,380 Maybe you want to actually separate out that information onto separate pages that we can toggle back 172 00:12:26,380 --> 00:12:28,620 and forth between. 173 00:12:28,660 --> 00:12:36,070 So if I for example grab my toolbox and if I insert a label onto my page one here and we see this says 174 00:12:36,100 --> 00:12:43,690 label two when I toggle to my page to that label to disappears and this works for all of our other controls 175 00:12:43,720 --> 00:12:44,140 as well. 176 00:12:44,140 --> 00:12:51,340 So if I insert for example an actual text box that we could type into and insert this onto my page two 177 00:12:51,340 --> 00:12:54,870 of my tabs control here when I come back over. 178 00:12:55,010 --> 00:13:01,030 Now on toggling between page one which has the label and page two which has the actual text box. 179 00:13:01,150 --> 00:13:06,040 So this can be a really powerful tool for if you have a form that has a lot of information on it that 180 00:13:06,040 --> 00:13:11,620 needs to get filled out or maybe you have different roles in each of these roles needs different information 181 00:13:11,680 --> 00:13:12,960 to go along with it. 182 00:13:12,970 --> 00:13:18,610 That's the kind of thing where you might want to have a multi-page document like this going across the 183 00:13:18,610 --> 00:13:24,340 top of your screen where they could individually click on each page and walk through that information. 184 00:13:24,340 --> 00:13:26,740 It's also really good for building a dashboard. 185 00:13:26,740 --> 00:13:32,530 So if you have a lot of different functions that go into your database maybe you have an entire set 186 00:13:32,530 --> 00:13:39,550 of functions for an administrator and then an entire set of functions for a salesperson and maybe a 187 00:13:39,550 --> 00:13:43,250 third page and that's all a set of functions for your production line. 188 00:13:43,270 --> 00:13:50,980 This is something where you could put a whole bunch of buttons actually on to these pages and use this 189 00:13:51,010 --> 00:13:57,270 as your dashboard rather than trying to create a dashboard on your actual excel file. 190 00:13:57,280 --> 00:14:04,600 So these are all of the different tools that you would see the different controls that you might insert 191 00:14:04,630 --> 00:14:06,010 onto a form. 192 00:14:06,010 --> 00:14:13,030 And an example of the form would be building something like the employee ad form which we will be doing 193 00:14:13,030 --> 00:14:17,920 over the course of the next couple of videos to actually set this up and tie it to code and make sure 194 00:14:17,920 --> 00:14:19,270 everything's running smoothly.