How to Use MsgBox For Excel VBA
When programming Microsoft Excel functions, you can use a language called Visual Basic for Applications (VBA). VBA is a stripped down version of the Visual Basic programming language. It's designed to provide modified, customized functions in the Microsoft Excel and Access applications. One function in VBA is the "MsgBox()" function. The MsgBox function displays a dialog box to the user. You can display a dialog box that just shows information, or you can use it to return an answer from the user.
Other People Are Reading
Instructions
-
-
1
Open Microsoft Excel and load an existing or new spreadsheet. In Excel 2007, click the developer ribbon and then click the "Visual Basic" button. In older Excel versions, click the "Tools" menu and then click "Macro." From this menu, click "Visual Basic Editor."
-
2
Double-click the "Workbook" icon on the left on the window pane. This opens a VBA editor window. In the dropdown box where "Declarations" is selected, change it to "Workbook." This creates a new function that is triggered when the workbook is opened called "Workbook_Open()." For this example, the MsgBox will be programmed to display when the user opens the workbook.
-
-
3
Enter MsgBox "Hello World" into the newly created function. This is the way to display information to the user. The message box displayed is purely for information and does not retrieve an answer. The user clicks the "OK" button and dialog box disappears.
-
4
Retrieve an answer from the user by assigning the message box to a variable. Below is the code to retrieve an answer:
Answer = MsgBox("Do you want to continue ?", vbYesNo)
The answer is a one or a zero and stored in "Answer." The vbYesNo is a constant, and it instructs the MsgBox dialog box to display a "Yes" and "No" button. The answer can later be used in the code to determine how the application will function. -
5
Save the file and close the workbook. Open the workbook again to test the code. As soon as the workbook loads, you are given a dialog box with the text coded in the MsgBox function.
-
1
Tips & Warnings
The developer ribbon does not normally show by default in Excel. To add it to your view, click the Office button, and click "Excel Options" at the bottom of the menu. In the "Popular" tab, check the box labeled "Show Developer Ribbon."
