VBA/Excel Combobox : Disable Auto-Complete

Question

I’m typing a text that’s in a Combobox of VBA/Excel, but the initial part of the text matches another already included in the list, besides completing it automatically (which I do not want), it changes the position of the list (the index).

Is there any way to stop it? For while typing it does not bring another text that matches?

Example: in the list of Combobox there are the colors YELLOW, BLUE and RED and I will include "GREEN", when starting to type, it automatically completes to "RED", since the initials coincide, however, if I correct and type GREEN, it has already changed the index (Combobox.Topindex) to the position of RED (will replace one with the other, which also do not want).

If RED is capitalized and I want to write in lower case, it always brings in upper case.

Has to lock this auto-complement?

Can someone help me?

Answer 1

There is, for this we will create a temporary Combobox to interact, because, we need an editable component for this and the List is not editable!

Get to work!

1. Create any list with some options in Upper Case and Lower Case (which is your case).
2. Now, open the Developer tab (if not available, open the File/Options/Customize Ribbon Menu). Enter in any location a Activex Combobox Control and change your name for Comboboxtemp.
3. Still in the Developer tab, click on the Visual Basic button of a double click on the sheet where the component and the list are (attention, it is important to be in the right place) and add (copy and paste) this code!

Option Explicit


Private Sub ComboBoxTemp_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)

    'Ocultar caixa de combinação e mover a próxima célula com Enter e Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'Nada
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet

    Set ws = ActiveSheet
    Set wsList = Sheets(Me.Name)
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Application.CutCopyMode Then
      'Permite copiar e colar na planilha
      GoTo errHandler
    End If

    Set cboTemp = ws.OLEObjects("ComboBoxTemp")
      On Error Resume Next
      With cboTemp
        .Top = 10
        .Left = 10
        .Width = 0
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With

    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = str
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate

        'Abrir a lista suspensa automaticamente
        Me.ComboBoxTemp.DropDown
        Me.ComboBoxTemp.MatchEntry = fmMatchEntryNone 'Aqui faz acontecer o que você deseja
        Me.ComboBoxTemp.AutoWordSelect = False
        End If

errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

End Sub

Close Visual Basic, where the List was created will be replaced by Combobox!

Run the tests, await feedback!

Answer 2

In the VBA of Excel these properties give the expected result when "set" like this:

     MatchEntry = fmMatchEntryNone 

     AutoWordSelect = False