A form is an interface with a standard structure and format that makes it easier to capture, organize, and edit information (here is an overview of forms). You can create forms on spreadsheets or on VBA user forms, by using controls . But before creating a customized user form, consider using built-in dialog boxes available from Excel.

1. Excel built-in dialogs

Message boxes: allows you to print something for the user to see in a small dialog box with an OK button.

MsgBox (Prompt, [Buttons], [Title])
MsgBox (“Task completed”)
MsgBox (“The value of the output is ” & ValueVar)

Note: [Buttons] and [Title] are optional arguments.

You can set the type of buttons displayed with the Button argument

0 = OK button only (default)
1 = OK and Cancel buttons.
3 = Yes, No, and Cancel buttons.

MsgBox returns an integer indicating which button the user clicked:

1 = OK
2 = Cancel
6 = Yes
7 = No

For more information about MsgBox go to MsgBox Function.

Input box: allows you to prompt the user to enter some value in a small dialog box and returns a string containing the contents of the text box

x = InputBox (Prompt, [Title], [DefaultResponse])
x = InputBox (“Insert a number”, ,10)

If the user clicks Cancel, a zero-length string is returned

Input box method: displays a dialog box to input more specific type of data (formula, number, text, logical value or range of cells)

x = Application.InputBox (prompt:=”Insert a formula”, type = 0)
Set r = Application.InputBox (prompt:=”Select a range”, type := 8)

 where Type specifies the return data type

0 = Formula
1 =  Number
2 = Text (String)
4 = Logical value (True or False)
8 = Cell reference (Range object)
16 = Error value
64 = Array of values

For more information about the input box method go to Application.InputBox Method.

Other built-in dialog boxes are GetOpenFilename method and GetSaveAsFilename method.  The Dialogs object of the Application object contains all the built-in Excel dialog boxes.

2. Controls

Note: In this section we focus on ActiveX Controls, which provide more interaction with VBA macros. You can find more about ActiveX Controls here.

Controls are objects that display data or make it easier for users to enter or edit data, perform an action, or make a selection. The Control Toolbox in VBE contains a set of controls, which can be placed directly on the spreadsheet or in user forms.

 

Each control has a set of properties which can be modified and a set of events triggered by some action. This website provides a list of properties and events for the most common controls. Here is a summary:

Control	Properties	Events
Label	Font: the font of text to be displayed on the label Caption: the text of the label
Text Box	Text: the text of the textbox Multiline: If true, the textbox can accept multiple lines of text.	Keypress: triggered when the keyboard is pressed. Change:  triggered when the text of the textbox is changed.
Button	Caption: text displayed on the button	Click: triggered when the button is clicked.
Combo Box	Value: displayed value of the combo box List: contains all items of the combo box	Change: triggered when an item of the combo box is selected. AddItem: adds an item
Check Box	Caption: text displayed on the right of the checkbox Value: True when the checkbox is checked, otherwise it is unchecked.	Change: triggered when the checkbox is checked or unchecked.
List Box	Value: value of the selected item List: contains all items of the list box	Change: triggered when an item of the listbox is selected. AddItem: adds an item to the list
Option Button	Caption: text displayed on the right of the option button Value: True when the option is selected, otherwise it’s not selected.	Click: triggered when the option is clicked

Event Procedures connect actions of controls to a specific macro of VBA code. You can find the complete list of event procedures for a control at the top of the Code Window. Select an object (left hand list) and then the event (right hand list). You can also set the control properties in the properties window.

3. VBA User Forms

VBA User Forms are custom dialog boxes built with controls and their attached macros. To create a form follow these steps:

1. Insert a UserForm into your VBAProject: on VBE, go to Insert → User Form  from the menu.

2. Write a procedure to display the UserForm: you can display the form when you click a button or open a workbook. Use the UserForm1.Show statement.

3. Set up your form properties: on the VBE properties window set the form properties such as Name, Caption, BorderStyle and ScrollBars.

4. Add controls from the Control Toolbox

5. Modify properties for the controls: they can be changed when designing the form (using VBE’s Properties window), or by writing code that manipulate these characteristics at run time.

6. Write event-handler procedures for the controls: event procedures are written in a separate user form module.

Typically you will need to define a procedure to initialize your controls when your form is displayed. You can do this with the Initialize event of the user form: UserForm1_Initialize(). In this procedure you can set your combo box or list box values and initialize your form variables.

At some point you will need to close or hide your form. Use the statements UserForm1.Hide in any procedure or Unload Me within some of the controls’ event procedures.

Here‘s a detailed walkthrough of creating a form.