Microsoft Excel Auto Suggest

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.

Excel Auto Suggest Word VB

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.

Advertisements

About Paripurnachari

Finance Professional, Coder, Engineer
This entry was posted in Technology and tagged , , , , . Bookmark the permalink.

4 Responses to Microsoft Excel Auto Suggest

  1. Hi, I think your blog might be having browser compatibility issues. When I look at your blog site in Chrome, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, very good blog!

  2. Ranjith says:

    Really Interesting to know the capabilities of Excel .

  3. Pingback: NSE Live Stock (Share Price) Data into Excel – Part 2 | Thinking Through Excel A1

  4. Pingback: NSE Live Stock (Share Price) Data into Excel – Part 2 « ExploreAbout.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s