combo box list shortcut

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 ?
 
D

Dave Peterson

There's a MatchEntry property for the combobox.

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

Roy Gudgeon

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.
 
D

Dave Peterson

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top