Moving to a different cell automatically.

[Fernandoalberte]

Guys...can someone please help me i am going crazy.

What i woudl like to do is when someone is in a workbook and they have finished entering data into cell a1, as soon as they hit enter i woudl like them to be taken to a cell of my choosing and not to a2 which is the excel default.

I am sure there is a way to do this...but i just can't figure it out.

Can anyone help me?

thanks

fernando

[bhofsetz]

Fernando,
You can use a worksheet_change event handler to do just this.

Right click on the worksheet tab then at the top of the VBA editor window paste:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then Range("B10").Select
End Sub

HTH

[Fernandoalberte]

thank you i ave tried what you have suggested an not much happens.

if i start off with range a1 selected and then hit return to go to a2, nothing happens...i was expecting it to go to b10.

Am i doing somthing wrong?

[Piranha]

Fernando,

This code works fine for me. -- Have you put the code into the "Sheet" module?
Not in a Regular module or Worksheet module.

Dave

thank you i ave tried what you have suggested an not much happens.

if i start off with range a1 selected and then hit return to go to a2, nothing happens...i was expecting it to go to b10.

Am i doing somthing wrong?

Fernando,
You can use a worksheet_change event handler to do just this.

Right click on the worksheet tab then at the top of the VBA editor window paste:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then Range("B10").Select
End Sub

[Gary Keramidas]

it works here. i think they forgot a step and not sure if you did it or not.
right click the sheet tab at the bottom. choose view code and then paste in
their code.

--


Gary


"Fernandoalberte"
<[email protected]> wrote in
message news:[email protected]...
>
> thank you i ave tried what you have suggested an not much happens.
>
> if i start off with range a1 selected and then hit return to go to a2,
> nothing happens...i was expecting it to go to b10.
>
> Am i doing somthing wrong?
>
>
> --
> Fernandoalberte
> ------------------------------------------------------------------------
> Fernandoalberte's Profile:
> http://www.excelforum.com/member.php...o&userid=24493
> View this thread: http://www.excelforum.com/showthread...hreadid=386474
>

[Bernie Deitrick]

Fernando,

With Excel XP or later, you can lock all the cells (Format cells, protection tab) and unlock the
cells that you want to allow the user to edit (let's say, A1 and B10). When you protect the sheet,
choose the option to only allow editing of unlocked cells, and pressing enter will move the cursor
to the next unprotected cell.

HTH,
Bernie
MS Excel MVP


"Fernandoalberte" <[email protected]> wrote in message
news:[email protected]...
>
> Guys...can someone please help me i am going crazy.
>
> What i woudl like to do is when someone is in a workbook and they have
> finished entering data into cell a1, as soon as they hit enter i woudl
> like them to be taken to a cell of my choosing and not to a2 which is
> the excel default.
>
> I am sure there is a way to do this...but i just can't figure it out.
>
> Can anyone help me?
>
> thanks
>
> fernando
>
>
> --
> Fernandoalberte
> ------------------------------------------------------------------------
> Fernandoalberte's Profile: http://www.excelforum.com/member.php...o&userid=24493
> View this thread: http://www.excelforum.com/showthread...hreadid=386474
>

[bhofsetz]

Fernando,
The code I proveded is a worksheet_change event and therefore the contents of A1 would have to be changed from whatever the current value is for the macro to be triggered (ie. just hitting return won't jump you to B10).

If you really want to restrict the user to certian cells for data entry then Bernie's suggestion may be the way to go.

HTH