Combobox, when I hit enter (or Tab) i need excel to move to next cell

[Bravo2003]

I have hunted high and low for this one with out any solutions appearing?

I have a Combobox that auto completes as I type in it from the relevant list, but when I hit enter (or Tab) it doesn't change to the next cell and just stays highlighted in the combobox.


PLEASE HELP ME... driving me mad!

 

[ask2tsp]

Bravo2003 see if this works for you:
Right-click on the sheet tab and select 'view code' (not sure since I use the Dutch version)
Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
    'if target has table lookup validation
    If Target.Count = 1 And Target.Validation.Type = 3 Then
        Target.Offset(1).Select
    End If
End Sub

note: this always selects the cell below. I do not know how to act differently if the tab key is pressed. Anybody?

 

[Greg Truby]

Based on the fact that you've stated that your ComboBox is autocompleting, this tells me that (a) it really is a combobox control and not a case of data validation and (b) it is an ActiveX combobox control and not a forms combobox control. This means that what you want to do is possible. If you are in design mode and you right-click the control and pick view code from the popup menu, you will automatically hop to the worksheet's code module and you'll be put inside a _Change event handler stub for the combobox control.

At the top of the code window you will see a dropdown control with the word Change in it. Click the dropdown and select KeyDown. This will create a stub for the combobox's _KeyDown event handler. Make it look like so:

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)    
    If KeyCode = 9 Or KeyCode = 13 Then ActiveCell.Offset(1).Select
End Sub

Nix the _Change event handler stub and edit the ActiveCell.Offset(1) and add seasonings to taste.

 

[Greg Truby]

Note that if the workbook did not already have VBA code in it, you'll need to change the extension and save it as an .xlsm (macro-containing) workbook.

 

[jbiehl]

Hi All!
I'm having the same issue. When I try #3, I get the error below. Any advice? Thanks!

 

[jbiehl]

Solution: I replaced "ActiveCell.Offset(1).Select" with the actual name of the next combobox.Activate.

Private Sub CB_ST_GRA_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Or KeyCode = 13 Then CB_ST_SUB.Activate
End Sub