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

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

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