Tab order on worksheet

[Jerett]

I need some help with this.
I am trying to set the tab order in an excel worksheet using VBA. I
found the following code on one of the newsgroups:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
Dim bBackwards As Boolean
Const ctlPrev As String = "G10"
Const ctlNext As String = "TextBox2"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
If Application.Version < 9 Then Sheet1.Range("A1").Select
If bBackwards Then
Range(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

It works very well but I would have to put this in for each ole object
and cell pointing to an ole object. What I would rather do is pull an
array from an excel worksheet and use a routine to handle this. This
would make the tab order a little more controllable and would use a
lot less code, I have about 100 textboxes, checkboxes, option buttons,
and dropdowns on the worksheet.. I think that I would need to put the
property type in as a variable as well so I would have to specify in
the array whether the item is a Range or OLEObject. As you can see
from the example above I modified the original code that I found to
point to a cell instead of another OLEObject.
And just one more question: Why did Microsoft make this so difficult.
Seems like it should be an easy problem.

 

[Kalle]

I think it is not such hard to achieve it.
I just don't know, what You want to achieve.

 

[Guest]

Sorry, I was not very clear
In Excel there is no way to control the tab order of cells and ActiveX objects (textbox, checkbox, option button, or dropdown list). In fact if you want to use the tab or enter keys to move from control to control you must code the movement with code similar to what I posted. But I am having trouble setting up an array to handle this movement. What I would like to do is set up another worksheet “Sheet2†in the workbook that will store the Tab order for “Sheet1â€. I have put in 3 columns and about 150 rows
The columns are
Index, Object type, object Nam
1, OLEObject, textbox
2, range, c1

the vba procedure will take the name of the active object and use its place in the array to control the tab movement from the cell or object

 

[Kalle]

Did you look on the properties Taborder and Tabstop?

Sorry, I was not very clear.
In Excel there is no way to control the tab order of cells and ActiveX

objects (textbox, checkbox, option button, or dropdown list). In fact if
you want to use the tab or enter keys to move from control to control you
must code the movement with code similar to what I posted. But I am having
trouble setting up an array to handle this movement. What I would like to
do is set up another worksheet "Sheet2" in the workbook that will store the
Tab order for "Sheet1". I have put in 3 columns and about 150 rows.

The columns are:
Index, Object type, object Name
1, OLEObject, textbox1
2, range, c15

the vba procedure will take the name of the active object and use its

place in the array to control the tab movement from the cell or object.

 

[Guest]

Thank you very much for your response.
Two further issues though. First I will have to include a function to control the “shift-tab†for previous item. Second I will need to be able to address the transition between cells and objects. The transition between cells and objects is a problem for me

Thank you again
Jeret