Tabbing from Cell to ComboBox

Thanks for the quick reply Leith but I am not sure this is exactly what I need.

I have tried the code but doesn't do the trick. I have attached a sample of the workbook. It would open with the first input field already selected and the user would then tab through the fields (including the combobox) using the keyboard

When the user tabs from the input field 3, the focus will move to the combobox and the cursor would appear in the field ready for text entry.

TIA
Nicki

Hi Nicki,

not checked your sheet yet, but you need tabbed via various controls and even cells on sheets, right ? So I think about it a little bit and some solution could be like that:

Rewrite the default Excel's TAB handler, add events to controls and finally write tab loops.

Public tab_idx As Integer

Public Sub tab_handler()
    tab_idx = tab_idx + 1
    If tab_idx = 7 Then tab_idx = 0
    
    Select Case tab_idx
        Case 0
            Worksheets(1).Range("b2:c7").Select
        Case 1
            Call set_control_focus("ListBox1")
        Case 2
            Call set_control_focus("OptionButton1")
        Case 3
            Call set_control_focus("OptionButton2")
        Case 4
            Call set_control_focus("CheckBox1")
        Case 5
            Call set_control_focus("TextBox1")
        Case 6
            Call set_control_focus("CommandButton1")
    End Select
End Sub

Public Sub set_control_focus(ByVal objname As String)
    Dim obj As OLEObject
    On Error GoTo err
    Set obj = Worksheets(1).OLEObjects(objname)
        obj.Activate
    Exit Sub
err:
End Sub

Rewriting default TAB:

Private Sub Workbook_Open()
    tab_idx = 0
    Application.OnKey "{TAB}", "tab_handler"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "{TAB}", ""
End Sub

The wine definitely away ?

As I've seen inside your sheet it's needed to hold the default excel's TAB behaviour. So solution via OnKey method is forbidden now.

So something else. Here we check outgoing cells, in your case it's the celll above the combobox. Once the cell is left we focus the combobox.

Combobox has some key event too to focus next cell below.

module1::

Public last_adr As String

Public Sub set_control_focus(ByVal objname As String)
    Dim obj As OLEObject
    On Error GoTo err
    Set obj = Worksheets(1).OLEObjects(objname)
        obj.Activate
    Exit Sub
err:
End Sub

Workbook event::

Private Sub Workbook_Open()
    last_adr = ""
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If last_adr <> "" And last_adr = "$F$11" Then set_control_focus objname:="ComboBox1"
    last_adr = Target.Address
End Sub

ComboBox1 event::

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then Worksheets(1).Range("F15").Select
End Sub

Have fun !

Hi Nicki,

of course i can try within your test sheet. But what about the test sheet protection ? Any key ? Or post the sheet once again but with unprotected VB.