PDA

View Full Version : [SOLVED:] MsgBox is making the code hang



simora
05-25-2025, 04:53 PM
I have a worksheet change event that fires if a specific condition exists.
[cells to the left are blank ]
I want to tell the User to post data to those cells first, but my MsgBox is making the code hang.
Any ideas.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Offset(-4, 0).Value = "" Or Target.Offset(-7, 0).Value = "" Then
Target.Value = ""
MsgBox " POST THE DOC FIRST "
Exit Sub
End If
End Sub

jindon
05-25-2025, 07:47 PM
1) It will raise the run-time error when target row is smaller than 7
2) When you change the value with Change event, you need to disable events, otherwise infinite event loop...


Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row < 7 Then Exit Sub
Application.EnableEvents = False
If .Offset(-4, 0).Value = "" Or .Offset(-7, 0).Value = "" Then
.Value = ""
MsgBox " POST THE DOC FIRST "
End If
Application.EnableEvents = True
End With
End Sub

simora
05-25-2025, 08:24 PM
Thanks jindon (http://www.vbaexpress.com/forum/member.php?2856-jindon)
http://www.vbaexpress.com/forum/images/statusicon/user-offline.png
Forgot all about disabling events scenarios.
That solved the problem.