Using "Tab" to move from cell to adjacent Combobox

[GColeman]

I've looked this up and haven't found much luck on the subject, so I thought I'd try my luck here again.
The sheet I'm using has both data validation tables, and comboboxes. The comboboxes are all the same in configuration and properties. There are 50 of them, all in Column D. I went with Combobox because I wanted to predictive text feature for the list.
Everything works beautifully, almost. I can have the user select an item from the list, and move to the adjacent cell with the Tab button. But I need to start by having the Tab button move from Column C to the combobox in D, rather than the cell under the combobox. I also need it to activate the drop down list as soon as the user starts typing, thus the predictive text feature. (The selection must be an exact match to the list used for future functions). This is where I'm stumbling.
Here is what I'm using to exit the combobox.

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

Ideas? Modify it to select an object, rather than (0,1)?

 

[Domenic]

With the following SelectionChange event handler, whenever you tab or arrow to a cell located in Column D, starting at D2, the combobox within that cell gets activated, if one exists. Note that you'll need to place this code in the code module for the sheet containing your comboboxes (right-click the sheet tab, and select View Code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oOleObj As OLEObject
    If Target.Row > 1 And Target.Column = 4 Then
        For Each oOleObj In Me.OLEObjects
            If TypeName(oOleObj.Object) = "ComboBox" Then
                If oOleObj.TopLeftCell.Address = Target.Address Then
                    oOleObj.Activate
                    Exit Sub
                End If
            End If
        Next oOleObj
    End If
End Sub

Hope this helps!

 

[GColeman]

With the following SelectionChange event handler, whenever you tab or arrow to a cell located in Column D, starting at D2, the combobox within that cell gets activated, if one exists. Note that you'll need to place this code in the code module for the sheet containing your comboboxes (right-click the sheet tab, and select View Code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oOleObj As OLEObject
    If Target.Row > 1 And Target.Column = 4 Then
        For Each oOleObj In Me.OLEObjects
            If TypeName(oOleObj.Object) = "ComboBox" Then
                If oOleObj.TopLeftCell.Address = Target.Address Then
                    oOleObj.Activate
                    Exit Sub
                End If
            End If
        Next oOleObj
    End If
End Sub

Hope this helps!

I appreciate the quick reply!

Unfortunately it seems to do nothing in this case. I am just getting into VBA and trying my best to understand the codes I use line by line. I want to be self sufficient when it comes to writing my own stuff. The only part I didn't quite get in this was "If oOleObj.TopLeftCell.Address = Target.Address Then". Would that not be used more for a range or a chart? I'm not doubting it at all, just trying to follow along and keep up.

Any other suggestions?

 

[GColeman]

Also, the cells directly before and after contain drop down lists populated with data validation. I'm not sure if that makes a difference, but the more info you have the better.

 

[Domenic]

Unfortunately it seems to do nothing in this case.

It should activate the combobox, thereby positioning the cursor within the combobox. Is this not what happens?

The only part I didn't quite get in this was "If oOleObj.TopLeftCell.Address = Target.Address Then". Would that not be used more for a range or a chart?

TopLeftCell returns a Range object that refers to the cell that lies beneath the upper left corner of the object, which in this case is the combobox. So you'll need to make sure that the upper left corner of each combobox does not extend beyond the cell. Is this the case?

Also, the cells directly before and after contain drop down lists populated with data validation. I'm not sure if that makes a difference, but the more info you have the better.

It shouldn't make a difference.

 

[Domenic]

Also, did you place the code in the code module for the sheet? For example, let's say that Sheet1 contains your comboboxes, you would right-click the sheet tab for Sheet1, select View Code, and copy/paste the code into the code module for the sheet. Have you done so?

Also, just to make sure, you said that Column D contains your comboboxes, correct?

 

[GColeman]

I'm not sure exactly how to put a screenshot in here, it wont let me paste. I made certain that the combobox is within the borders of cell D4. Positioning my curser in C4 and tabbing over selects D4, not the combobox as intended. I am on tab "Jan", right click, View code, Insert Module, paste. I get no response or error when I close and try the tab. I also tried it outside the module, just to be sure.

 

[Domenic]

I am on tab "Jan", right click, View code, Insert Module, paste.

It looks like this is where the problem lies. You should not be inserting a regular Module. When you right-click the sheet tab and select View Code, you will automatically be taken to the sheet module. Post the code there.

Does this help?

 

[GColeman]

It looks like this is where the problem lies. You should not be inserting a regular Module. When you right-click the sheet tab and select View Code, you will automatically be taken to the sheet module. Post the code there.

Does this help?

No, where I said to tried it outside the module that's what meant. Really not sure what I'm doing wrong. Also tried it in Workbook.

As a workaround, I taught myself how to put a user form in instead. Still a similar issue in that Tab doesn't select the comboboxes, but it's a lot more user friendly. But I'm still interested in finding a solution, I hate losing... ?

 

[Domenic]

I don't understand what you mean when you say...

where I said to tried it outside the module that's what meant

Did you make sure to copy the code into the sheet module, and not a regular module?