Enter Key = macro execution

[felipepaz]

Hello guys, I'm new here!

I've created an event key to run another macro. My macro does a search on the worksheet and returns values like monthly billing, store code, city and others. I write the criterion I want on A5:G5 and it returns on A6:G?? ... my problem is when I type text the macro does not work, only when I click on the search icon it does but, when I write numbers on any cell of the search the macro works perfectly and I don't need to click on search icon.

What can it be?

Public Sub Pesquisa()

     Sheets("PESQUISA").Select
    Sheets("basepes").Visible = True
    Sheets("PESQUISA").Select
    Sheets("basepes").Range("A1:L5900").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("PESQUISA!Criteria"), CopyToRange:=Range( _
        "PESQUISA!Extract"), Unique:=False
    Sheets("basepes").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("A5:L5").Select
    Selection.ClearContents
    Sheets("PESQUISA").Select
    Range("A5").Select
    Application.OnKey "{ENTER}", "Pesquisa"
    
End Sub

 

[ImranMalek]

Hi ,
Welcome to the board.
If you want to execute this code when you press Enter ( {ENTER} is enter key from the numpad ) a better way would be to write this code in workbook activate event.

Private Sub Workbook_Activate()
  Application.OnKey "{ENTER}", "[COLOR=#333333]Pesquisa[/COLOR]"
End Sub

If you want the search results to be populated as soon as you change values in cells A5:G5 you need to write the code like this in worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5:G5")) Is Nothing Then
        Sheets("PESQUISA").Select
        Sheets("basepes").Visible = True
        Sheets("PESQUISA").Select
        Sheets("basepes").Range("A1:L5900").AdvancedFilter Action:=xlFilterCopy, _
                                                           CriteriaRange:=Range("PESQUISA!Criteria"), CopyToRange:=Range( _
                                                                                                                   "PESQUISA!Extract"), Unique:=False
        Sheets("basepes").Select
        ActiveWindow.SelectedSheets.Visible = False
        Range("A5:L5").Select
        Selection.ClearContents
        Sheets("PESQUISA").Select
        Range("A5").Select
        Application.OnKey "{ENTER}", "Pesquisa"
    End If
End Sub

Hope it helps.

 

[felipepaz]

Hi ,
Welcome to the board.
If you want to execute this code when you press Enter ( {ENTER} is enter key from the numpad ) a better way would be to write this code in workbook activate event.

Private Sub Workbook_Activate()
  Application.OnKey "{ENTER}", "[COLOR=#333333]Pesquisa[/COLOR]"
End Sub

If you want the search results to be populated as soon as you change values in cells A5:G5 you need to write the code like this in worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5:G5")) Is Nothing Then
        Sheets("PESQUISA").Select
        Sheets("basepes").Visible = True
        Sheets("PESQUISA").Select
        Sheets("basepes").Range("A1:L5900").AdvancedFilter Action:=xlFilterCopy, _
                                                           CriteriaRange:=Range("PESQUISA!Criteria"), CopyToRange:=Range( _
                                                                                                                   "PESQUISA!Extract"), Unique:=False
        Sheets("basepes").Select
        ActiveWindow.SelectedSheets.Visible = False
        Range("A5:L5").Select
        Selection.ClearContents
        Sheets("PESQUISA").Select
        Range("A5").Select
        Application.OnKey "{ENTER}", "Pesquisa"
    End If
End Sub

Hope it helps.

You won't believe but I've solved this issue just adding two more lines on my code.

 Public Sub Pesquisa()'
' Pesquisa Macro
'


     Sheets("PESQUISA").Select
    Sheets("basepes").Visible = True
    Sheets("PESQUISA").Select
    Sheets("basepes").Range("A1:L5900").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("PESQUISA!Criteria"), CopyToRange:=Range( _
        "PESQUISA!Extract"), Unique:=False
    Sheets("basepes").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("A5:L5").Select
    Selection.ClearContents
    Sheets("PESQUISA").Select
    Range("A5").Select
    Application.OnKey "~", "Pesquisa"
    Application.OnKey "{ENTER}", "Pesquisa"

Now, the users can run the macro pressing enter or enter numpad and it works perfectly. Thank you very much for the attention!!