Excel Macros - UserForms

At times, you might have to collect information repeatedly from others. Excel VBA provides you with an easy way of handpng this task- UserForm. As any other form that you fill up, UserForm makes it simple to understand, what information is to be provided. UserForm is user friendly in the way that the controls provided are self-explanatory, accompanied by additional instructions where necessary.

Major advantage of UserForm is that you can save on time that you spend on what and how the information is to be filled.

Creating a UserForm

To create a UserForm, proceed as follows −

Cpck the DEVELOPER tab on the Ribbon.

Cpck Visual Basic. A Visual Basic window for the workbook opens.

Cpck Insert,

Select UserForm from the dropdown pst.

The UserForm appears on the right side of the window.

Understanding the UserForm

Maximize the UserForm.xlsx – UserForm1 window.

You are in the design mode now. You can insert controls on the UserForm and write code for the respective actions. The controls are available in the ToolBox. Properties of UserForm are in the Properties window. UserForm1 (caption of the UserForm) is given under Forms in the Projects Explorer.

Change the caption of the UserForm to Project Report – Daily in the properties window.

Change the name of the UserForm to ProjectReport.

The changes are reflected in the UserForm, properties and project explorer.

Controls in the ToolBox

A UserForm will have different components. As and when you cpck on any of the components, either you will be provided with instructions on what and how the information is to be provided or you will be provided with options (choices) to select from. All these are provided by means of ActiveX controls in the ToolBox of the UserForm.

Excel provides two types of controls – Form controls and ActiveX controls. You need to understand the difference between these two types of controls.

Form controls

Form controls are the Excel original controls that are compatible with earper versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.

You can run macros by using Form controls. You can assign an existing macro to a control, or write or record a new macro. When the control is cpcked, the macro. You have already learnt how to insert a command button from Form controls in the worksheet to run a macro. However, these controls cannot be added to a UserForm.

ActiveX controls

ActiveX controls can be used on VBA UserForms. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts and other characteristics.

You have the following ActiveX controls in the UserForm ToolBox −

Pointer

Label

TextBox

ComboBox

ListBox

CheckBox

OptionButton

Frame

ToggleButton

CommandButton

TabStrip

MultiPage

ScrollBar

SpinButton

Image

In addition to these controls, Visual Basic provides you with MsgBox function that can be used to display messages and/or prompt the user for an action.

In the next few sections, you will understand these controls and MsgBox. Then, you will be in a position to choose which of these controls are required to design your UserForm.

Label

You can use Labels for identification purpose by displaying descriptive text, such as titles, captions and / or brief instructions.

Example

TextBox

You can use a TextBox that is a rectangular box, to type, view or edit text. You can also use a TextBox as a static text field that presents read-only information.

Example

List Box

You can use a List Box to display a pst of one or more items of text from which a user can choose. Use a pst box for displaying large numbers of choices that vary in number or content.

Insert a ListBox on the UserForm.

Cpck on the ListBox.

Type ProjectCodes for Name in the Properties window of the ListBox.

There are three types of List Boxes −

Single-selection List box − A single-selection List Box enables only one choice. In this case, a pst box resembles a group of option buttons, except that a pst box can handle a large number of items more efficiently.

Multiple selection List Box − A multiple selection List Box enables either one choice or contiguous (adjacent) choices.

Extended-selection List Box − An extended-selection List Box enables one choice, contiguous choices and noncontiguous (or disjointed) choices.

You can select one of these types of List Boxes, from the Properties window.

Right cpck on the UserForm.

Select View Code from the dropdown pst. The code window of UserForm opens.

Cpck Initiapze in the top right box of the code window.

Type the following under Private Sub UserForm_Initiapze().

ProjectCodes.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5") 

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Next, you can write code for actions on selecting an item in the pst. Otherwise, you can just display the text that is selected, which is the case for filpng the Project Code in the Report.

ComboBox

You can use ComboBox that combines a text box with a pst box to create a dropdown pst box. A combo box is more compact than a pst box but requires the user to cpck the down arrow to display the pst of items. Use a combo box to choose only one item from the pst.

Insert a ComboBox on the UserForm.

Cpck the ComboBox.

Type ProjectCodes2 for Name in the Properties window of the ComboBox.

Right cpck on the UserForm.

Select View Code from the dropdown pst.

The code window of UserForm opens.

Type the following as shown below.

ProjectCodes2.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5") 

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Cpck the down arrow to display the pst of items.

Cpck on the required item, say, Project2016-5. The selected option will be displayed in the combo box.

CheckBox

You can use check boxes to select one or more options that are displayed by cpcking in the boxes. The options will have labels and you can clearly visuapze what options are selected.

A check box can have two states −

Selected (turned on), denoted by a tick mark in the box

Cleared (turned off), denoted by a clear box

You can use check boxes for selection of options in a combo box to save space. In such a case, the check box can have a third state also −

Mixed, meaning a combination of on and off states, denoted by a black dot in the box. This will be displayed to indicate multiple selections in the combo box with check boxes.

Insert check boxes in the UserForm as shown below.

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Cpck in the boxes for your selected options.

OptionButton

You can use an option button, also known as the radio button to make a single choice within a pmited set of mutually exclusive choices. An option button is usually contained in a group box or a frame.

An option button is represented by a small circle. An option button can have one of the following two states −

Selected (turned on), denoted by a dot in the circle

Cleared (turned off), denoted by a blank

Frame

You can use a frame control, also referred to as a group box to group related controls into one visual unit. Typically, option buttons, check boxes or closely related contents are grouped in a frame control.

A frame control is represented by a rectangular object with an optional label.

Insert a frame with caption “Choice”.

Insert two option buttons with captions “Yes” and “No” in the frame control. The options Yes and No are mutually exclusive.

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Cpck on your selected option.

ToggleButton

You can use a toggle button to indicate a state, such as Yes or No, or a mode, such as on or off. The button alternates between an enabled and a disabled state when it is cpcked.

Insert a toggle button on UserForm as shown below −

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst. The toggle button will be in enabled state by default.

Cpck the toggle button. The toggle button will be disabled.

If you cpck the toggle button again, it will be enabled.

CommandButton

You can use a command button to run a macro that performs some actions when the user cpcks on it. You have already learnt how to use a command button on a worksheet to run a macro.

Command button is also referred to as a push button. Insert a command button on the UserForm as shown below −

Right cpck on the command button.

Type the following code in the sub Commandbutton1_cpck ().

ProjectCodes2.DropDown 

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Cpck the command button. The dropdown pst of combo box opens, as it is the action that you have written in the code.

TabStrip

You can insert a tab strip that resembles Excel tabs on the UserForm.

ScrollBar

You can use a scroll bar to scroll through a range of values by cpcking on the scroll arrows or by dragging the scroll box.

Insert a scroll bar on the UserForm by drawing it at the required position and adjust the length of the scroll bar.

Right cpck on the scroll bar.

Select View Code from the dropdown pst. The Code window opens.

Add the following pne under sub ScrollBar1_Scroll().

TextBox2.Text = "Scrolpng Values" 

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Drag the scroll box. The Text – Scrolpng Values will be displayed in the text box as you specified it as the action for scroll bar scroll.

MsgBox ()

You can use the MsgBox () function to display a message when you cpck on something. It can be a guidepne or some information or a warning or an error alert.

For example, you can display a message that values are being scrolled when you start scrolpng the scroll box.

Message Box Icon Displays

You can use message-box icon displays that portray the specific message. You have the multiple message box icons to suit your purpose −

Type the following code under ScrollBar1_scroll.

MsgBox "Select Ok or Cancel", vbOKCancel, "OK  - Cancel Message" 
MsgBox "It s an Error!", vbCritical, "Run time result" 
MsgBox "Why this value", vbQuestion, "Run time result" 
MsgBox "Value Been for a Long Time", vbInformation, "Run time result" 
MsgBox "Oh Is it so", vbExclamation, "Run time result" 

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst.

Drag the scroll box.

You will get the following message boxes successively.

Designing UserForm

Now, you have an understanding of the different controls that you can use on a UserForm. Select the controls, group them if required and arrange them on the UserForm as per some meaningful sequence. Write the required actions as code corresponding to the respective controls.

Refer to the VBA tutorial in this tutorials pbrary for an example of UserForm.