Combobox auto complete

[Yogi]

I have a worksheet named, "Database2". There are hundreds of names in colum B starting in row 3 of this worksheet.
In the same Workbook is a worksheet named, "Records". I want to create a userform with a combo box, an "Enter" and "Cancel" command button. When typing begins in the combo box I need it to recognize the name from the "Database2" list in colum B and executes auto complete. When the "Enter" button is clicked I need it to enter the name in the first available row in colum B starting in row 3 in the Worksheet named "Records".
Any help is greatly apreciated.
Thanks

 

[Andrew Poulsom]

Try adding:

<pre>
.MatchEntry = fmMatchEntryComplete
</pre>

in your With...End With clause. Or set the property manually.

 

[Yogi]

I tried adding
.MatchEntry = fmMatchEntryComplete
but it still did not perform the function. I'm not quiet sure how to set this property manually, but willing to learn.
Thanks

 

[bobm]

Yogi

This property can be set on the combobox's property page(manually).
I currently have a post on a similar topic, maybe we can both get the answers we need.

Bob

 

[bobm]

Yogi

There is probably nothing new here(it is more or less a summary of previous posts), but your last post suggested you had not yet achieved success. Anyways, I know this works.

On your worksheet "Database2", select the list in column B and call it "Names".
Create a userform with three controls, a combobox(cboNames) and two buttons(cmdEnter,cmdCancel).
Do not change any properties for the combobox except for the name.
In the code module for the Userform, place the following code:

<pre>Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdEnter_Click()
Dim lngRows As Long
Dim rngNextEmpty As Range
lngRows = Worksheets("Records").Rows.Count
Set rngNextEmpty = Worksheets("Records").Cells(lngRows, 2).End(xlUp).Offset(1, 0)
rngNextEmpty.Value = cboNames.Value
cboNames.SetFocus
End Sub

Private Sub UserForm_Initialize()
With cboNames
.Style = fmStyleDropDownList
.MatchEntry = fmMatchEntryComplete
.EnterFieldBehavior = fmEnterFieldBehaviorSelectAll
.RowSource = "Database2!Names"
.ListIndex = 0
End With
End Sub</pre>

If you want the form to be available when you move to the sheet named "Records", place this code in the code module for the sheet named "Records".
<pre>Private Sub Worksheet_Activate()
UserForm1.Show vbModeless
End Sub</pre>

Bob

 

[Yogi]

Bob,
It worked!
Thanks a million.
Yog

 

[Yogi]

The formula offered by bobm worked great. Is there a way if "Enter" is pressed on the keyboard while the cursor is in the combobox that the value would be entered the same as clicking the "Enter" button?
Thanks

 

[bobm]

Yogi

On the property pages for the three controls, make certain that these properties are set:

cboNames
Tab Index = 0

cmdEnter
Tab Index = 1
Default = True

cmdCancel
Tab Index = 2
Cancel = True

Now, when the user presses the Enter key, the code in your Enter button will execute.
If the user presses the Esc key, the code in your Cancel button executes.

Bob
This message was edited by bobm on 2003-01-23 03:47

 

[Yogi]

bobm,
Perfect! Thanks again and again.
yog

 

[Yogi]

I have run into one minor problem. If the name isn't in the database2 it won't allow me to type the name. Is this something that can be overcome?
Thanks

 

[Jaafar Tribak]

Hi Yogi,

In Bob's UserForm Initialize code,change the following line from:

.Style = fmStyleDropDownList

To:

.Style = fmStyleDropDownCombo


Jaafar.