VB realize the Excel in fast data retrieval-CodePudding

Implementation effect is shown in figure:
Directly on the code:
 
Public serachedRowIndex As Integer 

Private Sub CommandButton2_Click () 
LoadStandardData 
End Sub 
'drop-down box click events 
Private Sub ListBox1_DblClick (ByVal Cancel As MSForms. ReturnBoolean) 
TextBox1. TopLeftCell=ListBox1. List (ListBox1 ListIndex, 2) 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column 1)=ListBox1. List (ListBox1 ListIndex, 1) 
TextBox1. TopLeftCell (2, 1). Select 
End Sub 
'drop-down box key processing 
Private Sub ListBox1_KeyDown (ByVal KeyCode As MSForms. ReturnInteger, ByVal Shift As an Integer) 
On the Error Resume Next 
If KeyCode=8 Then 
TextBox1. Activate 
End the If 
If KeyCode=13 Then 'press enter 
If ListBox1. ListCount & gt; 0 Then 
TextBox1. Visible=False 
'If ListBox1. Text="" Then ListBox1. ListIndex=0 
TextBox1. TopLeftCell=ListBox1. List (ListBox1 ListIndex, 2) 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column 1)=ListBox1. List (ListBox1 ListIndex, 1) 
TextBox1. TopLeftCell (2, 1). Select 
KeyCode=0 
End the If 
End the If 
If a KeyCode=27 Then Esc 
 'TextBox1. Visible=False 
ListBox1. Visible=False 
End the If 
If KeyCode=37 Then 'direction left 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column 1). Select 
End the If 
If KeyCode=38 And ListBox1. ListIndex=0 
 Then 'directionMe. TextBox1. Activate 
End the If 
If KeyCode=39 Then 'right 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column + 1). Select 
End the If 
End Sub 
'text box key event 
Private Sub TextBox1_KeyDown (ByVal KeyCode As MSForms. ReturnInteger, ByVal Shift As an Integer) 
On the Error Resume Next 
With TextBox1 
The Select Case KeyCode 
Case 13 "enter 
. TopLeftCell=ListBox1. List (ListBox1 ListIndex, 2) 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column 1)=ListBox1. List (ListBox1 ListIndex, 1) 
If ListBox1. ListIndex=1 Then 
. TopLeftCell="" 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column 1)="" 
End the If 
TopLeftCell (2, 1). Select 
KeyCode=0 
Case 40 'down 
ListBox1. Activate 
27 'Esc Case 
TextBox1. Visible=False 
ListBox1. Visible=False 
Selection. Select 
Case left 37 'direction 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column 1). Select 
The Case of 38 'direction 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 2, TextBox1. TopLeftCell (2, 1). The Column). Select 
Case 39 'right 
Sheets (" stay on the meter ".) Cells (TextBox1. TopLeftCell (2, 1). The Row 1, TextBox1. TopLeftCell (2, 1). The Column + 1). Select 
End the Select 
End With 
End Sub 
'text box key event 
Private Sub TextBox1_KeyUp (ByVal KeyCode As MSForms. ReturnInteger, ByVal Shift As an Integer) 
If ListBox1. ListCount & lt;> Sheets (" standard clock "). The Range (" A65536 "). The End (3). Then the Row - 1 
'data is inconsistent, reload 
LoadStandardData 
The Else 
If TextBox1. Text="" Then 
ListBox1. ListIndex=1 
SerachedRowIndex=0 
GoTo exitFlag 
End the If 
'-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
'for the first time, is to retrieve 
'-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
For j=serachedRowIndex To ListBox1 ListCount - 1 'Sheets (" standard clock "). The Range (" A65536 "). The End (3). The Row 
'-- -- -- -- -- - 
'first accurate search, find and fuzzy search 
'-- -- -- -- -- - 
S=ListBox1. List (j, 3) 'pinyin code 
If InStr (1 S, UCase (TextBox1. Value))=1 Then 
ListBox1. ListIndex=j 
SerachedRowIndex=ListBox1. ListIndex 
GoTo exitFlag 
End the If 

S=ListBox1. List (j, 1) coding 
 'If InStr (1 S, UCase (TextBox1. Value))=1 Then 'pinyin code 
ListBox1. ListIndex=j 
SerachedRowIndex=ListBox1. ListIndex 
GoTo exitFlag 
End the If 

S=ListBox1. List (j, 2) the name of '
If InStr (1 S, UCase (TextBox1. Value))=1 Then 'pinyin code 
ListBox1. ListIndex=j 
SerachedRowIndex=ListBox1. ListIndex 
GoTo exitFlag 
End the If 
S=ListBox1. List (j, 0) 'serial number 
If InStr (1 S, UCase (TextBox1. Value))=1 Then 'serial number 
ListBox1. ListIndex=j 
SerachedRowIndex=ListBox1. ListIndex 
GoTo exitFlag 
End the If 
Next j 
'-- -- -- -- -- -- -- -- -- -- -- -- -- 
'reverse precise search 
'-- -- -- -- -- -- -- -- -- -- -- -- -- 
For I=serachedRowIndex Step 1 To 0 
'-- -- -- -- -- - 
'first accurate search, find and fuzzy search 
'-- -- -- -- -- - 
S=ListBox1. List (I, 3) 'pinyin code 
If InStr (1 S, UCase (TextBox1. Value))=1 Then 
ListBox1. ListIndex=I 
SerachedRowIndex=ListBox1. ListIndex 
GoTo exitFlag 
End the If 

S=ListBox1. List (I, 1) coding 
 'If InStr (1 S, UCase (TextBox1. Value))=1 Then 'pinyin code 
ListBox1. ListIndex=I 
SerachedRowIndex=ListBox1. ListIndex 
GoTo exitFlag 
End the If 

S=ListBox1. List (I, 2) the name of '
If InStr (1 S, UCase (TextBox1. Value))=1 Then 'pinyin code 
ListBox1. ListIndex=I 
SerachedRowIndex=ListBox1. ListIndex 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull