Excel VBA – Macro Runs When Worksheet Changed

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

VBAEventChange01

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

VBAEventChange03

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.

________

You may also like...

2 Responses

  1. Yılmaz says:

    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. MERLAN says:

    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 to MERLAN Cancel reply

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