09-01 Add Group Box and Option Button form controls without VBA - YouTube

For this lesson, you need to open the sample file: Mortgage Calculator-1, from your Sample Files folder.

In this lesson, we’re going to add some controls to a form.

Because form controls are a very advanced feature of Excel, Microsoft hides them from normal users.

You need to add a Developer tab, to the Ribbon, in order to reveal them.

The easiest way to do this is to right-click in a blank area of the Ribbon, and select: Customize the Ribbon, from the Shortcut menu.

And you can see that all of the main tabs are enabled on the Ribbon, except the Developer tab.

So I’ll check the Developer checkbox, and click: OK.

And you can see that a Developer tab has now appeared on the Ribbon.

I’m going to replace the Deposit textbox, in row 5, with a Group Box control and Option Button controls.

So I need a little more room on the form.

I’ll begin by deleting row 5.

And then, I’ll insert two rows, so that I’ve got four clear rows, between the Property Price and the Arrangement Fee.

And now, I’m going to add a Group Box control.

To do that, I’ll click my new Developer tab, on the Ribbon.

And in the Controls group, I’ll click: Insert.

And you can see that there are two sets of very similar controls: Form Controls and ActiveX Controls.

The Form Controls are designed to be used without any understanding of the Visual Basic for Applications

(or VBA) programming language.

While the ActiveX Controls are intended to be used as part of the Visual Basic programming environment.

Unless you have Visual Basic programming skills, you’ll probably not find a use for the ActiveX Controls.

You’re going to learn a little more about VBA’s relationship with Excel in Lesson 9-10.

So I’m going to choose a Group Box control, from the Form Controls section of this dialog.

I click on: Group Box, and then, I hold down the Alt key, on the keyboard.

By holding down the Alt key, the Group Box will snap to the corner of the cells that I draw the Group Box in.

You’ll see this happening now.

I’ll position the cursor close to the top-left corner of where I want my Group Box to be.

And then, click-and-drag.

And you can see that the Group Box is snapping to the corner of the cells.

And now, I’ll release the mouse button.

And I’ll give my Group Box a name.

To do that, I’ll click on Group Box 1, and I’ll change the caption of the Group Box, to: Deposit. And I’ll add a Colon.

So that you can see where we’re going with this Group Box, I’ll now place a graphic of the finished item,

next to the Group Box I’m building.

And you can see that my Group Box is going to contain six Option Button controls.

Allowing the user to select a deposit, from 5% to 30%, in increments of five percent.

So let’s now add those Option Button controls.

To do that, I click: Developer, on the Ribbon.

In the Controls group, it’s: Insert.

And from the Form Controls group, the Option Button control.

I click, and now, I’m going to draw an Option Button control, inside my Deposit Group Box.

Now, there’s a few important rules to remember, when you do this.

First of all, the border of the Option Button cannot cross the border of the Group Box.

If you do that, then the Option Button control won’t work properly.

Also, keep your Option Buttons small, because you don’t want them to overlap.

And it’s really important that you add them in the right sequence: 5% first, then 10%, then 15%.

You’re going to see why that’s important later, in this session.

So there’s my first Option Button.

And now, let’s add the others.

So: Option Button. Draw it alongside. That’s my second button.

Another Option Button. That’s the third one.

Another Option Button. There’s my fourth one.

Another Option Button. There’s Option Button number five.

And, finally, we’ll have Option Button number six.

Now, the Option Buttons don’t look very neat, at the moment.

But don’t worry about that. We’re going to automatically align them later.

Now, I’m going to add labels for my Option Buttons, from 5% to 30%.

And to do that, I need to double-right-click each Option Button.

So I’ll double-right-click the first Option Button, and type: 5%.

Double-right-click the second, and that’s: 10%.

Double-right-click the third. That’s: 15%.

Double-right-click the fourth one. That’s: 20%.

Double-right-click the fifth. That’s: 25%.

And double-right-click the last Option Button. That will be: 30%.

I’ve now added captions for all of my Option Buttons.

Now, I’m going to test the Option Buttons.

Because if any of them have overlapped the boundary of the Group Box, they won’t work properly.

To test them, I’ll click on: 5%.

And when I click on the 10% Option Button, the 5% button should extinguish. That’s worked.

That’s worked.

That’s worked.

That’s worked and so has the last one.

So all of my Option Buttons are working correctly.

Now, I want to space, size and align the Option Buttons.

Now, you could align them simply by double- right-clicking an Option Button.

And then, use the Arrow keys, on the keyboard, to move them around.

But you won’t be able to perfectly align them, in this way.

To give your form the most professional appearance, you’ll want to do this automatically.

Before I automatically align and space the Option Buttons, first, I’m going to make sure that they’re all the same size.

To do that, I’ll right-click on one of the Option Buttons, and select: Format Control, from the Shortcut menu.

And then, I’ll click the Size tab.

And I can see that the size of this Option Button is 0.24 high and 0.33 inches wide.

On your system, you may see centimeters here, instead, depending upon your Locale settings.

So I’ll make a careful note of those two numbers.

And then, I’m going to automatically size all of the other Option Buttons, so that they’re the same.

To do that, I’ll first click: Cancel, to remove the Format Control dialog.

Then, I’ll hold down the Ctrl key, on the keyboard, and I’ll right-click on each of the Option Buttons in turn.

Now, for the sizing. I’ll right-click, on any of the selected Option Buttons, select: Format Object, click on the Size tab.

And for the height, I’ll type in that value of 0.24 that I made a note of earlier.

And you can see that, in every case, the widths are already 0.33 inches.

When I click: OK, all of the Option Buttons are sized to be exactly the same.

Now, I want to align the tops of my Option Buttons.

To do that, I’ll use the Format tab, in the Drawing Tools group of the Ribbon.

Then, I’ll click the Align option, and: Align Top.

And the tops of all of the Option Buttons are now perfectly aligned.

Now, I can see that the text of the 30% Option Button is over-spilling the side of the Group Box control.

So I’m going to move that in a little, before progressing.

I’ll click away from the Group Box control, then double-right-click on the 30% Option Button.

And use the Left Arrow, on the keyboard, just to move that in from the border.

Now, I’m going to select all of my Option Buttons, again.

So I’ll hold down Ctrl, on the keyboard, and right-click on each of the other buttons in turn.

And now, I’m going to space them perfectly, so that the gap between each Option Button is exactly the same.

To do that, it’s the Drawing Tools group, and: Format, on the Ribbon.

And in the Arrange group: Align, and then: Distribute Horizontally.

And now, the Option Buttons are perfectly spaced and perfectly aligned.

That may have seemed like rather a lot of work.

But your Form Controls will look far more professional, if you automatically align them, rather than align them manually.

Now, I’m going to add a very similar Group Box and Option Buttons, to replace the existing Term box.

First, I need to make a lot more space.

So I’ll delete row 12, and then, I’ll insert three rows, to replace it.

I now have five clear rows, between the Interest box and the Amount Financed box.

It might be useful, at this stage, to show you how the Term Group Box and Option Buttons are going to look.

So I’ll paste in a graphic.

You can see that I’m going to add a Group Box control, with six Option Button controls inside it.

So let’s begin with the Group Box control.

That’s: Developer, on the Ribbon.

In the Controls group: Insert.

And in the Form Controls section, the Group Box.

I hold down the Alt key, to make the Group Box snap to the cells.

And then, starting in the top-left corner, drag down towards the bottom-right corner.

And there’s my Group Box.

Now, I’m going to click once, on: Group Box 8, and give the Group Box the name: Term, followed by a colon.

Now, I need to add those Option Buttons.

And remember that the Option Buttons mustn’t overlap the sides of the Group Box.

And I need to add them in the correct order.

That means 5 Years first, then 10, then 15, and so on.

So: Developer, on the Ribbon: Controls: Insert.

There’s an Option Button control.

And I’ll put that just to the top-left of the Group Box.

Now, the next Option Button, in the center-top of the Group Box.

The next Option Button will go to the right-top.

The next Option Button will go to the bottom-left.

The next Option Button will go bottom-center.

And the final Option Button will go bottom-right.

Now, I’m going to test those Option Buttons, just to make sure none of them overlap the side of the Group Box.

So I’ll click on the first. That’s good.

When I click on the second, the first should extinguish. That worked well.

Three, four, five, and six.

And I can see that all of the controls are working just fine.

Now, I’m going to add the captions to the controls.

So double-right-click: 5 Years.

Double-right-click, and: 10 Years.

You can see I can’t see all of the text yet, but we’ll attend to that in a moment.

Double-right-click, and: 15 Years.

Double-right-click, and: 20 Years.

Double-right-click, and: 25 Years.

Double-right-click, and 30 Years.

Now, I’m going to resize all of the Option Button controls.

You can see that, at the moment, none of them are wide enough to show me the text within them.

So I’ll need to resize at least one of them.

I’ll double-right-click on the 25 Years Option Button.

And then, just drag the border out a little, so that I can see ‘Years’.

And then, I’ll look at the size of this Option Button control.

So I’ll right-click on the 25 Years Option Button: Format Control: Size tab.

And I can see it’s 0.22 inches high and 0.73 inches wide.

And, once again, I’ll make a careful note on a piece of paper of those sizes.

Now, I’ll select all of the Option Buttons.

To do that, I’ll hold down the Ctrl key, on the keyboard, and right-click on each, in turn.

And now, I’ve selected them all. I’ll release the Ctrl key, and right-click inside any of the selected controls.

And I’ll select: Format Object, once again, and the Size tab.

For the Height, I’ll type: 0.22 (the value that I remembered from earlier).

And for the Width, I’ll have: 0.73 (as I noted earlier).

And then, I’ll click: OK, and you can see that all of the Option Buttons are now of a reasonable size.

Now, I need to space and align the Option Buttons.

I’ll begin with the top row. I’ll double-right-click on 5 Years, hold down Ctrl, right-click on 10, and right-click on 15.

And the first thing I’m going to do, is align the tops of these three controls.

So: Drawing Tools: Format, on the Ribbon.

In the Arrange group: Align, and: Align Top.

And now, I’m going to get the spacing perfect, so that the gap between each control is exactly the same.

So: Drawing Tools: Format: Arrange: Align, once more.

And, this time: Distribute Horizontally.

Now, I need to make the three lower controls perfectly line up with the three upper controls.

So I’ll double-right-click on the 20 Years Option Button control, hold down Ctrl, on the keyboard.

And right-click on the 5 Years Option Button control: Drawing Tools: Format, tab on the Ribbon.

And in the Arrange group: Align.

And, this time, I’m going to align the lefts of the Option Button.

So I’ll click: Align Left.

This time, I want to align 15 Years and 30 Years.

And I think I want to make the lefts line up, this time. Because 30 Years is a little too far to the right.

So I’ll double-right-click on 30 Years, hold down the Ctrl key, and right-click on 15 Years.

Then: Drawing Tools: Format, on the Ribbon.

In the Arrange group: Align. And, this time: Align Left.

Now I know that my 30 Years and 20 Years Option Button controls are lined up with 15 and 5,

I can equally space and align the tops of all of the Option Button controls on the bottom row.

So double-right-click on 20 Years, hold down Ctrl, right-click on 25, right-click on 30:

Drawing Tools: Format, on the Ribbon.

In the Arrange group: Align.

First of all, I’ll align the tops, and then, I will Distribute Horizontally.

And now, the Option Button controls are perfectly spaced and perfectly aligned with each other.

Now that I’ve completed the form, I’ll delete those two graphics that I added.

And all that remains, for this lesson, is to save your work.

And I’m going to save with the new name: Mortgage Calculator-2.

And, as usual, I’ll save to the folder above my Sample Files folder.

I click the Save button, and you’ve now completed Lesson 9-1: Add Group Box and Option Button controls to a worksheet form.