Sheet sub to fire after DV selection is changed

I'm using the sheet sub below to auto-scroll horizontally
In C3 is a formula: =ADDRESS(5,MATCH(C2,G5:HJ5,0)+6,4)
In C2 is a DV which selection feeds into C3 for the resulting cell address

Despite calculations set to auto mode, I find that after I select the DV, I
still have to click inside C3, then press ENTER before the auto-scroll sub
will work.

How could the sub be amended to work straight off after the DV selection is
changed?
Thanks for insights
----------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Address = Range("C3").Address Then
Application.Goto Range(Target.Value), True
End If
Exit Sub

Handler:
Dim msg As String
Select Case Err.Number
Case 1004
msg = "Probably invalid address"
Case Else
msg = "Unknown error'"
End Select
MsgBox msg
End Sub
--------

GarysStudent
Thu Jul 24 21:23:09 CDT 2008

The problem is that when you select the DV in C2, the event macro sees Target
as C2 and not C3.

Either use the Calculate event to monitor C3 or use the Change event to
monitor C2.

I suggest the latter. Once C2 is manually changed, disable events, do an
application.calculate, enable events, and then use Range("C3").Address for
the goto.
--
Gary''s Student - gsnu2007j


"Max" wrote:

> I'm using the sheet sub below to auto-scroll horizontally
> In C3 is a formula: =ADDRESS(5,MATCH(C2,G5:HJ5,0)+6,4)
> In C2 is a DV which selection feeds into C3 for the resulting cell address
>
> Despite calculations set to auto mode, I find that after I select the DV, I
> still have to click inside C3, then press ENTER before the auto-scroll sub
> will work.
>
> How could the sub be amended to work straight off after the DV selection is
> changed?
> Thanks for insights
> ----------
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo Handler
> If Target.Address = Range("C3").Address Then
> Application.Goto Range(Target.Value), True
> End If
> Exit Sub
>
> Handler:
> Dim msg As String
> Select Case Err.Number
> Case 1004
> msg = "Probably invalid address"
> Case Else
> msg = "Unknown error'"
> End Select
> MsgBox msg
> End Sub
> --------
>
>
>

Max
Thu Jul 24 21:46:20 CDT 2008

Thanks, GS. I'm game to try, but need help with doing up the revised sheet
sub.

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:8698A631-8674-4804-85D4-E20C3BABB660@microsoft.com...
> The problem is that when you select the DV in C2, the event macro sees
> Target
> as C2 and not C3.
>
> Either use the Calculate event to monitor C3 or use the Change event to
> monitor C2.
>
> I suggest the latter. Once C2 is manually changed, disable events, do an
> application.calculate, enable events, and then use Range("C3").Address for
> the goto.
> --
> Gary''s Student - gsnu2007j

GarysStudent
Thu Jul 24 22:11:17 CDT 2008

Check back tomorrow
--
Gary''s Student - gsnu2007j


"Max" wrote:

> Thanks, GS. I'm game to try, but need help with doing up the revised sheet
> sub.
>
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:8698A631-8674-4804-85D4-E20C3BABB660@microsoft.com...
> > The problem is that when you select the DV in C2, the event macro sees
> > Target
> > as C2 and not C3.
> >
> > Either use the Calculate event to monitor C3 or use the Change event to
> > monitor C2.
> >
> > I suggest the latter. Once C2 is manually changed, disable events, do an
> > application.calculate, enable events, and then use Range("C3").Address for
> > the goto.
> > --
> > Gary''s Student - gsnu2007j
>
>
>

Bob
Fri Jul 25 03:58:21 CDT 2008

Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = Me.Range("C3")
On Error GoTo Handler
If Target.Address = Range("C3").Address Then
Application.Goto Range(Target.Value), True
End If
Exit Sub

Handler:
Dim msg As String
Select Case Err.Number
Case 1004
msg = "Probably invalid address"
Case Else
msg = "Unknown error'"
End Select
MsgBox msg
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Max" <demechanik@yahoo.com> wrote in message
news:OGiM9Bg7IHA.2064@TK2MSFTNGP02.phx.gbl...
> Thanks, GS. I'm game to try, but need help with doing up the revised sheet
> sub.
>
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in
> message news:8698A631-8674-4804-85D4-E20C3BABB660@microsoft.com...
>> The problem is that when you select the DV in C2, the event macro sees
>> Target
>> as C2 and not C3.
>>
>> Either use the Calculate event to monitor C3 or use the Change event to
>> monitor C2.
>>
>> I suggest the latter. Once C2 is manually changed, disable events, do an
>> application.calculate, enable events, and then use Range("C3").Address
>> for
>> the goto.
>> --
>> Gary''s Student - gsnu2007j
>
>

Max
Fri Jul 25 08:18:48 CDT 2008

Many thanks, Bob. That worked well.