Why Message Boxes Repeat?

I am Googled and Forum searched out! Most likely there is a simple explanation for this, but I have yet to find it.
How to put in words....I have a worksheet change event on a particular cell. If that cell changes then a sub is called
to "do something." If the cell = a specified name, another sub is called to display a first msgbox, and depending on
the response (Yes or No) either of 2 msgboxes are displayed. Let's say if "Yes" is clicked, msgbox 2 is displayed,
to which OK is clicked. On the other hand if "No" is clicked on the first msgbox, msgbox 3 is displayed, to which
OK is also clicked. The problem is if I answer "Yes" on the 1st msgbox, and OK on the 2nd msg box, they repeat.
That is, I see msgbox 1 again, and I click "Yes" again, but this time when msgbox 2 appears and I click OK, this
time it disappears like it should. The same thing happens if "No" is selected on the 1st msgbox. I think(?) it has to
do with the worksheet change event? There are supposed fixes - Application.??? = false/true - forgot the second
term, but that didn't work. I guess it would help to post the code - Any assistance would be most, most appreciated,
since I at wits end trying to figure out what I'm doing wrong. Thank you all in Advance!!! It is the sub - CheckForLead
that is giving me fits! Sgt. Rock!!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down

    If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then Call ClearColumn
               
    If Range("C4").Value = "Lead" Then Call CheckForLead
           
End Sub

Sub ClearColumn()
    With Worksheets("Threshold").ListObjects("ThresholdInputTable").ListColumns("ActivityTRIChemical")
        .DataBodyRange.ClearContents
    End With
End Sub

Sub CheckForLead()

'Check if lead is contained in stainless steel, brass or bronze alloy

    Dim myTitle As String
    Dim MyMsg As String
    Dim Response As VbMsgBoxResult
            
            If Worksheets("Threshold").Range("C4").Value = "Lead" Then
            myTitle = "Qualify Lead & Lead Compounds"
            MyMsg = "Is the lead contained in stainless steel, brass, or bronze alloy?"
            Response = MsgBox(MyMsg, vbQuestion + vbYesNo, myTitle)
       
            If Response = vbYes Then
                MsgBox "The normal thresholds of 25,000 pounds (Mfg,Proc) or 10,000 pounds (OWU) apply"
                    
            ElseIf Response = vbNo Then
                MsgBox "The PBT 100 pound theshold applies to lead in all activities"
            Exit Sub
            
        End If
        End If

End Sub

It could be that you code changes the value of the "Target" cell and therefore runs the code again ""
Try adding :- "Application. EnableEvent" code as shown below'
NB:- Untested !!

Private Sub Worksheet_Change(ByVal Target As Range)
'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down
Application.EnableEvents = False
    
    If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then Call ClearColumn
               
    If Range("C4").Value = "Lead" Then Call CheckForLead

Application.EnableEvents = True
    End Sub

I agree with @Richard Buttery but my gut feeling is that the change event is firing for some reason. Perhaps you need the bracket your code in the event handler with Application.EnableEvents = False ... Application.EnableEvents = True
You also need to ensure that the second statement is executed in the event of error-ing out of the event handler.


Sent from my iPad using Tapatalk

Apologize for the convoluted description - hence why I included the code.
Have tried the Application...enable true/false to no avail - agree, "something" is triggering
the msgboxes to show again, but on second time through, they do as they should - simply close.
I first thought Exit Sub was not working...but that is not the case.
Possibly, just possibly due to the fact it's the same cell (C4)for the change event - but also for the msgboxes as well...???
Thoughts, suggestions?

Cell C4 is a pull down of various chemicals from a table on a different worksheet - when the name changes - a column is cleared on the same worksheet as C4 - simple.
However, and here's the kicker, if there happens to be a particular name in that cell, C4, e.g. lead (Pb), then I "invoke" a message box which asks a question.
If the response is "yes" - then another message pop's up, whereupon I want to simply click OK, and exit, but it doesn't do that -
goes back to the first message box. If I answer the same way Yes, the OK, it exits. Same is True if I initially answer "No" to the first msgbox - a subsequent message
appears - simply making a statement, to which I click OK, and again should exit but does the same thing as above - goes again but on 2nd go through, it will exit.

I most appreciate your viewing this since there are a gazillion questions on here and I do try my very best to solve on my own. Have done good so far in this program,
but this issue as really thrown me for a big loop!!!

Best weekend wishes,
Sgt Rock (Mort in Dallas)

FYI...did as you suggest and added the Application code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down

Application.EnableEvents = False
    If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then Call ClearColumn
               
    If Range("C4").Value = "Lead" Then Call CheckForLead
Application.EnableEvents = True
End Sub

Now no message boxes at all appear even if Lead appears in cell C4 - this strange issue has happened at lot - I change the code a lil bit, and it stops working - have to go back to
the original workbook to get it going again...I'm stumped!!!
Best, SR

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down

Application.EnableEvents = False

On Error GoTo exitEnabled

    If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then Call ClearColumn
               
    If Range("C4").Value = "Lead" Then Call CheckForLead

exitEnabled:
Application.EnableEvents = True
End Sub

Code edited to make it safe.
Have you set a break point in the error handler and stepped through?
And searched for other calls to CheckForLead?


Sent from my iPad using Tapatalk

Thanks "CoolBlue"
Tried the additions and nada. The more I read the more I know it has something to do with events and I think this issue is the same cells is associated with 2 events,
which may be inappropriate or not possible?
Event 1 is if the C4 changes to a new chemical, then ClearColumn is called and a column in a Table is cleared of information, ready for a new entry in Cell C4.
Event 2 is if the chemical in C4 happens to be lead (Pb) then I want some messages to appear to alert the user of issues with lead that must be known.

I guess the question is, can a single cell have two events associated with it, and if not, what is a work around??? That I have not Googled as yet and will do so now.
Thanks again for your suggestions...
Sgt Rock (Mort in Dallas)

I didn't really think that would fix it, it's just very dangerous not to do it because EventsEnabled is not reset until excel is re-started.
I don't think you have two event handlers... Unless you have another Worksheet Change event in ThisWorkbook, which I doubt...
All you need to do is, in the event handler, detect which change occurred and call the appropriate routine.


Sent from my iPhone using Tapatalk

Thanks CoolBlue....makes perfect sense - decide which change occurred and the call the appropriate sub
You are correct - this is the ONLY change event in the entire workbook.
Best for your weekend!! I do most appreciate you taking your time to look over - most, most courteous!!
I'm still "new" to VBA - and felt I accomplished a lot by just realizing what in blue-blazes is wrong,
much less how to fix it! Smile.
Sgt Rock (Mort in Dallas)

Spend some time figuring out how to use debug.print with the immediate window and setting break points and using the watch window. Also find the MSDN library elements referring to vba and excel. This is what you need to help you to methodically work through your code.


Sent from my iPhone using Tapatalk

Most appreciate CoolBlue!!!
Have never heard of debug.print - will Google
Do about the Immediate window though

After everyone's assistance I feel it only fitting to post the VBA code that works. An associate was able to steer me
in the right direction. I almost had it. Again the issue was with a "worksheet change event" and having a "second event"
on the same cell to test. 1st event, is clear a column in a Table once cell C4 is changed to a new chemical.
2nd event is if cell C4 = Lead then give some msgboxes stating various issues with Lead. You can "see" the messages
in the code below. Hopefully this code may be of some use for someone to use in a "look-a-like" situation?

Best wishes to the Forum and their helpfulness!!
Sgt Rock (Mort in Dallas)

Private Sub Worksheet_Change(ByVal Target As Range)

'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down

If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then
     With Worksheets("Threshold").ListObjects("ThresholdInputTable").ListColumns("ActivityTRIChemical")
        .DataBodyRange.ClearContents
     End With
     
'If Lead is selected as the chemical whose threshold needs to be calculated
'Warn the user that threshold could be the original 25,000 lbs/yr - 10,000 lbs/year (if contained in certain metals)
'Or that the lower 100 lbs/year PBT thrshold might apply
    
     If Range("C4").Value = "Lead" Then
        
        Dim myTitle As String
        Dim MyMsg As String
        Dim Response As VbMsgBoxResult
            
            If Worksheets("Threshold").Range("C4").Value = "Lead" Then
                 myTitle = "Qualify Lead & Lead Compounds"
                 MyMsg = "Is the lead contained in stainless steel, brass, or bronze alloy?"
                 Response = MsgBox(MyMsg, vbQuestion + vbYesNo, myTitle)
       
                 If Response = vbYes Then
                    MsgBox "The normal thresholds of 25,000 pounds (Mfg,Proc) or 10,000 pounds (OWU) apply"
                    Exit Sub
                 End If
        
                 If Response = vbNo Then
                    MsgBox "The PBT 100 pound theshold applies to lead in all activities"
                    Exit Sub
                 End If
            End If
    End If
End If
           
End Sub

Good job Sarge 😉


Sent from my iPad using Tapatalk

Hey hope it helps...have developed another question but I suppose it requires a new thread...
Best CoolBlue...Mort

Thanks for letting me know how to mark thread as solved Forum!!
Mort