Arrow key operation among multiple TextBoxes in a UserForm

I have several "data entry" TextBoxes in a UserForm, three across and
three down.

After trying several things, I come to these conclusions:

(1) The arrow keys (right, left, up, down) will not work among the
Textboxes as they do among cells in a worksheet.

(2) The OnKey event works if the focus is on a worksheet, not on a
userform.

Are these correct, or have I missed something?

Converting system from worksheets to userforms. Two of ten have this
"data entry" format. Users are accustomed to using the arrow keys.

Thanks,
George
Windows XP, Office 2000

Have you explored setting the Tab Stop and Tab Order for a UserForm ? this
is the major method of controlling the focus. Also, in the code for each
Textbox_Change event you can set the next focus with code.

"George" wrote:

>
> I have several "data entry" TextBoxes in a UserForm, three across and
> three down.
>
> After trying several things, I come to these conclusions:
>
> (1) The arrow keys (right, left, up, down) will not work among the
> Textboxes as they do among cells in a worksheet.
>
> (2) The OnKey event works if the focus is on a worksheet, not on a
> userform.
>
> Are these correct, or have I missed something?
>
> Converting system from worksheets to userforms. Two of ten have this
> "data entry" format. Users are accustomed to using the arrow keys.
>
> Thanks,
> George
> Windows XP, Office 2000
>
>

Tab and Shift-Tab work OK.

Boxes in this arrangement:
123
456
789
After completing boxes 1 - 6 in rows 1 and 2, focus is on box 7 of
third row, user sees mistake in box 1. Easiest method (in data sheet)
is arrow up to box 1.
Can code do this on a userform?

Not that I know of. I haven't checked this out but I think the up arrow
would go back from box 7 to 6 then 5..... to 1. In other words you can't go
straight to jail without passing Go. (as set up in the Tab Order: Rt click
the form>Tab Order

Barring this to get directly from 7 to 1 requires a left mouse click.

"George" wrote:

> Tab and Shift-Tab work OK.
>
> Boxes in this arrangement:
> 123
> 456
> 789
> After completing boxes 1 - 6 in rows 1 and 2, focus is on box 7 of
> third row, user sees mistake in box 1. Easiest method (in data sheet)
> is arrow up to box 1.
> Can code do this on a userform?
>
>

> focus is on box 7 ~ arrow up to box 1.

What you describe is default behaviour in Excel 2003 Userforms.
I'm not sure about earlier versions.

Such functionality can also be coded. For example, the following code will
control the beaviour of the 4 arrow keys whilst editing the center textbox
(Textbox5). You'd need to write somethig similar for each of your
9 textboxes. This code belongs in the Userform's code module of course.


Regards,
Vic Eldridge


Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)

Select Case KeyCode

Case 37 'Left arrow pressed
If Me.TextBox5.SelStart = 0 _
And Me.TextBox5.SelLength = 0 Then
Me.TextBox4.SetFocus
End If

Case 39 'Right arrow pressed
If Me.TextBox5.SelStart = Len(Me.TextBox5) _
And Me.TextBox5.SelLength = 0 Then
Me.TextBox6.SetFocus
End If

Case 38 'Up arrow pressed
Me.TextBox2.SetFocus

Case 40 'Down arrow pressed
Me.TextBox8.SetFocus

End Select

End Sub



"George" wrote:

> Tab and Shift-Tab work OK.
>
> Boxes in this arrangement:
> 123
> 456
> 789
> After completing boxes 1 - 6 in rows 1 and 2, focus is on box 7 of
> third row, user sees mistake in box 1. Easiest method (in data sheet)
> is arrow up to box 1.
> Can code do this on a userform?
>
>

In my computer/form, Right and Left arrows do not work at all.
123
456
789
If I'm on Box 1, Down arrow goes down but skips a row to Box 7.
If I'm on Box 7, Up arrow goes up but skips a row to Box 1.
Similar actions in other columns (Box 2 Down goes to Box 8).
Up and Down arrow keys ignore TabIndex and TabStop = False



gocush wrote:
> Not that I know of. I haven't checked this out but I think the up
arrow
> would go back from box 7 to 6 then 5..... to 1. In other words you
can't go
> straight to jail without passing Go. (as set up in the Tab Order:
Rt click
> the form>Tab Order
>
> Barring this to get directly from 7 to 1 requires a left mouse click.
>
> "George" wrote:
>
> > Tab and Shift-Tab work OK.
> >
> > Boxes in this arrangement:
> > 123
> > 456
> > 789
> > After completing boxes 1 - 6 in rows 1 and 2, focus is on box 7 of
> > third row, user sees mistake in box 1. Easiest method (in data
sheet)
> > is arrow up to box 1.
> > Can code do this on a userform?
> >
> >

Vic,
Thanks for that code.
I reduced it to ...

Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
Select Case KeyCode
Case 37 'Left arrow pressed
Me.TextBox4.SetFocus
Case 39 'Right arrow pressed
Me.TextBox6.SetFocus
Case 38 'Up arrow pressed
Me.TextBox2.SetFocus
Case 40 'Down arrow pressed
Me.TextBox8.SetFocus
End Select
End Sub

.... and made other subs for each Textbox.
The arrow keys work just as in a worksheet, which was the objective.

I'm not sure what the extra code you have for Left and Right arrow
does, if you would please explain.
It appears that the insertion point changes with my "reduced" code.
This seems to be a minor point now, but probably would be better if the
insertion point stayed consistent for the user.

The next thing I may try is make a function that does this (assuming
TextBox names starting with 01) with input arguments
(Number of rows, Number of columns).

Thanks again.

Hi George,

You might consider a Class to handle events for all your similar textboxes,
eg

''''''''''''''''''''
''start code in Class module named clsTextBoxes

Public WithEvents tb As MSForms.TextBox
Dim id As Long
Public Property Let clsID(n As Long)
id = n
End Property

Private Sub tb_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift
As Integer)
Dim nCtrl As Long

If KeyCode > 36 And KeyCode < 41 Then
Select Case KeyCode
Case 38
If id > 3 Then nCtrl = id - 3
Case 37
If id = 1 Then nCtrl = 9 Else nCtrl = id - 1
Case 39
If id = 9 Then nCtrl = 1 Else nCtrl = id + 1
Case 40
If id < 7 Then nCtrl = id + 3
End Select
If nCtrl Then UserForm1.Controls.Item(nCtrl).SetFocus
End If
End Sub
'' end code clsTextBoxes
'''''''''''''''''
''Start code in Userform
''
''Include a Commandbutton - item(0)
'' and 9 textboxes - items 1 to 10

Private caTB(1 To 9) As New clsTextBoxes

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
With Me.Controls
For i = 1 To 9
Set caTB(i).tb = .Item(i)
caTB(i).clsID = i
Next
.Item(1).SetFocus
End With
End Sub
'' end code userform
'''''''''''''''''''''

The order and Item number of each control is defined by the order they were
placed on the form, and cannot be changed with tab order. So change:
for i = 1 to 9 to i = first to last where first is the item number of the
first textbox.

You might find the class useful to handle other parts of your textbox code.

Small point - only use "New" Class at the top of the array with defined
array for a fixed number of same class that are always going to be
instantiated, ie you have a fixed number of textboxes that you want to
handle with the class.

Regards,
Peter T

PS I did this quickly - double check the code handles arrow keys tthe way
you want



"George" <[email protected]> wrote in message
news:[email protected]...
> Vic,
> Thanks for that code.
> I reduced it to ...
>
> Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
> ByVal Shift As Integer)
> Select Case KeyCode
> Case 37 'Left arrow pressed
> Me.TextBox4.SetFocus
> Case 39 'Right arrow pressed
> Me.TextBox6.SetFocus
> Case 38 'Up arrow pressed
> Me.TextBox2.SetFocus
> Case 40 'Down arrow pressed
> Me.TextBox8.SetFocus
> End Select
> End Sub
>
> ... and made other subs for each Textbox.
> The arrow keys work just as in a worksheet, which was the objective.
>
> I'm not sure what the extra code you have for Left and Right arrow
> does, if you would please explain.
> It appears that the insertion point changes with my "reduced" code.
> This seems to be a minor point now, but probably would be better if the
> insertion point stayed consistent for the user.
>
> The next thing I may try is make a function that does this (assuming
> TextBox names starting with 01) with input arguments
> (Number of rows, Number of columns).
>
> Thanks again.
>

typo:

> Small point - only use "New" Class at the top of the array with defined
> array for a fixed number of same class that are always going to be
> instantiated, ie you have a fixed number of textboxes that you want to
> handle with the class.
>

- only use "New" Class at the top of the Module with dimensioned array -

Peter T

> I'm not sure what the extra code you have for Left and Right arrow
> does, if you would please explain.

It means you can still use the left and right arrow keys to move the insertion
point forward or backwards past each character in Textbox5. The left arrow
key will only jump to Textbox4 if the insertion point is at the very beginning
of Textbox5. Similarly, the right arrow key will only jump to Textbox6 when
the insertion point is at the very end of Textbox5.
Personally, it would drive me batty if I had to use the mouse every time I
wanted to shift the insertion point.


Regards,
Vic Eldridge



"George" wrote:

> Vic,
> Thanks for that code.
> I reduced it to ...
>
> Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
> ByVal Shift As Integer)
> Select Case KeyCode
> Case 37 'Left arrow pressed
> Me.TextBox4.SetFocus
> Case 39 'Right arrow pressed
> Me.TextBox6.SetFocus
> Case 38 'Up arrow pressed
> Me.TextBox2.SetFocus
> Case 40 'Down arrow pressed
> Me.TextBox8.SetFocus
> End Select
> End Sub
>
> .... and made other subs for each Textbox.
> The arrow keys work just as in a worksheet, which was the objective.
>
> I'm not sure what the extra code you have for Left and Right arrow
> does, if you would please explain.
> It appears that the insertion point changes with my "reduced" code.
> This seems to be a minor point now, but probably would be better if the
> insertion point stayed consistent for the user.
>
> The next thing I may try is make a function that does this (assuming
> TextBox names starting with 01) with input arguments
> (Number of rows, Number of columns).
>
> Thanks again.
>
>