Using scroll bars and spin buttons to facilitate forms creation

- In this worksheet we’re seeing two examples of form controls: a scroll bar and a spin button. They’re very similar. The scroll bar does have an advantage in ease of use when dealing with larger numbers. You’ve probably seen these, too. Not only with internet usage, but perhaps in some Excel worksheets. They’re easy to use and they’re, if not self-explanatory, fairly close. I’ve got a scroll bar up here. I’m going to drag this slider bar to the right a little bit. Keep an eye on cell C2 as I’m doing this. Typically form controls are linked to a certain cell, in this case: C2.

The chart to the right is also linked to cell C2, and so when I finally let go of the mouse here the number “35” is present, we also see that being reflected in the chart. Now, if I need to change the score I can certainly use the slider bar, or if it’s nearby maybe I’ll just use the arrows here; something like this. That’s the chart reacting as cell C2 changes. I can slide this farther to the right, and as I start to click the arrows here, also keep an eye on cell G2.

Suddenly it turns in to “eligible.” This is actually a function. It’s the “IF” function, which simply says if cell 2 is greater than 75, display the word “eligible.” Perhaps the score on this test, if it’s over 75 it means that you are eligible for a scholarship or aid or something like that. The point is there are no hard and fast rules as to how you might use the controls, so in the example here all we’re saying is as we make a change to the control either by using arrows or the slider bar, we change cell C2.

What we do with that information can be wide ranging. In this case, for the moment, all we’re doing is we’ve got a chart that is reflecting that total, we also have a formula in G2 that gives us different answers. A spin button is similar. We could’ve used a spin button up above. We’re using it down below. But as soon as we start to use it I think you can see a difference here. As I use these up or down arrows here we see the scores here changing. But what if I wanted to rapidly go down to score number five? I can’t get there very quickly without starting to click rapidly; it’s going to take me a while to get there.

So spin buttons are likely to be used when the range of scores is a bit tighter. Like, maybe even tighter than this. If the scores are wide-ranging, a scroll bar like we see up above is likely to work better. When I created this spin button I could’ve made it more horizontal or vertical; so here’s the horizontal version of it; and as with all form controls, if you CTRL + click on them, you’ve got some control over how wide or tall or wide this is. This doesn’t have to be very wide.

Doesn’t have to be very tall. Your call on how you want these to look. So I’d like to show you how to create these, and how you might use them. Notice as I click the up arrow or down arrow on the PAC Score the chart to the right is reacting. As I reach certain levels here, suddenly the word “good” pops in there. And if I go up a bit more we see “very good” and so-on. I’m just holding it down, “excellent.” Now, that may or may not be how you’ll be using this, but it gives you some idea. If the score is 60 then it’s “perfect,” and so-on.

This is actually a formula here. I’ll double-click it so you can see this. Let me make this a little bit narrower here. You can see it even better. There we go. It involves a little bit of math here, where we’re simply taking that value from C8. So once again, we’re taking the result of having used the control, in other words the spin button changes cell C8, the chart reacts immediately, and the formula that we’ve set up here simply takes that value, divides it by 10, and then uses the “CHOOSE” function to come up with these different scores.

So, different kinds of creativity can be involved here. I’d like to show you how to create these. Pretty similar in how we do this. With the CTRL key I’ll select the scroll bar up above and simply press “delete.” And I’ll delete the reference here in C2. And create one of these. In order to create a form control you need to have visible the “Developer” tab in the ribbon. If you don’t see this in the ribbon, right-click any other tab, choose “Customize the Ribbon,” and you’ll see “Developer” here.

I’ve already got it checked; I’ll simply click “OK.” If you don’t, you’ll make it checked and then “OK.” Once it’s visible, on the “Developer” tab you will see, with the “Insert” button on the controls group here, different kinds of form controls. Right there we see scroll bar. Right there we see a spin button. So let’s say we want to create a scroll bar. When you create these, if at first it’s not obvious, should this be tall, wide, narrow? Not always sure, just make it somewhat medium. You’ll decide later.

You can easily change the shape of this. Now the key step here in making this all come together is the idea that you want this linked to a certain cell. The chart to the right, by the way, is already set up to be linked to cell C2. That was done ahead of time. And I’m not at all suggesting that a chart is required in these situations. It does give, in this example, additional visual impact to the scroll bar. So right-clicking the scroll bar; sometimes you have to do this a second time; choose “Format Control.” The range of values here will be from 0 - 100.

And these don’t always match up perfectly with what you’re seeing here, so you might have to make a change. Every time I’ll be clicking the right and left arrow as we see them on the ACT Score, in the actual scroll bar, we want the value to change by one. That’s the incremental change. “Cell link” right here, I want this to be linked to cell C2. The “3-D shading” helps a little bit on the visual. Click “OK.” So this could be wider, taller. We’ll worry about that maybe a bit later. Click outside of this.

As I click the right arrow watch cell C2. If I start to click it a few times you see what’s happening in the chart. We can also use the slider bar. One advantage of scroll bar over spin button is that when the range of numbers is wide we can move along much faster here when we’re changing this. Notice also in cell G2, as I click the right arrows here we see “not eligible,” suddenly we see “eligible,” so what’s happening here? In G2 is a formula, and it simply says if cell C2 is greater than 75, as it currently is, we will display the test “eligible.” Otherwise we display “not eligible.” Now, when I created this I could’ve made it more vertical, so I’m gonna hold down the CTRL key and simply make a copy of this, and now CTRL, drag-downward, let go of the mouse first.

What if I make this more vertical? Here’s what’s a little bit odd. If this were a vertically oriented scroll bar, I’ll click the up arrow, watch the number to the left. It went down as I click up. That doesn’t quite relate to my sense of logic. I keep pressing the up arrow, and this number goes down. So I wouldn’t use the scroll bar this way. It just doesn’t make sense logically. So CTRL + click this and get rid of it. With the spin button, then it will work. So let’s get rid of the spin button and do the same kind of thing.

A little bit faster this time. CTRL + click, get rid of that. CTRL + click here, press “delete” again. Also delete this. And let’s set up a spin button. Here too, “Developer” tab, in the controls group “Insert,” there’s our spin button, click. We can make it horizontal or vertical, I’ll make it horizontal first. And when you do this at times you’ll say, “Oh, it should be this high, this wide.” You’ll change it, perhaps, later. We need to link this to cell C8.

Right-click, “Format Control,” “Cell link,” C8. And just click on it. But here the minimum value and maximum value are going to be different. Between 10 and 60. And as in the previous example, the incremental change, as it is often, is going to be one. But with a much larger range of numbers, suppose it was between 0 and 10,000 or something, or 0 - 1,000 even, we might want the incremental change to be 5, or 10, or 100, or whatever.

Here the incremental change is “1.” That simply means what happens when you click the arrow, it doesn’t mean that you can’t go to other values. “Cell link” here, “OK.” I click the arrow, well click outside of it first, then click the arrow. Notice that this cell here for the moment is blank. But when we reach a certain level... We see the word “good” when we reach 30. Here’s that formula again. It’s simply saying, depending upon the value in C8 we’re gonna divide it by three, and then if it’s equal to a 30, anything in the 30’s is gonna be “good,” anything in the 40’s “very good,” 50’s “excellent,” and if it’s 60 it’s a “perfect” score.

We see the different displays that would emerge from that. Like we did before with the scroll bar, let’s make a duplicate of this; so CTRL + click to select this, and now with the CTRL key again I’ll drag this over here and reshape it. It could be vertical. Is this any better? Not necessarily, but here at least when we click the up arrow the score is going up. And so, that certainly is a viable alternative. The horizontal or the vertical versions here work equally well, whereas they didn’t in the scroll bar.

So I’ve seen different techniques. You might also notice here that when we click one of these with the CTRL key, that the “Format” tab, the contextual format tab, in the ribbon appears but hardly any feature here is usable. So we’ve seen two different examples of form controls, both created by way of the “Developer” tab and both used in different ways, and yet they’re both tied to the same chart. One of many different ways you can use these form controls.