Creating autocomplete search forms | by Jesko Rehberg | May, 2022
Tkinter vs Excel VBA
User-friendly search masks are almost indispensable in today’s flood of data. In this article we will see how to develop a responsive search for translation purposes using Tkinter and compare it with VBA.
Background:
Our table consists of two columns. The first column contains English terms and the second column the corresponding German translations.
We want to provide the user with a user-friendly input mask for searching translations. Both languages are to be searched. For fun, we will create this once in Excel VBA and then match it with Python’s Tkinter. This way we will once again realize why Python is such a great programming language. Joking aside, this is mainly for those of you coming from VBA and now want to learn Python. I hope this will help you understand the basic concepts more easily.
Solution:
The searched word is immediately listed in the hit list together with the corresponding translation. With each additional letter entered in the search field, the hit list is narrowed down accordingly.
To realize this in Excel VBA, a whole series of different steps are necessary. I will only explain the most important aspects, which we also compare with Tkinter. But you can find the complete file and code in my Github repository if you want to study the code in full detail.
The Excel form is separated into an user input called “SearchText”:
While the results are displayed in “ListBoxTerms”:
If your search was successful, you can double-click on each hit to transfer the translation to the SearchSelect tab:
Private Sub ListBoxTerms_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBoxTerms.ListIndex = -1 Then
MsgBox "No dataset selected"
Exit Sub
End If
Dim wks As Worksheet
Set wks = Worksheets("SearchSelect")
With Me.ListBoxTerms
wks.Range("F11:F12").ClearContents
wks.Range("F12").Value = .List(.ListIndex, 0)
wks.Range("F11").Value = .List(.ListIndex, 1)
End With
Unload Me
End Sub
If no search has been entered, the list is filled with the range A1 to B1000 by default (the data comes from the “Standards” tab):
Sub ListFill()
Dim SD As Worksheet
Set SD = Sheets("Standards")
Dim LastRow%
LastRow = Sheets("Standards").Range("A1000").End(xlUp).Row + 1
ListBoxTerms.RowSource = "Standards!A1:B" & LastRow
End Sub
The display of the filtered list according to the most recent string search might be worth looking at a little more closely. We use uppercase (UCase) to ensure that the search is not case sensitive. Then we compare each row in the first column (containing the English words) from the Standards tab to see if it is contained in the string (InStr) of the searched text (SearchText). If it is (>0), then the word from the Standards tab is displayed in the ListBoxTerms (the list box that displays the matches corresponding to the user’s current search). This English word is displayed in the ListBoxTerms using VBA`s AddItem. The same is true for the second column of the Standards tab, which contains the German translation (SD.Cells(i, 2)). This German word is inserted into the ListBoxTerms with the help of VBA`s ListCount function.
Sub ListFillFilter()
If SearchText = "" Then
Call ListFill
Exit Sub
End If
Dim SD As Worksheet
Set SD = Sheets("Standards")
Dim LastRow%
Dim i%
LastRow = Sheets("Standards").Range("A1000").End(xlUp).Row + 1
ListBoxTerms.RowSource = ""
i = 1
Do
If InStr(UCase(SD.Cells(i, 1)), UCase(SearchText)) > 0 Or InStr(UCase(SD.Cells(i, 2)), UCase(SearchText)) > 0 Then
ListBoxTerms.AddItem SD.Cells(i, 1)
ListBoxTerms.List(ListBoxTerms.ListCount - 1, 1) = SD.Cells(i, 2)
End If
i = i + 1
Loop Until i > LastRow
End Sub
With the initialization function of Excel VBA, the list form is filled whenever the file is opened:
Private Sub SearchText_Change()
Call ListFillFilter
End Sub
Second, using VBA’s change function, we call the ListFillFilter each time an input is entered into the search form:
Private Sub UserForm_Initialize()
Call ListFill
End Sub
This is the way to set up a search form in Excel.
Now let’s see the complete solution to this problem using Tkinter in Python.
The aim of this article is not to argue about which solution is the better option. But it might be interesting for you to compare VBA with Python, especially for the three most important functions in this example. Does the Python code look slimmer to you as well (or is it just my different screenshot technique that distorts this impression)?
At least we can clearly see from the ListFillFilter above that Python makes the comparison more convenient. The looping is less effortful in Python because we don’t have to count to the end of the translation dictionary.
Summary:
Congratulations, you have programmed a dynamic search that makes data entry easier for users. In this example, we have only searched for matches of strings, which is a very simple search function. But for our translation requirement, this is already sufficient. In another post, we will compare this “string matching approach” against a highly scalable cognitive search (small teaser at this point: Haystack and Elasticsearch are the magic words). Until then, thanks for reading! You can find the full code and files in my Github repository. Feel free to connect with me on LinkedIn, Twitter or Workrooms.
Tkinter vs Excel VBA
User-friendly search masks are almost indispensable in today’s flood of data. In this article we will see how to develop a responsive search for translation purposes using Tkinter and compare it with VBA.
Background:
Our table consists of two columns. The first column contains English terms and the second column the corresponding German translations.
We want to provide the user with a user-friendly input mask for searching translations. Both languages are to be searched. For fun, we will create this once in Excel VBA and then match it with Python’s Tkinter. This way we will once again realize why Python is such a great programming language. Joking aside, this is mainly for those of you coming from VBA and now want to learn Python. I hope this will help you understand the basic concepts more easily.
Solution:
The searched word is immediately listed in the hit list together with the corresponding translation. With each additional letter entered in the search field, the hit list is narrowed down accordingly.
To realize this in Excel VBA, a whole series of different steps are necessary. I will only explain the most important aspects, which we also compare with Tkinter. But you can find the complete file and code in my Github repository if you want to study the code in full detail.
The Excel form is separated into an user input called “SearchText”:
While the results are displayed in “ListBoxTerms”:
If your search was successful, you can double-click on each hit to transfer the translation to the SearchSelect tab:
Private Sub ListBoxTerms_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBoxTerms.ListIndex = -1 Then
MsgBox "No dataset selected"
Exit Sub
End If
Dim wks As Worksheet
Set wks = Worksheets("SearchSelect")
With Me.ListBoxTerms
wks.Range("F11:F12").ClearContents
wks.Range("F12").Value = .List(.ListIndex, 0)
wks.Range("F11").Value = .List(.ListIndex, 1)
End With
Unload Me
End Sub
If no search has been entered, the list is filled with the range A1 to B1000 by default (the data comes from the “Standards” tab):
Sub ListFill()
Dim SD As Worksheet
Set SD = Sheets("Standards")
Dim LastRow%
LastRow = Sheets("Standards").Range("A1000").End(xlUp).Row + 1
ListBoxTerms.RowSource = "Standards!A1:B" & LastRow
End Sub
The display of the filtered list according to the most recent string search might be worth looking at a little more closely. We use uppercase (UCase) to ensure that the search is not case sensitive. Then we compare each row in the first column (containing the English words) from the Standards tab to see if it is contained in the string (InStr) of the searched text (SearchText). If it is (>0), then the word from the Standards tab is displayed in the ListBoxTerms (the list box that displays the matches corresponding to the user’s current search). This English word is displayed in the ListBoxTerms using VBA`s AddItem. The same is true for the second column of the Standards tab, which contains the German translation (SD.Cells(i, 2)). This German word is inserted into the ListBoxTerms with the help of VBA`s ListCount function.
Sub ListFillFilter()
If SearchText = "" Then
Call ListFill
Exit Sub
End If
Dim SD As Worksheet
Set SD = Sheets("Standards")
Dim LastRow%
Dim i%
LastRow = Sheets("Standards").Range("A1000").End(xlUp).Row + 1
ListBoxTerms.RowSource = ""
i = 1
Do
If InStr(UCase(SD.Cells(i, 1)), UCase(SearchText)) > 0 Or InStr(UCase(SD.Cells(i, 2)), UCase(SearchText)) > 0 Then
ListBoxTerms.AddItem SD.Cells(i, 1)
ListBoxTerms.List(ListBoxTerms.ListCount - 1, 1) = SD.Cells(i, 2)
End If
i = i + 1
Loop Until i > LastRow
End Sub
With the initialization function of Excel VBA, the list form is filled whenever the file is opened:
Private Sub SearchText_Change()
Call ListFillFilter
End Sub
Second, using VBA’s change function, we call the ListFillFilter each time an input is entered into the search form:
Private Sub UserForm_Initialize()
Call ListFill
End Sub
This is the way to set up a search form in Excel.
Now let’s see the complete solution to this problem using Tkinter in Python.
The aim of this article is not to argue about which solution is the better option. But it might be interesting for you to compare VBA with Python, especially for the three most important functions in this example. Does the Python code look slimmer to you as well (or is it just my different screenshot technique that distorts this impression)?
At least we can clearly see from the ListFillFilter above that Python makes the comparison more convenient. The looping is less effortful in Python because we don’t have to count to the end of the translation dictionary.
Summary:
Congratulations, you have programmed a dynamic search that makes data entry easier for users. In this example, we have only searched for matches of strings, which is a very simple search function. But for our translation requirement, this is already sufficient. In another post, we will compare this “string matching approach” against a highly scalable cognitive search (small teaser at this point: Haystack and Elasticsearch are the magic words). Until then, thanks for reading! You can find the full code and files in my Github repository. Feel free to connect with me on LinkedIn, Twitter or Workrooms.
Denial of responsibility! Techno Blender is an automatic aggregator of the all world’s media. In each content, the hyperlink to the primary source is specified. All trademarks belong to their rightful owners, all materials to their authors. If you are the owner of the content and do not want us to publish your materials, please contact us by email – [email protected]. The content will be deleted within 24 hours.