Using the TAB Key from Entry Field to Entry Field

[insearchuvu]

I have discovered that I am a REALLY REALLY NOVICE Excel USER. In my
mind I was pretty smug about the simplicity of formulae until I began
to develop some tools as a consultant to a client with whom I have been
working for the past several months.

I have to admit that I don't even know some of the shortcuts to avoid
going to the dropdown menus for so many of my tasks.

BUT, here's my Question with a hopefully "Simple" Solution.

I have created a Workbook with each sheet representing a progressive
process of performing a moving estimate at the home of a client. There
are eight (8) sheets with only a single "page" to each sheet except one
which is two "pages" or "screens".

The employee who does the client's estimating would prefer to use the
TAB Key to move about the "page" and, more specifically, move from one
"entry field" to the next.

However, due to the difference in the information gathered from one
sheet to the next, they are all formatted quite differently and any one
line of entry information is followed by either empty rows for spacing
or text rows for information before you get to the next entry field.
There are a few instances where the entry fields go across the screen
or row before dropping down to a lower row.

What I am afraid of is that each page would require its own VBA Macro
linked to the TAB Key to accomplish this and that is way beyond me for
the moment since this client has a number of other tasks they are
anxious for me to move on to but, It's these little requests that keep
a client happy.

Is there a simple way to "link" one entry field to the next with the
use of the TAB Key to JUMPING ACROSS EMPTY CELLS either to the right or
DOWN OVER EMPTY CELLS on the page TO THE NEXT ENTRY CELL skipping the
cells that serve only as spaces or paragraphs of text ?

 

[William]

Hi

If you know which cells will require data input, try the following for each
sheet.............

1) Select all cells within the worksheet
2) Select "Format>Cells>Protection", place a tick in "Locked" and select
"OK"
3) Select all the cells require data input
4) Select "Format>Cells>Protection", remove the tick from "Locked" and
select "OK"
5) Protect the sheet
6) Advise your client to select "Tools>Options>Edit" and remove any tick
from "Move Selection After Enter" option.

Pressing the Tab key will move the cursor from one unprotected cell to the
next.

If you want to do this via code, say, when the workbook is opened, you could
try something like the following. Amend sheet names and ranges as
appropriate (and you may want to restore the "Move After Selection" setting
to its original setting when the workbook is deactivated). Place the code in
the "ThisWorkbook" module, not a general module

Private Sub Workbook_Open()
Dim r As Range
With Sheets("Sheet2")
Set r = .Range("D6:H42,A1:A25")
..Unprotect
..Cells.Locked = True
r.Locked = False
..Protect
End With

With Sheets("Sheet1")
Set r = .Range("H77:H142,AA1:AA125")
..Unprotect
..Cells.Locked = True
r.Locked = False
..Protect
End With
'and so on for each sheet

Application.MoveAfterReturn = False
End Sub




--
XL2002
Regards

William
(e-mail address removed)

| I have discovered that I am a REALLY REALLY NOVICE Excel USER. In my
| mind I was pretty smug about the simplicity of formulae until I began
| to develop some tools as a consultant to a client with whom I have been
| working for the past several months.
|
| I have to admit that I don't even know some of the shortcuts to avoid
| going to the dropdown menus for so many of my tasks.
|
| BUT, here's my Question with a hopefully "Simple" Solution.
|
| I have created a Workbook with each sheet representing a progressive
| process of performing a moving estimate at the home of a client. There
| are eight (8) sheets with only a single "page" to each sheet except one
| which is two "pages" or "screens".
|
| The employee who does the client's estimating would prefer to use the
| TAB Key to move about the "page" and, more specifically, move from one
| "entry field" to the next.
|
| However, due to the difference in the information gathered from one
| sheet to the next, they are all formatted quite differently and any one
| line of entry information is followed by either empty rows for spacing
| or text rows for information before you get to the next entry field.
| There are a few instances where the entry fields go across the screen
| or row before dropping down to a lower row.
|
| What I am afraid of is that each page would require its own VBA Macro
| linked to the TAB Key to accomplish this and that is way beyond me for
| the moment since this client has a number of other tasks they are
| anxious for me to move on to but, It's these little requests that keep
| a client happy.
|
| Is there a simple way to "link" one entry field to the next with the
| use of the TAB Key to JUMPING ACROSS EMPTY CELLS either to the right or
| DOWN OVER EMPTY CELLS on the page TO THE NEXT ENTRY CELL skipping the
| cells that serve only as spaces or paragraphs of text ?
|
|