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?

 

[FrancisM]

it works!!!

Monday I will see if I can reverse the order. Here are my reasons. The user will enter the name, etc for a new entry. At a late date the box will be checked. I will keep you posted.

 

[Fluff]

OK, unfortunately change events do not get triggered by checkboxes.

 

[FrancisM]

You are correct. What I did was to reverse the order of columns. that solved the problem. Since we had several messages. I posted the working code that you provided, for future users.

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("H4:H10, H13:H17")
      If LCase(cell.Value) = "no" Then
         If Target.Value = "No" And Target.Offset(, 15).Value = True Then
            MsgBox "You have No & true"
         End If
      End If
   Next
Application.ScreenUpdating = True
 End Sub

Than you for your assistance & being so patient with me.

 

[Fluff]

Glad it's working & thanks for the feedback

 

[FrancisM]

I left a piece of code out in the previous response.

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"
                 Call Referals
         End If
      End If
   Next


Application.ScreenUpdating = True


 End Sub