How To Create A Custom Message Box In Excel Vba

MsgBox1 If you're familiar with Excel, you know that it supports macros. What are macros exactly? Macros are nothing but pre-written instructions that you can insert into your Excel spreadsheet. You can carry out various tasks with the help of macros. In Excel, macros are written in Visual Basic for Applications – which is both a software interface as well as a programming language. Visual Basic for Applications was developed by Microsoft to allow Microsoft Office Suite users to add enhanced functionality to the standard apps, like Excel.

If you're learning Excel (here's a course on Excel 2013 that can help you out), or if you use Excel a lot at work, it's a good idea to learn VBA. It's not a complicated language to learn- you can pick up everything you need to know through our Excel VBA beginner course. We teach you how to write your own macros, which will help you enhance your productivity at work. It will also make your life easier, in general. If you have prior experience with VBA, you may still want to do a quick VBA refresher with this short tutorial.

The Excel VBA MsgBox

In this tutorial, we're going to learn how to write a simple MsgBox (message box) in Excel using VBA. So what isa MsgBox exactly? A MsgBox is the dialog box that pops up when you press a command button on your spreadsheet. You must be familiar with message boxes. For example, when you try to exit from most applications, a dialog box pops up, asking you if you're sure you want to quit. This dialog box is a customized MsgBox.

A MsgBox is a very useful, and important, feature of VBA. It gives the user an idea of the result of their actions. For example,it can be used to show the results of a calculation or to advise the user that their actions could potentially cause problems in the spreadsheet. These are only some of the ways in which a message box can be used.

Writing a Simple MsgBox

It's standard to link a MsgBox with a command button in VBA. You can display a message box without a command button, but it's easier to simply pair it with a command button. So before you write the code for a MsgBox, you should insert a command button in your Excel spreadsheet. To add a command button, you need to enable the Developer tab, if you haven't already. To enable the Developer tab, right click anywhere on the ribbon at the top (just below the menu buttons) and select the Customize the Ribbon option in the menu. Check the Developer checkbox in Main Tabs and click ok. This should bring up a new tab called Developer, next to the View tab at the top.

Now, click the Developer tab and then click the Insert option. In the ActiveX Controls selection, choose the command button option. Your mouse pointer will transform into a plus icon (+). Click on any cell in your spreadsheet. A command button will appear on your spreadsheet. You can drag and drop this command button anywhere you want.

Now we need to link a MsgBox to our command button. To do that, right click on the command button and choose the View Code option in the menu. This will open up the Visual Basic for Applications code screen. By default, the command button will have the following code:

Private Sub CommandButton1_Click() End Sub

This is where we add the code that will bring up a message box when we click on the command button. The syntax for a MsgBox is as follows:

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

Only the prompt part of the code is required. The rest of the code (buttons, title, helpfile and context) are optional. The prompt part of the code is the message you want inserted in your message box. For example, if you want to wish a user "Good morning" when he or she clicks on the command button, you would insert the following code line:

Private Sub CommandButton1_Click() MsgBox("Good Morning") End Sub

After you've inserted this code line, exit from the VBA screen. Then deselect the Designer Mode (on the ribbon, in the Developer tab). Finally click on the command button. A Message Box will pop up, wishing the user "Good Morning", as shown above.

Changing the Title of the Message Box

Notice that the title of the Message Box is Microsoft Excel by default. If you added a Message Box is Power Point, the title of the Message Box would be Power Point. You can change the title of the Message Box to anything you want. Take a look at the syntax of the MsgBox again:

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

The title part of the code lets you set the title of your message box. Let's change our title to "Message Box Example". To edit the code on your command button, you have to enable Design Mode again. Right click on the command button and choose view code. To edit the title of your MsgBox, you need to set the Infer Option to on. We're assuming you know how to do that. If you don't know how, we recommend that you sign up for our VBA course. Alternatively, you can browse the Microsoft library for information.

The code should be changed as follows:

Dim message = "Good Morning" Dim ourtitle = "Message Box Example" MsgBox(message,  , ourtitle)

Now, when you click on the command button, it will bring up a message box with the title "Message Box Example".

Adding Buttons to the Message Box

It's possible to add buttons to your MsgBox. A MsgBox will, by default, come with an "Ok" button. You can change the id of the button to get it to become a "Yes" button, a "No" button, an "Abort" button or a "Retry" button (among many others). Here is a list of the possible button settings that are available in the official documentation:

MsgBox2

As you can see, you can do a lot with your message box.You can change the style of your MsgBox as well. If you want to learn how to do that, sign up for our Ultimate Excel VBA course.