VBA / Excel Combobox: Disable Auto-Complete

Question

VBA / Excel Combobox: Disable Auto-Complete

Navigation

#1 by (7 votes)
#2 by (0 votes)

11

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

Can you handle this? When typing it does not bring other matching text?

Example: in the ComboBox list there are YELLOW, BLUE and RED colors and I will include "GREEN" when I start typing, it automatically completes to "RED", since the initials match, but if I correct and type GREEN, it already changed the index (ComboBox.TopIndex) to the RED position (it will replace one with the other, which I also do not want).

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

Can you block this automatic add-on?

Can anyone help me?

excel vba combobox

asked by anonymous 24.11.2015 / 22:27

2 answers

0

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

     MatchEntry = fmMatchEntryNone 

     AutoWordSelect = False

03.07.2016 / 00:43

How to sort an array by values? How to make a bubble chart, each filled in according to a ratio

score 7 · Accepted Answer

As a matter of fact, we're going to create a temporary ComboBox to interact with, so we need an editable component for this and the List is not editable!

Get to work!

Create any list with some uppercase and lowercase options (which is your case).

Now, open the Developer Tab (if it is not available, open the File Menu / Options / Customize the Ribbon). Enter in any place a ComboBox ActiveX Control and change its name for ComboBoxTemp .

In the Developer tab, click on the Visual Basic button, double click on the worksheet where the component and the list (attention, it's 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 the ComboBox!

Take the tests, I'm waiting for the feedback!