moving cursor to nect cell automatically

  • Thread starter Thread starter Andy Sandford
  • Start date Start date
A

Andy Sandford

hi

I have a data entry sheet with the relevant empty cells dotted around.

Once data has been entered in the first cell and validated, I need the
cursor to jump to the next cell automatically.

e.g. when data has been entered in cell G11, I need to jump to C17 for the
next entry.

Is there any way I can automate the path of the cursor?

Thanks in advance

Andy
 
There is a simple way to do this in VBA.

The following bit of code tells excel which cell to write in (in thi
case C9), and then asks the user what number hw would like in there.


Range("c9").Select
ActiveCell.Value = InputBox("Enter value 1")


To try this out yourself, press Alt+F11, select Insert, Procedure, the
paste the example of code shown above the end statement.

Swap back to your sheet and press Alt+F8 to try it.

If you like how this works, just add more lines to your code to selec
the relevant cells and create input boxes asking for the differen
values.

I hope this helps.

Dav
 
Hi David

Can't get it to work!

I hit Alt+F11 - the PDFwriter opens, but when I hit Insert/procedure I get a
dialog box requesting a name etc. Once I've entered this, I get a page full
of code!

I guess my question is where do I enter the code you described?

Confused 8o))

Andy
DavidObeid said:
There is a simple way to do this in VBA.

The following bit of code tells excel which cell to write in (in this
case C9), and then asks the user what number hw would like in there.


Range("c9").Select
ActiveCell.Value = InputBox("Enter value 1")


To try this out yourself, press Alt+F11, select Insert, Procedure, then
paste the example of code shown above the end statement.

Swap back to your sheet and press Alt+F8 to try it.

If you like how this works, just add more lines to your code to select
the relevant cells and create input boxes asking for the different
values.

I hope this helps.

Dave


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Andy

A couple of methods not involving Sheet Protection

You can do it with a Named Range.

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

A second method is to use Worksheet Event Code.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$G$11"
Range("C17").Select
Case "$C$17"
Range("E2").Select
Case "$E$2"
Range("A5").Select
End Select
End Sub

Copy/pasted to a worksheet module. Right-click on sheet tab and "View Code".
Paste the code in there.

Gord Dibben XL2002
 
Gord

The last example you gave - the worksheet event code - was EXACTLY what I
was looking for.

Thanks very much for your help!

Andy
 
Back
Top