Hi Guys,


I'm trying to create a macro that automatic jumps to the "next" cell in a
table, but I simply can't make it work...

When the user hit the [Enter] key, I want:

If the cursor is placed in a cell in column J, the cursor should jump to the
same cell in column L (same row).
If the cursor is placed in a cell in column L, the cursor should jump to
column B in the next row.

It must not matter or not, if the user enters anything or not, in the
cells....

How to do this???

Thank you in advance...


CE

Hi Charlotte,

Am Mon, 21 Oct 2013 15:28:52 +0200 schrieb Charlotte E.:

If the cursor is placed in a cell in column J, the cursor should jump to the
same cell in column L (same row).
If the cursor is placed in a cell in column L, the cursor should jump to
column B in the next row.

try:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column
Case 10
Application.Goto Cells(Target.Row, "L")
Case 12
Application.Goto Cells(Target.Row + 1, "B")
End Select

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I already tried that, but as I worte in my question:

It must not matter or not, if the user enters anything or not, in the
cells....

So, the Worksheet_change event doesn't do the trick, since it requies a
change to take place...


CE



"Claus Busch" wrote in message
...
Hi Charlotte,

Am Mon, 21 Oct 2013 15:28:52 +0200 schrieb Charlotte E.:

If the cursor is placed in a cell in column J, the cursor should jump to
the
same cell in column L (same row).
If the cursor is placed in a cell in column L, the cursor should jump to
column B in the next row.

try:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column
Case 10
Application.Goto Cells(Target.Row, "L")
Case 12
Application.Goto Cells(Target.Row + 1, "B")
End Select

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

So, the Worksheet_change event doesn't do the trick, since it requies
a change to take place...

Try using SelectionChange instead...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Thanks, Garry, but I already did that - problem with this method is that it
will also change the cell, if I select the cell with the mouse - by clicking
the cell with the mouse...

As stated in my question: It must be when the user hits the [Enter] key.

CE


"GS" wrote in message ...
So, the Worksheet_change event doesn't do the trick, since it requies a
change to take place...

Try using SelectionChange instead...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus
protection is active.
http://www.avast.com

As stated in my question: It must be when the user hits the [Enter]
key.

Too bad there's not a Keypress event! I usually use sheet protection to
control navigation order/direction, but perhaps this won't work for you
either?<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Hi Charlotte

I believe OnKey can be of help. Run Test:

Sub test()
Application.OnKey "~", "Jada" 'enter
Application.OnKey "{ENTER}", "Jada" 'numeric keypad enter
End Sub

Sub Jada()
MsgBox "Jada"
'replace with select case activecell.column or similar
End Sub

(Jada is norwegian for "oh yes")

Best wishes Harald


"Charlotte E." skrev i melding
...
Hi Guys,


I'm trying to create a macro that automatic jumps to the "next" cell in a
table, but I simply can't make it work...

When the user hit the [Enter] key, I want:
(....)

I believe OnKey can be of help. Run Test:

Sub test()
Application.OnKey "~", "Jada" 'enter
Application.OnKey "{ENTER}", "Jada" 'numeric keypad enter
End Sub

Sub Jada()
MsgBox "Jada"
'replace with select case activecell.column or similar
End Sub

Harald,
<FWIW
This was my 1st thought but this will work only as long as there's no
way the selected cell changes. The direction to move must be set to
'none' so the cell's address can be tested. (I actually prefer no cell
change on 'Enter', but that's just me! Others may have a problem with
this approach and so is why I didn't mention it)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

"GS" skrev i melding ...
<FWIW
This was my 1st thought but this will work only as long as there's no way
the selected cell changes. The direction to move must be set to 'none' so
the cell's address can be tested. (I actually prefer no cell change on
'Enter', but that's just me! Others may have a problem with this approach
and so is why I didn't mention it)

You are probably right, Garry, I did not follow the lead and investigate its
implications.If activecell changes before onkey fires then this gets a bit
complicated.

Also, I agree that overriding default or chosen behavior is a bad idea. But
I know Charlotte as a reflected person, I'm sure that there is an acceptable
reason for this design.

Some of my best solutions are based on really silly ideas :-)

Best wishes Harald

"GS" skrev i melding
...
<FWIW
This was my 1st thought but this will work only as long as there's
no way the selected cell changes. The direction to move must be set
to 'none' so the cell's address can be tested. (I actually prefer
no cell change on 'Enter', but that's just me! Others may have a
problem with this approach and so is why I didn't mention it)

You are probably right, Garry, I did not follow the lead and
investigate its implications.If activecell changes before onkey fires
then this gets a bit complicated.

Also, I agree that overriding default or chosen behavior is a bad
idea. But I know Charlotte as a reflected person, I'm sure that there
is an acceptable reason for this design.

Some of my best solutions are based on really silly ideas :-)

Best wishes Harald

Thanks, Harald!
I'm confident that Charlotte will work it out. I usually promote using
the Tab key for navigation, and the Enter key when commiting input. It
seems to work fairly well for me and so I just stick with it. What's
more difficult is getting users to read/use the userguide, which
explains everything about using the app!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Hi there Harald

Long time gone since I see your name.
How you doing?

Gord

On Fri, 25 Oct 2013 20:30:15 +0200, "Harald Staff"
wrote:

"GS" skrev i melding ...
<FWIW
This was my 1st thought but this will work only as long as there's no way
the selected cell changes. The direction to move must be set to 'none' so
the cell's address can be tested. (I actually prefer no cell change on
'Enter', but that's just me! Others may have a problem with this approach
and so is why I didn't mention it)

You are probably right, Garry, I did not follow the lead and investigate its
implications.If activecell changes before onkey fires then this gets a bit
complicated.

Also, I agree that overriding default or chosen behavior is a bad idea. But
I know Charlotte as a reflected person, I'm sure that there is an acceptable
reason for this design.

Some of my best solutions are based on really silly ideas :-)

Best wishes Harald

Hi Gord!

Yes, long time no see. Newsgroups are not what they used to be -so where is
everybody? Where is the prime source of first class peer to peer assistance
these days?

Everything is good here, thank you. I hope you and yours are fine as well.

Best wishes Harald


"Gord Dibben" skrev i melding
...
Hi there Harald

Long time gone since I see your name.
How you doing?

Gord