Home and Learn - Free Excel VBA Course

9.1 Getting Started with Excel User Forms

In this section, you'll learn about User Forms. So you can get an idea of just what User Forms are, we'll create a simple one. It will just be a button and a text box.

 

You can start a new spreadsheet for this. Call it user_forms.xlsm. Now bring up the VBA Editor in the usual way. From the menu bar at the top of the Editor, click Insert > User Form:

The Insert User Form menu

When you click on User Form, you should see a grey square appear with a toolbox beside it:

A new User Form in the VBA Editor

If you can't see the toolbox, or accidentally lose it, click the View menu at the top of the Editor. From the View menu, select Toolbox.

Now have a look at the Project Explorer on the left. You should see a new folder appear, along with your User Form. If you accidentally get rid of the form, double click here to bring it back.

The Project Explorer

By default, the form has the name UserForm1. (You can change this name, if you like, and you'll see how to do this shortly.)

Look closely at the toolbox and you'll see it's a grid of items that you can click on. Click on the Command Button item to select it, circled in red below:

A Command Button highlighted in the Toolbox

With the Command Button selected, move your mouse over to the grey form. Hold down your left mouse button and drag to the right to draw a button on the form. Draw one about a third of the width of the form, but not very high. It should look like this when you let go of the left mouse button:

A Command Button drawn on a VBA Form

You can change the height and width of a button with the white resizing handles. Simply click and drag a white square to resize the button.

Every object you draw on a form comes with a list of properties that you can change. The properties area should appear on the left of the Editor, just below the Project Explorer. If you can't see the Properties area, click the View menu at the top of the Editor. From the menu, select Properties Window.

With your button selected, have a look at the list of properties it has:

The Properties area for a button

The word to the left of the list is the property itself. To the right is the value for the property. Properties are mainly nouns. Think of a TV. It has Power property. The values for TVPower would be On or Off. You'll also have a Channel property that can take channel numbers as a value:

TVPower = On
TVChannel = 3

 

Adding Controls to a User Form

The first property in the Properties area is Name. The default value for the Name of a button is CommandButton, then a number. Click inside of where it says CommandButton1. Now press the backspace key on your keyboard to delete it. Type a new Name property. Call it cmdGetNumber. You should see the top of the Properties area change to this:

Setting the Name property

The internal name of the button is now cmdGetNumber.

You might have noticed, though, that the button on the form still says CommandButton1. This is because this is the text on the button, and not its name. The text on a button is a different property. With your button still selected, locate the Caption property:

Setting the Caption property of a button

Again, click to the right of Caption and delete the default text. Type Get Number instead:

The Caption property reset

The text on the button on your form will change to this:

Caption property on a button

We're going to add a text box next. First, we can move to the button to the right of the form, in a more natural position. We'll then have the text box on the left.

One way to move a control is via the properties area. Have a look at the list and you'll see that there is a Top and Left property. These refer to top of the form and the left side of the form. Simply type a new value for these and watch your button jump around the form (You can also change the width and height of your form in the same way.

Another way to move a button (or any control) around the form is with the selection arrow, circled in red below:

The Toolbox selection arrow

You must have the arrow selected before moving your control. Don't, for example, right click a button without the arrow being selected, otherwise you'll get a new button on the form. If this happens to you, select the unwanted button with the black selection arrow then press the delete key on your keyboard. This will get rid of the button.

To add a text box to your form, click the TextBox item in the toolbox:

The textbox control in the VBA toolbox

Now draw one out on your form. Change the Name property of the text box to txtValue. Change the Width property to 50 and the Height to 25. Set the Top property to 20 and the Left property to 25. Your form should now look like this:

A textbox and a button on an Excel VBA form

If you have a look in the top left of the form, you'll see the text UserForm1. This is the form Caption. You can change this to anything you like. Click anywhere on the grey from that is not a control (button and text box). With the form highlighted, locate the Caption property in the Properties area. Delete UserForm1 and type Get A New Number in its place. The top of your form will then change to this:

Setting a Caption property for a VBA form

Now that you have the form designed, press F5 to see what it looks like when it's running. You'll see that the dots on the grey area have vanished. The dots are there only in design mode, to help you position controls on the form.

Although you can click inside of the text area, nothing will happen when you click the button. That's because we haven't written any code for it yet. Click the red X to get rid of the form and return to the VBA Editor. We'll now add some code to the button. We'll do that in the next section below.

< Write File

Next Lesson: 9.2 Button Code >