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
Write this code inside the textbox change event handler
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
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
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).
ReplyDeleteIt works perfectly thank you so much, I had a hard time looking for such solution
ReplyDelete