Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet sub to fire after DV selection is changed
Many thanks, Bob. That worked well.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change does it fire for a cell changed via foruma? | Excel Programming | |||
ListBox Selection to fire Macro | Excel Programming | |||
What was ActiveSheet before changed to new sheet? | Excel Programming | |||
Fire events for all comboboxes in my sheet | Excel Programming | |||
Essbase Causing Selection Change Event to Fire | Excel Programming |