在Excel中自动完成文本框VBA [英] Auto complete text box in excel VBA

问题描述

我正在创建一个Excel表格，可以根据特定列中的文本自动完成文本。尝试使自己失败后，我正在网上查看示例代码，我可以修改并纳入我的程序。 （而不是剽窃）

I am creating a excel sheet that would autocomplete a text based on the text present in a particular column. After trying to make one myself unsuccessfully, I was looking online for sample codes that I could modify and incorporate in my program. (and not plagiarize)

我从 http://www.ozgrid.com/forum/showthread.php?t=144438

代码是

The code is

Option Explicit

Dim ufEventsDisabled As Boolean
Dim autoCompleteEnabled As Boolean
Dim oRange As Range

Private Sub TextBox1_Change()
    If ufEventsDisabled Then Exit Sub
    If autoCompleteEnabled Then Call myAutoComplete(TextBox1)
End Sub

Sub myAutoComplete(aTextBox As MSForms.TextBox)
    Dim RestOfCompletion As String
    On Error GoTo Halt
    With aTextBox
        If .SelStart + .SelLength = Len(.Text) Then
            RestOfCompletion = Mid(oRange.Cells(1, 1).AutoComplete(.Text), Len(.Text) + 1)
            ufEventsDisabled = True
            .Text = .Text & RestOfCompletion
            .SelStart = Len(.Text) - Len(RestOfCompletion)
            .SelLength = Len(RestOfCompletion)
        End If
    End With
Halt:
ufEventsDisabled = False
On Error GoTo 0
End Sub

Private Sub TextBox1_AfterUpdate()
    Dim strCompleted As String
    With TextBox1
        strCompleted = oRange.AutoComplete(.Text)
        If LCase(strCompleted) = LCase(.Text) Then
            ufEventsDisabled = True
            .Text = strCompleted
            ufEventsDisabled = False
        End If
    End With
End Sub

Private Sub TextBox1_Enter()
    Set oRange = ThisWorkbook.Sheets("Sheet1").Range("f4")
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    autoCompleteEnabled = KeyCode <> vbKeyBack
    autoCompleteEnabled = ((vbKey0 <= KeyCode) And (KeyCode <= vbKeyZ))
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

如果您注意到RestOfCompletion = Mid（oRange.Cells（1，1）.AutoComplete（.Text），Len（.Text）+ 1）的行，我想知道AutoComplete在这里做什么。它不是一个内置的功能，并没有在任何地方定义。仍然代码运行正常。我很好奇。

If you'd notice the line RestOfCompletion = Mid(oRange.Cells(1, 1).AutoComplete(.Text), Len(.Text) + 1), I was wondering what AutoComplete is doing here. Its not a in built function and is not defined anywhere. Still the code runs fine. I am very curious.

谢谢

推荐答案

.AutoComplete是Range的一个功能对象 - 它基于将文本传递到工作表上其他位置的范围。

The .AutoComplete is a function of the Range object - it is based on passing the text to a range that exists elsewhere on the sheet.

您可以在此处查看有关此功能的文档：
http://msdn.microsoft.com/en-us/ library / bb209667（v = office.12）.aspx

You can see the documentation on this function here: http://msdn.microsoft.com/en-us/library/bb209667(v=office.12).aspx

myAutoComplete函数根据范围处理自动填充数据的查找，如果存在，代码中的其他部分用于突出显示正确的文本。

The myAutoComplete function handles the finding of the autocomplete data against the range if it exists, and the other pieces in the code are for highlighting the correct piece of text.

这篇关于在Excel中自动完成文本框VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！