VBA/Excel Combobox: disable auto-complete

I am typing a text that is in a ComboBox of VBA/Excel, but the initial part of the text coincides with another already included in the list, besides IT auto-complete (which I do not want), it changes the position of the list (the index).

How do you lock this? So when typing it does not bring other text that matches?

Example: in the list of ComboBox there are the colors yellow, blue and red and I will include "green", when starting the type, it automatically completes to "red", since the initials match, however, if I correct and type green, it has already changed the index (ComboBox.TopIndex) to the position of the Red (will replace one with the other, which I also do not want).

If RED is uppercase and I want to write in lowercase, it always brings in uppercase.

How to lock this auto add-on?

Can anyone help me?

Author: Leo, 2015-11-24
Source

2 answers

Yes, 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 uppercase and lowercase options (which is your case).
  2. now open the Developer tab (if it is not available, open the File Menu / options / Customize Ribbon). Enter in any location a control ActiveX ComboBox 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 located (attention, it is important to be in the correct place) and add (copy and cole) 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!

Do the tests, wait for the feedback!

 7
Author: Junior Moreira, 2015-11-25 09:53:55

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

     MatchEntry = fmMatchEntryNone 

     AutoWordSelect = False
 0
Author: Leo, 2016-07-02 22:43:25