VBA Lesson 24: Forms (Userforms) in VBA for Excel
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 values, parameters and information from the user to feed the VBA procedure. Different basic controls can be added to the userform they are called: Label, TextBox, ComboBox, ListBox, CheckBox, OptionButton, Frame, CommandButton, SpinButton and Image . You can find all kinds of other controls on the Internet but the problem is that if you send your workbook to other people and the new control is not installed on their computer it might not work. This problem might even occur with the calendar control. To learn more about all the controls see lessons 26 to 33.

Tab Order

Once you have added all the needed controls you must set the tab order. The user can use the mouse to move from one control to the other but he should also be able to move from one control to the other by entering a value in one control and clicking "Enter" or "Tab". Then the focus will be set on the next control where he is supposed to enter a value not on any label or other control. More importantly when the user enters a value in the final control you want the focus to be on the right command button (Submit) and not on another one like "Close Form" .

To make sure that the user moves from one control to the next one in a set order you need to set the tab order. To do so, right click on the form itself and select the "Tab Order" item. Follow the instructions. The first control in the list will be the one that is active (flashing cursor within) when the form is activated. Bring the controls that are not to be used by the user (labels) at the end of the list. For the controls that are not to be used you can also set the "TabStop" property of the individual control to "False" in the properties window.

VBA code for Useforms

In lesson 25 of the downloadable tutorial you will discover the "Activate" and "Close" events of the userform and what kind of VBA you can use within these events.
VBA Lesson 25: Userforms Properties and VBA Code
As you have seen in lesson 1 on the Visual Basic Editor you double click on the userform's name in the Project Window and its properties appear in the Properties Window:



In the Properties window of the VBE you MUST change the name "(Name)" of the form, its caption (the name in the blue band at the top of the UserForm) and you can also modify the default setting of any of the 32 other properties of the form.

When you name a form always use the prefix "frm" (example: "frmDatabase") and be as descriptive as you can be so that your code will be easy to read. Always use one or more upper case letters in the name. When you write "frmdatabase.show" in lower case letters Excel will capitalize some letters "frmDatabase.Show" letting you know that the name is spelled correctly.

The caption is what your users will see at the top of the userform. Be as informative as possible and complete the information with a label if necessary.



Other than the Name and Caption there are just a few properties that you might want to modify. You can select a different color for the background with the property "BackColor" .

By default the userform appears in the center of the screen. If you want it to show somewhere else set the "Start" property to "0-Manual" and use the "Top" and "Left" properties to set a new position.

The Code

Opening and Closing the Userform

The first thing to do is to create code to call your userform. Following is a basic line of code for this purpose:
frmCity.Show

The line of code to close the userform is:
frmCity.Hide or Me.Hide "Me" being the active form

The "Hide" sentence is usually part of the code of a command button on the form. A user clicks on a "GO" , "SEND", "SUBMIT" or "CANCEL" button and part of what must happen is that the userform disappears from the screen.


--------------------------------------------------------------------------------
VBA Lesson 26: The Labels in VBA for Excel
In the toolbox the label has this icon . The label is a passive control meaning that the user never really acts on it. It is there to inform the user and to label other controls like text boxes, combo boxes or list boxes.

Properties

Among the other properties of the label are:

- TabStop: To make the control invisible for the "Tab" and "Enter" keys (see Tab Order) set this property to "False" .
- WordWrap: If you want to write more than one line of text in a label set this property to "True" .

Code

There is not much coding developed for the labels although there are 8 events related to the label. For example there is an event named "MouseMove" . If you develop code within this event it is executed when the mouse moves over the label. If the code is the following:
MsgBox "do not forget to..."
a message box will appear when the user moves the mouse over the label.

You can stack many labels one over the other and make their "Visible" property to "False". You can then make any of the labels visible from an event related to another control. For example if a user chooses a certain value in a combo box a certain label appears.


--------------------------------------------------------------------------------
VBA Lesson 27: The Text Boxes in VBA for Excel
In the toolbox the text box has this icon .

The text box is the simplest control that requires an entry by the user. The user types something in it and this value can then be used in your VBA procedure. You will usually add a label to accompany the text box.

For most controls including the VBA for Excel text box there are general properties that allow you to set the font, the color of the font, the color of the background, the type of background, the type of border and other design features. Using the 3 windows in the Visual Basic Editor you will see the following properties in the "Property" window when the text box is selected.

Properties

Among the other properties of the text boxes are:

- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button.
- MaxLength to limit the number of characters entered by the user.


You might want to ask users to submit a password to run certain macros. To do so develop a userform with a text box and a command button. In the text box you can modify the "PasswordChar" property so that when the user enters the password nobody around can read it. Use an asterisk, an ampersand or any other character as password character.

Code

The most important thing to remember is that a text box is what its name says it carries text. So if you want to send a numerical value from a text box to a cell you must use the "Value" thing:
Range("A1").Value=tbxInput.Value


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

In "vba-form-text-boxes.xls" (one of the 25 Excel spreadsheets included with the downloadable tutorial on VBA (Macros)) you will discover how to format text boxes to show commas as separator, percentages, currencies, dates and times. See also how to develop calculated text boxes.




--------------------------------------------------------------------------------
VBA Lesson 28: The Command Buttons in VBA for Excel
In the toolbox the command button has this icon . The command button is a very active control and there is always VBA code behind it.

The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go" , "Run" , "Submit" , "Cancel" , etc.

Properties

Among the other properties of the command button are:

- WordWrap to be able to write more that one line on a button,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button,

For advanced users there are the:
- Enabled and Visible properties that you can change programmatically to disable or render invisible a command button following a previous selection in another control of the userform.

Code

Name your command button before developing your code. VBA uses the name of the command button when it creates lines of code related to events. So if you do not name your command button VBA will create the private sub::
Private Sub CommandButton1_Click()
as if you name the command Button " cmbSubmit" for example the private sub will start with:
Private Sub cmbSubmit_Click()

If you name your command buttons after private subs have been created they will not work anymore.

A very simple VBA procedure for the command button would look like this:

Private Sub cmbSubmit_Click()
Sheets("Code").Range("F1" ).Value = cbxInput.Value
frmPassword.Hide
End Sub

The content of the combo box "cbxInput" is entered in cell "F1" of the sheet "Code" and the form (frmPassport) is closed.

VBA Lesson 29: The Combo Boxes in VBA for Excel
Before we begin on the Combo Box

The difference between a combo box and a list box is that the combo box is a drop-down list and the user can submit a single value from the drop-down list. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values.

Combo Box

List Box



--------------------------------------------------------------------------------

If you are looking for a drop-down list (also called pull-down lists) to use on a regular worksheet see the much easier and user friendly Excel drop-down lists in the website on Excel.

When you double click on the combo box in the Visual Basic Editor you will see all its properties in the Properties window .

No programming is needed to submit the list of values that will be offered to the user within the combo box. Look for the RowSource property.

The RowSource Property:

The values that should appear in the drop-down list of the combo box are submitted in the RowSource property. For example, if the value of the RowSource property is Balance!A1:A12 The values residing in cell A1 to A12 of the sheet named Balance will be offered as choices to the user who clicks on the small arrow of the combo box.

The rules to submit the RowSource property is the name of the sheet where the list resides followed by an exclamation point (!), the address of the first cell, a colon and the address of the last cell.

IMPORTANT NOTE: if there is a space or a special character within the name of the sheet where the list resides you must surround it with simple quotes. For example: 'New Balance'!A1:A12.


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

Case Study

The client needs a first combo box where the user can select a country (see picture below). In the second box the user can select a city but from a list that contains only cities from the country selected in the first combo box. We call it cascading combo boxes. In "vba-tutorial-combo-box.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros)) you will discover how to develop such pairs of combo boxes.




--------------------------------------------------------------------------------
VBA Lesson 30: The List Boxes in VBA for Excel
Before we begin on the List Box

The difference between a combo box and a list box is that the combo box is a drop-down list and the user can submit a single value from the drop-down list. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values.

Combo Box

List Box



--------------------------------------------------------------------------------

In the toolbox the list box has this icon .

No programming is needed to submit the list of values that will be offered to the user within the combo box. Look for the RowSource property.

The RowSource Property:

The values that should appear in the drop-down list of the combo box are submitted in the RowSource property. For example, if the value of the RowSource property is Balance!A1:A12 The values residing in cell A1 to A12 of the sheet named Balance will be offered as choices to the user who clicks on the small arrow of the combo box.

The rules to submit the RowSource property is the name of the sheet where the list resides followed by an exclamation point (!), the address of the first cell, a colon and the address of the last cell.

IMPORTANT NOTE: if there is a space or a special character within the name of the sheet where the list resides you must surround it with simple quotes. For example: 'New Balance'!A1:A12.


--------------------------------------------------------------------------------
VBA Lesson 31: Option Buttons, Check Boxes and Frames
In the toolbox the option button has this icon , the check box has this one and, the frame this one .

You do not need to add a label to accompany the check box or the option button because they come with their own.

The check boxes and the option buttons are both used to offer the user a choice. The main difference between check boxes and option buttons is that if you have 5 of each on a form a user can check all 5 check boxes but can only select one of the option buttons.

If you want to create two sets of option buttons read below on frames and option buttons. If you do not want to use frames to create groups of option buttons you will need to use the "GroupName" property of the option buttons. All option buttons with the same GroupName work together.

Properties

- WordWrap to be able to write more that one line in the caption,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the option button or the check box.
- Enabled and Visible are properties that you can change programmatically to disable or render invisible an option button or a check box following a previous selection in another control of the userform.

Frames

Frames are also a passive control. Frames are used to improve the layout of the userform. You can use them around a group of controls that have something in common.

Frames become more important to manage option buttons. If you have two sets of option buttons on a userform and you do not place them within a frame they all work together and you can choose only one. If you put each set within a frame you can choose one in each set.

When you move a frame all its controls move with it.


--------------------------------------------------------------------------------
VBA Lesson 32: Excel Spin Buttons
Spin Button

In the toolbox the spin button has this icon .

You can ask a user to enter a value directly in a text box but you can make things a little more attaractive by using a text box and a spin button.

The spin button is not really used by itself. Because the spin button does not show its value it is usually used with a text box. The text box shows a number and by clicking on the arrows of the spin button the value in the text box is increased (or decreased) by 1, or 5 or 10...by whatever value that is set within the properties of the spin button.

Properties

Among the other properties of the spin buttons are:

- Min is the minimum value of the spin button. It can be negative
- Max is the maximum value of the spin button. It can be negative
- Small is the value of the change when the user clicks on the arrows
- Large is the value of the change when the user clicks on the scroll bar of the spin button



--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

In "vba-form-spin-button.xls" (one of the 25 Excel spreadsheets included with the downloadable tutorial on VBA (Macros)) you will discover a pricing tool. The user selects a percentage of increase (or decrease) and a new price is calculated. The step by step instructions will show you how to program the relationship between the spin button and the percentage text box. You will also learn how to format text boxes to show currencies and percentages and how to create easy help files for small tools like this pricing tool. Also discover the VBA code to disallow the use of the "X" to close the form.




--------------------------------------------------------------------------------
VBA Lesson 33: Excel Image Controls
Image Control

There is a control in the toolbox called "Image" . Within this control you can show all types of pictures. You set an image control on a userform and you submit a picture in the property "Picture" . The picture becomes part of the control and userform.

Fitting the Picture

The first thing that you want to do is to fit the picture in the image control to make the size of the control adapt to the size of the picture.

When you are in the Visual Basic Editor and you single click on an image control a frame appears around it with 8 stretchers (picture below). If you double click on the middle stretcher (when a two tips arrow shows) of the right side or on the middle one at the bottom or on the bottom right corner stretcher the image control will adapt to the size of the image. Double clicking anywhere else will take you to the VBA code and will not adapt the control size to the picture size.



PictureSizeMode Property

Another property of the image control is the PictureSizeMode.

If the property is set to the default value 0-frmPictureSizeModeClip the control size can be changed without the picture size being modified. So you can see only part of the picture or there can be a background behind it in a clolor color you can change at will.

If the property is set to the 1-frmPictureSizeModeStretch the picture is resized as the control is. The image fills the control.

If the property is set to the 3-frmPictureSizeModeZoom the picture is resized as the control is but the picture and background are present.


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

See how you can do that with all the step by step programming instructions in "vba-form-image-control.xls" (one of the 25 Excel spreadsheets included with the downloadable tutorial on VBA (Macros)) . Coupled with a combo box you can develop an interesting selector using pictures to make the choices easy (image below).




--------------------------------------------------------------------------------