How can we control the sequence in which the cursor moves in a for
 

I have created a form using excel and need to force the cursor to jump from
input cell to input cell in a specific order, how do I achieve this? I have
tried all sorts of things, but so far, excel has the control of the cursor
and moves widely through the form. Appreciate any assistance. Thx Mandigos

How can we control the sequence in which the cursor moves in a for
 

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump from
input cell to input cell in a specific order, how do I achieve this? I have
tried all sorts of things, but so far, excel has the control of the cursor
and moves widely through the form. Appreciate any assistance. Thx Mandigos

How can we control the sequence in which the cursor moves in a
 

Gord,
appreciate your help. Yes, I am interested in using event code. thx Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump from
input cell to input cell in a specific order, how do I achieve this? I have
tried all sorts of things, but so far, excel has the control of the cursor
and moves widely through the form. Appreciate any assistance. Thx Mandigos

How can we control the sequence in which the cursor moves in a
 

Right-click on the sheet tab and "View Code"

Copy/paste the code into that module. Edit as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B1", "G3", "A11", "B10", "C3")
On Error GoTo enditall
Application.EnableEvents = False

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
enditall:
Application.EnableEvents = True

End Sub


Gord

On Sun, 27 Jan 2008 13:36:01 -0800, Mandigos
wrote:

Gord,
appreciate your help. Yes, I am interested in using event code. thx Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump from
input cell to input cell in a specific order, how do I achieve this? I have
tried all sorts of things, but so far, excel has the control of the cursor
and moves widely through the form. Appreciate any assistance. Thx Mandigos

How can we control the sequence in which the cursor moves in a
 

Gord
I was looking at your code (Anne's code) and I have a question. What is
the error that the error trap is there to trap? Thanks for your time. Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Right-click on the sheet tab and "View Code"

Copy/paste the code into that module. Edit as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B1", "G3", "A11", "B10", "C3")
On Error GoTo enditall
Application.EnableEvents = False

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
enditall:
Application.EnableEvents = True

End Sub


Gord

On Sun, 27 Jan 2008 13:36:01 -0800, Mandigos
wrote:

Gord,
appreciate your help. Yes, I am interested in using event code. thx
Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random
cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump
from
input cell to input cell in a specific order, how do I achieve this? I
have
tried all sorts of things, but so far, excel has the control of the
cursor
and moves widely through the form. Appreciate any assistance. Thx
Mandigos

How can we control the sequence in which the cursor moves in a
 

Maybe a typo:

aTabOrd = Array("A5", "B1", "G3", "A1x1", "B10", "C3")

Otto Moehrbach wrote:

Gord
I was looking at your code (Anne's code) and I have a question. What is
the error that the error trap is there to trap? Thanks for your time. Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Right-click on the sheet tab and "View Code"

Copy/paste the code into that module. Edit as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B1", "G3", "A11", "B10", "C3")
On Error GoTo enditall
Application.EnableEvents = False

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
enditall:
Application.EnableEvents = True

End Sub


Gord

On Sun, 27 Jan 2008 13:36:01 -0800, Mandigos
wrote:

Gord,
appreciate your help. Yes, I am interested in using event code. thx
Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random
cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump
from
input cell to input cell in a specific order, how do I achieve this? I
have
tried all sorts of things, but so far, excel has the control of the
cursor
and moves widely through the form. Appreciate any assistance. Thx
Mandigos




--

Dave Peterson

How can we control the sequence in which the cursor moves in a
 

Dave
Thanks for that. I would think you want to see that error. Otto
"Dave Peterson" wrote in message
...
Maybe a typo:

aTabOrd = Array("A5", "B1", "G3", "A1x1", "B10", "C3")

Otto Moehrbach wrote:

Gord
I was looking at your code (Anne's code) and I have a question. What
is
the error that the error trap is there to trap? Thanks for your time.
Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Right-click on the sheet tab and "View Code"

Copy/paste the code into that module. Edit as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B1", "G3", "A11", "B10", "C3")
On Error GoTo enditall
Application.EnableEvents = False

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
enditall:
Application.EnableEvents = True

End Sub


Gord

On Sun, 27 Jan 2008 13:36:01 -0800, Mandigos
wrote:

Gord,
appreciate your help. Yes, I am interested in using event code. thx
Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous
random
cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to
jump
from
input cell to input cell in a specific order, how do I achieve this?
I
have
tried all sorts of things, but so far, excel has the control of the
cursor
and moves widely through the form. Appreciate any assistance. Thx
Mandigos




--

Dave Peterson

How can we control the sequence in which the cursor moves in a
 

This same question came up a couple of days ago and here is the answer I
gave back then...

With the following code, the movement out of the last cell will be normal
(that is, it will be in accordance with your "Move selection after Enter"
option on Tools/Options/Edit(tab) from Excel's menu bar).

Private Sub Worksheet_Change(ByVal Target As Range)
Const Addr = "D1 G10 A5 B7 C3"
With Target
If InStr(" " & Addr & " ", " " & .Address(False, False) & " ") Then
If .Address(False, False) < Mid$(Addr, InStrRev(Addr, " ") + 1) Then
Range(Split(Split(Addr & " " & .Address(False, False), _
.Address(False, False) & " ")(1))(0)).Select
End If
End If
End With
End Sub

And, if you want the movement to continually cycle over and over again
instead of just stopping at the last cell, then use this code...

Private Sub Worksheet_Change(ByVal Target As Range)
Const Addr = "D1 G10 A5 B7 C3"
With Target
If InStr(" " & Addr & " ", " " & .Address(False, False) & " ") Then
If .Address(False, False) < Mid$(Addr, InStrRev(Addr, " ") + 1) Then
Range(Split(Split(Addr & " " & .Address(False, False), _
.Address(False, False) & " ")(1))(0)).Select
ElseIf .Address(False, False) = Split(Addr)(UBound(Split(Addr))) Then
Range(Split(Addr)(0)).Select
End If
End If
End With
End Sub

Simply assign the addresses (space delimited) for the cells you want to
visit, in the order you want to visit them, to the Addr constant in the
first line of the Worksheet Change event code above.

Rick



"Mandigos" wrote in message
...
Gord,
appreciate your help. Yes, I am interested in using event code. thx
Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random
cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump
from
input cell to input cell in a specific order, how do I achieve this? I
have
tried all sorts of things, but so far, excel has the control of the
cursor
and moves widely through the form. Appreciate any assistance. Thx
Mandigos

How can we control the sequence in which the cursor moves in a
 

Force of habit, but Dave is correct........you could have a typo in the code.

Otherwise no error should arise.


Gord

On Sun, 27 Jan 2008 18:45:35 -0500, "Otto Moehrbach"
wrote:

Gord
I was looking at your code (Anne's code) and I have a question. What is
the error that the error trap is there to trap? Thanks for your time. Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Right-click on the sheet tab and "View Code"

Copy/paste the code into that module. Edit as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B1", "G3", "A11", "B10", "C3")
On Error GoTo enditall
Application.EnableEvents = False

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
enditall:
Application.EnableEvents = True

End Sub


Gord

On Sun, 27 Jan 2008 13:36:01 -0800, Mandigos
wrote:

Gord,
appreciate your help. Yes, I am interested in using event code. thx
Mandigos

"Gord Dibben" wrote:

All depends upon the order in which you want to move.

There are a few methods of achieving this.

See Bob Phillips's site for a couple.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You could also employ event code to jump around to non-contiguous random
cells.

If interested, post back.


Gord Dibben MS Excel MVP


On Sun, 27 Jan 2008 12:54:01 -0800, Mandigos
wrote:

I have created a form using excel and need to force the cursor to jump
from
input cell to input cell in a specific order, how do I achieve this? I
have
tried all sorts of things, but so far, excel has the control of the
cursor
and moves widely through the form. Appreciate any assistance. Thx
Mandigos