allow entries only to specific cells
 

In an excel 2003 spreadsheet, I want to allow data entry to specific cells.
What I would like to occur is when data is entered in a specific cell, the
curser will go to another specific non-adjacent cell. Example: Cell D4 -
after data is entered and the enter key is hit, curser would go to D6, after
information entered curser would go to H8, ect.

allow entries only to specific cells
 

all the cells are locked by default. unlock the cells you want to allow entries
into. protect the sheet. then tab will cycle you through the unprotected cells.

--


Gary


"Ddemo1953" wrote in message
...
In an excel 2003 spreadsheet, I want to allow data entry to specific cells.
What I would like to occur is when data is entered in a specific cell, the
curser will go to another specific non-adjacent cell. Example: Cell D4 -
after data is entered and the enter key is hit, curser would go to D6, after
information entered curser would go to H8, ect.

allow entries only to specific cells
 

Gerry's method using protection is fine if your order is left to right and top
to bottom.

If not in that order, you will need another method.

See Bob Phillip's site for details.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You can also use event code to jump to cells after entry.

Post back if that would interest you.


Gord Dibben MS Excel MVP

On Sat, 19 May 2007 11:32:01 -0700, Ddemo1953
wrote:

In an excel 2003 spreadsheet, I want to allow data entry to specific cells.
What I would like to occur is when data is entered in a specific cell, the
curser will go to another specific non-adjacent cell. Example: Cell D4 -
after data is entered and the enter key is hit, curser would go to D6, after
information entered curser would go to H8, ect.

allow entries only to specific cells
 

Let me know about the event code please. I want this to be as user friendly
as possible. Thanks

"Gord Dibben" wrote:

Gerry's method using protection is fine if your order is left to right and top
to bottom.

If not in that order, you will need another method.

See Bob Phillip's site for details.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You can also use event code to jump to cells after entry.

Post back if that would interest you.


Gord Dibben MS Excel MVP

On Sat, 19 May 2007 11:32:01 -0700, Ddemo1953
wrote:

In an excel 2003 spreadsheet, I want to allow data entry to specific cells.
What I would like to occur is when data is entered in a specific cell, the
curser will go to another specific non-adjacent cell. Example: Cell D4 -
after data is entered and the enter key is hit, curser would go to D6, after
information entered curser would go to H8, ect.

allow entries only to specific cells
 

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
End Select
End Sub

Or this more robust code from Anne Troy.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy 's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10")

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
End Sub

These are both sheet events. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

NOTE: you can have only one WorkSheet_Change event per module.

I would go with Anne's version if you have quite a few cells. Just edit the

aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10") to suit.


Gord

On Sun, 20 May 2007 17:39:01 -0700, Ddemo1953
wrote:

Let me know about the event code please. I want this to be as user friendly
as possible. Thanks

"Gord Dibben" wrote:

Gerry's method using protection is fine if your order is left to right and top
to bottom.

If not in that order, you will need another method.

See Bob Phillip's site for details.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

You can also use event code to jump to cells after entry.

Post back if that would interest you.


Gord Dibben MS Excel MVP

On Sat, 19 May 2007 11:32:01 -0700, Ddemo1953
wrote:

In an excel 2003 spreadsheet, I want to allow data entry to specific cells.
What I would like to occur is when data is entered in a specific cell, the
curser will go to another specific non-adjacent cell. Example: Cell D4 -
after data is entered and the enter key is hit, curser would go to D6, after
information entered curser would go to H8, ect.