Worksheet Change ByVal Target As Range triggers once then stops

WildBurrow

New Member
Joined
Apr 5, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Your code always reaches the error MsgBox even if no error occurs. I suggest adding these two lines:
Rich (BB code):
'a ton of code here
Case Else
   Exit Sub
End Select

Application.EnableEvents = True

Exit Sub

myerror:
   Application.EnableEvents = True
   MsgBox "Error"
End Sub
 
Upvote 0
Your code always reaches the error MsgBox even if no error occurs. I suggest adding these two lines:
Rich (BB code):
'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 ReceivedWrites True or False to Cell Noted Below
AA164I272
AA167L272
AA170O272
AA173R272
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.
AA164I272 = True
AA167L262 = False
AA170O272 = False
AA173R272 = False
T272T272 = 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?
 
Upvote 0
Solution

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top