Direction of entry to a cell

[nathan_savidge]

Hi,

I am using the worksheet.selectionchange to skip a column of data, so if the
user enters the column (with a calculation in that is not to be changed) the
column after is selected, cells(target.row,target.column+1). This works
fine, from left to right, but from right to left, it just sticks at the
column you were in.

Is there a way of determining the direction that a cell is entered from? So
i can do +1 or -1 based on this.

TIA

Nathan.

 

[Harald Staff]

Hi Nathan

Why don't you simply lock the column and protect the sheet so that the
column can't be selected ?

Otherwise, sure, just compare to the previous target:

' *********** top of module ****************
Dim PrevTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not PrevTarget Is Nothing Then
If Target(1).Column > PrevTarget(1).Column Then
Application.StatusBar = "->"
ElseIf Target(1).Column < PrevTarget(1).Column Then
Application.StatusBar = "<-"
Else
Application.StatusBar = "-"
End If
End If
Set PrevTarget = Target
End Sub

Now look in Excels lower left corner when arrowing around.

HTH. Best wishes Harald

 

[Gary''s Student]

Good question, we just need to remember which side of the "protected" column
we came from:

Dim oldcolumn As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(oldcolumn) Then
Else
t = Target.Column
If t = 4 Then
Application.EnableEvents = False
If oldcolumn >= 4 Then
Target.Offset(0, -1).Select
Else
Target.Offset(0, 1).Select
End If
Application.EnableEvents = True
End If
End If
oldcolumn = ActiveCell.Column
End Sub

This avoids selecting a cell of column D. If we try to get to column D from
the right of column D, we end up in column C.

If we try to get to column D from the left of column D, we end up in column E.

 

[Harald Staff]

Note that Target can be multiple cells, like selecting aan area or a whole
row. If so
Target.Column
will err. I like to use Target(1) in those cases, which points to the upper
left cell in Target. But more sophisticated error handlers may be required
in more complex tasks.

Best wishes Harald

 

[Gary''s Student]

Thanks.
This is very useful advice.

 

[nathan_savidge]

Many thanks

Hi Nathan

Why don't you simply lock the column and protect the sheet so that the
column can't be selected ?

Otherwise, sure, just compare to the previous target:

' *********** top of module ****************
Dim PrevTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not PrevTarget Is Nothing Then
If Target(1).Column > PrevTarget(1).Column Then
Application.StatusBar = "->"
ElseIf Target(1).Column < PrevTarget(1).Column Then
Application.StatusBar = "<-"
Else
Application.StatusBar = "-"
End If
End If
Set PrevTarget = Target
End Sub

Now look in Excels lower left corner when arrowing around.

HTH. Best wishes Harald

 

[nathan_savidge]

Many thanks

Good question, we just need to remember which side of the "protected" column
we came from:

Dim oldcolumn As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(oldcolumn) Then
Else
t = Target.Column
If t = 4 Then
Application.EnableEvents = False
If oldcolumn >= 4 Then
Target.Offset(0, -1).Select
Else
Target.Offset(0, 1).Select
End If
Application.EnableEvents = True
End If
End If
oldcolumn = ActiveCell.Column
End Sub

This avoids selecting a cell of column D. If we try to get to column D from
the right of column D, we end up in column C.

If we try to get to column D from the left of column D, we end up in column E.