User Forms

The simplest ways to communicate with your users is by the message box and the input box. For more complex user interaction with a combination of choices and the use of list boxes and other controls it is better to create dedicated userforms. Implementing a userform however is quite a bit of work.

Message box

The message box we already saw a lot of times, is used in the following cases:

  1. Communicate information, a warning or an error message;
  2. Ask the user a question.

Syntax: MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Prompt: Contains String expression displayed as the message in the dialog box.

buttons: optional, Contains Numeric value specifying the number and type of buttons to display.The default button value is 0.

title: optional, Contains String expression displayed in the title bar of the dialog box.

MsgBox "This is an example Yes No Syntax", vbYesNo
MsgBox "Text to show", vbYesNo + vbInformation, "Here is Title"
MsgBox("Hello, Are you married? Choose:""" _ & vbCr & "Yes: if you are married""" _ & vbCr & "No: if you are Not married""" _ & vbCr & "Cancel: if you find the question inappropriate""" _ , vbYesNoCancel + vbQuestion)
The last messagebox shows  three possible choices, and if the text is too long to display, you can use & vbCr & to give enters between the sentences.

Message box More info

Input box

The InputBox function is an easy way to get information from the user. This function is easy enough to use with only the Prompt argument required. The Title argument is the titlebar of the dialog. If omitted the name of the application is taken.

Syntax: InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

Function Area()
Dim Length As Double
Dim Width As Double
Length = InputBox("Enter Length ", "Enter a Number")
Width = InputBox("Enter Width", "Enter a Number")
Area = Length * Width
MsgBox "Area is " & Area
End Function  

application inputbox More info application inputbox

inputbox builder More info on inputbox builder


In the VBA Editor from the menu bar click Insert > User Form:

Insert User Form menu

new User Form in the VBA Editor

If you can't see the toolbox, or accidentally lose it, click the View menu at the top of the Editor. From the View menu, select Toolbox.