Using Tab-Stop in between textbox components

[Guest]

Hi everyone,

I want know that is there any way to change the active textbox using TAB.
There is no tabstop property on textbox component in excel.

Thanks.

 

[Guest]

ozulku_omer:

add this code to Thisworkbook
'Thisworkbook
Dim txtBoxes(1 To 4) As New Class1

Private Sub Workbook_Open()
Dim ctr As OLEObject
Dim iCount As Integer
For Each ctr In Sheet1.OLEObjects
If TypeName(ctr.Object) = "TextBox" Then
iCount = iCount + 1
Set txtBoxes(iCount).TxtGroup = ctr.Object
End If
Next ctr
End Sub

Add a Class module to your project and add this code to it.

Public WithEvents TxtGroup As MSForms.TextBox
Private Sub TxtGroup_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Then
If TxtGroup.Index < 4 Then 'From 4 again rebounds 1
Sheet1.OLEObjects(TxtGroup.Index + 1).Activate
Else
Sheet1.OLEObjects(1).Activate
End If
End If
End Sub


download:
http://www.vba.holyou.net/file/9411241.xls

 

[Harald Staff]

This is a textbox from the controls toolbox ? Placed on a worksheet, on a
userform or where ? Which version of Excel are you using ?

Best wishes Harald

 

[Guest]

I got 9 textboxes. So am i change this code like that?
Dim txtBoxes(1 To 4) As New Class1
Dim txtBoxes(1 To 9) As New Class1
If TxtGroup.Index < 4 Then 'From 4 again rebounds 1
If TxtGroup.Index < 9 Then 'From 4 again rebounds 1


I have done all of them it but i doesn.t work like i wish. It focuses on
combobox object and stops when i hit the tab 3 times. The question is how can
i set the tabstop order. I mean my textboxs are in mixed order and i want to
focus on them in a fixed order. thanks a lot.

 

[Guest]

yes this is a textbox from controls toolbox placed on worksheet.XL 2003.

Thanks.

 

[Harald Staff]

You can subclass it as the other reply indicates. If you want it simpler
then add similar code for each box like this in the worksheet module:

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = 9 Then
If Shift = 1 Then
TextBox1.Activate
Else
TextBox3.Activate
End If
End If
End Sub

It's boring to write, but once done it's done.

HTH. Best wishes Harald