vba excel VBA中的自动完成文本框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7961201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto complete text box in excel VBA
提问by Ank
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)
我正在创建一个 Excel 工作表，它将根据特定列中存在的文本自动完成文本。在尝试自己制作一个失败后，我在网上寻找可以修改并合并到我的程序中的示例代码。（且不抄袭）
I downloaded Workbook1.xls from http://www.ozgrid.com/forum/showthread.php?t=144438
我从http://www.ozgrid.com/forum/showthread.php?t=144438下载了 Workbook1.xls
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
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.
如果您注意到 RestOfCompletion = Mid(oRange.Cells(1, 1).AutoComplete(.Text), Len(.Text) + 1) 行，我想知道 AutoComplete 在这里做什么。它不是内置函数，也没有在任何地方定义。代码仍然运行良好。我很好奇。
Thanks
谢谢
回答by Daniel Tallentire
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.
.AutoComplete 是 Range 对象的一个函数 - 它基于将文本传递到工作表上其他地方存在的范围。
You can see the documentation on this function here: http://msdn.microsoft.com/en-us/library/bb209667(v=office.12).aspx
您可以在此处查看有关此功能的文档：http: //msdn.microsoft.com/en-us/library/bb209667(v=office.12).aspx
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.
myAutoComplete 函数根据范围处理自动完成数据的查找（如果存在），代码中的其他部分用于突出显示正确的文本部分。
