autocomplete in combo box

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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_[B]BeforeDouble[U]Click[/U][/B](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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top