How to move focus between controls in VBA (Excel) 3

[Voga]

I have to do some work in not well familiar for me VBA Excel. I'd like to move focus from one control (txt) to another using only one Tab key on the keyboard. In VB it's easy just setting TabOrder numbers in the properties. What about in Excel? What event should I use? I figured out that KeyPress not working. I can move the focus using TextLength, but it is not a case I need. Also why within a control Tab key works only with Ctrl? How to move g.dam. focus and AutoTab works in this case? Please help.
Thank you all
Voga.

 

[Zathras]

Text boxes are best used on a form, or sparingly directly on a worksheet. If you really need to do that (as opposed to using individual cells for data entry) you could use VBA code. It's not pretty, and it may not be foolproof, but the final decision is yours:

Put this in a separate code module:
[blue]

Option Explicit

Sub ProcessTab(NextBox As Object, PriorBox As Object, Shift As Integer)
  If Shift = 0 Then
    NextBox.Select
    NextBox.Activate
  Else
    PriorBox.Select
    PriorBox.Activate
  End If
End Sub

[/color]

And put this in the code page for the sheet (a separate Sub for each text box, tailored for which boxes are "next" or "prior&quot:
[blue]

Option Explicit

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 9 Then ProcessTab TextBox2, TextBox3, Shift
End Sub

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

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 9 Then ProcessTab TextBox1, TextBox2, Shift
End Sub

[/color]

It is probably easier simply to unlock a few cells for use as input and then protect the worksheet, or use a form.

 

[Voga]

Zathras
Thank you for your help. I'll definitely try it.
...or use a form... How to use form in Excel?
Voga.

 

[lespaul]

To use a standard form, Data -> Form and excel will make a form for you. Or in the Visual Basic Editor Insert -> User Form and you can design your own.

HTH

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Voga]

lespaul
Thank you very much.
The message says, however "No list was found. Select a single cell within your list, and then click the command again"

 

[Zathras]

Voga, Leslie gave you two different techniques.

The first one Data/Form... provides a way to work with data arranged as a database in a worksheet. First row = headings, Rows 2 thru n = data. You need to first click a cell in the data area so Excel can find the database to generate a form for. I don't think that's what you want. It is very primitive and AFAIK does not allow for modification.

The second one is what you want: (From the VBA editor) Insert/User Form. That gives you a blank form on which you can drop your text boxes and command buttons. It's pretty well documented in the VBA help files. Give it a try.

 

[Voga]

Zathras
Thanks a lot
I choose a second method and already migrated all my controls to the Form. Everything looks MUCH more familiar just like in VB. This isn't my project I'm helping with VBA, but I'm learning along
Thanks again.