Move focus to a cell after ENTER

D

docdeptmgr

Greetings all,
How can I move the focus to another cell after entering a value in th
initially defaulted cell?
When my sheet opens, focus is on a cell [C6] that the user will enter
number. This number will act as a divisor for a range of numbers.
After entering the number and hitting ENTER, I would like the focus t
move to another cell [B4].
I'd just as soon NOT make this a form, but if I must...
Kindly respond to my e-mail: [email protected].

Thanks to all in advance. I will post the results.
FJ Krasovi
 
D

Don Guillett

right click on sheet tab>view code>copy/paste this>SAVE
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$6" Then Application.Goto Range("b4")
End Sub
 
G

Gord Dibben

FJ

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

There is a third method which requires VBA and a Worksheet_Change event. Post
back if that is desirable.

Gord Dibben XL2002



Greetings all,
How can I move the focus to another cell after entering a value in the
initially defaulted cell?
When my sheet opens, focus is on a cell [C6] that the user will enter a
number. This number will act as a divisor for a range of numbers.
After entering the number and hitting ENTER, I would like the focus to
move to another cell [B4].
I'd just as soon NOT make this a form, but if I must...
Kindly respond to my e-mail: [email protected].

Thanks to all in advance. I will post the results.
FJ Krasovic
Click to expand...

Gord Dibben XL2002
 
Top