Userform Combobox Behaviour

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,772
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That works amazingly! Thank you Dante!
I have lots of work ahead of me transitioning all my comboboxes to this method.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,772
Office Version
  1. 2007
Platform
  1. Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,144,528
Messages
5,724,854
Members
422,585
Latest member
k3n

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top