Tabbing between textbox's

[dr chuck]

I have several textbox's on a excel worksheet. I would like to be able to tab
between after data is entered. I posted a question in regards to this and
was told the following:

"check out the tabindex ..in the textbox properties. then you can just
TAB you way through...for each textbox set the indeax as the number in
the order you want"

When i look in the properties section of th textbox I dont see the tab
index. Any suggestions would be greatly appreaciated.

 

[stevebriz]

sorry I gave you it for if it is on a form

set TabKeyBehaviour to TRUE
then you can tab along.

 

[Bob Phillips]

That was aimed at userform textboxes. Presumably you have textboxes on a
worksheet.

If they are control toolbox controls, this code will work for them. This is
based upon 3 controls, and the one you determine to be previous or next is
defined up-front.


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

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

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "TextBox1"
Const ctlNext As String = "TextBox3"

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


Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "TextBox1"
Const ctlNext As String = "TextBox1"

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


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

add similar code for each extra control.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[dr chuck]

hey Bob..
Thanks for the info. This resolved my problem.