what's a combo box code that allows AutoComplete when typing more than one letter

Question

Hi Guys,

I created a combo box with a drop down list and activated the match entry from first letter as i have a long drop down list. My problem is that i have multiple values that start with the same first letter. Is there a code that allows typing more than one letter in a combo box?

Please help me as this can save heaps of my time.

Thanks

Answer 1

I tried to paste the code but still didn't work, i'm not a VBA expert so i didn't know what to amend in the code before pasting.

what do i have to amend to the code and the combo box properties in order to be to search within the drop down list as i type in the combo box

the code i'm currently using is 

'==========================

Private Sub Worksheet_BeforeDoubleClick _

  (ByVal Target As Range, _

    Cancel As Boolean)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")

  On Error Resume Next

  With cboTemp

  'clear and hide the combo box

    .ListFillRange = ""

    .LinkedCell = ""

    .Visible = False

  End With

On Error GoTo errHandler

  If Target.Validation.Type = 3 Then

    'if the cell contains

      'a data validation list

    Cancel = True

    Application.EnableEvents = False

    'get the data validation formula

    str = Target.Validation.Formula1

    str = Right(str, Len(str) - 1)

    With cboTemp

      'show the combobox with the list

      .Visible = True

      .Left = Target.Left

      .Top = Target.Top

      .Width = Target.Width + 5

      .Height = Target.Height + 5

      .ListFillRange = str

      .LinkedCell = Target.Address

    End With

    cboTemp.Activate

    'open the drop down list automatically

    Me.TempCombo.DropDown

  End If

errHandler:

  Application.EnableEvents = True

  Exit Sub

End Sub

'=========================================

Private Sub TempCombo_LostFocus()

  With Me.TempCombo

    .Top = 10

    .Left = 10

    .Width = 0

    .ListFillRange = ""

    .LinkedCell = ""

    .Visible = False

    .Value = ""

  End With

End Sub

Private Sub TempCombo_KeyDown(ByVal _

     KeyCode As MSForms.ReturnInteger, _

     ByVal Shift As Integer)

  Select Case KeyCode

    Case 9 'Tab

      ActiveCell.Offset(0, 1).Activate

    Case 13 'Enter

      ActiveCell.Offset(1, 0).Activate

    Case Else

        'do nothing

  End Select

End Sub

'====================================

Answer 2

The MatchEntry property controls the behavior

https://docs.microsoft.com/en-us/office/vba/lan...

Set it to FmMatchEntryComplete

Andreas.

Answer 3

Hi Maya,

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

I haven't tried it, but you can look into below

https://stackoverflow.com/questions/52616236/vb...

Do let me know if you require any further help on this. Will be glad to help you.

Disclaimer - This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.