Using textboxes in sheets

[PO]

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

 

[Tom Ogilvy]

Previously posted by Rob Bovey:

http://groups.google.com/groups?threadm=#[email protected]



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