Using option buttons, group boxes, and checkboxes to facilitate forms creation

- In this worksheet are different kinds of what are called Form Controls. We can use these for setting up Forms, we can use them for a variety of applications. They're appealing because users don't have to do any typing. Usually, it's just a selection. What we do with them can involve other formulas. In the example we're seeing here, where we have Option Buttons in a Group Box, we'll see different choices here. Their buttons, so-called radio buttons because we cannot choose any more than one at a time, are mutually exclusive.

As I check MS, MA, etc. here, a few things on the screen are going to change. Keep your eye on cell A3. Also look over in column E. Things change, and you can see what's happening here. Can't see the formulas just yet. Form Controls are typically linked with cells, and we can then take that information and look up and find other information related to it perhaps, even on different worksheets. The formula in E3 relates to the value in A3, which is linked to these buttons, so all we're doing here is simply displaying in a different way the results of the button selections.

It's going to be "Bachelors", "Masters" or "Doctorate", depending upon the value of A3. The way the CHOOSE function works, by the way, is simply, take this value, whatever it is, and if it's 1, here's the answer, if it's 2, here's the answer, and so on. And similarly, in the formula below this, it's simply taking that text, "Educational Bonus = and combining it with the CHOOSE function in a different way to display a different number, depending upon that choice. A Check Box, we see down below, a lot simpler to choose. The box is checked, we see the word TRUE off to the left.

This is linked to the check or the uncheck. I'll uncheck it, you see the word FALSE. We saw formulas before, here's another formula here relating to the result in cell B13. When B13 is TRUE, we display "In-State", when it's not true, we display "Out-of-State". If you've never seen this kind of use before, it might throw you because we don't see the word TRUE there at all. It's either TRUE or it's FALSE. So this means, just by its very presence, when it's TRUE, the answer is "In-State", when not, it's "Out-of-State", so clicking the box back and forth.

Maybe this would be slightly better if we had Resident? here, but imagine how these could be used in different Forms. I want to show you how to create these, both of them, and they're rather similar. They do require that we have a DEVELOPER tab visible in the Ribbon. If you don't see the word DEVELOPER in the Ribbon tabs, right click any other tab, choose Customize the Ribbon, and in this list here, choose Developer. I don't need to check it now, I'll simply click OK. So that's available.

I'm going to move this aside first. Now, if I click this, and simply drag it to the right, we'll ignore the color for the moment, that's not critical. What I'm going to do is create an Option Button, and rather than creating it three times, I'll simply create it once and copy it. So, DEVELOPER tab, Controls, Insert, here are various Form Controls. There's a Button, there's a Check Box, there's a Group Box, we'll be using that also. So, Option Button. Just click out here, there it is.

I'm going to change the wording of it right away. Click in here, and I'm going to type, BA, BS, etc. and get rid of the other text. I also want to link this to a cell. I'm going to right click. Sometimes when you right click, you don't get the menu that you want. You might have to click nearby, try it again. There we go. Format Control, that's the key phrase we use with Form Controls. 3-D shading, not necessary but helpful. Cell link, there's the key idea.

We're going to link it to cell A3, OK. You might also have noticed that there's a FORMAT tab available when a Form Control is selected, and many of the choices there are not available at all, but here's one for Shape Fill that is available. Maybe I'll just choose a different Color, Yellow. I might make this a little bit wider because some of the other entries might need to be wider. So I've essentially set up the length to the cell. I'm going to make a copy of this, and as I drag this downward, I'll be holding down the Ctrl and Shift keys to keep it in the same plane, copy there, and do that again from here, like that, and I'll just simply, by clicking outside of here, and then coming back with Ctrl + click here and change the B to an M and so on.

There's a little bit of typing to adjust that. And then here, I'll change the wording as well. And as I click these buttons, notice how that becomes 1, becomes 2, this becomes 3, and the formulas I had used before have simply been resurrected because they refer to the same cells. Now we can spend way too much time redesigning the look of this.

Obviously, it looks different than the other one. The Group Box, we can easily add, too. Sometimes that's not even necessary but it would be helpful here to surround this to indicate what this is being used for, so DEVELOPER tab, again, Insert, Group Box right there. Simply click and drag this. The Color background is on the cells, that's not a requirement. I don't have to match this up necessarily, there it appears. And it's pretty apparent now, I don't want this to say Group Box 30, so I'll click right in there, and type in what I do want it to say.

Degree Level or College Degree or whatever, something along those lines. Highest Degree Attained, something like that, and delete the other text, and click outside of it. So, relatively easy to set up, and you can imagine some variations on that. One more visual that you could change here. These aren't equally spaced. They are the same, left to right. I'm going to purposely misalign them just to show you can do that as well, too, and then do a Ctrl + click on this one and move it over a little bit. So, I can select all three of these by using the Ctrl key, and then, in the FORMAT tab, in the Ribbon, the contextual tab that appears when you select one of these Form Controls, I can Align their Left sides, Right sides, whatever.

Left side, there we go, and then, possibly, Distribute them Vertically. I think you can sense that's not the most important thing we do but recognize we can do that, too. A Check Box, I'll simply, with the Ctrl key, click it, move it off to the side for the moment, and get rid of the link. Setting up one of these, very similar to what we did before, a bit faster here. DEVELOPER tab, here's a Check Box. We'll simply click right here, and of course, we don't want that to say Check Box. We would want that to say Resident.

This time I'll put a "?" behind it, delete the other text, and by clicking here and going into the FORMAT tab, notice how Shape Fill is not available here. It's not available for this particular kind of Form. We do want to assign, by way of Format Control, the link Cell here, make it 3-D, too. That'll be cell B13, and OK. And we can move this around as we wish. Possibly, we want to align it with the data to the right, so, clicking outside of it, checking the box.

So, using your imagination a little bit, think of how these could be used in different Forms that you might be setting up. And the formulas here that we saw in cell E3, E4, and also down here in cell E13, and I'll leave one of them visible, this one right here. These, again, evolve out of your knowledge of the information being collected and what you want to do with it, so we can take this information and do other things with it. So I think you can see, by using these Controls, not only do you facilitate certain kinds of easier data entry, you can use that information in a valuable way in the worksheet.