Auto Completer for ComboBox in VBA Form

[prajul89]

Hi all,

What I want in a Combox is, when a user presses "A" all words in a list starting from "A" should appear in the DropDown List.
Similarly when he adds another alphabet to Combox say "b", all words in a list starting from "Ab" should appear in dropdow list.

This is what I have done till now.

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)        
    Dim varArray        As Variant
    Dim m_strUserList   As String
    Dim i               As Double
    Dim TheValue        As String
    Dim strSearchText   As String
    
    m_strUserList = "Afghanistan,Albania,Algeria,Andorra,Angola,Antigua and Barbuda,Argentina," & _
                    "Armenia,Australia,Austria,Azerbaijan,Top of Page,B,Bahamas, The,Bahrain,Bangladesh,Barbados,Belarus,Belgium,Belize,Benin,Bhutan,Bolivia,Bosnia and Herzegovina,Botswana,Brazil,Brunei ,Bulgaria,Burkina Faso,Burma,Burundi,Top of Page,C,Cambodia,Cameroon,Canada,Cape Verde,Central African Republic,Chad,Chile,China,Colombia,Comoros ,Congo, Democratic Republic of the,Congo, Republic of the ,Costa Rica,Cote d'Ivoire,Croatia,Cuba,Cyprus,Czech Republic,Top of Page,D,Denmark,Djibouti,Dominica,Dominican Republic,Top of Page,E,East Timor (see Timor-Leste),Ecuador,Egypt,El Salvador,Equatorial Guinea,Eritrea,Estonia,Ethiopia,Top of Page,F,Fiji,Finland,France,Top of Page,G,Gabon,Gambia, The,Georgia,Germany,Ghana,Greece,Grenada,Guatemala,Guinea,Guinea-Bissau,Guyana,Top of Page,H,Haiti,Holy See,Honduras,Hong Kong,Hungary,Top of Page,I,Iceland,India,Indonesia,Iran,Iraq,Ireland,Israel,Italy,Top of Page,J,Jamaica,***an,Jordan,Top of Page,K,Kazakhstan,Kenya," & _
                    "Kiribati,Korea, North,Korea, South,Kosovo,Kuwait,Kyrgyzstan,Top of Page,L,Laos,Latvia,Lebanon,Lesotho,Liberia,Libya,Liechtenstein,Lithuania,Luxembourg,Top of Page,M,Macau,Macedonia,Madagascar,Malawi,Malaysia,Maldives,Mali,Malta,Marshall Islands,Mauritania,Mauritius,Mexico,Micronesia,Moldova,Monaco,Mongolia,Montenegro,Morocco,Mozambique,Top of Page,N,Namibia,Nauru,Nepal,Netherlands,Netherlands Antilles,New Zealand,Nicaragua,Niger,Nigeria,North Korea,Norway,Top of Page,O,Oman,Top of Page,P,Pakistan,Palau,Palestinian Territories,Panama,Papua New Guinea,Paraguay,Peru,Philippines,Poland,Portugal,Top of Page,Q,Qatar,Top of Page,R,Romania,Russia,Rwanda,Top of Page,S,Saint Kitts and Nevis,Saint Lucia,Saint Vincent and the Grenadines,Samoa ,San Marino,Sao Tome and Principe,Saudi Arabia,Senegal,Serbia,Seychelles,Sierra Leone,Singapore,Slovakia,Slovenia,Solomon Islands,Somalia,South Africa,South Korea,South Sudan,Spain ,Sri Lanka,Sudan,Suriname,Swaziland," & _
                    "Sweden,Switzerland,Syria,Top of Page,T,Taiwan,Tajikistan,Tanzania,Thailand ,Timor-Leste,Togo,Tonga,Trinidad and Tobago,Tunisia,Turkey,Turkmenistan,Tuvalu,Top of Page,U,Uganda,Ukraine,United Arab Emirates,United Kingdom,Uruguay,Uzbekistan,Top of Page,V,Vanuatu,Venezuela,Vietnam,Top of Page,Y,Yemen,Top of Page,Z,Zambia,Zimbabwe"
    
    
    varArray = Split(m_strUserList, ",")
    
    
    With Me.ComboBox1
    
            TheValue = Me.TextBox1.Text
            
            .Clear
                      
            For i = 0 To UBound(varArray)
            
                strSearchText = Trim(varArray(i))
                
                If UCase(TheValue) = UCase(Left(Trim(strSearchText), Len(TheValue))) Then
                    
                    .AddItem (strSearchText)
                    
                End If
            
            Next i
            
            If TheValue <> "" Then
            
                Me.TextBox1.Text = TheValue
            
            End If
            
            .DropDown
            
            DoEvents
            
    End With


End Sub

 

[Andrew Poulsom]

The default behaviour for a sorted ComboBox is to jump to the first item that matches what the user types. Isn't that sufficient?

 

[prajul89]

I am dealing with really a huge List. hence that is not sufficient

 

[prajul89]

This code is quite close of the goal. The procedure works, but there is some visual effect problem.

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)    Dim varArray        As Variant
    Dim m_strUserList   As String
    Dim i               As Double
    Dim TheValue        As String
    Dim strSearchText   As String
    
    m_strUserList = "Afghanistan,Albania,Algeria,Andorra,Angola,Antigua and Barbuda,Argentina," & _
                    "Armenia,Australia,Austria,Azerbaijan,Top of Page,B,Bahamas, The,Bahrain,Bangladesh,Barbados,Belarus,Belgium,Belize,Benin,Bhutan,Bolivia,Bosnia and Herzegovina,Botswana,Brazil,Brunei ,Bulgaria,Burkina Faso,Burma,Burundi,Top of Page,C,Cambodia,Cameroon,Canada,Cape Verde,Central African Republic,Chad,Chile,China,Colombia,Comoros ,Congo, Democratic Republic of the,Congo, Republic of the ,Costa Rica,Cote d'Ivoire,Croatia,Cuba,Cyprus,Czech Republic,Top of Page,D,Denmark,Djibouti,Dominica,Dominican Republic,Top of Page,E,East Timor (see Timor-Leste),Ecuador,Egypt,El Salvador,Equatorial Guinea,Eritrea,Estonia,Ethiopia,Top of Page,F,Fiji,Finland,France,Top of Page,G,Gabon,Gambia, The,Georgia,Germany,Ghana,Greece,Grenada,Guatemala,Guinea,Guinea-Bissau,Guyana,Top of Page,H,Haiti,Holy See,Honduras,Hong Kong,Hungary,Top of Page,I,Iceland,India,Indonesia,Iran,Iraq,Ireland,Israel,Italy,Top of Page,J,Jamaica,***an,Jordan,Top of Page,K,Kazakhstan,Kenya," & _
                    "Kiribati,Korea, North,Korea, South,Kosovo,Kuwait,Kyrgyzstan,Top of Page,L,Laos,Latvia,Lebanon,Lesotho,Liberia,Libya,Liechtenstein,Lithuania,Luxembourg,Top of Page,M,Macau,Macedonia,Madagascar,Malawi,Malaysia,Maldives,Mali,Malta,Marshall Islands,Mauritania,Mauritius,Mexico,Micronesia,Moldova,Monaco,Mongolia,Montenegro,Morocco,Mozambique,Top of Page,N,Namibia,Nauru,Nepal,Netherlands,Netherlands Antilles,New Zealand,Nicaragua,Niger,Nigeria,North Korea,Norway,Top of Page,O,Oman,Top of Page,P,Pakistan,Palau,Palestinian Territories,Panama,Papua New Guinea,Paraguay,Peru,Philippines,Poland,Portugal,Top of Page,Q,Qatar,Top of Page,R,Romania,Russia,Rwanda,Top of Page,S,Saint Kitts and Nevis,Saint Lucia,Saint Vincent and the Grenadines,Samoa ,San Marino,Sao Tome and Principe,Saudi Arabia,Senegal,Serbia,Seychelles,Sierra Leone,Singapore,Slovakia,Slovenia,Solomon Islands,Somalia,South Africa,South Korea,South Sudan,Spain ,Sri Lanka,Sudan,Suriname,Swaziland," & _
                    "Sweden,Switzerland,Syria,Top of Page,T,Taiwan,Tajikistan,Tanzania,Thailand ,Timor-Leste,Togo,Tonga,Trinidad and Tobago,Tunisia,Turkey,Turkmenistan,Tuvalu,Top of Page,U,Uganda,Ukraine,United Arab Emirates,United Kingdom,Uruguay,Uzbekistan,Top of Page,V,Vanuatu,Venezuela,Vietnam,Top of Page,Y,Yemen,Top of Page,Z,Zambia,Zimbabwe"
    
    
    varArray = Split(m_strUserList, ",")
    
    
    With Me.ComboBox1
    
            TheValue = .Text
            
            .Clear
                      
            For i = 0 To UBound(varArray)
            
                strSearchText = Trim(varArray(i))
                
                If UCase(TheValue) = UCase(Left(Trim(strSearchText), Len(TheValue))) Then
                    
                    .AddItem (strSearchText)
                    
                End If
            
            Next i
            
            If TheValue <> "" Then
            
                .Text = TheValue
            
            End If
                                    
            .Style = fmStyleDropDownCombo
            
            .MatchEntry = fmMatchEntryNone
            
            .MatchRequired = False
                                    
            .DropDown
            
            DoEvents
            
    End With




End Sub