0
Thanks

A few words of thanks would be greatly appreciated.

Excel - 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. 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<bold>(ByVal Target As Range).

  • 8) <bold>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.



Photo: © Everypixel
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Excel - Run macro when data entered in a cell », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!