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

Sheet sub to fire after DV selection is changed
 

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

Sheet sub to fire after DV selection is changed
 

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

"Gary''s Student" wrote in message
...
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

Sheet sub to fire after DV selection is changed
 

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" wrote in message
...
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

Sheet sub to fire after DV selection is changed
 

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" wrote in message
...
Thanks, GS. I'm game to try, but need help with doing up the revised sheet
sub.

"Gary''s Student" wrote in
message ...
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

Sheet sub to fire after DV selection is changed
 

Many thanks, Bob. That worked well.