Controlling which cell becomes ACTIVE (gains focus). 2

[nat101]

Hi.
Within a sheet I have about 10 cells spread out which I would like to optionally keep in a navigation 'ring', ie, if the operator hits TAB (or F12), the next one of these cells should become active. And likewise for SHIFT-TAB, backwards.
To clarify; Cells A4, B2, G8, C2, Q5...In these cells ONLY, the TAB (or F12) key should activate the next one of these cells, from A4 to B2 to G8 to C2 to Q5.
Please note that I do NOT want to lock the cursor movement only in this 'ring'. The operator should be able to go from A4 to A5/A2/A3 via regular arrow keys. (Unlike protecting everything except these cells.)

Thank you all
-Nat

 

[xlbo]

So if they go outside the "loop" - how would you know which cell In the "loop" to go back to when they press TAB again - what are the rules here ?

 

[larryww]

You might look at VBA and Worksheet_SelectionChange, maybe working off of activecell.Row etc.

This may also well fit using a Form. Then they just fill in the form sequentially (again, you need to know VBA).

But of course I've skipped what might well be the obvious answer - protecting or disabling cells. However that would mean understanding Excel's clueless, backward implementation of cell & sheet protection, which is terrible.

 

[nat101]

xlbo;
They can go anywhere on the sheet. So, anywhere else TAB is a simple TAB. If they are in one of these 'ring' cells however, they should be able to express-shuttle to the next/previous in the ring, which they would want to do 85% of the time. (The other 15% of the time they need to adjust cells out of this ring, hence the need to allow 'regular' navigation.)

Hey! Just had an idea. Why not do a macro that checks current cell and activates the next one in the ring. Another macro would activate the previous one. Assign 1 macro to ctrl-f12, the other to ctrl-f11. Wadda you (and larry) think?
Furthermore I can then have two nice-sized buttons on the sheet labelled Next & Previous, to facilitate equeal oppurtunuty for the mouse.
-Nat

 

[larryww]

That sounds fine. I did not find a KeyPress or KeyDown facility for Excel macros, which might have been nice to separate arrow from tab keypresses, so the unambiguous button sounds good.

And to whoever: gee, thanks for the uncredited editing someone did to my prior post. Henceforth, I'll refrain from the popular variation of the fairly widespread phrase "bass-ackward"

 

[SkipVought]

Hi,
If you PROTECT a sheet, where there are cell that are UNLOCKED, then TAB will cycle thru all the UNLOCKED cells.

It's that simple Skip,
metzgsk@voughtaircraft.com

 

[nat101]

Aye Skip.
Evidently you didn't read my post(s). Anyhow, as long as its simple.
-Nat

 

[larryww]

no, Skip is always careful and accurate and for some people he gives great answers. You might be able to use his latest. You can incorporate his suggestion by 'buttoning' with a "toggle button" to turn protection on and off. I think that's the cleanest approach you can get, even with the counter anti-intuitive cell-protection scheme in Excel.

 

[nat101]

Hey. Skip has helped me quite a bit. That's why I thought he likely didn't read my post. But his way is kinda neat, huh? I guess the macro the protects the sheet will also have to unlock the specific cells. In other words, once unprotected Excel will might forget about the unlocked cells, so every time its protected these cell need to be unlocked. Is this true?

Anyhow, both of you deserve a helpful-post vote!
-Nat