Automatic cell selection

[Charlotte E.]

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

 

[Claus Busch]

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.

 

[Charlotte E.]

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

 

[GS]

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

 

[Charlotte E.]

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]

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

 

[Harald Staff]

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

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:

(....)

 

[GS]

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

 

[Harald Staff]

<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]

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

 

[Gord Dibben]

Hi there Harald

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

Gord

 

[Harald Staff]

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

 

[Charlotte E.]

Newsgroups are not what they used to be -so where is everybody?

Yeps, Forums has totally destryed the Internet - especially, as you put it,
first class user-to-user assistance.

Not only do I hate Forums, forcing you to provide all kind of personal
information about yourself, just to ask a simple question, but it also seems
that each and every weksite, want their own Forum, thus all the gems get
spread around almost impossible to find - sad to see that even Microsoft
junped on the Forum waggon.

Also, I don't want the companies "help", so that "we only target the
commercials that are relevant for you, if you just provide all these
personal informations" - how about not SPAMMING the Internet at all, and
just let people help each other in an open and spam free environment!

Where is the prime source of first class peer to peer assistance these
days?

For my part, I'll stick to the NewsGroups...
Forums and blogs sucks!

CE