Excel VBA: Show Message Automatically

Keeping Excel users on the right path is a big job, but somebody’s got to do it! Last week we created an Excel message box in an order form, to remind users to select a customer name.

Excel Message 00

However, we don’t want the message to appear in every order form – it should only show if the customer name cell is empty. We’ll modify the code so it checks for a customer name.

Sample File: You can download the Excel order form with last week’s message macro. Enable macros when the file opens.

Clear the Customer Name Cell

To see how to refer to the customer name cell, we’ll record a test macro.

  1. Select any sheet in the workbook, other than the OrderForm sheet.
  2. Start the Macro Recorder
  3. Name the macro NameTest, and store it in This Workbook
  4. Click on the OrderForm sheet tab, to activate that sheet
  5. Select cell B5, where the customer name is entered
  6. Stop the Macro Recorder

Excel Message 01

To see the recorded macro:

  1. On the Ribbon, click the View tab
  2. Click Macros, then click View Macros
  3. In the list of macros, click NameTest, then click Edit.

Here’s the recorded code, with the comment lines and blank lines removed. It shows you how to refer to a sheet and cell in Excel VBA code.

Excel Message 02

We’ll remove the Select in each line, and combine the two lines into one. At the end of the line, add: .Value = “”

The revised line of code will set the value of the customer name cell to an empty string.

Excel Message 03

Run the NameTest macro, and it should clear the customer name cell.

Check For a Customer Name

We’ll add the new code to the old code, that shows the message. To check for a customer name, we’ll create an IF…THEN section in the macro, similar to what you’d do in an IF formula on the worksheet.

In English, the instructions would be: IF the customer name cell is empty, THEN show the message. In the Excel VBA code, we’ll use these three lines:

ExcelMessage04

Now, when you run the CustomerMessage macro, you should see the message box only if the customer name cell is empty.

The Next Step

Next week, we’ll add code to make the macro run automatically, when someone tries to print the order form.

_________________

Previous Excel VBA articles:

_________________

You may also like...

2 Responses

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

    […] Excel VBA: Show Message Automatically […]

  2. muhammad farooq bawani says:

    IT IS REALY VERY HELP FULL THANKU VERY VERY VERY MUCH YOR WAY IS GREAT I AM COMPLETLY BLANK ABOUT VBA BUT NOW I AM UNDERSTANDING ITS ALL BCAUSE OF YOY THANKU VVVVVVVERY MUCH

Leave a Reply to Contextures Blog » Excel VBA: Show a Message Before Printing Cancel reply

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