Make a macro run when data entered in a cell

SandsB - Feb 20, 2009 at 11:52 AM
 Rajiv - Nov 15, 2017 at 12:38 PM

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.

11 responses

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

Whatever your original code was to run your macro, put that back in it's original form.

Since you are using Excel 2007, this is what you 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) Now 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 then the worksheet change event will "Call" your macro.
Hi Wutup,

just wanted to say thank you! I have quite some experience in VBA for Access, but honestly, in Excel I had no idea where to start.

Your introduction was great, and the best: It works :-)

Thanks again

I had the same issue and just wanted to thank the original questioner and WutWut for the really amazing explanation.