Help - Cancel MsgBox If Condition is Met

[ACPNR]

Hello,

I'm trying to write a macro that does one thing or the other. There are two ranges: Code (H15:I32) and Repair_Comments (N15:R32). First, if X is marked in Code range then a message box should appear saying, "Please include a repair comment for corresponding line." I got this part to work on its own.

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("H15:I32")) Is Nothing Then
          Dim Code, Repair_Comments As Range
          For Each Code In Range("$H$15:$I$32")
               If Code.Value = "X" Then
               MsgBox ("Please include a repair comment for corresponding line.")
               End If
               Next Code

     End If

End Sub

However, the message box continues to pop up as long as the X is marked even if the user writes a repair comment. I want to prevent the message box from popping up if a repair comment is added. However, if an X is marked and has a repair comment and another X is marked but is missing a repair comment, then I want the message box to pop up again.

So I want to be able to stop the message box from popping if they meet the requirement of inputting a comment, but as long as there is an X missing the requirement of inputting a comment then I want the box to show.

I've made an attempt but I can't seem to figure it out. This is the rest of the code I've attempted:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("H15:I32")) Is Nothing Then
          Dim Code, Repair_Comments As Range
          For Each Code In Range("$H$15:$I$32")
               If Code.Value = "X" Then
               MsgBox ("Please include a repair comment for corresponding line.")
               End If
               Next Code
          For Each cell In Intersect(Range("H15:I32,N15:R32"), ActiveSheet.UsedRange)
               If Code.Value = "X" And Repair_Comments = Not Blank Then
               Cancel MsgBox
               End If
               Next
     End If
End Sub

Any help will be appreciated!

[#2 · Accepted solution]

Hello @ACPNR ,

I think all you need to do is add another condition to the IF statement so it says in words "If the Code is X and the cell next to it is blank" then show the message.

You only need to loop through the codes in column H,  and you don't need the $ signs in the range reference.  Also, when you dim several elements in one row of code, you need to specify the data type of each. If you don't, it will be a variant. 

Also, you don't want to loop through the whole list. You only want to check the row where the data was entered, i.e. the target cell in column H. Therefore, the code should only watch column H, not column I.  When you operate on the target cell, you don't need to declare any ranges at all. 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H15:H32")) Is Nothing Then
        If Target.Value = "X" And Len(Target.Offset(0, 1)) = 0 Then
                MsgBox ("Please include a repair comment for corresponding line.")
        End If
    End If

End Sub

If you want to cater for a scenario where several rows of values are filled in at once, for example with a copy / paste operation, THEN you would need to loop through all the cells in the target. In that case it would also help to give the user an indication which row needs to be addressed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
    If Not Intersect(Target, Range("H15:H32")) Is Nothing Then
        For Each cel In Target
            If cel.Value = "X" And Len(cel.Offset(0, 1)) = 0 Then
                    MsgBox ("Please include a repair comment for row " & cel.Row & ".")
            End If
        Next cel
    End If

End Sub

[#3]

@Ingeborg Hawighorst 

I tried the first code you wrote, but it didn't work properly. I understand what you mean though. However, I still get the message box popping up even if the repair comments are filled.

[#4]

@ACPNR  the code I wrote will not pop up for every row. Post the code you are using.

[#5]

@Ingeborg Hawighorst 

I also edited the original post with a photo when I edit another row within the range and get the pop up message despite having the other cell filled in. It seems to ignore the second condition for some reason.

This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("H15:I32")) Is Nothing Then
          Dim Code As Range
          For Each Code In Range("H15:H32")
          If Code.Value = "X" And Len(Target.Offset(0, 2)) = 0 Then
          MsgBox ("Please include a repair comment for corresponding line.")
          End If
          Next Code
     End If
End Sub

[#6]

@ACPNR Well, that is not the code I posted, is it? 

What happens if you try the code I posted?

[#7]

@Ingeborg Hawighorst Like I said, the code you gave me didn't work properly or the way it was intended to. It worked in the sense of syntax (I didn't get any errors or bugs), but as mentioned earlier the message still popped up. I copy pasted it; didn't work.

I tried playing around with it and got some errors/bugs sometimes, other times I wouldn't get errors/bugs but would get the message popping up, and other times I wouldn't get errors/bugs but the message wouldn't pop up.

[#8]

@ACPNR I'm attaching a file with my code. It works fine for me. Let me know how you get on with this.

[#9]

@Ingeborg Hawighorst 

Yeah it ended up working this time (I don't know why it didn't work the first time though). Thank you for helping me out. I really appreciate it.