Hitting return key, linked to a button

**ebaynut** · 09-22-2011, 07:11 AM

I have a search setup in an excel sheet. Simply, I want to be able to hit the enter key and then it would automatically perform the function dedicated the button within the excel sheet.

Example: I type in 'Cat' and hit enter, it will use the code for the search button I created. So instead of clicking the search button, the enter key will act as if I did.

Do you know the code for that?

Thank you in advance.

**ChemistB** · 09-22-2011, 10:23 AM

I've never done this before but see if this works.

1. Modify your macro so that it brings in a string value. Something like this

Sub testing (ByVal TestVal As String)

Then set TestVal to whatever variable your macro searches for.

2. In the spreadsheet where you are entering your values, enter the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TestVal As String

If Target.Column <> 1 Then Exit Sub  'Assuming you are entering value in Column A
TestVal = Target.Value

Testing (TestVal)  'replace with your macro name

End Sub

Try this and see if it works for you.

**ebaynut** · 10-02-2011, 01:00 PM

I tried your code, I'm making it more complicated than it should be. Maybe you can help.

Thanks in advance.


Private Sub Search_Click()
Dim LR As Long, Val As String, cell As Range, Rng As Range, Code As Boolean
Dim dSh As Worksheet, v

'Setup
    Sheets("SEARCH BY ITEM CODE").Activate
    'First Search range
        If Range("C2") <> "" Then
            Val = Range("C2").Value & ""
            Code = True
        'ElseIf Range("E3") <> "" Then
            'Val = Range("E3").Value
            'Code = False
        Else
            MsgBox "Please enter ONE search criteria and try again."
            Exit Sub
        End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set dSh = Sheets("DATABASE")
    Range("A9:G200").ClearContents

'Transfer matching data
    LR = dSh.Range("A" & Rows.Count).End(xlUp).Row
    dSh.Range("B2").AutoFilter
    
    If Code Then
        dSh.Range("B2").AutoFilter Field:=2, Criteria1:="=*" & Val & "*"
    Else
        dSh.Range("B2").AutoFilter Field:=3, Criteria1:="=*" & Val & "*"
    End If
    
    dSh.Range("B2:G" & LR).SpecialCells(xlCellTypeVisible).Copy Range("B9")
    dSh.Range("B2").AutoFilter