Requiring popup message to run after 2 conditions are met.

[FrancisM]

I am trying to get a pop up message to run if the following 2 conditions are met "No" & "True". No is in cell range G4:G10, G13:G17; True is located in W4:W10, W13:W17. However true is a Boolean answer. I do have a formula located in cell range x4:x10, x13:x17 that concerts the Boolean answer to a 1 or 0. I thought that you had to convert a Boolean answer to a numeric answer to use in code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     '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("G4:G10, G13:g17" & lastRow)
    If InStr(1, cell.Value, "No") <> 0 Then
        If InStr(1, cell.Offset(, 17).Value, "1") <> 0 Then
        End If
    End If
Next


Application.ScreenUpdating = True
         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"
   
        Call Referals
    
  
 End Sub

When I run the code it sees the "no" & runs the message, & calls Referals. I have are 3 questions.
1.Do you have to convert Boolean answers to numeric?
2. How do you get excel to see the second condition?
3. Could you,explain what I am doing wrong?

 

[Fluff]

Interesting, indeed. I know that when I downloaded the file it stripped stuff out that is not compatible with Excel online, but that is normally checkboxes comboboxes etc.
The October sheet has these headings in row 3

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
3		Name	Last 4		Contact Information	Is this a Follow Up	SC %	Is a Follow Up Requested	Next meeting month	Follow up Date	Follow up Time	Funded	Requested Documentation for Next Visit/ Notes
October_Meeting_#_1

Is that what it should be?

 

[FrancisM]

I just noticed that I only answered half the question. The code is to run on only the sheets that have Meeting in the name. When I checked Column G data Validation, I learned that here an overlap in Column H. I fixed that, Thanks to your sharp eye for detail.

 

[FrancisM]

Close.

 

[FrancisM]

Close will give the correct headers.

 

[FrancisM]

Header A = Numeric (Not important), Header B =Name, Header C = Last 4, Header D = Time (Not important), Header E = Contact information, F= Contact Information/CPRS (Check boxed ( no formula), Header G = Is this a follow up ( yes/No is related to the formula. Only interested in No), Header H = Check in ( a checkbox that is related to a Boolean TRUE/FALSE answer. Answer in Column W). Columns I through T don't have any formulas attached to them.

 

[Fluff]

OK, it looks as though some of the columns have been stripped out.
If I insert a new column so that col G is "Is this a follow up" then this code works for me.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     '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("G4:G10, G13:G17")
      If LCase(cell.Value) = "no" Then
         If cell.Offset(, 16).Value = True Then
            MsgBox "You have No & true"
         End If
      End If
   Next

Application.ScreenUpdating = True
 End Sub

 

[FrancisM]

I copied your code, saved, & exited. Reopened, chose No in column G, checked the box in column H, visually saw that there was a TRUE I column W. There was no message shown.

 

[Fluff]

The code will not get triggered by clicking a checkbox, on;y by manually changing a cell, or with a data validation dropdown.
If you click a checkbox & then change col G does it work?

 

[FrancisM]

No. Nothing.

Here is the code I was using for just the No response, prior to trying to include the Boolean answer in column W(22). It does work for only No.

'The code below is a reminder to enter data in the Referral Workbook.
     If Intersect(Target, Sh.Range("G:G")) Is Nothing Then Exit Sub
     If Target.Value <> "No" Then Exit Sub
     If ReferralsCalled = False Then
     'Shows a 3 line message box.
          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"
     
         Call Referals
     End If
     
 End Sub

 

[Fluff]

Try

'The code below is a reminder to enter data in the Referral Workbook.
     If Intersect(Target, Sh.Range("G:G")) Is Nothing Then Exit Sub
     If Target.Value = "No" And Target.Offset(, 16).Value = True Then
     'Shows a 3 line message box.
          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"
     
         Call Referals
     End If
     
 End Sub