jump from a cell to the next appropriate cell

[Terry Cotton]

I have built up an invoice sheet. Many of the cells are not required so I
have lock/protected them
How do I get jump from only the unlocked cells. Not be able to 'land on' a
protected cell
or
using the tab or enter key, to jump from a cell to the next appropriate
cell?
Can I control this flow?
Thanks
Terry

 

[Max]

Have you applied sheet protection [Tools > Protection > Protect sheet] ?

When protection is applied, pressing the tab key repeatedly
will jump the cursor straight to / from an unlocked cell to the next
unlocked cell
(it will cycle through all unlocked cells in the sheet in a "clockwise"
fashion)

But I'm not sure how the cycle / flow can be controlled, though ..

 

[Gord Dibben]

Terry

To TAB to desired cells.......

1. Unlock the cells you want to TAB to then protect the worksheet.

If your unlocked cells are in a left to right, top to bottom series, the TAB
key will move you through them as long as Sheet Protection is enabled.

2. If not in this configuration.....you can do it with a Named Range and no
sheet protection.

Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.

Select the Second cell(B2) you want in the range then CRTL + click your way
through the range in the order you wish, ending with the First cell(A1). Name
this range under Insert>Name>Define>OK.

Now click on NameBox(top left corner above row 1 and col A), select the range
name to highlight the range. With these cells selected, you can input data
and Tab or Enter your way through the range in the order you selected.

Note: there is a limit of about 25 - 30 cells to a range using this method due
to a 255 character limit in a named range. Longer sheet names will reduce the
number of cells considerably.

If more needed, you can enter them manually in thr "refers to" box.

From Debra Dalgleish.....

The limit is 255 characters in the Name definition. For example, I can
define a range of 46 non-contiguous cells, with the following string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4,$B$6,$D$6,$F$6,$H$6,
$J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$12,
$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$B$16,$D$16,$F$16,$H$16,
$J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3

There is a third method which requires VBA and a Worksheet_Change event.

''moves from C2 through E5 at entry
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

Gord Dibben Excel MVP

 

[ElsiePOA]

From your post, I inferred that you had tried to jump from unprotecte
cell to unprotected cell using the tab key and that it hadn't worked.


If you follow the advice given in the responses to your post and you
tab key doesn't act like it should, go to "Options","Transition" an
uncheck the box next to "Transition navigation keys