The development Ajax has made feasible to get the information from server without reloading the web page. If it is technological development, Google has created a human advancement in how we search for the things in the form of habituating us to Auto Suggest feature while searching. As Nicholas Carr may say it as, manipulation of what we intended to search not just altering about our thinking process. But we search for Excel code, when you need most of it. So lets get to the business, leaving the psychology to the researchers out there for their doctoral thesis.
Our objective is to show the list of one column entries of tabular data based on the search string user inputs.
As we can not pop up the inner HTML like the auto suggest of Google, we will create a text box and list box to show the probable suggestions. The same can be accomplished with just the native Excel cells. So we create a Textbox named TextBox1 and Listbox named ListBox1. (Developer tab -> Insert ->ActiveX Controls). If you want user to select the entries in auto suggest list box and work on the selected entry, we can link the list box to cell in worksheet so that the necessary operations can be performed.
We need to trigger the auto suggest when the user enters / changes some of text box, so we will write our application code in the change event of text box.
'this code has to be in object' located sheet. Can be Sheet1 or whatever name Private Sub TextBox1_Change() 'get the value of user input textinput = Sheet1.TextBox1.Value 'replace 1 in the Mod operator for how many characters to trigger the macro 'also Len(textinput) > 3 shall optimize If Len(textinput) Mod 1 = 0 Then 'MsgBox "Event triggered" 'call the macro Macro1 End If End Sub
The following code uses the Find feature of excel and loop through the results to update the listbox
'this code has to be one of the module Sub Macro1() 'clear the list box of previous entries Sheet1.ListBox1.Clear 'select the range of column entries of parent table 'naming of the range can also be used 'here my data is in B1:B717 of sheet2 With Worksheets("Sheet2").Range("B1:B717") 'variable to keep the search items Dim findResult As Range 'use the builtin find functionality of excel 'looping through the cells can also do the same job 'my search input is in Sheet1.TextBox1.Value Set findResult = .Find(What:=Trim(Sheet1.TextBox1.Value), LookIn:=xlValues, LookAt:=xlPart) If Not findResult Is Nothing Then 'store the fisrt result of find to stop the loop as nextresult variable of last result will yield first result Dim firstFound As String firstFound = findResult.Address 'loop to add to list box Do 'keep adding the find results to listbox Sheet1.ListBox1.AddItem (findResult) Set findResult = .FindNext(findResult) 'loop until we came back to first address Loop While findResult.Address <> firstFound End If 'not necessary to use with the statement End With End Sub
The given code is not optimized and intended for Excel novice users like me. This program is working without any delay for 30,000 rows of data on i5 processor machine with 4GB RAM.
One optimizing way is trigger the search event after initial 3 characters, otherwise if users enter “a” may yield all the entries in the data.
Another feature that can be implemented is use of regular expression with wild characters in the search string, and multiple word search. For example, if users enters Flat Round and expecting Rounded Flat Bars, Flatten Rounded Coil in the results along with Flat Round Sheets, then in the find search input shall be of “*Flat*Round*” and “*Round*Flat*”. This is one simple example of using wild characters. Usage of escape sequencing will give more possible implementations.