Hi

I have a worksheet containing 22 textboxes. They don't seem to have a built
in tab-function. I've tried to use the key_down sub:

Private Sub TextBox01_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 9 or KeyCode = 13 Then TextBox02.Activate
End Sub

I don't want to copy the code 22 times (for all 22 textboxes - the code
quickly get unreadable), there must be a way to capture the keystrokes in
one event and then call a public sub with the textbox name as an
argument...for instance:

Sub Tabhandler(ByVal TextBoxName as String)
Dim strNr as String

strNr=Right(TextBoxName,2)
strNr=StrNr+1
ActiveSheet.Shapes(strNr).SetFocus
End Sub

I just want the user to be able to tab between all the textboxes in my
worksheet.
Perhaps there are other solutions to this problem?

TIA
PO

Previously posted by Rob Bovey:

http://groups.google.com/groups?thre...gp13.p hx.gbl



Hi Todd,

The TabOrder property is an inherited property. That means it comes from
the container that a control is situated in. A UserForm supplies this
property, a worksheet doesn't. You can still tab amongst controls on a
worksheet, you just have to code it yourself using each control's KeyDown
event procedure.

In the sample event procedure below I'll assume a hypothetical situation
where we have three textboxes: TextBoxPrevious, TextBoxCurrent, and
TextBoxNext. This event procedure shows you how to use VBA to emulate
tabbing behavior. Pressing Tab moves from TextBoxCurrent to TextBoxNext and
pressing Shift+Tab moves from TextBoxCurrent to TextBoxPrevious. The Up and
Down arrow keys and the Enter key are given similar behavior.

Private Sub TextBoxCurrent_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' In Excel 97 we must select a cell
''' before activating another control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then TextBoxPrevious.Activate Else _
TextBoxNext.Activate
Application.ScreenUpdating = True
End Select
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

================

You could probably craft the above into using this approach documented by
John Walkenbach:
http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

While written for commandbuttons on a userform, it should be easily
adaptable to you situation. You would have to add consideration for a "Tab
Order" amongst your textboxes

--
Regards,
Tom Ogilvy



"PO" <po wrote in message ...
Hi

I have a worksheet containing 22 textboxes. They don't seem to have a
built
in tab-function. I've tried to use the key_down sub:

Private Sub TextBox01_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal
Shift As Integer)
If KeyCode = 9 or KeyCode = 13 Then TextBox02.Activate
End Sub

I don't want to copy the code 22 times (for all 22 textboxes - the code
quickly get unreadable), there must be a way to capture the keystrokes in
one event and then call a public sub with the textbox name as an
argument...for instance:

Sub Tabhandler(ByVal TextBoxName as String)
Dim strNr as String

strNr=Right(TextBoxName,2)
strNr=StrNr+1
ActiveSheet.Shapes(strNr).SetFocus
End Sub

I just want the user to be able to tab between all the textboxes in my
worksheet.
Perhaps there are other solutions to this problem?

TIA
PO