A dialog box is an excellent alternative to a userform, they are built-in to vba and can save you time because you don't need to code and build a userform. Some of these dialog boxes also have built-in validation. This article shows you what you can and can not do with dialog boxes.

  1. Message box
    1. Buttons
    2. Icons
    3. Combine buttons and icons
    4. Default buttons
    5. Messagebox return value
    6. Helpfile
    7. Working with text in a message box
  2. Input box (vba)
  3. Input box (Excel)
    1. Number
    2. Range
    3. Combine data types
  4. DataForm
  5. FileDialog
    1. msoFileDialogFilePicker
    2. msoFileDialogFolderPicker
    3. msoFileDialogOpen
    4. msoFileDialogSaveAs
  6. GetOpenFilename
  7. GetSaveAsFilename

Message box

The most basic dialog box you probably have seen many times is the messagebox. In its most simple form it gives the user a message you specify and a OK button.

dialog-boxes-messagebox

Sub Macro1()
MsgBox "Hi, there!"
End Sub

It is a great tool for quickly troubleshooting a subroutine or a custom function. The messagebox have more options than displaying text or variables, these are the arguments:

Arguments

dialog-boxes-messagebox-arguments

dialog-boxes-messagebox-arguments1

Sub Macro1()
MsgBox "Prompt", vbOKOnly, "This is a title"
End Sub

Prompt - Text shown in message box
Buttons - You can select of a variety of different buttons and icons. If you can't find the buttons you need, a user form is required.

vbOKOnly - 0
vbOKCancel - 1
vbAbortRetryIgnore - 2
vbYesNoCancel - 3
vbYesNo - 4
vbRetryCancel - 5
vbCritical - 16
vbQuestion - 32
vbExclamation - 48
vbInformation - 64
vbDefaultButton1 - 0
vbDefaultButton2 - 256
vbDefaultButton3 - 512
vbDefaultButton4 - 768

Title - Text at the very top of the message box. (Optional)

Helpfile - Path to your helpfile. (Optional)

Context - A numerical expression. (Optional, required if you specify a helpfile)

Back to top

Buttons

There are six different button setups. The most basic message box shows a text string or a variable and an OK button.

dialog-boxes-messagebox1

Sub Macro1()
MsgBox "Prompt", vbOKOnly
End Sub

You don't even need to specify the vbOKOnly argument to show the above dialog box.

vbOKCancel argument displays OK and Cancel buttons.

dialog-boxes-messagebox-vbokcancel

Sub Macro1()
MsgBox "Prompt", vbOKCancel
End Sub

vbAbortRetryIgnore shows three buttons, abort, retry and ignore.

dialog-boxes-messagebox-vbabortretryignore

Sub Macro1()
MsgBox "Prompt", vbAbortRetryIgnore
End Sub

vbYesNoCancel also shows three button, Yes, No and Cancel

dialog-boxes-messagebox-vbyesnocancel

Sub Macro1()
MsgBox "Prompt", vbYesNoCancel
End Sub

vbYesNo diplays Yes and No buttons.

dialog-boxes-messagebox-vbyesno

Sub Macro1()
MsgBox "Prompt", vbYesNo
End Sub

vbRetryCancel shows Retry and Cancel buttons.

dialog-boxes-messagebox-vbretrycancel

Sub Macro1()
MsgBox "Prompt", vbRetryCancel
End Sub

Back to top

Icons

vbCritical shows this icon on the message box.

dialog-boxes-messagebox-vbcritical

Sub Macro1()
MsgBox "Prompt", vbCritical
End Sub

vbQuestion shows this query icon.

dialog-boxes-messagebox-vbquestion

Sub Macro1()
MsgBox "Prompt", vbQuestion
End Sub

vbExclamation shows a warning icon.

dialog-boxes-messagebox-vbexclamation

Sub Macro1()
MsgBox "Prompt", vbExclamation
End Sub

vbinformation shows an information icon.

dialog-boxes-messagebox-vbinformation

Sub Macro1()
MsgBox "Prompt", vbInformation
End Sub

Back to top

Combine buttons and icons

If you look at the message box arguments in the beginning of this post, you will see a number next to each constant. Combining for example an OK and Cancel button with a query icon you simply add the numbers for those constants. OK and Cancel has value 1 and the query icon has value 32.

1 + 32 = 33

dialog-boxes-messagebox-combining-buttons-and-icons

Sub Macro1()
MsgBox "Prompt", 33
End Sub

You can combine constants intead of values, if you prefer that. See macro below, it produces the same result as the macro above.

Sub Macro1()
MsgBox "Prompt", vbOKCancel + vbQuestion
End Sub

Back to top

Default buttons

You can change the default button for a message box, see the arguments in the beginning of this post. The following message box has the OK button as the default button, it has a dotted line around the text "OK".

dialog-boxes-messagebox-combining-buttons-and-icons

You can change it to the second button by adding 256 to the second argument. 33 + 256 = 289

Sub Macro1()
MsgBox "Prompt", 289
End Sub

dialog-boxes-messagebox-default-button

If you prefer you can add the constants to the second argument, as well. This macro shows the same message box as above.

Sub Macro1()
MsgBox "Prompt", vbOKCancel + vbQuestion + vbDefaultButton2
End Sub

Back to top

Messagebox return value

The message box returns one of these values depending on which button was clicked on by the user.

vbOK - 1
vbCancel - 2
vbAbort - 3
vbRetry - 4
vbIgnore - 5
vbYes - 6
vbNo - 7

This message box allows you to click the OK button or the Close button. Both buttons result in the value 1 or vbOK.

dialog-boxes-messagebox-return-value

Sub Macro1()
Dim btt
btt = MsgBox("You can click the OK button or the Close button", vbOKOnly)
MsgBox btt
End Sub

The following macro allows you to continue or stop a loop using a message box. If you click No the macro ends.

dialog-boxes-messagebox-return-value1

Sub Macro1()
Dim a As Integer
For a = 1 To 10
If MsgBox("a=" & a & " Continue?", vbYesNo) = vbNo Then Exit For
Next a
End Sub

Back to top

Help file

Here is an example of how to use a msgbox with a helpfile.

dialog-boxes-help-file

Sub Macro1()
MsgBox "Prompt", vbMsgBoxHelpButton, "This is a title", "c:\temp\helpfile.chm", 7
End Sub

How to build a chm file

Back to top

Working with text in a message box

The following macro shows you how to concatenate text and a variable.

Sub Macro1()
Dim nm As String
nm = "Jennifer"
MsgBox "My name is " & nm
End Sub

dialog-boxes-messagebox-working-with-text2

This macro shows you how to put text on two rows.

Sub Macro1()
MsgBox "First row" & vbNewLine & "Second row"
End Sub

dialog-boxes-messagebox-working-with-text1

You can also show data with a delimiting tab.

Sub Macro1()
Dim a As Integer, result As String
For a = 1 To 8
result = result & a & vbTab
Next a
MsgBox result
End Sub

dialog-boxes-messagebox-working-with-text3

Back to top

Input box (vba)

An input box asks the user for a value, you can specify the text and the title in the inputbox. You also have the option where to show the input box on screen and a default input value.

dialog-boxes-input-box-example

 

dialog-boxes-input-box-arguments

prompt - text in input box

title - Text at the very top of the input box

default - default value shown in the input box

xpo,ypos - specify where you want the input box on the screen from the upper-left corner.

helpfile - path to help file

context - A numerical value

Back to top

Returning value

This macro asks where you are from. Default value is "United Kingdom".

dialog-boxes-input-box-example1

Sub Macro1()
Dim Country
Country = InputBox("Where are you from?", "Country", "United Kingdom")
MsgBox Country
End Sub

Note, if you click Cancel button the Input box returns nothing.

Back to top

Input box (Excel)

Excel input box is more versatile, it will do basic validation for you and the user can select a cell range on a worksheet. You have also the option to select one or multiple data types returned by the input box.

dialog-boxes-excel-input-box-example1

prompt - text in input box

title - Text at the very top of the input box

default - default value shown in the input box

left, top - specify where you want the input box from the upper-left corner of the screen.

helpfile - path to help file

HelpContextID - A numerical value

Type - A number representing the data type returned

0 - Formula
1 - Number
2 - Text
4 - Logical value, True or False
8 - A cell reference
16 - Error value
64 - An array of values

Back to top

Number

dialog-boxes-excel-input-box-example2

Sub Macro1()
Dim cd
cd = Application.InputBox("Enter birth year", , , , , , , 1)
MsgBox "Your birth year is " & cd
End Sub

Excel provides basic data validation, if you type a text string excel gives you this warning message.

dialog-boxes-excel-input-box-example3

If you click "Cancel" button excel returns "False".

dialog-boxes-excel-input-box-example4

Back to top

Range

Data type 8 (Cell reference) lets the user select a cell range.

dialog-boxes-excel-input-box-example-6

Sub Macro1()
Dim rngAs Range
Set rng = Application.InputBox("Select a range: ", , , , , , , 8)
MsgBox "You selected " & rng.Address
End Sub

If the user enters something else than a cell reference excel gives this message until a valid cell reference is entered:

dialog-boxes-excel-input-box-example-8

Clicking on "Cancel" button makes excel error out:

dialog-boxes-excel-input-box-example-7

Back to top

Combine data types

dialog-boxes-excel-input-box-example-5

If you combine data types by adding their corresponding value, like this 1+2 = 3 the user is allowed to enter both numbers and text.

Sub Macro1()
Dim cd
cd = Application.InputBox("You can enter both text an numbers: ", , , , , , , 3)
MsgBox "You entered " & cd
End Sub

Back to top

Data Form

Data in this table is made up and random.

dialog-boxes-showdataform

The data form is a smart dialog box, it analyzes your table and customizes the dialog box using table headers.

Make sure you have a cell selected in the table you want to work with before running this macro:

Sub Macro1()
ActiveSheet.ShowDataForm
End Sub

Back to top

FileDialog

FileDialog property lets you prompt the user for a directory or open/save/select a file

dialog-boxes-filedialog

fileDialogType - Choose between 4 constants, see below.

msoFileDialogFilePicker - Select a file
msoFileDialogFolderPicker - Select a folder
msoFileDialogOpen - Open a file
msoFileDialogSaveAs - Save a file

msoFileDialogFilePicker

Allows the user to select a file. The following macro prompts the user for a file o multiple files and then a message box returns the path and file name for each file.

Sub Macro1()
Dim i As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.Show
For i = 1 To .SelectedItems.Count
MsgBox .SelectedItems(i)
Next i
End With
End Sub

msoFileDialogFolderPicker

The following macro lets the user pick a folder. A message box displays the path and folder name.

Sub Macro1()
Dim i As Integer
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
MsgBox .SelectedItems(1)
End With
End Sub

msoFileDialogOpen

The macro below asks the user for a file to open. A message box displays the path and file name. Note, it doesn't open the file.

Sub Macro1()
Dim i As Integer
With Application.FileDialog(msoFileDialogOpen)
.Show
MsgBox .SelectedItems(1)
End With
End Sub

msoFileDialogSaveAs

This macro lets the user pick a folder and a save name. A message box displays the path and file name. It doesn't save the file

Sub Macro1()
Dim i As Integer
With Application.FileDialog(msoFileDialogOpen)
.Show
MsgBox .SelectedItems(1)
End With
End Sub

Back to top

GetOpenFilename

dialog-boxes-getopenfilename

Filefilter - Filter criteria (Optional)
FilterIndex - Index number of default filter (Optional)
Title - title of dialog box (Optional)
Multiselect - Enables the user to select multiple filenames (Optional)

The following macro asks for a file to open, filters used are *.xlsx and *.xlsm. The second filter is the default value. Keep in mind, the macro doesn't open the file it just returns a file name and path.

Sub Macro1()
Dim txt
txt = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx, Excel macro files (*.xlsm),*.xlsm", 2)
MsgBox txt
End Sub

If the user clicks "Cancel" False is returned.

Back to top

GetSaveFilename

dialog-boxes-getsavefilename

Filefilter - Filter criteria (Optional)
FilterIndex - Index number of default filter (Optional)
Title - title of dialog box (Optional)

Sub Macro1()
Dim txt
txt = Application.GetSaveAsFilename("Excel Files (*.xlsx), *.xlsx, Excel macro files (*.xlsm),*.xlsm", 2)
MsgBox txt
End Sub

This macro does not save the file, it only returns the selected path and name.

Back to top