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

[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

 

[Gord Dibben]

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

 

[Mandigos]

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

 

[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

 

[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

 

[Dave Peterson]

Maybe a typo:

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

 

[Otto Moehrbach]

Dave
Thanks for that. I would think you want to see that error. Otto

 

[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

 

[Gord Dibben]

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

Otherwise no error should arise.


Gord