MsgBox & InputBox in VBA Excel : UserForm Alternatives

If you practice regularly, developing UserForms isn’t difficult. But using built-in functions of Excel to perform the same thing is easier.

VBA has two functions: InputBox and MsgBox. These two functions help us to display simple dialog boxes without creating UserForms in the VB Editor. We can customize these dialog boxes, but for sure they will not provide all the options we can perform using a UserForm.

This article is part of my series: VBA & Macros in Excel – A Step by Step Complete Guide and Make UserForms in VBA Excel with Total 6 Tutorials.

Excel VBA InputBox() Function

The InputBox function is useful for obtaining a single input from the user. A simplified version of the function’s syntax follows:

InputBox function: Syntax

InputBox ( prompt, [title], [default] )

InputBox function: Parameters or Arguments

The arguments are defined as follows:

  • prompt: This one is required. This text is displayed in the input box.
  • title: It is optional. This text is displayed in the input box’s title bar.
  • default: It is also optional. The default value showed in the input field.

The following macro (StandardInputBox) uses an InputBox function. Observe closely.

Sub StandardInputBox()
    Dim stringName As String
    'InputBox(prompt[, title] [, default] )
    stringName = InputBox("Enter you name:", "NAME ENTRY BOX", "Enter Your Name")
    'Exit sub if user press Cancel button or does not enter any text in the Input field.
    If stringName = vbNullString Then Exit Sub
    MsgBox "Hello " & stringName
End Sub

Run this VBA macro using Developer ⇒ Code ⇒ Macro ⇒ Select StandardInputBox Macro ⇒ Run. You will see a figure like below:

Alternatives of UserForm in Excel: InputBox & MsgBox Functions

StandardInputBox dialog box, it takes one value from the user.

I write my name in the Input field as “Kawser Ahmed” and then click OK button, it will show another dialog box as I have used this code in the macro:

MsgBox "Hello " & stringName 

 

Alternatives of UserForm in Excel: InputBox & MsgBox Functions

Showing my name in the message box.

Now I click OK and the dialog box vanishes. This is how you can use InputBox function in a macro instead of writing a UserForm.

Read More: How to Use the Select Case Structure in Excel VBA

VBA MsgBox() Function

The VBA MsgBox function is an easy way to display information and to take simple input (like Yes, No, Cancel) from the users.

MsgBox function: Syntax

A simplified version of the MsgBoxsyntax is as follows:

MsgBox ( prompt, [buttons], [title] )

MsgBox function: Parameters or Arguments

The arguments are defined as follows:

  • prompt: It is required. This text is displayed in the message box.
  • buttons: It is optional. Here we shall use some code such as vbYesNo, vbOKOnly in this place. According to this code, different buttons will appear in the message box. For example, see this MsgBox function in the GetAnswer macro:
Sub GetAnswer()
    Ans = MsgBox("Process the monthly report?", vbYesNo)
    If Ans = vbYes Then MsgBox ("Some reports will be showed :)")
    If Ans = vbNo Then Exit Sub
End Sub

This code (vbYesNo) will show two buttons in the message box, one Yes button and the other one is No button.

Alternatives of UserForm in Excel: InputBox & MsgBox Functions

‘Yes No’ Button showed in the dialog box due to code vbYesNo in the MsgBox function.

  • title: It is optional. This text appears in the message box’s title bar.

MsgBox function can be used by itself like this one: MsgBox “Click OK to continue” (parenthesis not included in this way) or it can be assigned to a variable like the above one: Ans = MsgBox(“Process the monthly report?”, vbYesNo).

In the following VBA macro, MsgBox function is assigned to a variable. Then the variable can be used in an effective way.

Sub GetAnswer()
    Ans = MsgBox("Process the monthly report?", vbYesNo)
    If Ans = vbYes Then MsgBox ("Some reports will be showed :)")
    If Ans = vbNo Then Exit Sub
End Sub

The Ans variable will have two values corresponding to vbYes or vbNo. If the value is vbYes, it will work one work and if the value is vbNo then it will do different work.

The following Table lists the built-in constants that you can use for the buttons argument. You can display your preferred buttons, make visible an icon, and select which button will be the default.

MsgBox TABLE: Constants Used in the MsgBox Function

ConstantValueDescription
vbOKOnly0It displays OK button.
vbOKCancel1It displays OK and Cancel buttons.
vbAbortRetryIgnore2It displays Abort, Retry, and Ignore buttons.
vbYesNoCancel3It displays Yes, No, and Cancel buttons.
vbYesNo4It displays Yes and No buttons.
vbRetryCancel5It displays Retry and Cancel buttons.
vbCritical16It displays Critical Message icon.
vbQuestion32It displays Query icon (a question mark).
VBExclamation48It displays Warning Message icon.
vbInformation64It displays Information Message icon.
vbDefaultButton10First button is default.
vbDefaultButton2256Second button is default.
vbDefaultButton3512Third button is default.

Observe the following VBA macro. I have used a combination of constants (vbYesNo, vbQuestion and vbDefaultButton2) to show a dialog box with a Yes button, a No button, and a Question mark icon. Default button (vbDefaultButton2) will be the second button (No button).

Sub GetAnswer()
    Config = vbYesNo + vbQuestion + vbDefaultButton2
    Ans = MsgBox("Process the monthly report?", Config)
    If Ans = vbYes Then MsgBox "You pressed to see the Monthly Report."
    If Ans = vbNo Then Exit Sub
End Sub

See the following figure.

Alternatives of UserForm in Excel: InputBox & MsgBox Functions

Showing Yes, No buttons and a question mark beside the Text.

Download Working File

Download the working file from the link below:

MsgBox-function.xlsm

Happy Excelling ☕


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply