Excel - Run macro when data entered in a cell
On June 29,2023 by Tom Routley
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. Let's study this a little bit further.
Solution
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 to put it back into its original form.
Since 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 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
8)
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.
Photo: © Everypixel
Leave a Reply