Tabbing from Cell to ComboBox

**barefaced66** · 10-19-2007, 03:10 PM

I am trying to create a excel based form (not User Form) which meets some our business accessibility standards (i.e. a user who cannot use a mouse, must be able to complete it using the keyboard).

I have got around the checkbox issue by creating faux checkboxes and asking the users to enter an X if appropriate. However, part of the information that they need to complete is in the form of a drop-down list (the information is being collected by a hidden datasheet for importing into Access and we need to control the format of the information being provided). I originally implemented this by using data validation on a cell and linking to a named range on a hidden sheet (there are over 100 options to select from). This made it easy for the user to tab to the cell with the data validation in it but, because of the length of the options list, it was a bit unwieldy and users were a bit frustrated by not being able to enter the first couple of letters and for the entry to auto-complete (maybe this is possible in a data validation created list but I couldn't seem to implement it). I have now created a combobox in place of the cell (from the Control Toolbox) and linked this to the name range - so far so good and I have autocomplete working. The trouble now is that I can't get the combobox to get focus without using the mouse to click it.

After writing "War and Peace", what I wanted to know is if I can do this by VBA? The trouble is that I am a good magpie and can kind of cut and paste code if I can find it but I am shocking when it comes to writing anything from scratch. My instinct tells me that I want to trigger some kind of code when the user tabs out of the cell before the combobox to activate and dropdown the combobox and then to be able to either tab or hit enter in order to give focus to the next cell in the form.

The sheet and workbook are protected with only the cells requiring completion being unlocked.

Hope someone can help. TIA
Nicki

**Leith Ross** · 10-19-2007, 06:19 PM

Hello Nicki,

This macro works with one Control Toolbox TextBox named "TextBox1" on the worksheet. The macro can be expanded to handle more TextBoxes if you need them. If you have any questions let me know.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim Obj As Object
  Dim ObjRng As Range
  
    Set Obj = ActiveSheet.OLEObjects("TextBox1")
      With Obj
       Set ObjRng = Range(.TopLeftCell, .BottomRightCell)
      End With
      
    If Not Intersect(Target, ObjRng) Is Nothing Then Obj.Activate
    
End Sub

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

**barefaced66** · 10-19-2007, 07:09 PM

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

**bettatronic** · 10-19-2007, 08:03 PM

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

**barefaced66** · 10-19-2007, 08:07 PM

Bettatronic - thanks I'll look at this tomorrow when the level of red wine decreases and it makes more sense!

**bettatronic** · 10-19-2007, 09:11 PM

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 !