MsgBox and InputBox in VBA

What is MsgBox?

To display a message box in Excel VBA, we use the MsgBox function.

A more formal definition would go like this, a MsgBox is simply a dialog box that you can use to notify your users by displaying a custom message or to receive some basic inputs (such as Yes/No or OK/Cancel).

For example,

Syntax to use MsgBox

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

Prompt is a required argument, and all other arguments are optional.

Different ways to use MsgBox

To begin, enter a number into cell A1 and then use MsgBox Function.

MsgBox "Entered Value at A2 is " & Range("A2").Value

*Here, we used the “&” operator to concatenate (join) two strings

Result:

Now coming one another way to use MsgBox,

Use vbNewLine to begin a new line in a message.

MsgBox "Line1" & vbNewLine & "Line 2"

Result:

InputBox Function

Users are prompted to enter values using the InputBox function. Suppose the user selects the OK button or presses ENTER on the keyboard after entering the values. In that case, the InputBox function will return the text in the text box. The function will return an empty string if the user selects the Cancel button.

Syntax of InputBox function

InputBox(prompt[,titleofBox][,default][,xpos][,ypos][,helpfile,context])

Prompt is a required argument, and the rest are optional arguments.

Now let's learn how to use the InputBox Function. First, declare the variable myVar of type Variant.

Dim myVar As Variant

*We use a Variant type variable in this case because it can hold any sort of value. This allows the user to enter text, numbers, and so on.

Now, by using the following code line to show the input box:

myVar = InputBox("Give me some input")

Suppose you enter 7 in the Blank and press OK.

Code to write the value of myVar to cell A1.

Range("A1").Value = myVar

There are more optional arguments for the InputBox function. The code below displays an input box with a title displayed in the title bar and a default value. If no extra input is supplied, the default value will be utilized.

myVar = InputBox("Give me some input", "Hi", 1)

Result

MsgBox V/s InputBox

I hope now you have got a clear idea, of how these functions are used and how they differ from each other—now moving to our FAQs section.

FAQs

What is a VBA?

VBA is an abbreviation for Visual Basic for Applications, a Microsoft event-driven programming language that is now primarily used with Microsoft office applications such as MSExcel, MSWord, and MSAccess.

How to prompt a MsgBox using Sub?

Sub DefMsgBox()

MsgBox "This is a sample Msgbox using Sub"

End Sub

How to take input from users in VBA?

userInput = InputBox("Enter UserInput, ""Enter a Number") 

Conclusion

In this article, we have extensively discussed the VBA and its two most important functions: MsgBox and InputBox. Later, we discussed their use and Syntax and concluded by pointing out their differences.

To learn more about Excel you can refer to this blog, also to get some basic idea about the terms of excel you can check out these, Array, Worksheets and Google sheets, and Function and Sub

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses, refer to the mock test and problems; look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

“Happy Coding!”