How to use MsgBox in Excel 2022

In this tutorial we will learn how to use MsgBox in Excel 2022.

The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. Place a command button on your worksheet and add the following code lines:

1. A simple message.

MsgBox “This is fun”

Result when you click the command button on the sheet:

Simple Message in Excel VBA

2. A little more advanced message. First, enter a number into cell A1.

MsgBox “Entered value is ” & Range(“A1”).Value

Result when you click the command button on the sheet:

Little More Advanced Message

Note: we used the & operator to concatenate (join) two strings. Although Range(“A1”).value is not a string, it works here.

3. To start a new line in a message, use vbNewLine.

MsgBox “Line 1” & vbNewLine & “Line 2”

Result when you click the command button on the sheet:

New Line

MsgBox Function in Excel VBA

The MsgBox function in Excel VBA can return a result while a simple MsgBox cannot.

Situation:

MsgBox Function Example

Place a command button on your worksheet and add the following code lines:

1. First, we declare a variable called answer of type Integer.

Dim answer As Integer

2. We use the MsgBox function to initialize the variable answer with the input from the user.

The MsgBox function, when using parentheses, has three arguments. The first part is used for the message in the message box. Use the second part to specify which buttons and icons you want to appear in the message box. The third part is displayed in the title bar of the message box.

answer = MsgBox(“Are you sure you want to empty the sheet?”, vbYesNo + vbQuestion, “Empty Sheet”)

See also  How to add an Excel Macro to the Toolbar [guide]

Note: Place your cursor on vbYesNo in the Visual Basic Editor and click F1 to see which other buttons and icons you can use. Instead of the constants vbYesNo and vbQuestion, you can also use the corresponding values 4 and 32.

3. If the user clicks the Yes button, Excel VBA empties the sheet. If the user clicks the No button, nothing happens. Add the following code lines to achieve this.

If answer = vbYes Then
    Cells.ClearContents
Else
‘do nothing
End If

4. Click the command button on the sheet.

5. Click Yes.

Msgbox Function in Excel VBA

Result:

MsgBox Function Result

InputBox Function in Excel VBA

You can use the InputBox function in Excel VBA to prompt the user to enter a value. Place a command button on your worksheet and add the following code lines:

1. First, declare the variable myValue of type Variant.

Dim myValue As Variant

Note: we use a variable of type Variant here because a Variant variable can hold any type of value. This way the user can enter text, numbers, etc.

2. Add the following code line to show the input box.

myValue = InputBox(“Give me some input”)

InputBox Function in Excel VBA

3. Write the value of myValue to cell A1.

Range(“A1”).Value = myValue

Result when the user enters the value 5 and clicks the OK button.

InputBox Function Result

4. The InputBox function has more optional arguments. The following code line shows an input box with a title displayed in the title bar and has a default value. The default value will be used if no other input is provided.

myValue = InputBox(“Give me some input”, “Hi”, 1)

InputBox Function in Excel VBA

Result when the user only clicks the OK button.

InputBox Function Result

Note: Place your cursor on InputBox in the Visual Basic Editor and click F1 for help on the other optional arguments.

See also  How to use Macro Recorder in Excel 2022

5/5 - (3 votes)
SAKHRI Mohamed
SAKHRI Mohamed

The blog of a computer enthusiast who shares news, tutorials, tips, online tools and software for Windows, macOS, Linux, Web designer and Video games.

Articles: 3746

Leave a Reply

Your email address will not be published.