Excel Worksheet Change Event in a nick of time

1. Enter an Excel worksheet ; To Define Name > "TagNumber" > ReferTo :=
D2.

2. Intention ; After TagNumber is updated (in a Change Event), the
focus would move to the adjacent cell to the right, Offset(0,1).

3. And therefore, the event procedure is devised as follows ;-

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Address = Range("TagNumber").Address Then
Range("TagNumber").Offset(0, 1).Activate
End If

End Sub

4. However, at run-time ; As soon as Tagnumber is activated, the focus
shifts to the adjacent cell to the right already. And therefore, in
that nick of time, TagNumber is not updated at all. No chance, you
know.

5. Please share your comment. Regards.

It should be noted that the Selection_change event occurs when you
select the cell not when you update a cell.Thus the moment you click
the cell the event happens.

The Change event is a better option since it occurs only when the cell
value is updated but it considers the activecell as the cell containing
focus.For example if you update a value in cell D2 and press enter then
the change event considers the activecell as E2 as it contains the
focus.

So you should rewrite the code.Hope this works

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Offset(-1, 0).Address = Range("TagNumber").Address Then
Range("TagNumber").Offset(0, 1).Activate
End If
End Sub

This code is written on the assumption that you always hit enter after
entering the value


If you like this solution, you may also like
http://groups.google.co.in/group/Ans...6502c223298627
http://groups.google.co.in/group/Ans...1ad015c37603ae


For more,post your questions on
http://groups.google.co.in/group/answers-for-everything
or e-mail to
[email protected]

TKT-Tang wrote:
> 1. Enter an Excel worksheet ; To Define Name > "TagNumber" > ReferTo :=
> D2.
>
> 2. Intention ; After TagNumber is updated (in a Change Event), the
> focus would move to the adjacent cell to the right, Offset(0,1).
>
> 3. And therefore, the event procedure is devised as follows ;-
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If ActiveCell.Address = Range("TagNumber").Address Then
> Range("TagNumber").Offset(0, 1).Activate
> End If
>
> End Sub
>
> 4. However, at run-time ; As soon as Tagnumber is activated, the focus
> shifts to the adjacent cell to the right already. And therefore, in
> that nick of time, TagNumber is not updated at all. No chance, you
> know.
>
> 5. Please share your comment. Regards.

Mr. Prabhuraaman,

Thank you for responding to my query.

1. Look at the following procedure :-

> Private Sub Worksheet_Change(ByVal Target As Range)
> If ActiveCell.Offset(-1, 0).Address = Range("TagNumber").Address Then
> Range("TagNumber").Offset(0, 1).Activate
> End If
> End Sub

2. Since TagNumber is cell D2 (=ActiveCell.Offset(-1, 0).Address), that
means that the worksheet has entered into the state after the
ActiveCell shifts to cell D3. That's the transient state ; thereafter,
the focus is drawn to Range("TagNumber").Offset(0, 1), in a nick of
time.

3. It's devised such that it would suit Edit Settings, Shift Selection
after Enter, by moving the Activecell one step below. However, my
preference setting (under Edit) is to remain in the prevalent cell.

4. To work around when it's inevitable, it shows manipulating the
prevalent situation to suit one's advantage ; granted liberty, it might
be tantamount to trimming one's feet to suit MS-sized shoes.

5. Feedback is essential for the benefits of other readers around this
neighbourhood.

6. Regards.