Working with Form Controls

Quick reference

• 00:04 In this module, we're gonna look at a different way for
• 00:07 driving our user input using form controls.
• 00:11 Now the key thing for form controls is that you find them on the Developer tab.
• 00:15 And as you can see right now, I do not have the Developer tab showing.
• 00:19 So if you're in a situation like I am,
• 00:21 you right click on any tab and you say customize the ribbon.
• 00:26 And then when this big guy pops up here on the right hand side, you're gonna find
• 00:30 there's an unchecked box for Developer, which you're gonna check and then say OK.
• 00:35 And you have a brand new tab in here that has a bunch of developer
• 00:39 controls on it like things like macros and what not.
• 00:43 But also, and more importantly, the option to insert some form controls.
• 00:48 Now I highly advise you to stay away from the ActiveX controls
• 00:51 unless you get into VBA coding.
• 00:53 What you really want to focus on is the form controls.
• 00:57 Now, let's grab this first guy, the combo box.
• 01:00 And what we're gonna do is we're gonna left click and drag, and
• 01:04 we're gonna create a combo box right around in this area here.
• 01:08 And you'll notice that it looks like a drop down widget.
• 01:11 Now what I'm gonna do with this is I'm gonna right click on it and
• 01:15 I'm gonna say format control.
• 01:18 And in here we have a few different items that we need to look at.
• 01:21 The first one is the input range, so I'm gonna click on the little arrow,
• 01:25 I'm going to scroll up to the top of the worksheet where I find my product lines.
• 01:30 And I'm gonna pick up that particular information.
• 01:33 And then it says, where's the cell link that I would like to link this thing to?
• 01:38 And for this, I'm gonna go back to the category number that we have in C35, and
• 01:43 we're gonna click on it, and we're gonna say OK.
• 01:48 And now you'll notice that it disappears and goes to 0, well, that's
• 01:51 a little bit weird, so let's go click on the drop down arrow, and select Tents.
• 01:57 And you'll notice that it picks up all of the tent data, and if I scroll down,
• 02:01 you can see the chart has updated to reflect it.
• 02:05 There's my Tarps and Groundsheets.
• 02:07 There's my Sleeping Bags.
• 02:09 So this is kinda neat.
• 02:10 The key part about these controls on these guys is that
• 02:15 they link into a cell and return the index number of the item selected.
• 02:19 And this is why those index and match functions become really, really important.
• 02:24 You have some limited formatting that you can play with on these guys.
• 02:27 I'm not gonna spend a lot of time working on this because they're
• 02:30 very, very limited.
• 02:30 You can't change the font size or anything like that but what we can do is we can
• 02:34 move this guy up, hold our Alt key to snap it into the top left hand corner and
• 02:39 we can hold our Alt key while we're resizing the buttons.
• 02:42 And at this point we can get something that looks okay for
• 02:46 what we're working with.
• 02:47 So there we are,
• 02:48 we've got a nice little drop down control that allows us to toggle our categories.
• 02:52 So what if I want a button to toggle through the individual categories?
• 02:57 Well, that is what a Spin button is for.
• 02:59 Now when we're adding new controls the only thing that's really important on
• 03:02 this is trying to get the width about right.
• 03:05 So I'm gonna set it up a little bit below here.
• 03:08 The reason being is because when I right click on Sleeping Bags here, and
• 03:11 go to the drawing tools tab, I can see that the height 0.12 or 0.21.
• 03:15 If I go over to my other control, I can see that the height also is 0.21.
• 03:20 I'm gonna hold down my control key,
• 03:23 I could change them if I needed to there, but in this case I don't.
• 03:25 So I'm gonna hold down my control key and say align to top.
• 03:30 And that will get them into approximately the right spot.
• 03:33 Now, I might want them a little bigger, I might want them a little smaller,
• 03:36 which I can obviously resize but that key part here is they're aligned nicely.
• 03:39 They look like they belong.
• 03:40 So now I'm gonna right click and I'm going to format control and
• 03:44 set up my spin button.
• 03:46 The most important piece here.
• 03:48 The minimum value and the maximum value have to be the lowest and
• 03:52 maximum number of items in your list.
• 03:54 So in this case one and four.
• 03:56 We only have four items and I never want to be at zero.
• 04:00 I'm gonna start with a current value of 1.
• 04:02 That works just fine for me.
• 04:04 My incremental change, every time I click up or
• 04:06 down is gonna change the current value by one.
• 04:09 And now I need a cell link.
• 04:11 The challenge is, though, my cell is hiding behind sleeping bags.
• 04:15 And this will not let me arrow to it.
• 04:16 So I'm gonna need to manually modify to make this C35.
• 04:20 And now, we're set.
• 04:23 Current value 1, Minimum value 1, Max 4, Incremental change 1, let's say OK.
• 04:29 And what we have at this point is it reverts to tens because I forced this to
• 04:32 have one.
• 04:35 If I go to two for Sleeping Bags it still works.
• 04:38 And now my spin button, I can go to Tarps and Ground Sheets up to Accessories.
• 04:41 It won't let me go any higher because I only had four items.
• 04:45 But I can spin back through the other categories.
• 04:47 So I now have the option to quickly jump to one place and
• 04:50 spin through the others quite nicely.
• 04:52 So how about my dates?
• 04:53 Well, let's try this, Developer tab, Insert.
• 04:56 This time we'll go with a scroll bar.
• 04:59 And on the scroll bar, I'm gonna go and hook this guy up, and
• 05:03 then hold down my Alt key and snap it up to the top here and make it size nicely.
• 05:08 That's all good.
• 05:09 So now we'll right click, format control, and with the scroll bar,
• 05:14 we want it to actually have a little bit more control over certain things here,
• 05:18 so we'll set the current value to 1 for January.
• 05:20 The Minimum value will be 1, and the maximum 12,
• 05:23 because it's gonna take us to December.
• 05:25 The incremental change will be one month at a time, but
• 05:28 the page change, I'm going to set to 3.
• 05:31 And then I'm going to link this into December.
• 05:35 Now, what's the page change all about?
• 05:38 Well, here's the thing.
• 05:39 It restates the chart.
• 05:41 If I arrow across, it's going to bring me a bunch of data.
• 05:44 But the page change is the bar in between.
• 05:47 And if I click on that, it will jump me three months at a time instead.
• 05:51 So I now have the ability to go backwards and
• 05:53 forwards on this chart as well, although sometimes backwards doesn't
• 05:58 always render correctly until you hit a couple of data points for some reason.
• 06:03 But overall it still looks pretty darn good.

Lesson notes are only available for subscribers.