combo box list shortcut

  • Thread starter Thread starter Roy Gudgeon
  • Start date Start date
R

Roy Gudgeon

Hi

I have a combo box on a VBA data entry form. One of the validated lists is
approx 100 names long and is a pain scrolling through. Is it possible to
maybe enter the first letter of the name and bring up only those beginning
with that letter ?
 
There's a MatchEntry property for the combobox.

Try changing it to fmMatchEntryFirstLetter (either in code or via the properties
window.
 
Hi Dave

thanks for prompt reply (as always !)

This suggestion helps but I am finding that it simply finds the first name
in the "t's" for example when I may want the 4th or 10th etc....

Is it possible to type a single letter and have the combo box to display
just names beginning with that letter (eg T) and then allow you to select the
correct name?
--
thanks
Roy


Dave Peterson said:
There's a MatchEntry property for the combobox.

Try changing it to fmMatchEntryFirstLetter (either in code or via the properties
window.
Click to expand...
 
You may want to try ".MatchEntry = fmMatchEntryComplete". Then you can just
keep typing the entry.

The only way I know to do what you want is to have two comboboxes. One for the
first letter and one that is populated only after that choice is made in the
first combobox.

Something like:

Option Explicit
Private Sub ComboBox1_Change()
Dim myRng As Range
Dim myCell As Range

If Me.ComboBox1.ListIndex < 0 Then
'nothing chosen
Beep
Exit Sub
End If

With Worksheets("Sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ComboBox2
.Clear
For Each myCell In myRng.Cells
If UCase(Left(myCell.Value, 1)) = UCase(Me.ComboBox1.Value) Then
.AddItem myCell.Value
End If
Next myCell

If .ListCount > 0 Then
.Enabled = True
Else
'no matches
.Enabled = False
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim lCtr As Long
With Me.ComboBox1
For lCtr = Asc("A") To Asc("Z")
.AddItem Chr(lCtr)
Next lCtr
End With

With Me.ComboBox2
.Enabled = False
.MatchEntry = fmMatchEntryComplete
End With
End Sub

Roy said:
Hi Dave

thanks for prompt reply (as always !)

This suggestion helps but I am finding that it simply finds the first name
in the "t's" for example when I may want the 4th or 10th etc....

Is it possible to type a single letter and have the combo box to display
just names beginning with that letter (eg T) and then allow you to select the
correct name?
Click to expand...
 
Back
Top