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.
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
- 9) Meaning, when you change the value in D10, the worksheet change event will "Call" your Macro.