Monday, 18 June 2012

Lesson 24: Forms (Userforms) in VBA for Excel

Excel Macros Section 3: Userforms

In the first section you have discovered the programming environment (Visual Basic Editor and Macro Recorder). In the second section you have learned many useful VBA words (code). After these 2 sections of the course on Excel macros (VBA) you can use all the macros available for free on the Internet and you can build your own.
In section 3 you will learn how to create customized dialog windows (also called userforms).

When the message box or the input box (that you have discovered in lesson 17) is not enough to communicate with the user you can create your own simple or complex dialog windows like the one below.

In the course on Excel macros (VBA) you will find everything that you need to develop simple or very complex userforms. On this website you will find a simple exercise (starting in lesson 24 and ending in lesson 28) that will show you how to develop your first userform. The simple form that you will create (image below) will allow a user to enter a value in the first cell of a database.


When the message box or the input box are not sufficient any more to communicate with the user you need to start developing userforms.
The form is used to require information  from the user to feed the VBA procedure. Different basic controls can be added to the userform they are called: labelstext boxescombo boxeslist boxescheck boxesoption buttonsframescommand buttonsspin buttons and images . To learn more about all the controls see lessons 26  to 33.

Creating a Userform in Excel
Userforms are created in the Project Window of the Visual Basic Editor. You will also find the toolbox that allows you to add controls to your userforms in the Visual Basic Editor.
In the Visual Basic Editor you right click in the project window and you will see this menu appear:

Go to "Insert" and select "UserForm". You will then see the following:

On the right you see the userform that you have just added to your workbook. On the left is the toolbox with all the controls that you can add to your userform. You can hide that toolbox by clicking on the "X" and bring it back by clicking on the toolbox icon   or by going to the menu bar "View/Toolbox". We will use the toolbox later in this section.

No comments:

Post a Comment