Using list boxes and combo boxes to facilitate forms creation

- In this worksheet are two different examples of what we call form controls. We've got a List Box and a Combo Box. These are very similar in nature, and you can use them in a variety of ways. You've probably seen something like this on the internet in filling out a form. If I choose California here, or Connecticut, or Colorado, it's pretty easy. If I don't see the state I need, I can certainly scroll up and down, make the choice that way. There are arrows, top and bottom here, that I can use as well. Make a choice that way.

Notice as I do this, cell A3 has changed. Now, these List Boxes are tied to, or linked to, a cell, in this case A3. They're also linked to an input range, in this case it's column F. So, think of situations where you might want to allow people to pick a name from a list, and then, based on that choice, do something with that choice. In other words, this is six for Colorado. Can we somehow use that information and go pick up the population that we see off to the right? Certainly could.

These lists, by the way, the Source Lists and any allied information, could be on a different worksheet. They could even be in a different workbook, so it all depends upon the scenario that you're trying to set up. A Combo Box is very similar. I can click the drop arrow here, we see our list of states. I can scroll up and down here, this way. Notice what I can't do so easily is move up above this. Now, I can use my arrow keys to go to the upper part of the list, but I can't use the mouse to get to it. But I can certainly use the mouse to make a choice, so they're similar and yet different, the way these are being used.

Now, with this bit of information, depending on what we also have adjacent to or nearby that list of states, we can also, for example, extract population. So I want to know the population of Colorado. I could use a vlookup or possibly the index function, as I will in this example. The index function allows us to pick information from an array or a list. Let's say that list is over in column G, comma, now Colorado gives us the number six as we look at the list over there, it is the sixth state, but it's going to be in the seventh row if we want to pick up the population.

So, I'll simply use that six out of cell A3, plus one. And complete, enter. And there's the population of Colorado. And if I click District of Columbia, we see what its entry is there. California, there it is there, and, similarly, with the rank. What's the rank here? If you're familiar with the rank function, we can again use information from this cell that gave us the population, comma, and we want to compare that with all the other populations in column G, so we'll just click column G, and enter, and we've got that information.

And we can do the same kind of thing with the Combo Box down here. We're interested in Massachusettes, maybe. So we can simply copy these formulas, both of them. I'll drag this downward with the Control key, and we've got similar information down here for Massachusetts, and the rank, and so on. Now, you wouldn't be using both of these together, as I am here, off the same list. It's going to be one or the other, and you'll just have to experiment a bit with these. But let's show how these are being built. I'm going to remove the List Box, simply by holding down Ctrl, click an edge of it, and press Delete, and I'll delete the linkage cell too, for the moment, and, of course, this information is all wrong.

I'll do the same thing down here with the Combo Box. I'll show you how to create both of them. Once we create one, it'll be easy to create the other. So, creating a List Box. We need to have active the Developer tab. If that's not active in the ribbon, if you don't see it here, if it's not one of the ribbon tabs, right-click any other ribbon tab, customize the ribbon. Along the right-hand side you'll see Developer. Possibly you'll see some other choices, here. Click OK, the Developer tab is active, and, on the Developer tab, in the Controls group, Insert.

We've got a Combo Box here, List Box here. Let's choose List Box, and click and drag. Now, you will want to experiment with the size of this, and you can always change the size later, if you wish. So we'll say that's big enough. For the moment, this is not associated with any cell, so let's right-click it, and go to Format Control. 3-D Shading helps a little bit. The Cell link, let's say it's going to be B3. There it is, right there.

The Input range is going to be all those cells from F2 downward, to the bottom of the list. Just highlight it. That's where the list is. Ok. So we've made the association. Click outside of this. If I choose Arizona, we see the number three appear right here. Earlier I'd had that in column A1, I'll just move it over there to be in sync with what we had done before. And for the moment this is incorrect, but let's set it up again. Same formula as before, but instead of REF+1 here, we'll simply use A3, so that should be working.

That's Arizona's population, it's the 15th large state according to these estimates, and here's California. You know it's number one in population, so there it is. And Texas, if we slide down there, we'll see that bit of information as well, too. If you want to change the size of this, hold down the Ctrl key, click, and possibly reconsider, we don't need to make it that wide, maybe. That's good enough. Or, we could make it taller or shorter. If you make it too short, I think you'll realize it's not going to be as efficient if we're trying to scroll through a list of 50 states. In fact, we could make this a lot bigger, and if we didn't have other information below we might want to even make it big enough to show all the states.

I think most of the time you wouldn't want to go that far, but, again, it's your call on how big you want this to be. And we wouldn't, as I suggested earlier, have both of these together at the same time. So, how do we set up a Combo Box? Almost the same idea. Developer tab, in the Controls group, Insert, there's the Combo Box. We'll just click and drag here. Notice here, if we make it bigger, we simply have a bigger arrow that way. Leave it there for the moment, but again, as before with the List Box, right-click, Format Control, the Cell link here will be cell A10, the Input range those states again, from cell F2 down to the bottom here, F52.

And OK. So, we're set up there. Click outside of this, click here. And we see our list. Again, this didn't have to be this big, so Ctrl+click. Sometimes use Combo Box in the interest of saving worksheet space, but we see the entry here, and we can scroll up and down this way, too. So, different ways to use that. Here, too, on the link here, Arizona, we see the number three pop up here. And of course these totals fall into place, as well.

So, a lot of imagination is involved here in how you might use these, but I think you can see that these are appealing, but I think you can see how appealing these are visually. There's also the idea that any technique that involves people not typing has some real merit to it. Cut down on the amount of time it takes to type, and also the inaccuracies that often occur with these. But you can imagine, with lists here, state lists that also include other bits of information, how powerful this could be, how useful it could be, in setting up certain kinds of forms.