Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for specific entries in a column | Excel Discussion (Misc queries) | |||
Create a drop down box in a column with specific entries ? | Excel Worksheet Functions | |||
Making labels out of specific entries in Excel | Excel Worksheet Functions | |||
How do I take data entries and assign them to specific cells that. | Excel Discussion (Misc queries) | |||
Removing specific entries from comboboxes | Excel Programming |