Forum Discussion
Help - Cancel MsgBox If Condition is Met
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!
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 SubIf 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
8 Replies
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 SubIf 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 SubI 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.
ACPNR the code I wrote will not pop up for every row. Post the code you are using.
