Combobox auto complete

Jaafar Tribak

Well-known Member

Joined

Dec 5, 2002

Messages

9,797

Office Version

1. 2016

Platform

1. Windows

• Jan 17, 2003

• #2

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.

 

Upvote 0

Y

Yogi

Board Regular

Joined

Oct 21, 2002

Messages

74

• Jan 18, 2003

• #3

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

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 18, 2003

• #4

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.

 

Upvote 0

Y

Yogi

Board Regular

Joined

Oct 21, 2002

Messages

74

• Jan 18, 2003

• #5

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.

 

Upvote 0

Y

Yogi

Board Regular

Joined

Oct 21, 2002

Messages

74

• Jan 18, 2003

• #6

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.

 

Upvote 0

Jaafar Tribak

Well-known Member

Joined

Dec 5, 2002

Messages

9,797

Office Version

1. 2016

Platform

1. Windows

• Jan 18, 2003

• #7

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.

 

Upvote 0

Y

Yogi

Board Regular

Joined

Oct 21, 2002

Messages

74

• Jan 19, 2003

• #8

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

 

Upvote 0

Jaafar Tribak

Well-known Member

Joined

Dec 5, 2002

Messages

9,797

Office Version

1. 2016

Platform

1. Windows

• Jan 19, 2003

• #9

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

 

Upvote 0

Y

Yogi

Board Regular

Joined

Oct 21, 2002

Messages

74

• Jan 19, 2003

• #10

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

 

Upvote 0