How to autocomplete a textbox when typing in Excel?
By default, Excel can remember what you have entered in cells of current worksheet and autocomplete this content next time when typing a related initial letter into a new cell. However, if you want to make all contents you have entered in worksheet to be autocomplete in a textbox (ActiveX Control), how could you do? This article will provide a VBA method to help you autocomplete a textbox when typing an initial letter inside.
Autocomplete a textbox when typing with VBA code
Autocomplete a textbox when typing with VBA code
Please do as follows to make a textbox autocomplete when typing an initial letter inside the textbox.
1. Please insert a textbox by clicking Developer > Insert > Text Box (ActiveX Control). See screenshot:
2. And then click Developer > Insert > List Box (ActiveX Control) to insert a list box into current worksheet. See screenshot:
3. Right-click the sheet tab, then click View Code from the context menu as below screenshot shown.
4. In the Microsoft Visual Basic for Applications window, please copy and paste below VBA code into the Code window. And then click Tools > References, and then check the Microsoft Scripting Runtime box in the References – VBAProject dialog box. See screenshot:
VBA code: Autocomplete a textbox when typing
Dim xRg As Range
Dim xDic As New Dictionary
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Me.ListBox1.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xVal As String
On Error Resume Next
If IsNumeric(Target.Value) Then
xVal = Str(Target.Value)
Else
xVal = Target.Value
End If
If xVal <> "" Then
If Not xDic.Exists(xVal) Then
xDic.Add xVal, xVal
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ListBox1.Visible = False
End Sub
Private Sub Worksheet_Activate()
Dim I As Long
Dim xStr As String
On Error Resume Next
If xRg Is Nothing Then
Set xRg = ActiveSheet.UsedRange
End If
Me.ListBox1.Visible = False
xDic.RemoveAll
With Me.ListBox1
For I = 1 To xRg.Count
xStr = xRg(I).Value
If xStr <> "" Then
.AddItem xStr
If Not xDic.Exists(xStr) Then
xDic.Add xStr, xStr
End If
End If
Next
End With
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With Me.ListBox1
.Top = Me.TextBox1.Top
.Left = Me.TextBox1.Left + Me.TextBox1.Width
.Width = Me.TextBox1.Width
End With
TextBoxVal Me.TextBox1.Object
End Sub
Sub TextBoxVal(xTextBox As Variant)
Dim I As Long
Dim xStr As String
On Error Resume Next
Application.ScreenUpdating = False
If xRg Is Nothing Then Exit Sub
Me.ListBox1.Clear
xStr = xTextBox.Value
If xStr = "" Then
Me.ListBox1.Visible = False
Application.EnableEvents = True
Exit Sub
End If
For I = 0 To UBound(xDic.Items)
If Left(xDic.Items(I), Len(xStr)) = xStr Then
Me.ListBox1.AddItem xDic.Items(I)
End If
Next
Me.ListBox1.Visible = True
If Me.ListBox1.ListCount > 0 Then
With xTextBox
.Value = Me.ListBox1.List(0)
.SelStart = Len(xStr)
.SelLength = Len(Me.ListBox1.List(0))
End With
End If
Me.ListBox1.Activate
Me.ListBox1.Selected(0) = True
Application.ScreenUpdating = True
End Sub
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Me.TextBox1.Value = Me.ListBox1.Value
End If
End Sub
Note: In the code, ListBox1 and Textbox1 are the name of the list box and textbox you have inserted into your worksheet.
5. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.
6. Turn off the Design Mode by clicking Developer > Design Mode in the worksheet.
7. Now shift to another worksheet and then go back to the previous worksheet to enable the VBA code.
From now on. When entering an initial letter into the textbox, all texts which begin with that letter you have entered into the worksheet will be listed inside the list box which located on the right side of the textbox. Please double click your needed one to enter it into the textbox. See screenshot:
Note: You can use the Up or Down arrow key to move among all autocomplete texts in the list box, and then press the Enter key to enter needed one into the textbox.
Related articles:
- How to auto select text of a textbox when it is selected in Excel?
- How to clear contents of textbox when clicked in Excel?
- How to concatenate texts of multiple cells into a textbox in Excel?
- How to disable editing in textbox to prevent user inputting in Excel?
- How to format a textbox as a percentage in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
