How to specify tab through ranges

T

tsac1234

I would like to know how to rearrange the direction in which users ta
through my worksheet. Rather than tabbing through strictl
horizontally, I would like to allow users to tab through specific cel
ranges then move on to the next unlocked range. For example, rathe
than tabbing horizontally from A10 through H20, the first range to b
filled or tabbed through would be A10 through C20. The second rang
would be F10 through H20. The worksheet has many locked cells and i
protected.

I appreciate the help
 
G

Gord Dibben

Assume you start in A10. Use TAB to go to B10 and C10. Hit ENTER key and you
will go back to column A one row down(A11).

If you start in F10 and TAB across to H10 then ENTER, you will go back to F11.

Other methods.

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
Click to expand...

In your case I might be inclined to go with 2 named ranges...rng1 for A10:C20

and rng2 for F10:H20

Gord Dibben Excel MVP
 
Top