CODEWITHSUNDEEP
vbaexcel
user1285419
user1285419

Reputation: 2225

Do seaching in excel on typing

I am dealing with data input. Every time before I input one record, I need to search and locate one cell in about 5000 rows. I am thinking if it is possible to do the search while typing instead of using the search function in excel. My idea is to capture the keypress event of a spreadsheet, if it is a character (letter 'A' to 'Z' or 'a' to 'z'), append it to a string called searchVal do the search immediate with a function. When the user press ESC, it will clean up the searchVal. First of all, I got the following code from online, trying to capture the ESC keydown

Private searchVal As String


Private Sub Workbook_Activate()
    Application.OnKey "ESC", "CleanSearchKey"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "ESC"
End Sub

Sub CleanSearchKey()
  searchVal = ""
  MsgBox "CleanSearchKey"
End Sub

But his code doesn't work, it seems that the CleanSearchKey never triggered. As for capturing the key down for 'A' to 'Z' and 'a' to 'z', I don't want know how to capture it in a sheet so I add an inputbox on sheet1, assigned the following macro to the inputbox

Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If (KeyAscii >= 65 And KeyAscii <= 90) Or (KeyAscii >= 97 And KeyAscii <= 122) Then
    searchVal = searchVal & KeyAscii
  Else
    KeyAscii = 0
  End If

End Sub

But it then said "Compile error: User-defined type not defined". So what is the right way to capture the key on a sheet? Thanks.

Upvotes: 1

Views: 1255

Answers (1)

David Zemens
David Zemens

Reputation: 53653

The code you added for a TextBox is only applicable to a TextBox which appears on a UserForm object. I don't think text boxes on worksheets respond to events like KeyPress.

That said, you could easily configure a simple userform to do this.

enter image description here

enter image description here

I modified your code a little bit. I think this should get you started. In a normal code module, do a procedure like this which will launch the UserForm. This will allow you to show the form from the Macros ribbon menu. Otherwise, you will have to invoke it manually from the Immediate Window or by pressing F5 in the VBE.

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Create a UserForm, and add a textbox. Its default name should be TextBox1, if it is not, then make sure to change it. In the userForm's code module, do this:

Option Explicit
Dim searchVal As String
Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

  searchVal = TextBox1.Value

  If (KeyAscii >= 65 And KeyAscii <= 90) Or (KeyAscii >= 97 And KeyAscii <= 122) Then
    searchVal = searchVal & Chr(KeyAscii)
    TextBox1.Value = searchVal
    Call FindValue

  End If
  If KeyAscii = 27 Then 'ESC
    searchVal = vbNullString
    TextBox1.Value = vbNullString

  Else:
    KeyAscii = 0
  End If

End Sub
Sub FindValue()
Dim rngFound as Range

    With ActiveSheet
        set rngFound = .Cells.Find(searchVal)
    End With
    If rngFound Is Nothing Then
        MsgBox searchVal & " not found!"
    Else
        MsgBox searchVal & " found at " & rngFound.Address
    End If
End Sub

Upvotes: 3

Related Questions