Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    run macro on pressing Enter key? (Excel 2003)


    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!

    Mary B

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: run macro on pressing Enter key? (Excel 2003)

    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
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts