cursor movement within a sheet

[Guest]

How can I move directly control the cursor movement after pressing enter.
For example, I would like my cursor movement to go something like this:



CELL MOVEMENT

B5 DOWN
B6 DOWN
B7 DOWN
B8 FIRST CELL IN NEXT SECTION

G5 RIGHT
H5 DOWN/LEFT
G6 RIGHT
H6 DOWN/LEFT
G7 RIGHT
H7 DOWN/LEFT
G8

I use protected worksheets allowing entry into unlocked cells.

If I set "Move selection after Enter" direction to "right" then after B5 I
end up going to G5 instead of B6. If I set "Move selection after Enter" to
"down" then my entry into the "B" column goes well but then I move from G5 to
G6 instead of H5 (which is where I really wanted to go next).

So.....

How may I more directly control cursor movement after the Enter key is
pressed?



Thanks in advance,
Janet

 

[Otto Moehrbach]

Janet
One way is to use the tab key, instead of the Enter key when you make an
entry. That way, the focus will move to the next unlocked cell. However,
with this method Excel dictates the order of the focus movement, not you.
Excel will go by rows first, then by columns. If this fits into your
scheme, good. Try it out.
There is another way. Below is a write-up I have on that method. HTH
Otto
This Way Involves Range Names:

Note that you dictate the order in which the focus changes by the order in
which you select the cells below.

Select your SECOND cell for data entry and then hold down <Ctrl> and
continue to select all the rest of the desired cells IN ORDER, ending with
the FIRST cell.

While the cells are still selected, click in the name box and give this
range a name. You can also click Insert - Name - Define and type in the
name you want.



Now, when you're ready for data entry, simply click on the range name in the
name box, or hit F5, select the range name you want, and click OK. The
focus for the range is the first cell for data input so all you have to do
now is type your data and hit enter and the focus then moves to each
successive cell in your range. Just mouse click away to break out of the
loop. HTH Otto

 

[Guest]

Well, I've only read this and haven't tried it, but it sounds to me like
there are a lot of extra keystrokes and movements that I don't want. I
simply want to be able to go to the "desired" cell once I press the enter key.

Are you saying there is no way to do some sort of post processing on a cell
that basically does something like "go to cell g5"?

 

[Gord Dibben]

Janet

You can use worksheet event code to govern the movement after you enter data and
hit the ENTER key.

'moves from C2 through E5 at entry
'add cases as needed
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
End Select
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

 

[Guest]

Thank you, Gord! This works perfectly!

Janet

 

[Gord Dibben]

Thanks for the feedback Janet.

Happy to assist.

Gord

Thank you, Gord! This works perfectly!

Janet

Gord Dibben MS Excel MVP

 

[Guest]

Gord:
I too am creating a protected worksheet allowing entry into unlocked cells.
I did as you suggested, going TAB>VIEW CODE and pasting in my edited version
of your code. (I added more cases, put in the cells' addresses, and observed
the syntax that was present.)
Nothing happened. I did get an Excel warning about security level, so I set
the level to LOW, which allows any macro to run. Still no result.
In the Visual Basic work window, there is a pulldown menu at the right side
of the title bar, with 9 options. Do I need to select one of these? Do I
need to include the worksheet or spreadsheet name in the code?
I did a lot or programming in my previous job, but never used VB. any
suggestion you (or anyone reading this) can provide would be much appreciated.
---Patrick Riley

 

[Pete_UK]

Try saving your file, closing Excel, then starting up again to see if
this has the desired effect.

Hope this helps.

Pete

 

[Guest]

I CAN REPORT SUCCESS, mostly, via 2 actions.

I had already tried shutting down Excel and reopening. First off, I simply
hadn't tested far enough. I have 18 protected cells or ranges. I start at
cell H4. I was getting hung up trying to get to the 2nd (H6) before the 3rd
(R4); it would go to the 3rd then the 2nd, and then keep yo-yo-ing between
the two, never the reaching stop #4. (All of the first 3 stops are ranges.)

What I hadn't checked was beyond the first 3 stops. Turns out the final 15
stops were working just fine.

Secondly: So how did I get past the sticking point? I decided to swap the
order between the 2nd and 3rd locked cells, and it all worked fine---thru all
18 cells. Not what I really wanted, but workable.

Maybe Excel got stymied over finding its way thru a sequence where the 1st
and the original 2nd stop (H6 and R4, respectively, and each a range) each
resided in a SINGLE (although different) row, and the original 3rd stop, R4,
spanned THREE rows plus columns. Or maybe it just wanted to go Left to Right
regardless of my code. (You probably don't care about the details, but the
original first 2 cells had pulldowns (using Data>Validation>List) and were
situated in the upper left corner of the form, while the original 3rd, for
text entry, resided in the upper right corner.)

Anyway, I thank all the contributors to this thread for guiding me thru this.
Patrick Riley

 

[Sandy Falgout]

I am having a similar problem and would appreciate any assistance that is
available.

I have copied the code and edited it accordingly. It works great for 56 cell
movements but then it loops over the last three cells. It won't move on to
the 57th cell. If I manually put the cursor in the 57th cell, it moves
through all the rest correctly.

I have tried saving, closing, and reopening but that doesn't change anything.

Any suggestions?

Also, is there a way to make the TAB button move the cursor? With the
select case statements, you must enter something into the cell before the
cursor will move. Can you make the cursor move with the cell being empty?