Cyberdude
03-13-2009, 05:17 PM
When I enter a value into a cell in column “C” I want the cursor to jump right 3 columns to column “F”. I use event processing to implement this abnormal cursor move:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColNum&
Application.EnableEvents = False
ColNum = Target.Column
Select Case ColNum
Case 3: ActiveCell.Offset(0, 2).Select '<--Moves to column "F"
Case 6: ActiveCell.Offset(0, 2).Select
Case 9: ActiveCell.Offset(0, 2).Select
End Select
Application.EnableEvents = True
End Sub If I want the cursor to move right 3 columns, then the “OFFSET” command must specify 2 (not 3).
This code works as expected, except when I press the CLEAR button to clear the cell then the move to the right is one less cell (2 in this example) than I expected. I discovered that the problem is that when clearing the value from a cell it leaves the cursor over the cell. In this example, the number of cells that the cursor moves is one less than the “OFFSET” statement specifies.
So in normal operation, when I enter a value into a cell, then the cursor moves right by 1 cell. If I then clear the value from the cell, the cursor remains over the cell cleared.
Is this the way that Excel is supposed to work? How can I write the logic so that clearing a cell will cause the cursor to move the same number of cells as it does when I enter a value into the cell?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColNum&
Application.EnableEvents = False
ColNum = Target.Column
Select Case ColNum
Case 3: ActiveCell.Offset(0, 2).Select '<--Moves to column "F"
Case 6: ActiveCell.Offset(0, 2).Select
Case 9: ActiveCell.Offset(0, 2).Select
End Select
Application.EnableEvents = True
End Sub If I want the cursor to move right 3 columns, then the “OFFSET” command must specify 2 (not 3).
This code works as expected, except when I press the CLEAR button to clear the cell then the move to the right is one less cell (2 in this example) than I expected. I discovered that the problem is that when clearing the value from a cell it leaves the cursor over the cell. In this example, the number of cells that the cursor moves is one less than the “OFFSET” statement specifies.
So in normal operation, when I enter a value into a cell, then the cursor moves right by 1 cell. If I then clear the value from the cell, the cursor remains over the cell cleared.
Is this the way that Excel is supposed to work? How can I write the logic so that clearing a cell will cause the cursor to move the same number of cells as it does when I enter a value into the cell?