Enable Events Issue

[brenner]

The procedure below is called within all of my other procedures, including event procedures (worksheet change and worksheet selection change). Stepping through the code, the procedure the following line evaluates to true (Trim(Range("Rationale").Item(1).Text) <> "" And Trim(Range("Rationale").Item(1).Text) <> Rationale1 And Trim(Range("Rationale").Item(1).Text) <> PMRationale2). The code executes the setting events to false and colors the cell, but it doesn't execute the next line and instead jumps back to the next line following where this procedure was called. As a result, my events are never re-enabled.


Sub Rationale(Optional ListedChange As Boolean)

If ListedChange Then
If Range("IsListed") = "Yes" Then
bBypassSelectChange = True
Application.EnableEvents = False
Range("Rationale") = Rationale1
Range("Rationale").Interior.ColorIndex = ReqColorIdx
Application.EnableEvents = True
ElseIf Range("IsListed") = "No" Then
bBypassSelectChange = True
Application.EnableEvents = False
Range("Rationale") = Rationale2
Range("Rationale").Interior.ColorIndex = ReqColorIdx
Application.EnableEvents = True
Else
bBypassSelectChange = True
Application.EnableEvents = False
Range("Rationale").ClearContents
Range("Rationale").Interior.ColorIndex = ReqColorIdx
Application.EnableEvents = True
End If
Else
If Trim(Range("Rationale").Item(1).Text) <> "" And Trim(Range("Rationale").Item(1).Text) <> Rationale1 And Trim(Range("Rationale").Item(1).Text) <> Rationale2 Then
Application.EnableEvents = False
Range("Rationale").Interior.ColorIndex = StdColorIdx
Application.EnableEvents = True
Else
Call Rationale(True)
End If
End If
End Sub

 

[dmt32]

without fully understanding your code, try setting EnableEvents from the calling procedure e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 Rationale True
 Application.EnableEvents = True
End Sub
 
Sub Rationale(Optional ListedChange As Boolean)
'rest of code
End Sub

Dave

 

[brenner]

thanks dave.