Locked lesson.
About this lesson
A variety of different objects that add some visual style to your dashboards, as well as provide mechanisms to force valid data entry
Exercise files
Download this lesson’s related exercise files.
Working with Form controls.xlsx53.1 KB Working with Form controls - Completed.xlsx
56.2 KB
Quick reference
Working with Form controls
Adding Form controls to drive user input.
When to use
Use when you want to provide an attractive interface that makes it easy to cycle through valid options.
Instructions
Exposing Form controls
- Form controls are found under the Insert button on the Developer tab
- To activate the Developer tab, right click on any ribbon tab --> Customize the Ribbon and check the box next to Developer
Creating Form Controls
- Select the control you want, then left click and drag on the worksheet to create it
- Right click the control, choose Format Control to configure it
Combo Boxes
- Input range: Select a range of cells on the workbook to use as the control’s list
- Cell link: Provide a cell where Excel will store the item chosen
- When a user selects an item, it’s numeric index will be placed in the cell
Spin Button
- Minimum Value: If using this to drive an INDEX function, make sure this is set to 1
- Maximum Value: If using this to drive an INDEX function, make sure it doesn’t exceed the item count
- Cell link: Provide a cell where Excel will store the item chosen
- When a user clicks up or down, the numeric value will be placed in the cell
Scroll Bar
- This control contains options similar to Spin Button in addition to some others
- Incremental Change: Controls how much the index value changes when an arrow is clicked
- Page Change: Controls how much the index value changes when the bar is clicked
Hints & tips
- Form controls are a great way to capture intersections for use in an INDEX() function
- Right click a control to get back into Edit mode, then click on the worksheet to de-select it
- There are other types of Form controls than those shown
- 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.