Sunday, December 18, 2016

Excel VBA wait until typing is finished

Assume that you have an ActiveX textbox (or any input control that accepts typing text) in your user-form or work-sheet that will be used for instant search or filter. This instant search will be called from the event handler of textbox change. When the user is typing too fast in this textbox the search command will be triggered so fast as well which may cause the application screen to flash multiple times or slow down.

Consider this example: the user want to search for "123", the normal code will search for "1" when "1" is typed, search for "12" when "2" is typed, and finally search for "123" when "3" is typed.  This leaded to three search commands while the required is only one search command. For large data sets, this will be a huge problem.

So, how to make the application understands that fast typing is finished and it has to start the search?
The answer is: when the textbox text changes; start a small timer function after which the search will start. If the user is typing too fast, the new timer will overwrite the last one before it has been ended. In other words, the timer will be reset each time the user is typing.

This post answer the question asked in http://www.mrexcel.com/forum/excel-questions/682986-my-textbox-change-reacts-too-quickly.html

To apply this concept, write the following code in a module

' Test whether you are using the 64-bit version of Office 2010.
#If Win64 Then
   Declare PtrSafe Function GetTickCount64 Lib "kernel32" () As Long
#Else
   Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#End If
 
' Define timer sub that will wait for 1000 milliseconds
Sub WaitTyping()
t1 = GetTickCount()
Do Until (GetTickCount() - t1) > 1000
DoEvents
Loop
End Sub

Write this code inside the textbox change event handler
 
Private Sub SearchTextBox_Change()
WaitTyping    ' We defined this function in module
SearchForText     ' Search will be done after 1000 millisecond
End Sub





Tags:

Excel VBA delay function

Excel VBA instant search textbox speed

Excel VBA wait until user has finished typing

Excel VBA instant search slow response

Excel VBA instant search trick

Improve Excel VBA instant search performance

Excel VBA smart instant search

1 comment:

  1. There are two errors in this example. First, the call "GetTickCount" in the procedure "WaitTyping" fails if the 64-bit version of office is used because the name "GetTickCount64" is used instead of "GetTickCount" in the API declaration. Second, the timer is not "retriggered" after each entry. In this example, the event is always triggered after 1000 ms after entering the first character (and not after the last entry).

    ReplyDelete