Excel - Run macro when data entered in a cell

September 2016

If a user wishes to run a Macro in Microsoft Excel when the D10 cell is found to be populated, worksheet change event must be used in the Excel Worksheet, not in the module. http://www.4shared.com/file/89145449/43c8c38/Change_Value_of D10.htlml is downloaded and the original code that was used to run the Macro is set. The Visual basic icon of the Developer tab is selected for Microsoft Excel-2007. After viewing the worksheet where one wishes the code to be run, Declarations and General are dropped down and the worksheet is selected. The word 'Selection' is removed from the 'Private Sub Worksheet_SelectionChange' in the code window. For changing the value in D10 cell, '$D$10' is the target address that is set. This in turn would make the worksheet change event "Call" the Macro.

Issue

I have a Macro that runs when the user clicks on a button. Instead, I want the Macro to run when Cell D10 is populated. The data for D10 comes from a Data Validation drop down, if that matters. This should be easy but I can't figure it out.

Solution

You can keep your Macro in module one. You must use the worksheet change event in the worksheet itself, not a module. Let me explain: I will provide a file to go along with the explanation. In the worksheet, range A5:A25 is filled with numbers. If you enter a numeric value in cell D10, range G5:G10 will copy what is in A5:A25. If you delete the value in D10, then the contents of range G5:G10 will be deleted.

http://www.4shared.com/file/89145449/43c8c38/Change_Value_of_D10.html

Whatever your original code was to run your Macro, put that 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 "Selection" 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(ByVal Target As Range)
  • 8) This is where you want the code...


Private Sub Worksheet_Change(ByVal Target As Range)   

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

Call MyMacro

End If

End Sub
  • 9) Meaning, when you change the value in D10, the worksheet change event will "Call" your Macro.

Note

Thanks to WutUp WutUp for this tip on the forum.

Related :

This document entitled « Excel - Run macro when data entered in a cell » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.