Excel VBA – Macro Runs When Worksheet Changed
Are you ready for Spreadsheet Day on October 17th? Maybe you can add a Spreadsheet Day message to all your workbooks, using the technique described in this blog post. I'm sure your co-workers would enjoy that!
The Mission: Remind Users to Fill in Customs Form
In this example, the workbook has an order form, with a data validation drop down list, where you can select a customer name.
There are VLOOKUP formulas that pull the address information for the selected customer, to fill in the top of the Order Form sheet. If the customer is located in Canada, you'd like to remind the user to fill in a customs form.
Create a Worksheet Change Macro
By using Event code in Excel VBA, you can make a macro run automatically if something happens on the worksheet. In this example, you want the macro to run if there is a change on the worksheet.
The following code will make a message appear when the selected customer is in Canada (cell E7).
Private Sub Worksheet_Change(ByVal Target As Range) If Range("E7").Value = "Canada" Then MsgBox "Please fill in customs form" End If End Sub
Show Message Only When Specific Cell is Changed
On a worksheet where there are multiple cells that can be changed, you might want the message to appear only when a specific cell is changed. In the order form, the message should appear after the customer name is selected, but not every time a product or quantity is entered.
You can add a couple of lines of code, so it only runs when cell B5 is changed (the customer name).
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" Then If Range("E7").Value = "Canada" Then MsgBox "Please fill in customs form" End If End If End Sub
Now the message box will only appear if cell B5 was changed, and the customer is located in Canada.
Watch the Excel Worksheet Event Macro Video
To see the steps for creating an Excel Worksheet Change Event macro, watch this short Excel video tutorial.
Download the Sample Workbook
To see the code, and experiment with the Order Form macro, you can download the Order Form Event Code workbook. The file is in Excel 2007/2010 macro enabled format, and is zipped. After you unzip and open the file, enable the macros, so you can see the message box.