VBA 获取组合框以“建议”一个选项

VBA Get Combobox to 'suggest' an option

提问by user3142948

I am relatively new to VBA and I am trying to solve a problem working with a userform in Excel 2010.

我对 VBA 比较陌生，我正在尝试解决在 Excel 2010 中使用用户表单的问题。

I am writing a pseudo spell checker that validates words against a list. The unknown word is presented in a text box and the list of allowed words is in a combo box below. I would like the combo box to present a 'suggestion' based on the unknown word. i.e. the unknown word is "Excavation" and one of the allowed words in the combo box is "Excavate". I would like the combo box to suggest the term "Excavate". My problem is that autocomplete doesn't offer a suggestion because the unknown word is longer than the allowed word.

我正在编写一个伪拼写检查器，用于根据列表验证单词。未知单词显示在文本框中，允许的单词列表显示在下面的组合框中。我希望组合框根据未知单词提出“建议”。即未知词是“挖掘”，组合框中允许的词之一是“挖掘”。我希望组合框建议使用“挖掘”一词。我的问题是自动完成不提供建议，因为未知单词比允许的单词长。

My thought on solving the problem is to do the following: - Parse the unknown word into a character array. - Add the characters one at a time to the combo box text property and allow autocomplete to run. - As soon as autocomplete stops working, remove one character and insert the word that autocomplete suggests.

我解决问题的想法是执行以下操作： - 将未知单词解析为字符数组。- 一次将一个字符添加到组合框文本属性并允许自动完成运行。- 一旦自动完成停止工作，删除一个字符并插入自动完成建议的词。

My problem is I cannot find anything to tell me once auto complete has stopped working.

我的问题是，一旦自动完成停止工作，我就找不到任何可以告诉我的信息。

Any thoughts, suggestions, or alternate approaches welcome.

欢迎任何想法、建议或替代方法。

Thanks in advance,

提前致谢，

Will

将要

回答by PatricK

You may want to change 2 properties for the ComboBox to force an entry from a list is selected:

您可能希望更改 ComboBox 的 2 个属性以强制选择列表中的条目：

1. MatchEntry --> 1 - fmMatchEntryComplete
2. MatchRequired --> True

1. MatchEntry --> 1 - fmMatchEntryComplete
2. MatchRequired --> 真

So when a user try to select a word outside of the list, they get a "Invalid property value.":

因此，当用户尝试选择列表之外的单词时，他们会收到“无效的属性值”。：

回答by Doug Glancy

This code assumes a TextBoxand ComboBoxas you described, still with their default names. In addition there's a button, which when pressed prompts you for a word. This word is then pasted into the textbox, which I think duplicates the behavior you're coding for:

此代码假定 a TextBoxandComboBox正如您所描述的，仍然使用它们的默认名称。此外，还有一个按钮，按下时会提示您输入单词。然后将这个词粘贴到文本框中，我认为它复制了您正在编码的行为：

Private Sub UserForm_Activate()
With Me.ComboBox1
    .AddItem "bat"
    .AddItem "battleship"
    .AddItem "battle"
    .AddItem "batty"
    .AddItem "bathhouse"
End With
End Sub

Private Sub CommandButton1_Click()
Me.TextBox1 = Application.InputBox("Word", , , , , , , 2)

End Sub

Private Sub TextBox1_Change()
Dim WordToMatch As String
Dim AvailableWords() As String
Dim i As Long
Dim MatchedWordPosition As Long
Dim LongestWordLength As Long

With Me.ComboBox1
    .ListIndex = -1
    WordToMatch = Me.TextBox1.Text
    ReDim AvailableWords(0 To .ListCount - 1)
    For i = LBound(AvailableWords) To UBound(AvailableWords)
        AvailableWords(i) = .List(i)
        LongestWordLength = WorksheetFunction.Max(Len(.List(i)), LongestWordLength)
    Next i
    For i = 0 To Len(WordToMatch) - 1
        On Error Resume Next
        MatchedWordPosition = WorksheetFunction.Match(WordToMatch & WorksheetFunction.Rept("?", (LongestWordLength - Len(WordToMatch)) - i), AvailableWords(), 0)
        If MatchedWordPosition > 0 Then
            Exit For
        End If
    Next i
    If MatchedWordPosition > 0 Then
        .ListIndex = MatchedWordPosition - 1
    End If
End With
End Sub

I imagine there are a few ways to skin this cat, but this is my best effort.

我想有几种方法可以给这只猫剥皮，但这是我最大的努力。