May 02 2019 09:42 AM - last edited on Nov 09 2023 11:09 AM by
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!
May 02 2019 03:04 PM
SolutionHello @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
May 06 2019 08:51 AM
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.
May 06 2019 01:43 PM
@ACPNR the code I wrote will not pop up for every row. Post the code you are using.
May 06 2019 02:40 PM - edited May 06 2019 02:43 PM
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
May 08 2019 01:32 PM
@ACPNR Well, that is not the code I posted, is it?
What happens if you try the code I posted?
May 08 2019 02:42 PM
@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.
May 08 2019 03:20 PM
@ACPNR I'm attaching a file with my code. It works fine for me. Let me know how you get on with this.
May 09 2019 07:37 AM
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.
May 02 2019 03:04 PM
SolutionHello @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