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

 

[Jaafar Tribak]

Hi Yogi,

Paste the following codes in your userform class madule:

Private Sub CbCancel_Click()
Unload Me
End Sub

Private Sub CbEnter_Click()

RwsCwnt = Sheets("records").Rows.Count
Set FrstEmptCll = Sheets("records").Cells(RwsCwnt, 2).End(xlUp).Offset(1)

FrstEmptCll.Value = ComboBox1.Value

End Sub

Private Sub UserForm_Initialize()

Nms = Sheets("database2").Range(Cells(3, 2), Cells(3, 2).End(xlDown))

With ComboBox1

.SetFocus
.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
.List = Nms
.ListIndex = 0
End With

End Sub

Adjust the controls names to suit yours.

Hope this helps.

Jaafar.

 

[Yogi]

I created a userform for the workbook then inserted a class module in the userform. When I click the "Enter" button on the userform nothing happens. Any Ideas?
Thanks

 

[Andrew Poulsom]

Either:

1. Rename your "Enter" button as CbEnter, or

2. Change the CbEnter in this line:

Private Sub CbEnter_Click()

to the name of your "Enter" button.

 

[Yogi]

I made sure the names are correct and I get the same results - it doesn't do anything when I press "Enter".
Could it have something to do with where I'm pasting the formula. I inserted a class module in the userform and placed the formula there. I'm stumped.

 

[Yogi]

I found the problem. I messed up on my userform name. Now the only thing is it doesn't recognize the list in "database2" and perform autocomplete as a name already present is typed. For instance in the "database2" list in colum B is the name "Greg Heard". When I type "G-R-" it doesnt recognize this mane and display it in the combo box. Any ideas? Thanks for all of the help so far.

 

[Jaafar Tribak]

Hi Yogi,

Is this the only problem you have?
Is the combobox populated with all the names on the list?

If the answer is yes then it could be that there is another record on the list that starts with "GR" as well.Try entering more characters or try another nameand see what happens.

Hope this helps.

Jaafar.

 

[Yogi]

Jaafar,
This is the only problem. I only have one name beginning with "G".
Let me make sure, did the code go on the Userform code or do I insert a class module in the Userform and place the code there. Anyway this is the code you gave me:

Private Sub CbCancel_Click()
Unload Me
End Sub

Private Sub CbEnter_Click()

RwsCwnt = Sheets("records").Rows.Count
Set FrstEmptCll = Sheets("records").Cells(RwsCwnt, 2).End(xlUp).Offset(1)

FrstEmptCll.Value = ComboBox1.Value

End Sub

Private Sub UserForm1_Initialize()

Nms = Sheets("database2").Range(Cells(3, 2), Cells(3, 2).End(xlDown))

With ComboBox1

.SetFocus
.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
.List = Nms
.ListIndex = 0
End With

End Sub

Did I miss something?
Thanks
This message was edited by Yogi on 2003-01-19 00:19

 

[Jaafar Tribak]

No you don't insert a new class module.
You have to paste the code in the userform module.
Jaafar.

 

[Yogi]

I placed the formula in the userform code. The code enters the name but doesn't recognize the name and execute autocomplete if the name already exists in database2. Any other ideas?
Thanks