How to run macro when data entered in a cell

If a user wishes to run a Macro in Microsoft Excel 2007 when the D10 cell is found to be populated, worksheet change event must be used in the Excel Worksheet, not in the module. This article will show you how to run a macro when cell value changes.

For example, when you have a Macro that runs when the user clicks on a button, but instead, you want the Macro to run when Cell D10 is populated. In this case, you can keep your Macro in module one, and you must use the worksheet change event in the worksheet itself, not a module. So, whatever your original code is to run your Macro, we recommend you put it back into its original form.

If you are using Excel 2007, this is what you need to do:

1) Click on the Developer tab.

2) Click on the Visual Basic icon.

3) On the left pane window, double-click the sheet where you need your code to run.

4) Now, at the top of the code window, you will see (General with a drop-down, and (Declarations) with a drop-down.

5) Click the drop-down by (General) and select Worksheet.

6) Now, drop-down in the code window, you will see Private Sub Worksheet_SelectionChange(ByVal Target As Range) .

7) Remove the word Selection. You want to remove it because that means when you click on a cell in the worksheet, something will happen. You do not want that, you want to enter a value in D10. It should now read Private Sub Worksheet_Change<bold>(ByVal Target As Range).

8) Code:

Private Sub Worksheet_Change(ByVal Target As Range)   

If Target.Address = "$D$10" Then   

Call MyMacro   

End If   

End Sub 

9) When you change the value in D10, the worksheet will "Call" your Macro.