如何在Excel中输入文本框时自动完成

我们可以在Excel中使用自动完成功能，将文本框中的元素与列表中已经存在的元素联系起来。由于这个过程在默认情况下无法完成，我们需要对工作簿做一些修改，以便使自动完成成为可能。阅读本教程，了解如何在Excel中输入时自动完成文本框。

输入时自动完成一个文本框

这里我们将为文本框分配一个宏。让我们看看一个简单的过程，以了解我们如何在Excel中输入时自动完成一个文本框。我们需要使用注释框并列出ActiveX命令。

第1步

让我们考虑一个新的Excel工作表，为了插入文本框和列表框，点击开发人员，然后点击插入，并画出框的边界，如下图所示。

如何在Excel中输入文本框时自动完成？

现在，右击工作表名称，选择查看代码，打开vba应用程序，并在文本框中输入程序，如下图所示。

代码

Dim xRg As Range
'Updated By Nirmal
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