Tabbing out of a ComboBox (Excel 2003 SP3)

[steveh]

Afternoon all

I have several Combos (Embedded, Control Type) and I would like the user to be able to tab out of them allto whatever the next cell is, I have found this snippet of code

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 9 Then
TextBox1.Activate
End If

End Sub

But it a does not do anything (even I make TextBox1) how can I make all Combo boxes allow the tab key to the next (unlocked) cell?

[HansV]

The code you mention is intended for a userform. Try changing it like this:

Code: Select all

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 9 Then
    Range("A4").Select
  End If
End Sub

Change A4 to the address of the cell you want to activate.

[steveh]

Thank you Hans

Perfecto

[ABabeNChrist]

Hi
I was just practicing with this code by adding additional cell options within same drop down. I tried a few different ways to achieve this, but it keeps going to cell C5 no matter what number selection is made.

Code: Select all

Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 5 Then
Range("B1").Select
Else
  End If
If KeyCode = 9 Then
Range("C5").Select
Else
  End If
If KeyCode = 11 Then
Range("D5").Select
Else
  End If
If KeyCode = 21 Then
Range("E5").Select
  End If
End Sub

[StuartR]

If Keycode=9 means "If the tab key was pressed"

Keycode = 5 would be created by typing control-E, but since this is checked in a "keydown" event I don't know if it is even possible to use key combinations like that.

Edited by StuartR to add
Here is a list of keycodes so you can check for other options. It shows that keycode 9 is HT, which stands for Horizontal Tab

[HansV]

KeyCode is not the value selected in the combo box, it is the code for the key pressed by the user. 9 is the code for the Tab key.
So you should only check for KeyCode = 9, and then select a cell depending on the value selected by the user:

Code: Select all

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 9 Then ' User pressed Tab
    Select Case Me.ComboBox1
      Case 5
        Range("B1").Select
      Case 9
        Range("C5").Select
      ' etc.
    End Select
  End If
End Sub

[ABabeNChrist]

Thank you HansV
Got it and it works good thanks