Userform Combobox Behaviour

[Ark68]

I am struggling to find the right setting or combination of settings to reproduce this preferred behaviour of my combobox.

- I only want the user to be able to select values from this list, no typing in the value (current setting : Style - frmStyleDropDownList)
- I wish for the user to type the first letter, and instead of processing the change event based the first value in the list that matches that first letter, to move the user seletion to the first entry in the list that starts with that list allowing the user to click on whatever value in the list they choose. Difficult to explain, but...

My dropdown list containes over 250 items in alphabetical order. 20 of those begin withthe letter 'S'. Although the user could scroll through all 250 list items, I would like to be able to press 'S' and it takes me to the point in the list where the 'S' values start. As I have it set now, as soon as the user presses 'S' it assumes the first value in the list starting with 'S' and triggers the change code. This is not necessarily the value the user wishes to have selected.

 

[DanteAmor]

Try this option, the data in sheet1 starting in cell A1.

But you must change Style to "fmStyleDropdownCombo". Because when entering an "s" the "s" does not exist in the data, so you must allow any.

Option Explicit
Private IsArrow As Boolean

Private Sub ComboBox1_Change()
  Dim i As Long
  With Me.ComboBox1
    If Not IsArrow Then .List = Sheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0).Value
    If .ListIndex = -1 And Val(Len(.Text)) Then
      For i = .ListCount - 1 To 0 Step -1
        If LCase(Left(.List(i), Len(.Text))) <> LCase(.Text) Then .RemoveItem i
      Next i
      .DropDown
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Sheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0).Value
  ComboBox1.MatchEntry = fmMatchEntryNone
End Sub

 

[Ark68]

That works amazingly! Thank you Dante!
I have lots of work ahead of me transitioning all my comboboxes to this method.

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.