Worksheet Change ByVal Target As Range triggers once then stops

[WildBurrow]

I've noticed that my code will run correctly with the first select; however, if the User goes back and changes the cell value the code will not run. Basically, they may "toggle" back and forth with their answers. When I place the Application.EnableEvents=True in the immediate window, things will re-trigger. I then found some code to handle errors ('Worksheet change events stop working after Debug? by dmt32) and tacked on an 'Error' message box. To my horror, the message box trigger for every cell noted in my code!!!! Any advice on what is going on and how to fix it?

So this my code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo myerror
'snippet of code below:
Set ws2 = Worksheets("Tables")
'If ID Type equals API format ID number:
    If Not Application.Intersect(Target, Range("G35")) Is Nothing And Range("B35").Value = "API" And ws2.Range("V3").Value = "Format" Then
        Target.NumberFormat = "00-000-0-0000-0000"
        If IsNumeric(Target.Value) = False Or Len(Target.Value) <> 14 Then
             msgbox "Numeric values only - 14 digits required."
                Application.EnableEvents = False
                Target.Value = ""
                Target.Activate
                Application.EnableEvents = True
        End If
    End If
Select Case Target.Address

Case "$I$5" 'Control access to Dashboard command buttons based upon 1509 Status
    If Target.Address <> "$I$5" Then Exit Sub
    If Target.Value = "1509 Related" And Range("I8").Value = "Stray Gas" Then
        Worksheets("Dashboard").CommandButton10.Enabled = True
        Worksheets("Dashboard").CommandButton2.Enabled = False
        Worksheets("Dashboard").CommandButton3.Enabled = False
        Worksheets("Dashboard").CommandButton4.Enabled = False
    End If

'Remediation#1 Completed interior color change if not N/A
Case "$O$164"
    If Target.Address <> "$O$164" Then Exit Sub
    If Target.Value <> "" Then
        Range("S164").Interior.Color = RGB(255, 255, 0)
    End If

Case "$S$164"
    If Target.Address <> "$S$164" Then Exit Sub
    If Target.Value = "No" Then
        Target.Interior.Color = RGB(255, 255, 255)
        Range("W164").Interior.Color = RGB(231, 230, 230)
        Range("AA164").Interior.Color = RGB(231, 230, 230)
    ElseIf Target.Value = "Yes" Then
        Target.Interior.Color = RGB(255, 255, 255)
        Range("W164").Interior.Color = RGB(255, 255, 0)
        Range("AA164").Interior.Color = RGB(255, 255, 0)
    End If
    
Case "$W$164"
    If Target.Address <> "$W$164" Then Exit Sub
    If Target.Value <> "" Then
        Target.Interior.Color = RGB(255, 255, 255)
    End If
 
Case "$AA$164"
    If Target.Address <> "$AA$164" Then Exit Sub
    If Target.Value <> "" Then
        Target.Interior.Color = RGB(255, 255, 255)
    End If
    
    If Target.Value = "Yes" Then 'Store temporary change value to audit attachment requirements
        Range("I272").Value = True
    ElseIf Target.Value <> "Yes" Then
        Range("I272").Value = False
    End If

'a ton of code here
Case Else
Exit Sub
End Select

myerror:
Application.EnableEvents = True
msgbox "Error"
End Sub

 

[6StringJazzer]

Your code always reaches the error MsgBox even if no error occurs. I suggest adding these two lines:

'a ton of code here
Case Else
   Exit Sub
End Select

Application.EnableEvents = True

Exit Sub

myerror:
   Application.EnableEvents = True
   MsgBox "Error"
End Sub

 

[WildBurrow]

Your code always reaches the error MsgBox even if no error occurs. I suggest adding these two lines:

'a ton of code here
Case Else
   Exit Sub
End Select

Application.EnableEvents = True

Exit Sub

myerror:
   Application.EnableEvents = True
   MsgBox "Error"
End Sub

6StringJazzer:

Your suggestion to add the two lines did, in fact, correct the over zealous error message box. Thank you for that.

However, the primary issue of the change event not triggering after an initial selection is still a problem. I see that I failed to properly identify that in my first post.

To provide a bit more detail which may help identify other potential culprits. The user completes the required fields on the 'Report' sheet then runs the Save/Audit macro which checks to see if all the required information has been provided. To accomplish the Save/Audit, I copy/paste select cell values to a 'Tables" worksheet that contains formulas. If the formula returns "Missing Info", a message pops up telling the user how to fix the missing information. This is where the change event stops working. The change event will function properly and allow me to make multiple changes to a cell if I place 'Application.EnableEvents = True' in the Immediate Window.

So, digging in a bit deeper; one example of this Audit process pertains to support documentation and report attachments. In this instance, there are four specific cells that might indicate receipt of support documentation (AA164, AA167,AA170, and AA173). Because I only wanted to pass a single value to the 'Tables' worksheet, I wrote an if/or formula at the bottom of the 'Report' worksheet that writes True/False if documentation has been received (see table below).

User Selected Documentation Received	Writes True or False to Cell Noted Below
AA164	I272
AA167	L272
AA170	O272
AA173	R272

In cell T272 formula: =IF(OR(I272=TRUE,L272=TRUE,O272=TRUE,R272=TRUE),"TRUE",""). The value of T272 is passed to the 'Tables' worksheet.

Let's say the User specifies in cell AA164 that documentation has been received but also selected the 'No Attachment' option, he/she will receive an Audit message to either 1) change the value under support documentation to None or, 2) change the value under attachments from None. The table below shows the values at the bottom of the report.

AA164	I272 = True
AA167	L262 = False
AA170	O272 = False
AA173	R272 = False
T272	T272 = True

Now, lets say the User changes cell AA164 to indicate that support documentation was not received. Cell I272 should change to False, but it is not; the change byval is not functioning. It will function if I place the 'Application.EnableEvents = True' in the Immediate Window.

I hope this isn't too much information. What is causing the enable events to stop functioning?