如何在 Excel 中輸入時自動完成文字框？

MS Excel 微軟技術計算機

我們可以使用 Excel 中的自動完成功能將文字框中的元素與列表中已存在的元素關聯起來。由於此過程無法預設完成，因此我們需要對工作簿進行一些更改才能實現自動完成。閱讀本教程，瞭解如何在 Excel 中輸入時自動完成文字框。

輸入時自動完成文字框

在這裡，我們將為文字框分配一個宏。讓我們來看一個簡單的過程，瞭解如何在 Excel 中輸入時自動完成文字框。我們需要使用註釋框並列出 ActiveX 命令。

步驟 1

讓我們考慮一個新的 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