Macros - Filling out a form

[Chris]

Hello,

I'm very new to creating macros in Excel and I could not
find a way to create a macro that would bounce me from cell
to cell, and wait for input at each cell. I have created a
pretty basic worksheet for internal company use, I would
just like to create this macro to make it easier for the
sales dept. to fill out the needed info. Please help if
you can, it would be greatly appreciated.

Thanks
Chris

 

[Chris]

Steve, thanks for the reply, I thought that should work
too, but for some reason when I hit tab, it jumps right to
the next column that is off screen. I don't know if
there's a setting somewhere for that, or what is happening.
It stays on the same row, no matter what cell you're in on
that row, it jumps to the same row cell that is in the next
column off screen. If there is a way to fix that, I'm all
ears.

Thanks again
Chris

 

[Gord Dibben]

Chris

First point......

The TAB jumping too far is most likely caused by having Lotus Transition
Navigation Keys checked in Tools>Options>Transition.

Second point..........

If your unlocked cells are in a left to right, top to bottom series, the TAB
key will move you through them as Stephen suggests.

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 the "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

Gord Dibben Excel MVP - XL97 SR2 & XL2002