自动完成只有文本而不是数字ComboBox Excel VBA [英] Auto-Complete with only text and not numbers ComboBox Excel VBA

问题描述

假设我在Excel VBA中有一个ComboBox，具有以下种类的信息：

  1234 apples 
 2345 pears 
 2367 oranges 
  

我希望用户能够输入a或ap '或'应用程序'等。所以自动完成将建议1234苹果。截至目前，如果用户键入条目的开始部分，即我的情况下，组合框将仅自动完成。谢谢

解决方案

首先给出这个答案的一些上下文。 SectorDropDown1_1（它是表单的一部分）是一个下拉列表，其中填充了数字值的串联字符串和来自列J的字符串值。在此上下文中_change（）方法响应用户键入值。在具有数字和文本分隔的电子表格SectorSearch中有2列R和S。所以现在如果用户输入数字或文本，则选择下拉列表中的适当值。但是这里是问题，我的代码尴尬地跳到了确切的值，所以我想平滑的事情，所以说，在用户键入超过2个字符匹配这个值现在选择的适当的值下拉列表显示附近的值。

  Option Explicit 
 
私人子SectorDropDown1_1_Change（）
 
 Dim i As Long 
 Dim StringRange1 As String 
 Dim StringRange2 As String 
 Dim Stringrange3 As String 
 Dim LengthOfValue As Integer 
 Dim TotalSectorCodes As Integer 
 
 If SectorDropDown1_1.Value<> 和Len（SectorDropDown1_1.Value）> 2然后
 TotalSectorCodes = Worksheets（SectorSearch）。Range（J：J）。Cells.SpecialCells（xlCellTypeFormulas）.Count 
 LengthOfValue = Len（SectorDropDown1_1.Value）
对于i = 2 To TotalSectorCodes 
 StringRange1 =R& CStr（i）
 StringRange2 =S& CStr（i）
 Stringrange3 =J& CStr（i）
选择案例SectorDropDown1_1.Value 
案例离开（Worksheets（SectorSearch）。Range（StringRange1）.Value，LengthOfValue）
 SectorDropDown1_1.Value = Worksheets（SectorSearch） .Range（Stringrange3）.Value 
退出
案例左（Worksheets（SectorSearch）。Range（StringRange2）.Value，LengthOfValue）
 SectorDropDown1_1.Value = Worksheets（SectorSearch） .Range（Stringrange3）.Value 
退出
案例Else 
'... 
结束选择
下一个i 
如果
 
 End Sub 
  

Suppose I have a ComboBox in Excel VBA with the following sorts of info:

1234 apples
2345 pears
2367 oranges

I want the user to be able to type 'a' or 'ap' or 'app' etc. So that auto-complete will suggest 1234 apples. As of right now the combo-box will only auto-complete if the user types the beginning part of the entry , i.e. in my case the numbers. Thanks

解决方案

So first to give some context to this answer. SectorDropDown1_1 (which is part of a form) is a drop down list that is populated with the concatenated strings of a number value and a string value coming from a column J. In this context the _change() method responds to the user typing in values. I have 2 columns "R", and "S", in a spreadsheet "SectorSearch" that have separated the number and the text. So now if the user enters either the number or the text, then the appropriate value in the Drop Down list is selected. But here is the problem, my code sort of awkwardly jumps to the exact value and so I want to "smooth things out" so to speak so that after the user types more than 2 characters that match the appropriate value that this value is now selected and the drop down list shows the nearby values.

Option Explicit

Private Sub SectorDropDown1_1_Change()

Dim i As Long
Dim StringRange1 As String
Dim StringRange2 As String
Dim Stringrange3 As String
Dim LengthOfValue As Integer
Dim TotalSectorCodes As Integer

If SectorDropDown1_1.Value <> "" And Len(SectorDropDown1_1.Value) > 2 Then
    TotalSectorCodes = Worksheets("SectorSearch").Range("J:J").Cells.SpecialCells(xlCellTypeFormulas).Count
    LengthOfValue = Len(SectorDropDown1_1.Value)
    For i = 2 To TotalSectorCodes
        StringRange1 = "R" & CStr(i)
        StringRange2 = "S" & CStr(i)
        Stringrange3 = "J" & CStr(i)
        Select Case SectorDropDown1_1.Value
            Case Left(Worksheets("SectorSearch").Range(StringRange1).Value, LengthOfValue)
                SectorDropDown1_1.Value = Worksheets("SectorSearch").Range(Stringrange3).Value
                Exit For
            Case Left(Worksheets("SectorSearch").Range(StringRange2).Value, LengthOfValue)
                SectorDropDown1_1.Value = Worksheets("SectorSearch").Range(Stringrange3).Value
                Exit For
            Case Else
                '...
        End Select
    Next i
End If

End Sub

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