Macro allowing Tab key in ComboBox - requires extra keypress?

Hi all,

Having a small problem with a worksheet. I've added some code (found on this forum I think) to navigate in & out of a combo box using the Tab key. The path is from E5 -> C6 (which has the combo box on top of it) -> C7:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Allows navigation INTO combo boxes using Tab key

If Not Intersect(Range("C6"), Target) Is Nothing Then
    ActiveSheet.ComboBox1.Activate
End If

End Sub

Private Sub ComboBox1_KeyDown(ByVal Keycode As MSForms.ReturnInteger, ByVal shift As Integer)
'Allow navigation OUT using the Tab, Enter, arrow keys
    If Keycode = vbKeyUp Or Keycode = vbKeyLeft Or shift = 1 And Keycode = vbKeyTab Then
        Range("E5").Activate
    ElseIf Keycode = vbKeyTab Or Keycode = vbKeyRight Or Keycode = vbKeyReturn Or Keycode = vbKeyDown Then
        Range("C7").Select
    End If
End Sub

Problem: selection sticks on C7 for an extra keystroke. ie: if pushing TAB each time it goes like this: E5 -> combobox -> C7 -> C7 -> C8. Strangely, it doesn't stick anywhere when using Shift-Tab to go backwards.

Does anyone know why this happens / how to fix it?

Thanks in advance

No problem, thanks for your reply. Here is the file with all superfluous elements stripped.

I also noticed that if I tab from E5 -> C6 -> C7, and then immediately shift-tab, it skips C6 and jumps back to E5? It's like the underlying cell C6 remains selected when the combobox on top of it is activated.

Haha, agreed it's not ideal, but hey whatever works...

It's not a 100% solution because now whenever a combobox is selected with the mouse or via a shift-tab, the next tab keystroke skips straight over the adjacent cell. I might have to fiddle around and find the right combination of cell and control activation to prevent that... as if my code isn't ad-hoc enough already!

Cheers for your help, if you think of anything else let me know

After some playing around I've found the solution.

The problem was because the cell underlying the combobox was actually a merged cell range - C6:G6 etc. I still don't quite know why this is a problem, but I fixed it by un-merging the cells and locking D6:G6.

Now the only cell underlying the box is C6, and the tab key moves smoothly

I couldn't see a way to do what I wanted with validation dropdowns. The user has to be able to select an entry from the list, which automatically populates the corresponding 3-letter code, but the also has to be able to:
* type the first few letters and have it auto-complete
* modify the selected list entry without losing the 3-letter code they have selected, and
* type their own entry if they can't find it in the list.
Since the user has to be able to enter their own 3-letter codes, I couldn't have a formula in those cells either.

For your interest here is the code for one of the change events:

Private Sub ComboBox1_Change()

    'Search the list on the Data sheet for text matching the selected policy
    Set srcrow = Sheets("Data").Range("PolicyTypes").Find(what:=Me.ComboBox1.Value)
    
    'If policy name is found, get the 3 letter code next to the name
    If Not srcrow Is Nothing Then
        Range("PolicyCode_Label1").Value = srcrow.Offset(0, 1).Resize(1, 3).Value
        'Write the 3 letter code into a buffer variable
        Set code_buffer(1) = Sheets("Input").Range("PolicyCode_Label1")
        'Write the full policy name onto the label
        Sheets("Print").Range("C6").Value = ComboBox1.Value
    Else
        'If text in combobox changes & is not recognised, keep 3 letter code as the last correct value
        Range("PolicyCode_Label1").Value = code_buffer(1).Value
        Sheets("Print").Range("C6").Value = ComboBox1.Value
    End If
    
End Sub

I'm currently trying to make some of the boxes get their list from a dynamic named range ie " =OFFSET(Input!$J$3,1,0,COUNTA(Input!$J:$J)-1,1) " but that causes exceptions for some reason I haven't yet figured out.

Agreed, the activex controls kind of appeared & evolved over time which is why they're there, but it would probably be better to design from scratch with userforms.