Preventing Call codes from running multiple times.

[FrancisM]

I have code that fluff was kind enough to help me with. It works as written, but when I added a call code, it would provide the following message multiple times "Check to verify veteran data is entered in FY ## REFERALS." "It's critical that the veteran data is captured." It didoes not matter if Yes or No is checked. the the response is the same. It then runs the call command the same number of times. I have tried moving moving the location of the call code, but the response is the same. Here is the code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Select Case Sh.CodeName
    'These are the worksheets here that are not to be called with change
         Case "Sheet1", "Sheet11", "Sheet21", "Sheet31", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet 45", "Sheet46", "Sheet47", "Sheet48", "Sheet49", "Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", "Sheet56", "Sheet57", "Sheet82"
         
             Exit Sub
     End Select
     
     
     If Not (Application.Intersect(Target, Sh.Range("B4:B10, B13:B17")) _
       Is Nothing) Then   'Note that Range is now identified with the calling Sheet object variable (Sh)
         With Target
             If Not .HasFormula Then
                 Application.EnableEvents = False
                 .Value = UCase(.Value)
                 Application.EnableEvents = True
             End If
         End With
     End If
     'The code below is a reminder to enter data in the Referral Workbook.
     Application.ScreenUpdating = False
     Dim lastRow As Long
     Dim cell As Range
     lastRow = Range("G" & Rows.Count).End(xlUp).Row




   For Each cell In Range("H4:H10, H13:H17")
      If LCase(cell.Value) = "no" Then
         If Target.Value = "No" And Target.Offset(, 15).Value = True Then
            MsgBox "Check to verify veteran data is entered in FY ## REFERALS." & vbCr & _
                "It's critical that the veteran data is captured." & vbCr & _
                 "You have entered No into cell" & Target.Address, vbInformation, "Career Link Meeting List"
                
         End If
      End If
   Next
 'Call Referals
Application.ScreenUpdating = True


 End Sub

The piece of code that causing a problem is Call Referals​.

 

[FrancisM]

I have just learned that the number of times the message appears & the call code is related the the number of times there is a "No" in Column H & TRUE in Column W. I am trying to get it to run only once. I have Googled, but have not found anything like this. I will keep looking.

 

[offthelip]

If you are writing anything to the workbok in your call code it will automatically retrigger the worksheet change event software so you will get multiple responses. This could be the cause of the problem. To avoid this you must wrap

Application.EnableEvents = False
'' your code
Application.EnableEvents = True

around the cdoe that writes to the workbook

 

[FrancisM]

That code I found, just before your message. I found that it is still running the same way. I am thinking, possibly interrupting the code.

 

[FrancisM]

My idea did not work. I will post a sample in a drop box in a few hours.

 

[Fluff]

Try adding this

[COLOR=#ff0000]Dim Chk As Boolean[/COLOR]
For Each cell In Range("H4:H10, H13:H17")
   If Target.Value = "No" And Target.Offset(, 15).Value = True Then
      MsgBox "Check to verify veteran data is entered in FY ## REFERALS." & vbCr & _
          "It's critical that the veteran data is captured." & vbCr & _
           "You have entered No into cell" & Target.Address, vbInformation, "Career Link Meeting List"
      [COLOR=#ff0000]Chk = True
      Exit For[/COLOR]
   End If
Next
[COLOR=#ff0000]If Chk Then Call Referals[/COLOR]

 

[FrancisM]

I tried that no change. Attached is a sample
https://1drv.ms/f/s!Ak-4iXjPpsJMgQ_pcn5fLaELzGN0

 

[Fluff]

That's because the code you are running & the code you posted are not the same.
Try using the code you posted along with the mods in post#6

 

[FrancisM]

I checked & you are correct. That was an old copy, So sorry. I check & this is the one that has your corrections. https://1drv.ms/x/s!Ah63gfPqLn_XhWiA8gl40th4a8Yj

 

[Fluff]

The code in that file looks to be exactly the same as the last one & is not the same as the code you posted here, which makes it nigh on impossible for us to help you.
Change the code in your workbook to what you posted here, along the changes I showed.