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.

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" <(E-Mail Removed)> wrote in message
news:BD787D53-BA64-406C-883F-(E-Mail Removed)...
> 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.

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.
--
Gary''s Student - gsnu200824


"nathan_savidge" wrote:

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

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" <(E-Mail Removed)> wrote in message
news:61698708-E8B4-4C14-ACC1-(E-Mail Removed)...
> 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.
> --
> Gary''s Student - gsnu200824
>
>
> "nathan_savidge" wrote:
>
>> 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.

Thanks.
This is very useful advice.
--
Gary''s Student - gsnu200824


"Harald Staff" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:61698708-E8B4-4C14-ACC1-(E-Mail Removed)...
> > 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.
> > --
> > Gary''s Student - gsnu200824
> >
> >
> > "nathan_savidge" wrote:
> >
> >> 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.
>
>

Many thanks

"Harald Staff" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:BD787D53-BA64-406C-883F-(E-Mail Removed)...
> > 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.
>
>

Many thanks

"Gary''s Student" wrote:

> 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.
> --
> Gary''s Student - gsnu200824
>
>
> "nathan_savidge" wrote:
>
> > 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.