Moving between cells

[Alec H]

Hi,

I have constructed a sheet that the user has to input data into and then upon completion press an on sheet command button to confirm.

My problem is simple (I hope), I want to be able to dictate which cell the curser will move to on completion of each entry.

My example is;

Cell B2 - Input
then
Cell C7 - Input
then
Cell B6 - Input
then
Command Button

Is there an easy way of doing this without forcing the user to use the mouse to select the cells they need in the right order?

[nsv]

Look at this example which works with the DOWN-ARROW, which I find more simple than pushing a botton. It starts out at G11 and after filling out G11 you push the DOWN-ARROW. This makes the cursor jump to A14, etc.


Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'macro jump cell

If Target.Row = 12 And Target.Column = 7 Then 'jump A14
Target.Offset(2, -6).Select
End If

If Target.Row = 15 And Target.Column = 1 Then 'jump B14
Target.Offset(-1, 1).Select
End If

End Sub

[Alec H]

Thank You

[Chip Pearson]

You should disable events in this procedure, and turn them back
on at the end.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'macro jump cell
Application.EnableEvents = False '<<<<<<<<<<<
If Target.Row = 12 And Target.Column = 7 Then 'jump A14
Target.Offset(2, -6).Select
End If

If Target.Row = 15 And Target.Column = 1 Then 'jump B14
Target.Offset(-1, 1).Select
End If
Application.EnableEvents = True '<<<<<<<<<<<
End Sub

Otherwise, you'll find that the macro is calling itself. The
SelectionChange code changes the Selection, which calls
SelectionChange again.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"nsv" <[email protected]> wrote in
message news:[email protected]...
>
> Look at this example which works with the DOWN-ARROW, which I
> find more
> simple than pushing a botton. It starts out at G11 and after
> filling
> out G11 you push the DOWN-ARROW. This makes the cursor jump to
> A14,
> etc.
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> 'macro
> jump cell
>
> If Target.Row = 12 And Target.Column = 7 Then 'jump A14
> Target.Offset(2, -6).Select
> End If
>
> If Target.Row = 15 And Target.Column = 1 Then 'jump B14
> Target.Offset(-1, 1).Select
> End If
>
> End Sub
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile:
> http://www.excelforum.com/member.php...o&userid=26500
> View this thread:
> http://www.excelforum.com/showthread...hreadid=522094
>

[nsv]

...but Chip, until now I have run my spreadsheet without the disabling of events and it works perfectly. It is only a small one, actually just a form to be filled out in certain cells only and there are almost no calculations.

I will of course put in the extra lines you recommend, but what can go wrong if I do not disable events?

NSV

[Dave Peterson]

I'm not Chip, but If you put a break point in your code (right near the top),
then stepped through the code after the break, you'd see what was really
happening. Not quite what you expect. But with the speed of the pc (and the
code you're running), you don't notice the difference.

You can also see it by adding a single msgbox:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'macro jump cell

MsgBox "hi from worksheet change!"

If Target.Row = 12 And Target.Column = 7 Then 'jump A14
Target.Offset(2, -6).Select
End If

If Target.Row = 15 And Target.Column = 1 Then 'jump B14
Target.Offset(-1, 1).Select
End If

End Sub

And depending on what your code does, lots can go wrong or nothing can go, er,
look wrong.

Here's a worksheet_change event that looks like it would just add something to
the cell below the changed cell:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Offset(1, 0) = "hi"
End Sub

But changing that cell below causes the event to fire, which causes the cell
below to change which causes the event to fire......until excel gets tired (it
does try to protect itself a little bit).

I changed A1 and excel got tired when it changed A227 (xl2003).

nsv wrote:
>
> ..but Chip, until now I have run my spreadsheet without the disabling
> of events and it works perfectly. It is only a small one, actually just
> a form to be filled out in certain cells only and there are almost no
> calculations.
>
> I will of course put in the extra lines you recommend, but what can go
> wrong if I do not disable events?
>
> NSV
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=522094

--

Dave Peterson