Tabbing between Text Boxes

[Todd]

This seems to be a no-brainer, but I can't seem figure it
out. One of my colleagues has built a form that has a
number of embedded text boxes in it. I'd like to make it
so that you can tab from one text box to the next, instead
of having to use the mouse to select each successive one
(what a pain!). I can't seem to get Excel to understand
this, and VBA is telling me that .TabIndex is not a
recognized method. (And, sure enough, TabIndex doesn't
show in the TextBox properties window. The only properties
there that have anything to do with Tabbing are AutoTab
and TabKeyBehavior, and the values available for both of
these properties are True and False. I've tried all
variations of these values and they apparently don't
affect the ability to tab through the text boxes.)

I'm stumped! Anyone ever had to deal with this?

Thanks!

Todd

[Trent Argante]

In VBA IDE (Alt+F11) select your form or frame, then click
on the View menu, then click the Tab Order selection.
HTH
Trent

>.
>

[Todd]

Thanks, Trent, but that one's a no go. There is no Tab
Order selection on the View Menu. Probably because this
isn't a form or a frame in the strict sense of the term,
but text boxes embedded in a worksheet.

I appreciate the thought, though!

Any other ideas?

Todd

>.
>

[Todd]

More correctly, Trent, I shouldn't say that there is no
Tab Order, but rather that the Tab Order is grayed out, or
inactivated...

Todd

>.
>

[Trent Argante]

Sorry for the confusion, Todd.
In this case, you may have "textboxes" from the "Forms" toolbar, which
are actually called "Edit Boxes", which are not tabbing enabled.

You can, however, replace them with "Text Boxes" from the "Control
Toolbox's" toolbar, which are tabbing enabled, I believe. Try a test run
on a "New" worksheet before committing.

Trent

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rob Bovey]

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/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Todd" <ttho...@edd.ca.gov> wrote in message
news:02a801c31bd2$bd5481a0$a101...@phx.gbl...

[Greg Malenky]

Todd,

Try unprotecting the cells you want to tab to (right
click on each cell, choose the protection tab, and click
on the lock button so there is no check in it). Then
protect the sheet. This will allow you to tab between the
unprotected cells.

>.
>

[Jungleman]

I saw your posting here and did as you stated with the tabs and the
alignment/order. When I deactivate or click off the Design Mode, I
click on the first row box which has 7 columned text boxes and a Combo
box at the end, I still can't tab. I'm working with MS Office 2000 by
the way.

What am I doing wrong?

Thanks,
Eric

[Dave Peterson]

The thread has aged off (well, for me anyway).

You may want to take a look at the way Harald Staff did it in this thread:
http://google.co.uk/groups?threadm=uDCxIjX3EHA.1144%40TK2MSFTNGP09.phx.gbl

(If this was a followup to something I posted, I kind of remember thinking that
you were using a UserForm--but I could be mistaken.)

--

Dave Peterson