Moving cusor around spread sheet

[rfjm]

I am making spread sheets & would like to have the cusor move to a specified
cell after entering data in a cell when entering data. This way I don't have
to move cursor through each cell. This could be done with TAB or ENTER keys.

 

[Gary''s Student]

Let's say that whenever you have entered data in cell B9 you want the cursor
to automatically move to cell Z100. Enter the following macro in the
worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then
Else
Range("Z100").Select
End If
End Sub

REMEMBER: the worksheet code area, not a standard module.

 

[rfjm]

You'll have to excuse me. I am a self taught (trial & error) Excel user.
I found the worksheet code area.
Do you need to enter this for every cursor movement?
I want to move through out the worksheet to many cells.

 

[Gary''s Student]

You will need some kind of From/To table. The macro needs to know which
cells are departure cells and the destination cell for each departure cell.

 

[rfjm]

Thanks;
I will play around with this & see how it works.

RFJM

 

[RagDyeR]

You might try this approach, instead of using code:

http://tinyurl.com/2gzlwp

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

You'll have to excuse me. I am a self taught (trial & error) Excel user.
I found the worksheet code area.
Do you need to enter this for every cursor movement?
I want to move through out the worksheet to many cells.

 

[Darren Bartrup]

If your cells are in order - i.e you want to move from cell A1 to D1 to G1 to
A2 you could unlock the cells in the cell formatting and protect your sheet.
Tabbing will skip across the locked cells (across columns and then down
rows) and enter will skip down (down rows and then across columns).
Just untick the 'Select locked cells' option in the protect sheet dialog.

 

[Gord Dibben]

How many is many?

Here is event code to move around to some cells as you enter.

Adjust and add to aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10")

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", "B5", "C5", "A10", "B10", "C10")

'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

End Sub


Gord Dibben MS Excel MVP