1 00:00:01,410 --> 00:00:06,390 In this video we're going to talk about controls which you can build into your Excel file for the user 2 00:00:06,390 --> 00:00:07,830 to interact with. 3 00:00:07,890 --> 00:00:13,980 Generally speaking not every user is going to be familiar with VBA so you don't really want those individuals 4 00:00:14,070 --> 00:00:16,790 accessing your code when they don't understand it. 5 00:00:16,870 --> 00:00:19,570 And most of them wouldn't know how to run a macro anyways. 6 00:00:19,650 --> 00:00:23,310 So instead we can build controls into the file for them to use. 7 00:00:24,030 --> 00:00:30,690 So for example in my Excel file over here I go to my developer tab and open up my visual basic window 8 00:00:31,620 --> 00:00:36,780 we can see I've already inserted a module and I've pre coded a little message box here. 9 00:00:36,780 --> 00:00:40,350 So I called this subroutine Hello message with a little message box. 10 00:00:40,350 --> 00:00:41,740 Tell the User Hello. 11 00:00:41,880 --> 00:00:46,380 And if you aren't sure how I created this you can reference the videos on creating a subroutine and 12 00:00:46,380 --> 00:00:47,840 dialog boxes. 13 00:00:48,000 --> 00:00:52,470 Otherwise I'll go ahead and give you a second to pause and copy all of this code if you want to follow 14 00:00:52,470 --> 00:00:53,300 along. 15 00:00:53,310 --> 00:00:56,170 Otherwise you can just follow along on the video. 16 00:00:56,170 --> 00:01:00,860 So now let's go ahead and go back to our excel file normally to run a macro. 17 00:01:00,870 --> 00:01:06,510 We would go to the developer tab click on macros highlight the macro that we want to run. 18 00:01:06,520 --> 00:01:12,010 In this case Hello message and click the run button and we can see I get a little message box that says 19 00:01:12,010 --> 00:01:13,150 hello user. 20 00:01:13,150 --> 00:01:17,350 Your code has successfully run and this is how we expect the system to behave so click. 21 00:01:17,380 --> 00:01:18,230 OK. 22 00:01:18,370 --> 00:01:24,430 Now with this method you're expecting your user to have access to the developer tab and understand how 23 00:01:24,430 --> 00:01:31,210 to use the macros button how to find the appropriate macro and run that macro off from this window. 24 00:01:31,210 --> 00:01:36,550 You're also assuming that by giving your user access to the developer tab you're also giving them access 25 00:01:36,550 --> 00:01:41,320 to your visual basic to all of your code in the hidden aspect of your database. 26 00:01:41,410 --> 00:01:43,780 And that's not necessarily something that you want to do. 27 00:01:43,810 --> 00:01:50,410 So instead we can build in a graphical user interface or G UI which you'll sometimes hear referred to 28 00:01:50,440 --> 00:01:51,850 as a gooey. 29 00:01:51,850 --> 00:01:59,270 Now this means that we can go ahead and use this insert button under controls in the developer tab God 30 00:01:59,280 --> 00:02:05,970 and click on this first button control here we can see it says button form control and go ahead and 31 00:02:05,970 --> 00:02:10,560 just draw a big square on your Excel file. 32 00:02:10,560 --> 00:02:13,720 Now we're going to tie this to a macro and this will make sense in a second. 33 00:02:13,720 --> 00:02:19,320 So just go ahead and click on the hello message and click ok and we can see now we have a really big 34 00:02:19,320 --> 00:02:22,230 button that's appearing in our excel file. 35 00:02:22,290 --> 00:02:28,770 Now the form control that we just entered onto our excel file if we look in the insert menu this button 36 00:02:28,800 --> 00:02:31,080 is found under foreign controls. 37 00:02:31,180 --> 00:02:37,620 There's a different version of controls called Active X controls now form controls are much much simpler 38 00:02:37,620 --> 00:02:41,040 and they're built into the Excel system itself. 39 00:02:41,050 --> 00:02:46,410 Active X controls are a little bit more flexible but lots of the company systems won't actually trust 40 00:02:46,440 --> 00:02:51,320 the Active X controls and it will disable them as soon as you open up the excel file. 41 00:02:51,420 --> 00:02:53,320 There are also Microsoft specific. 42 00:02:53,340 --> 00:02:56,700 So they generally don't work on Mac operating systems. 43 00:02:56,700 --> 00:03:01,800 So as a general rule of thumb you're going to want to use form controls to build your duty rather than 44 00:03:01,800 --> 00:03:08,410 active X controls so let's talk about this button that we just inserted when I click on this button 45 00:03:08,830 --> 00:03:15,790 it will automatically run my code for me it's because we've tied our code to this button click but let's 46 00:03:15,790 --> 00:03:21,600 say I want to select this button and I want to do something say change the text to select the button 47 00:03:21,610 --> 00:03:27,460 don't left click right click on the button and this will give you the option to edit the text to assign 48 00:03:27,460 --> 00:03:30,540 a different macro to change the formatting of it. 49 00:03:30,580 --> 00:03:39,160 So let's go ahead and change the edit text and we'll change this caption to read Message button and 50 00:03:39,160 --> 00:03:43,140 we can go ahead and click out of the button and we'll see that the text has been changed. 51 00:03:43,180 --> 00:03:49,060 We can also change the format of this so we'll go down to format control and we can see we can make 52 00:03:49,060 --> 00:03:55,240 this superscript a subscript we can make things bold if we wanted to go through properties we can change 53 00:03:55,240 --> 00:03:58,820 what happens the behavior of the button as you change things. 54 00:03:58,900 --> 00:04:04,270 So if you want your button to be seated in a particular set of cells you might want to say move and 55 00:04:04,270 --> 00:04:09,610 size with cells whereas if you want to just have your button set on your screen and be totally unrelated 56 00:04:09,610 --> 00:04:13,450 to the cells you can use Don't move or size with the cells. 57 00:04:13,450 --> 00:04:17,470 So there's a bunch of different things that you can do to format your button to behave however you want 58 00:04:17,470 --> 00:04:22,200 it to behave changing the size changing the margins all of that sort of thing. 59 00:04:22,200 --> 00:04:23,630 So for now we'll click Okay. 60 00:04:23,740 --> 00:04:28,300 We can see my text has become bold because that's what I just changed in my format. 61 00:04:28,300 --> 00:04:32,940 The last thing that you'll want to use if you right click is a sign macro. 62 00:04:33,400 --> 00:04:40,290 So if we click on a macro we can see it pulls this window back up and this is that first pop up winter 63 00:04:40,300 --> 00:04:45,130 that we got when we inserted the button initially and usually this will list all of the macros that 64 00:04:45,130 --> 00:04:47,180 are found in this workbook. 65 00:04:47,440 --> 00:04:52,000 So usually you'll have two or three different macros and you'll want to be careful about which one you 66 00:04:52,000 --> 00:04:53,170 assign to which button. 67 00:04:53,170 --> 00:04:57,880 So in this case if we wanted to change this to something else you could open up this window click on 68 00:04:57,880 --> 00:05:02,050 whichever one you wanted to tie to your button and click OK to change it. 69 00:05:02,770 --> 00:05:09,110 So again when I click on this button we see my code has been successfully run. 70 00:05:09,260 --> 00:05:13,490 Now there are different types of controls outside of just the button control. 71 00:05:13,490 --> 00:05:20,360 There are dropped down the list box combo box check boxes there's a whole bunch of different options 72 00:05:20,360 --> 00:05:22,800 that you can use in these form controls. 73 00:05:22,820 --> 00:05:28,010 So just as an example let's just click on this checkbox and draw a little checkbox on here somewhere 74 00:05:28,640 --> 00:05:33,560 and we can see it pops up as checkbox 3 we can click it we can on click it to check and uncheck this 75 00:05:33,560 --> 00:05:34,670 box. 76 00:05:34,670 --> 00:05:38,760 So this is a different way that you might allow your user to interact with your code. 77 00:05:38,780 --> 00:05:44,630 Generally speaking if you're going to be putting things that are more complex than just buttons on your 78 00:05:44,630 --> 00:05:49,790 Excel file for user to interact with you're going to want to create a user form instead. 79 00:05:49,790 --> 00:05:51,140 And there are a couple reasons for that. 80 00:05:51,140 --> 00:05:56,930 The primary reason being a user form is much easier to control from the VBA window. 81 00:05:56,960 --> 00:06:03,860 So just to give you an idea of what that might look like if I open up my VBA window I can insert a user 82 00:06:03,860 --> 00:06:09,740 form and we'll talk about all of this in greater detail later but we can see that on my user form I 83 00:06:09,740 --> 00:06:17,420 have the same set of controls I can insert a text box here I could insert buttons you can insert whatever 84 00:06:17,420 --> 00:06:20,500 you want onto this user form so it's the same set of controls. 85 00:06:20,660 --> 00:06:25,490 The difference between the controls appearing on your user form and the controls appearing on your Excel 86 00:06:25,490 --> 00:06:31,970 file is that these controls on the user form are a lot easier to tie direct code to they act almost 87 00:06:31,970 --> 00:06:37,790 like active X controls which are the more complex controls that we were talking about in this dropdown 88 00:06:37,790 --> 00:06:38,750 window. 89 00:06:38,750 --> 00:06:46,970 So generally as a rule of thumb it's best to put only button basic simple controls onto your user excel 90 00:06:46,970 --> 00:06:54,020 file to create user dashboard and those buttons can be linked to more complex data entry fields like 91 00:06:54,050 --> 00:06:55,280 a user form. 92 00:06:55,310 --> 00:06:56,300 So that's the best way. 93 00:06:56,300 --> 00:07:02,210 Generally speaking to set up your Gilly and the main thing that you need to remember is to use the insert 94 00:07:02,210 --> 00:07:09,560 button and use form controls right click the control to assign the macro so I will go ahead and talk 95 00:07:09,560 --> 00:07:14,570 a little bit about the active X controls just in case you ever do have a situation where you need to 96 00:07:14,570 --> 00:07:16,490 use an active X control. 97 00:07:16,490 --> 00:07:22,390 Let's go ahead and insert an active X button on here and we can see right off the bat. 98 00:07:22,390 --> 00:07:27,220 This is already a very different control when I've inserted this button it didn't pop up and asked me 99 00:07:27,220 --> 00:07:33,150 what macro that I wanted to tie to this button like it did when we entered our form control up here. 100 00:07:33,220 --> 00:07:37,210 Now we can see this is also automatically activated design mode. 101 00:07:37,210 --> 00:07:39,630 And what this does is I take this out of design mode. 102 00:07:39,640 --> 00:07:43,840 If this button had a command or something to run with it a macro tied to it. 103 00:07:44,200 --> 00:07:48,960 When I click on that button it would automatically run when I turn on design mode. 104 00:07:48,970 --> 00:07:52,720 If I click on this button it instead selects the button for me. 105 00:07:52,840 --> 00:07:58,300 So as we were talking about what this form control we have to right click on the form control to pull 106 00:07:58,300 --> 00:08:02,290 up a window to interact with it with active controls. 107 00:08:02,290 --> 00:08:07,960 You can turn on design mode and instead of right clicking them you can just regularly click on them. 108 00:08:07,960 --> 00:08:12,670 Now when we do right click on this we can see we have a properties option here. 109 00:08:12,670 --> 00:08:17,530 So we'll go ahead and click on properties our properties window has popped up and this is what it looks 110 00:08:17,530 --> 00:08:18,150 like. 111 00:08:18,220 --> 00:08:24,910 You'll notice this looks very similar to the properties window in our actual VBA window that we've interacted 112 00:08:24,910 --> 00:08:31,910 with a couple of times in the past so we can see we can actually rename this button we can change the 113 00:08:31,940 --> 00:08:33,640 color of this button. 114 00:08:33,710 --> 00:08:39,050 The caption is the text that appears on the front of the button so we can change all of these different 115 00:08:39,410 --> 00:08:41,750 appearance options of this button. 116 00:08:41,750 --> 00:08:47,540 It is a little more in depth than just a regular form control button like we looked at earlier. 117 00:08:47,540 --> 00:08:49,610 The other thing we can do when we write click on this. 118 00:08:49,610 --> 00:08:57,050 We can view the code that's tied to this button and we can actually put code directly into this window 119 00:08:57,590 --> 00:09:04,130 and this would be tied directly to our button that's appearing on our main fact sheet here. 120 00:09:04,640 --> 00:09:09,890 So this is a little bit different than the farm control where we pre wrote the code and then came back 121 00:09:09,890 --> 00:09:11,660 through and tied it to the button. 122 00:09:11,900 --> 00:09:17,810 If you use Active X controls you can insert the button first and then Ty code and build it into this 123 00:09:17,810 --> 00:09:19,370 button click itself. 124 00:09:19,370 --> 00:09:20,600 So it is a little bit different. 125 00:09:20,600 --> 00:09:26,900 It's a little bit more complex and this is actually how most of our user form controls are going to 126 00:09:26,900 --> 00:09:27,380 behave. 127 00:09:27,740 --> 00:09:33,140 And so we'll talk about that when we'd go through user forums but just in case you ever do need to use 128 00:09:33,140 --> 00:09:35,840 an active X control for whatever reason. 129 00:09:35,840 --> 00:09:39,350 This is how they would behave this is how you would interact with them. 130 00:09:39,530 --> 00:09:44,510 And of course you would want to remember to turn off design mode whenever you're done building your 131 00:09:44,960 --> 00:09:46,280 user interface. 132 00:09:46,280 --> 00:09:52,310 So again in this video we talked about controls we talked about active controls and form controls. 133 00:09:52,310 --> 00:09:57,680 We talked a little bit about user forms which we'll discuss more in a later video. 134 00:09:57,680 --> 00:10:03,230 And generally speaking we decided if we are building a graphical user interface into our excel file 135 00:10:03,590 --> 00:10:10,460 it's best to use simple form controls like buttons for your user dashboard and then put the more complex 136 00:10:10,460 --> 00:10:19,240 controls like your active X controls into user form or some other method of interaction for your user. 137 00:10:19,250 --> 00:10:22,000 And again we'll talk about user forums more in a later video.