Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Combobox auto complete

This is a discussion on Combobox auto complete within the Excel Questions forums, part of the Question Forums category; I have a worksheet named, "Database2". There are hundreds of names in colum B starting in row 3 of this ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    74

    Default

    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

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Oct 2002
    Posts
    74

    Default

    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

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,336

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Posts
    74

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Oct 2002
    Posts
    74

    Default

    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.

  7. #7
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default

    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.

  8. #8
    Board Regular
    Join Date
    Oct 2002
    Posts
    74

    Default

    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 ]

  9. #9
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default

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

  10. #10
    Board Regular
    Join Date
    Oct 2002
    Posts
    74

    Default

    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

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com