Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer runs
I have the following code in my worksheeet:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False SetRisingRent Application.EnableEvents = True End Sub When I change any value in the specified range it automatically runs the SetRisingRent macro (a goal seek calculation), but as of today a fault occurred due to bad data and it suddenly stopped running automatically. I traced the failure to run automatically to Application.EnableEvents = False having run but not the later line setting it back to true. Can I trap this possibility in my code? In Delphi I am used to a structure like below where the code in the "finally" block is always executed even if a fault occurs but I can't see an equivalent in VB: Private sub .... If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False try SetRisingRent finally Application.EnableEvents = True end |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer runs
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ws_exit: If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False SetRisingRent ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Lorne" wrote in message ... I have the following code in my worksheeet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False SetRisingRent Application.EnableEvents = True End Sub When I change any value in the specified range it automatically runs the SetRisingRent macro (a goal seek calculation), but as of today a fault occurred due to bad data and it suddenly stopped running automatically. I traced the failure to run automatically to Application.EnableEvents = False having run but not the later line setting it back to true. Can I trap this possibility in my code? In Delphi I am used to a structure like below where the code in the "finally" block is always executed even if a fault occurs but I can't see an equivalent in VB: Private sub .... If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False try SetRisingRent finally Application.EnableEvents = True end |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer runs
Many thanks
"Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo ws_exit: If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False SetRisingRent ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Lorne" wrote in message ... I have the following code in my worksheeet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False SetRisingRent Application.EnableEvents = True End Sub When I change any value in the specified range it automatically runs the SetRisingRent macro (a goal seek calculation), but as of today a fault occurred due to bad data and it suddenly stopped running automatically. I traced the failure to run automatically to Application.EnableEvents = False having run but not the later line setting it back to true. Can I trap this possibility in my code? In Delphi I am used to a structure like below where the code in the "finally" block is always executed even if a fault occurs but I can't see an equivalent in VB: Private sub .... If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub Application.EnableEvents = False try SetRisingRent finally Application.EnableEvents = True end |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA macro runs fine, but freezes if I try to do ANYTHING else whileit runs | Setting up and Configuration of Excel | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
One macro runs then it auto runs another macro | Excel Discussion (Misc queries) | |||
Custom Toolbar Button No Longer Runs Assigned Macro | Excel Discussion (Misc queries) | |||
Which Macro Runs...? | Excel Discussion (Misc queries) |