run macro on pressing Enter key? (Excel 2003)

[mebhoya]

Greetings!

I have a very plain spreadsheet with one macro that returns a list of dates (when a given hymn was used) using the hymn number entered in D3. My problem (which is purely cosmetic) is that, as far as I know how to set these things up, I now have to click on cell D3, type in the hymn number, press "Enter" or in some other way have Excel "accept" the data for that cell, and only then click on my little button that runs the Advanced Filter macro. I would like that to be a one-step process - either type the hymn number, then press the "Enter" key and have the macro run (when focus shifts from D3 to D4) or be able to simply type in the hymn number and then press my macro button. As it stands right now, if I click the button before I press "Enter," the text-editing mode of cell D3 is still active, and nothing happens. I hope this made some sense, and I deeply appreciate any assistance!

Regards
Mary B

[HansV]

You can use the Worksheet_Change event to trigger the macro.
- Activate the Visual Basic Editor (Alt+F11)
- Double click the item corresponding to the worksheet containing the hymn number in D3.
- Enter code like the following, where MyMacro is the name of the macro you created:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D3")) Is Nothing Then
MyMacro
End If
End Sub

This event procedure will run each time you change the value of a cell. The code tests whether you changed D3, and if so, runs the macro (MyMacro in the example; replace this with the name of your macro)