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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
