Andy,
Here is a copy of a post I gave yesterday in response to a similar
question. The OP has posted back that it worked well.
There is no TabIndex property for worksheet controls. This because the
TabOrder property is an inherited property, that is it comes from the
container that the control is situated in. The userform container supplies
this property, a worksheet container does not.
There is a solution though (Can't say I have tried it personally) that was
previously posted by Rob Bovey. I attach it here.
'---------------------------------------------------------------------------
------------
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 must select cell before activating another
control.
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
'---------------------------------------------------------------------------
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
