Using Enter key in VBA textbox to call macro

Question

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!

FunThomas · Accepted Answer · 2017-09-29 09:31:36Z

1

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

answered Sep 29, 2017 at 9:31

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Enter key in VBA textbox to call macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related