Excel VBA – Macro Runs When Worksheet Changed

Excel 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.

It’s a macro that runs every time the worksheet changed. 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.

Excel worksheet with order form
Excel worksheet with order 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

VBAEventChange02

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
  • not appear if a product or quantity is entered

Revise the Worksheet Change Code

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

Message Box for Canada

Now the message box will only appear if cell B5 was changed, and the customer is located in Canada.

Message Box for Canada only
Message Box for Canada only

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 xlsm (macro enabled) format, and is zipped. After you unzip and open the file, enable the macros, so you can see the message box.
________

0 thoughts on “Excel VBA – Macro Runs When Worksheet Changed”

  1. I want to write a macro that allows me to copy a specific row to another worksheet in the workbook. For example there are dates in column A and if excel detects the date, i want it to copy the row that the date exists and paste it to the worksheet whose name is the date. How can you help me?

  2. why i cannot see all the codes after i download the file…I should have seen the codes but what it appears was only the partial incomplete codes and the rest appears option explicit..
    how should i view the codes to see how it was put up..
    thanks

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.