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.
http://www.4shared.com/file/89145449/43c8c38/Change_Value_of_D10.html
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.
Data entered in cross browser scripting