CODEWITHSUNDEEP
vbabutton
Darren Gillen
Darren Gillen

Reputation: 47

Using Enter key in VBA textbox to call macro

I've got a textbox in an Excel worksheet. Users can input text in the box and then click a 'Search' button beside the textbox to find that text. The 'Search' button calls a macro.

My problem is that the user has to click on the 'Search' button to run the macro whereas I would like to have the option of just pressing the Enter key instead after inputting the text. Is it possible to put code into the textbox so that, if a user presses the 'Enter' key, the Search macro is executed?

I've tried using the following code to no avail. 

Private Sub TextBox1_Change()

    If KeyCode = vbKeyReturn Then
        Call search
    End If

End Sub

The code for the search macro (if it helps) is this:

Sub search()

On Error GoTo Errmsg

Columns("A:B").Find(What:=Worksheets("Companies List").TextBox1.Value, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

Exit Sub

Errmsg:
MsgBox "Text can't be found."

End Sub

Thanks in advance!

Upvotes: 0

Views: 8781

Answers (1)

FunThomas
FunThomas

Reputation: 29296

To get the codes of special keys, use the KeyDown or KeyUp event

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    if KeyCode = asc(vbCr) then call search 
End Sub

Upvotes: 1

Related Questions