Worksheet_Change(ByVal Target As Range) Msg Pop up Box

[SanFelippo]

Hello,

So right now I have some coding set up to display a pop up box if any of a certain 4 cells contain specific "yes" or "no" answers. That coding is here:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Range("B5").Value = "No" Or Range("B6").Value = "Yes" Or Range("B7").Value = "No" Or Range("B8").Value = "No" Then
    MsgBox "This is not a HMDA reportable application. Please complete the Contact Information section and Submit."

This part works great, however, whenever one of those conditions is true, that message box continues to pop up every time a change is made to another cell. I completely understand why, since the sub is run every time a change is made to a cell and it then sees that one of the conditions is met in those 4 cells, and displays the message box again.

It makes it pretty annoying when you are trying to fill out the contact information that is in the cells below, because every time you enter data into one of the contact info cells, that message box pops up again.

My question is how would you get that message box to only pop up once? I need it to pop up as soon as one of the conditions I stated is met, which is why it is in the Worksheet_Change(ByVal Target As Range) sub in the first place, but after it has popped up once, it doesn't need to anymore. Is it possible to have it check only those 4 cells for a change to determine if the message box should appear? There is some other coding below that is in the sub that doesn't have to do with the message box, so I don't want to limit the entire sub to just looking for a change in those 4 cells, I just would want to limit the message box's pop up requirements to looking at those 4 cells so that if one of the conditions is met, the box doesn't appear after every change to any other cell on the sheet.

That was really hard to explain, so I hope it made sense. I will post the entire code below, but it is really only the first piece involving the message box that my question is about.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Range("B16").Value = "ZF85QS70XKPBG52R7N18"
  
    If Range("B5").Value = "No" Or Range("B6").Value = "Yes" Or Range("B7").Value = "No" Or Range("B8").Value = "No" Then
    MsgBox "This is not a HMDA reportable application. Please complete the Contact Information section and Submit."
    
    End If
    
    If Range("B21").Value = "Commercial Real Estate" Then
        Range("B24").Value = "NA"
    
    End If
    
    If Range("B21").Value = "(Select One)" Then
        Range("B24").Value = ""
    
    End If
    
'Reset Selections if B4 is on "(Select One)"
    Select Case Range("B4").Value
    
        Case "(Select One)"
        Range("B5").Value = "(Select One)"
        Range("B6").Value = "(Select One)"
        Range("B7").Value = "(Select One)"
        Range("B8").Value = "(Select One)"
        Range("B13").Value = ""
        Range("B14").Value = ""
        Range("B15").Value = ""
        Range("B21").Value = "(Select One)"
        Range("B22").Value = ""
        Range("B23").Value = ""
        Range("B24").Value = ""
        Range("B25").Value = ""
        Range("B26").Value = ""
        Range("B27").Value = ""
       
    End Select
   
Application.EnableEvents = True
End Sub

 

[V_Malkoti]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B5:B8")) Is Nothing Then
        ' code to run when change is made in B5:B8
    End If
End Sub

 

[SanFelippo]

Perfect, works great. Would you mind explaining to me what that first bit does, or rather how it does what it is doing? I want to understand the logic behind it so I can apply it elsewhere in different situations if I need to.

 

[V_Malkoti]

Intersect method performs intersection of two or more ranges, supplied as method arguments. If ranges have common cells, range object consisting of those common cells is returned. If no common cells are present, method returns null.

If statement is checking if the changed cell (Target) lies in the Range("B5:B8") by performing intersection of these two.

More information here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-intersect-method-excel

 

[V_Malkoti]

Intersect method performs intersection of two or more ranges, supplied as method arguments. If ranges have common cells, range object consisting of those common cells is returned. If no common cells are present, method returns null.

If statement is checking if the changed cell (Target) lies in the Range("B5:B8") by performing intersection of these two.

More information here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-intersect-method-excel