MsgBox

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.

Result when you click the command button on the sheet:

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

Result when you click the command button on the sheet:

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.

Result when you click the command button on the sheet:

MsgBox Function

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

Situation:

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

1. First, we declare a variable called answer of type 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.

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.

5. Click Yes.

Result:

InputBox Function

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.

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.

3. Write the value of myValue to cell A1.

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

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.

Result when the user only clicks the OK button.

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

Leave a Reply

Your email address will not be published.