PDA

View Full Version : Solved: Move to next cell



Philcjr
10-25-2005, 07:53 AM
All,

Need some help, I have a spreadsheet that I need to collect numbers from the end user.

I would like to move to the next cell once they have entered a number into the cell (and only a number).

Example: (Cells that will have one number in each cell)
F14, H14, J14, L14, N14, P14
F16, H16, J16, L16, N16, P16
F18, H18, J18, L18, N18, P18
F20, H20, J20, L20, N20, P20

Constraints:
The user will only enter numbers into one row... either row 14, 16, 18 or 20 NEVER more than one row

Is this a good time to use arrays?????

Any help would be greatly apprecitated.

Phil

MWE
10-25-2005, 10:16 AM
All,

Need some help, I have a spreadsheet that I need to collect numbers from the end user.

I would like to move to the next cell once they have entered a number into the cell (and only a number).

Example: (Cells that will have one number in each cell)
F14, H14, J14, L14, N14, P14
F16, H16, J16, L16, N16, P16
F18, H18, J18, L18, N18, P18
F20, H20, J20, L20, N20, P20

Constraints:
The user will only enter numbers into one row... either row 14, 16, 18 or 20 NEVER more than one row

Is this a good time to use arrays?????

Any help would be greatly apprecitated.

Phil
Attached is an example spreadsheet that does most of what you want. If the user enters data in any of the above cells, the WorkSheet_Change function selects the next logical cell. If data is entered into the last cell in the row, the "next" cell is "F" (easy to change that).

The procedure does not force data entry to a single row (that could be added), nor does it disallow entry into "other" cells (that could be added too), nor does it force data entry into the selected cell (that could be added).

The target cells are green just to make things easier to see.

Let me know if this is useful and if any additonal controls are necessary.

mdmackillop
10-25-2005, 11:02 AM
And for an alternative, here's a Userform solution.

Philcjr
10-25-2005, 11:28 AM
MWE,

PERFECT!! :clap:

MDmackillop,

Also perfect, however, the use of a user form in my speadsheet is not needed. But, THANK YOU anyway. :thumb


One last question, as I am still wet behind the ears when it comes to VBA... is there a was to "nudge" the user to the next cell without them having to hit the "enter key"?

Phil

mdmackillop
10-25-2005, 11:51 AM
Hi Phil,
If you can live with coloured cells and a few navigation glitches (as you will see), this will move with the Enter key or right arrow.

Philcjr
10-25-2005, 12:39 PM
MDmackillop,

Again, awesome!

Thanks for all your help and time. I have presented the file to the end-users and they are happy :cool:

Thank you!
Phil

mdmackillop
10-25-2005, 03:05 PM
I have presented the file to the end-users and they are happy.


I wonder what's wrong with the Enter key! .... but anything to keep the end users happy.

Regards
MD