TAB-ORDER from one TEXTBOX to another

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

on a userform this is easy! but I'm not using a userform!

two toolbox textboxes embedded on a worksheet.When I've entered atring
into textbox1 and I hit tab I want to jump into textbox2. Is this
possible? or is this a shortfall of excel?

help greatly appreciated

Jason.
 
Jason,

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
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If bBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

add similar code for each control.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top