Move to next cell (not adjacent) for data entry

**Winon** · 02-20-2007, 02:26 PM

Hi, I am new to this forum, so please be patient, as I will try to explain what need I help. Thank you!

I have created a spreadsheet into which users must enter data in cells scattered all over the sheet. How do I write a formula in visual basic to start at the first (active)cell and automatically 'jumps' to the next(active) cell for data entry if the user hits 'enter'? The user may or may not enter any 'value' in any of these cells, but it would still be very helpful if the next cell for data entry is activated when one hits enter.

I have battled for a long time with this problem, and maybe it is just not possible.?

Anyone, who can help? Pleeeze!

Many Thanks

**Conor** · 02-20-2007, 02:40 PM

is a userform not feasible? you mention that the data entry fields are scattered, but do they have to be?

**Conor** · 02-20-2007, 02:42 PM

or how about simply locking the cells that are used for entry and protecting the worksheet. Then with "tab" they will jump to the next unlocked cell. "Enter" may also work; I'm not sure.

**Conor** · 02-20-2007, 02:44 PM

That is "unlocking" the cells for entry and locking the others.

**Winon** · 02-21-2007, 08:01 PM

Hi Conor,

Thank you so much for your advice. Are you the only one who understands my problem? 'cause nobody else replied. I have tried the locking of the cells in the spreadsheet whilst unlocking the cells for data entry, and saving it under protect sheet with a password. Problem is that even with 'Tab' It does not follow the anticipated route to the unlocked cells. Using 'Enter' follows the settings for scroll to next cell 'left', 'right', 'up' or 'down'.

Unfortunately the cells can not be 'unscattered' as the data entries of the user, is analysed in a predetermined sequence of events, and the users' response thereto.

I cannot see that a Userform will work as the sheet contains information in all the other cells that are locked to which users must respond in the unlocked cells.(Duplicating the problem, don't you agree)

Can you think of something else maybe?

**Paul** · 02-21-2007, 08:28 PM

As I see it you have two options:

1. Create a seperate worksheet in your workbook with 10, 20 or xxx cells in a column that the user can enter for their data entry, then link those cells back to the scattered cells on the other sheets. Should be easy enough to do.

2. More difficult and time consuming: Create VBA code that captures the Enter or Tab key when exiting cell #1 and set the focus on scattered cell #2. Repeat the process for scattered cells 3 through xx.

**BradC** · 02-22-2007, 05:01 AM

When you protect the sheet by going to Tools --> Protect sheet, you have to un-select the "Select Locked Cells" option. Then conor's tab trick will work.

**Winon** · 03-11-2007, 06:44 AM

Originally Posted by pjoaquin

As I see it you have two options:

1. Create a seperate worksheet in your workbook with 10, 20 or xxx cells in a column that the user can enter for their data entry, then link those cells back to the scattered cells on the other sheets. Should be easy enough to do.

2. More difficult and time consuming: Create VBA code that captures the Enter or Tab key when exiting cell #1 and set the focus on scattered cell #2. Repeat the process for scattered cells 3 through xx.

Thanks Paul,

Consideration 1 is not an option to solve my unique problem, but point no.2 seems to be my only way out. However, I am not all that familiar with VBA to even want to try it without some guidance as to where or how to begin.
Could you maybe give me a "Jump-start" on the entering of that procedure?
Many thanks.

**Winon** · 03-11-2007, 06:48 AM

Originally Posted by BradC

When you protect the sheet by going to Tools --> Protect sheet, you have to un-select the "Select Locked Cells" option. Then conor's tab trick will work.

Thanx-A-Lot Brad! I have tried your suggestion but it does not solve my specific problem.