VBA Blogs: Using Message Boxes in Excel

This week, we’re going to look at how to set up message boxes in Excel and VBA.

It’s important to ensure that you can give feedback to users when something wrong occurs.  Often, it’s a nice touch to give feedback when everything works perfectly as well, if only just to let them know that the macro has finished running, and that they can start working in Excel again.  To do this, we’re going to move outside the realm of recorded macros, and start writing some simple VBA code from scratch.

The MsgBox function in Excel gives us one simple way that we can inform users about what we have done with our macros.  The syntax for this is pretty easy:

MsgBox <Prompt>, [Buttons], [Title], [HelpFile], [Context]

What do these all mean?

• Prompt: This is the only mandatory part of the message box.  Whatever you type in here - either by putting it in speech marks or by providing a value to output (e.g. Range(“A1”).Value) – will appear in the body of the message that pops up.  So our first example might be:
• Buttons: Have you ever wondered how applications determine what buttons to show you (“Ok”, “Cancel”, “Export to Excel”)?  The Buttons parameter gives you the flexibility to decide what buttons to display.  You can choose from a range of options including:
  
  OK only
  OK and Cancel
  Abort, Retry and Ignore
  Yes, No and Cancel
  Yes and No
  
  
• You can see other options on Microsoft’s help page here.  By default, OK will be the only button that shows.  These buttons are useful to help capture in VBA whether a user wants to continue or cancel a macro
• Title: Relatively self-explanatory, the Title parameter allows you to change the heading of the message box to be somethingrelevant.  If you leave it blank, it will display the application name (in our case, Microsoft Excel).
• HelpFile and Context: This allows you to reference a custom help file that you can specify, along with what message you would like displayed if someone clicks on the Help button that will appear.  To be completely honest, this is uncommon for most purposes, and we’re not going to be looking at specific examples of how you can make this work (does anyone still use help files these days, rather than Google..?).

So putting all these things together, we could create a message box that checks if a user definitely wants to continue to run the macro.  This might look something along the lines of:

This is a simple way of providing a message to users and getting their feedback. Next week, we will cover how to use the Status Bar to provide updates while the macro is running, without needing to wait for user input to keep things running behind the scenes. Stay tuned!