Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
When a cell is selected, move to a different cell
I have a large excel 2007 spreadsheet for data entry. My users are highly
accustomed to using Enter rather than Tab to move right across each row from columns A to V to enter data, so Enter is set to move the active cell Right. When users get to column V they may or may not enter data, but they need to return to column A of the next row. To facilitate data entry, I put the text "go back" in all cells of column W, and used the code below to move the active cell back to column A of the next row when a user hits Enter and moves from column V to column W. Users really like this function. The PROBLEM: If anyone selects more than one cell of a row or column on any place in the worksheet, run-time error 13 occurs. Any solutions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value = "go back" Then Cells(Target.Row + 1, 1).Select End If End Sub -- JM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
When a cell is selected, move to a different cell
JM wrote:
I have a large excel 2007 spreadsheet for data entry. My users are highly accustomed to using Enter rather than Tab to move right across each row from columns A to V to enter data, so Enter is set to move the active cell Right. When users get to column V they may or may not enter data, but they need to return to column A of the next row. To facilitate data entry, I put the text "go back" in all cells of column W, and used the code below to move the active cell back to column A of the next row when a user hits Enter and moves from column V to column W. Users really like this function. The PROBLEM: If anyone selects more than one cell of a row or column on any place in the worksheet, run-time error 13 occurs. Any solutions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value = "go back" Then Cells(Target.Row + 1, 1).Select End If End Sub Give this a try. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim GoBack As Range Set GoBack = Range("W:W") If Not Application.Intersect(Target, GoBack) Is Nothing Then If Application.Intersect(Target, GoBack).Value = "go back" Then Cells(Target.Row + 1, 1).Select End If End If End Sub Actually, this can be simplified. You can remove the "go back" labels in column W and just test to see if the user hit that column: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim GoBack As Range Set GoBack = Range("W:W") If Not Application.Intersect(Target, GoBack) Is Nothing Then Cells(Target.Row + 1, 1).Select End If End Sub Nice productivity hack, BTW. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
When a cell is selected, move to a different cell
Your simplified approach works great. Thanks.
-- JM "smartin" wrote: JM wrote: I have a large excel 2007 spreadsheet for data entry. My users are highly accustomed to using Enter rather than Tab to move right across each row from columns A to V to enter data, so Enter is set to move the active cell Right. When users get to column V they may or may not enter data, but they need to return to column A of the next row. To facilitate data entry, I put the text "go back" in all cells of column W, and used the code below to move the active cell back to column A of the next row when a user hits Enter and moves from column V to column W. Users really like this function. The PROBLEM: If anyone selects more than one cell of a row or column on any place in the worksheet, run-time error 13 occurs. Any solutions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value = "go back" Then Cells(Target.Row + 1, 1).Select End If End Sub Give this a try. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim GoBack As Range Set GoBack = Range("W:W") If Not Application.Intersect(Target, GoBack) Is Nothing Then If Application.Intersect(Target, GoBack).Value = "go back" Then Cells(Target.Row + 1, 1).Select End If End If End Sub Actually, this can be simplified. You can remove the "go back" labels in column W and just test to see if the user hit that column: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim GoBack As Range Set GoBack = Range("W:W") If Not Application.Intersect(Target, GoBack) Is Nothing Then Cells(Target.Row + 1, 1).Select End If End Sub Nice productivity hack, BTW. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hit enter in cell & move text down in cell, not go to cell below. | Excel Worksheet Functions | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
Excel sheets should move while selected cell stays in middle | Excel Discussion (Misc queries) | |||
how read value from last selected cell? It is possible? how get adress last selected cell? | New Users to Excel | |||
The selected area grows when I click a cell or press keys to move. | Excel Discussion (Misc queries) |