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

M

Mandigos

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
 
M

Mandigos

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

Gord Dibben

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
 
O

Otto Moehrbach

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
G

Gord Dibben

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

Otherwise no error should arise.


Gord
 
Top