Excel VBA: Show a Message to Users

Unfortunately, no one has found a way to zap users with a mild shock from the keyboard, so we have to rely on messages to help people do the right things in Excel.

You can create messages without Excel VBA. For example, you can add a comment to a cell,

Excel Message Comment

or use a data validation input message.

Excel Message Input

To keep things really simple, you could even type a message in a cell, or in a textbox on the worksheet.

MessageTextbox

Create a Message With Excel VBA

Sometimes you need a message with more impact – visual impact, that is. Maybe the users are ignoring your polite and informative worksheet messages, and you want a message that’s hard to miss.

You can use Excel VBA to create a message box, and make that message appear when someone tries to print the order form.

Create the Macro

You can’t record all the steps for creating a message, but you can use the Record Macro button to get started.

  1. At the bottom left of the Excel 2007 window, click Record Macro
  2. Type a one word name for the macro, e.g. CustomerMessage
  3. Store the macro in ‘This Workbook’ and click OK
  4. At the bottom left of the Excel 2007 window, click Stop Recording

To see the macro code that you created:

  1. On the Excel Ribbon, click the View tab, then click Macros
  2. Click View Macros
    • Excel Ribbon View Macros
  3. Click on your macro’s name, then click Edit

The Visual Basic Editor (VBE) opens, and you can see the macro. There are Sub and End Sub lines, and one comment line.

Excel VBA code 01

Type a Line of Code

You can’t record the code that shows the message box, so you’ll have to type it.

  1. Click in the blank line between the Sub and End Sub lines.
  2. Type this code:  Msgbox “Select a Customer Name”

CustMsg02

Test the Macro

To see warning message, switch back to Excel.

  1. On the Excel Ribbon, click the View tab, then click Macros
  2. Click View Macros
  3. Click on your macro’s name, then click Run

The message will appear in the centre of the Excel window.

  1. Click OK to close the message.

CustMsg03

Edit the Message Box Macro

The message box appears, and the user will have to click OK before they can continue working in Excel. That should get their attention, but you can add an icon to the message box, to make it look scarier.

  1. Go back to the VBE, to edit your macro.
  2. Click at the end of the MsgBox line, and type a comma
  3. A drop down list of options will appear, as well as a yellow tip box.
    • CustMsg04
  4. Use the down arrow on your keyboard to select vbCritical
  5. Press the Enter key to select that option

Test your macro again, to see the Critical icon in the revised message box.

CustMsg06

Woohoo! That should keep those pesky users in line.

Make the Macro Even Better

Next week, we’ll revise the macro again, so it only runs if there’s no customer name in cell B5. Then, we’ll make the macro run automatically, if someone tries to print the worksheet, without a customer name selected.

_________________

Previous Excel VBA articles:

_________________

You may also like...

10 Responses

  1. Jon Peltier says:

    “Mild” shock?? What good is that? Through the keyboard?

    How about 50,000 Volts through their Aeron?

  2. Gerald Strever says:

    If Microsoft had consulted me, I would have suggested a mild shock. But, for some strange reason, I was not consulted. So I use the following message instead: Formatting Hard Drive…

  3. Dave says:

    Is there a way to get around that pesky macros warning? I’ve had trouble recently with several users not enabling, which Microsoft really didn’t make easier with that weak bar under the ribbon.

  4. Good idea Jon! The chairs might be expensive, but would pay for themselves in increased productivity.

    Nice one, Gerald. That message should wake up a few people.

    Dave, I don’t know of any way around that security bar warning, except by changing the security level. I’ve had a few phone calls from people complaining that a file isn’t working, and then we discover that the macros haven’t been enabled. They’re used to seeing the popup message from earlier versions.

  5. Jon Peltier says:

    They really messed up the whole Enable Macros thing in 2007. In 2003, you always got the dialog, and if you used the red X to dismiss the dialog, the file was not opened. In 2007 you don’t always get the dialog, and the Red X is the same as clicking Disable, because the file opens anyway. If you don’t get the dialog, you get a light gray bar that blends in with the rest of the gaudy UI. At least in 2010 they made this bar bright red, so it’s harder not to notice it.

  6. Andy says:

    Debra,
    This is an amazing macro and your tutorial is very well presented. Thanks for all of these great ideas. We’d love to hear from you at http://www.facebook.com/office

    Dave here are some instructions on changing security settings for macros: http://office.microsoft.com/en-us/excel/hp100969191033.aspx

    Cheers,
    Andy
    MSFT Office Outreach

  7. Contextures Blog » Excel VBA: Show Message Automatically says:

    […] Excel VBA: Show a Message to Users […]

  8. Contextures Blog » Excel VBA: Show a Message Before Printing says:

    […] Excel VBA: Show a Message to Users […]

  9. Contextures Blog » Collect Data From Users in Excel VBA says:

    […] can use Excel VBA to show messages to someone who’s using your workbook. This messages lets the user know that a customer name must […]

  10. blaise says:

    j’espere que c’est une bonne idee.
    mais pourquoi pas s’il y a une duplication des information alors macro stop?
    je veux un peu ca.

Leave a Reply to Andy Cancel reply

Your email address will not be published. Required fields are marked *