allow entries only to specific cells

G

Guest

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.
 
G

Gary Keramidas

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.
 
G

Gord Dibben

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
 
G

Guest

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

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top