Form (1)

The third example I’m going to discuss is the ActiveX control “Form”.
We use a form, for example to add data to a table. As an example, I create a form, with which we can add customers to the “customers” worksheet (1) in the workbook.

To add a form to your spreadsheet, select the “Developers” tab (2) and you click the “Visual Basic” button (3). For version 2003 and earlier, click the “Tools”, choose “Macro” and click “Visual Basic Editor”.

The hotkey for all versions is Alt F11 on your keyboard.

This opens the “Visual Basic for Applications”. A form consists of two parts.

One part is the form, the other part is the VB code to enter the data using the form into the table. The first thing we do is create the form.

Just click the “Insert” button in the VB window and select “User Form” in the drop-down menu.


This adds a form in the window (A). You can resize this form by clicking and dragging the blocks on the edge of the form. When the form is added, it is put together with a toolbox (2) with all the different objects. If you need to close this toolbox for some reason, close it by clicking the cross icon in the upper left corner, then you can always return by clicking on “View” and click “Toolbox”.

The form that you just added appears in the “Project” window (3). If for some reason the form is closed, double-click it and the form is opened again.

In the “Properties” box (4) we select the properties for the form.


The main field in the “Properties” window is the “(Name)” box (1). It is with this name that we make contact between the form and the VB code. The nice thing is that when you form a name, this name begins with the letters “frm” followed by an appropriate name. For example frmCustomerData. This will give us more info when we later read the VB code.

In the “Caption” (2), type the name you wish to place on the top of the form. This has nothing to do with the VB code created, so here you can type whatever you want.

The remaining boxes in the “Properties” window relate more to the layout of the form, so explore them as you want.

The only thing I have to change is the background color of the form. If I want to change this later, I can still do this.

In the next lesson, we will first fill boxes and labels.


You've completed Lesson 8