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?

 

[Tim_Excel_]

Do I understand correctly?

If the first range contains "No" and the second range contains TRUE, then you want that message to be displayed?

You can use

If variable = TRUE then

 

[Fluff]

Maybe something like

For Each Cell In Range("G4:G10, G13:g17" & LastRow)
    If InStr(1, Cell.Value, "No") <> 0 Then
        If Cell.Offset(, 16).Value = True Then
        MsgBox "You have No & true"
        End If
    End If
Next

 

[KennyGreens]

Hi, maybe I am missing something and just don't understand the code, but what is the point of having the if statements if they are not being used? There is no code in the if statements so it simply checks to see if the conditions are met and then moves on without actually doing anything.

Does cell G4 have "no" as the value? That would explain why you would think the code recognized the "no".


Edit: Looks like Fluff beat me to the punch

 

[FrancisM]

Correct. No is related to the question Is this a follow up? Some entries will be a follow up & some will not. Both entries will be checked in, when they check in for their appointment. I want it to run only when there is a No" & at check in. So you don't have to convert the Boolean answer to numeric? Thee may seem like dumb questions. Where do I place the "If variable.... hat other suggestion do you have so the code follows best practices?

 

[Tim_Excel_]

Maybe something like

For Each Cell In Range("G4:G10, G13:g17" & LastRow)
    If InStr(1, Cell.Value, "No") <> 0 Then
        If Cell.Offset(, 16).Value = True Then
        MsgBox "You have No & true"
        End If
    End If
Next

I would change:

        If Cell.Offset(, 16).Value = True Then
            MsgBox "You have No & true"
            Exit For
        End If

As it does not appear OP cares about whether the if statement is 1 or more times true

 

[FrancisM]

correct

 

[KennyGreens]

Correct. No is related to the question Is this a follow up? Some entries will be a follow up & some will not. Both entries will be checked in, when they check in for their appointment. I want it to run only when there is a No" & at check in. So you don't have to convert the Boolean answer to numeric? Thee may seem like dumb questions. Where do I place the "If variable.... hat other suggestion do you have so the code follows best practices?

The message that you are trying to have pop up based on the conditions needs to be placed within the if statements. Take a look at the code that Fluff posted and notice the placement of Msgbox, how it is within the if statements.

 

[Tim_Excel_]

If something is something then
    do something if the if statement is true
ElseIf something is something then
    do something if the elseif statement is true and the if statement is false
Else
    do something if the if (and elseif) statements are false
End

//

If a = 1 Then
    MsgBox "first statement is true"
ElseIf a = 2 Then
    MsgBox "second statement is true, first is false"
Else
   MsgBox "No statements are true"
End if

This is why the MsgBox line should be IN the if function

 

[FrancisM]

Fluff thank you for responding so quickly. I tested you code & got the my message when No, Yes is entered, & when I cleared the cell. I did not get your message. No I am confused.