Results 1 to 5 of 5

autocomplete in combo box

This is a discussion on autocomplete in combo box within the Excel Questions forums, part of the Question Forums category; I read an article here: http://www.contextures.com/xlDataVal14.html Don't bother to read it. Just download the sample file available at the bottom ...

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    16

    Default autocomplete in combo box

    I read an article here:
    http://www.contextures.com/xlDataVal14.html

    Don't bother to read it. Just download the sample file available at the bottom of that page. (Here it is: http://www.contextures.com/DataValComboboxClick.zip )

    I noticed that the Auto-complete feature works only if one clicks in the combo box. Is there a way to make this a keyboard only affair?

    Thanks.

  2. #2
    Board Regular
    Join Date
    Sep 2008
    Location
    Chicago area
    Posts
    139

    Default Re: autocomplete in combo box Contextures

    Hi iRounak - or maybe I should address you as uRounak?

    I had run across this the other day on Contextures, but was put off by the doubleclicking. I'm not sure I even downloaded it.

    However, I did tonight. Weird thing is, it seems to me that
    http://www.contextures.com/xlDataVal10.html
    http://www.contextures.com/DataValCombobox.zip
    and
    http://www.contextures.com/xlDataVal14.html
    http://www.contextures.com/DataValComboboxClick.zip
    are backwards.

    DataValCombobox.zip has
    Code:
    Private Sub TempCombo_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
    ...
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ...
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ...
    in it and

    DataValComboboxClick.zip has only
    Code:
    Private Sub TempCombo_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
    ...        
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ...
    As I have worked with DataValComboboxClick.zip, my experience is that it works quite well by keyboard alone or with the mouse. If I cursor to a cell, it goes into entry mode, I can up and down arrow to select values, or use the autocomplete, then return or tab out of the cell. No clicking required.

    (admittedly, the machine I'm on has Excel 2002, and I've not had a chance to test this on 2003 or 2007 yet)


    However, I also experienced a problem when I would use the enter key on the last row in the cells with validation. Excel would crash. Every time. I stepped through the code, fiddled with error handling, nothing would prevent the crash. I did find that modifying this part solved my problem:
    Code:
    Private Sub TempCombo_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
            Debug.Print KeyCode
        'Hide combo box and move to next cell on Enter and Tab
        Select Case KeyCode
            Case 9, 13, 17
            'tab, enter, ctrl (ie ctrl enter) select the same cell
                ActiveCell.Offset(0, 0).Activate
            Case 37 ' left arrow - move left
                ActiveCell.Offset(0, -1).Activate
            Case 39 ' right arrow - move right
                ActiveCell.Offset(0, 1).Activate
            Case 46 ' Del (8 would be back) - clear cell
                ActiveCell.Value = ""
            Case Else
                'do nothing
        End Select
    End Sub
    Actually this solves a problem I had at work, which led me to Contextures to begin with.

    Thanks iRounak!
    - Thanks,
    - Frank

    Where would we be without
    Google "search terms" site:mrexcel.com ?
    (Excel 2007)

  3. #3
    New Member
    Join Date
    Aug 2009
    Posts
    16

    Default Re: autocomplete in combo box

    vow!! its cool!!
    Thanks, foverman.

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

    Default Re: autocomplete in combo box

    Here is a different approach I came up with some time ago - The code is more involved but offers much more flexibility .

    Workbook example.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    New Member
    Join Date
    Aug 2009
    Posts
    16

    Default Re: autocomplete in combo box

    Hi Jaafar,
    If i press "I" , i can see "India" in the drop-down list. However, I do not want to press the "down" key to select it. This is because pressing the down key involves moving the right hand and then bringing it back to the home row. I would prefer that "Tab" key (but not "Enter" key) enters the first item from the drop-down list in the desired cell. Can you make it happen?

    Thanks

    I don't know much VBA. Is there a way to use another key instead of the down arrow key

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