Excel VBA MsgBox NOT closing

[simora]

I have a worksheet that is unprotected, but This MsgBox when displayed will NOT close when clicked.
Screen updating is NOT turned off.
I have to use Control Alt Delete to regain control of the worksheet after it's reopened.
Any ideas why this behaviour ? How can I resolve this ?

Here is the code



If Not Intersect(Target, Me.Range("J2:J65536")) Is Nothing Then
If Target.Value > 0 And Len(Target.Offset(0, -9).Value) >= 5 Then
Target.Offset(0, 3).Locked = False
Else
Target.Value = ""
Target.Offset(0, -9).Select

End If

MsgBox "YOU MUST HAVE A P FILE NUMBER"
End If

[大灰狼1976]

Hi simora!
It may be caused by the chain reaction of the change event.
Please paste the complete code of the event.
maybe:

If Not Intersect(Target, Me.Range("J2:J65536")) Is Nothing Then
If Target.Value > 0 And Len(Target.Offset(0, -9).Value) >= 5 Then
Target.Offset(0, 3).Locked = False
Else
Application.EnableEvents = False
Target.Value = ""
Target.Offset(0, -9).Select
Application.EnableEvents = True
End If
MsgBox "YOU MUST HAVE A P FILE NUMBER"
End If


--Okami

[SamT]

The first thing I would do is take the code out of the event and place it in a different sub

If Not Intersect(Target, Range("JJ")) Is Nothing Then SubJJ Target
Then add some tests

Private sub SubJJ(ByRef Target)
'Stop using Target as soon as possible
Dim Cel As Range
Set Cel = Target

If Cel.Row = 1 Then exit Sub
Application.EnableEvents = False

If Cel.Value2 > 0 And Len(Cel.Offset(0, -9).Value2) >= 5 Then
Cel.Offset(0, 3).Locked = False
MsgBox "First test"
Else
Cel.Value = 0 'Don't change Types in midstream
Cel.Offset(0, -9).Select
MsgBox "Second test"
End If

MsgBox "last Test"
MsgBox "YOU MUST HAVE A P FILE NUMBER"

Application.EnableEvents = True
End Sub

[simora]

大灰狼1976 (http://www.vbaexpress.com/forum/member.php?70849-%E5%A4%A7%E7%81%B0%E7%8B%BC1976) & SamT Thanks.

Both Great .
I used 大灰狼1976 (http://www.vbaexpress.com/forum/member.php?70849-%E5%A4%A7%E7%81%B0%E7%8B%BC1976) code because it was very similar to the code that I already had. That worked great.
I do like the idea of getting code out of the page event for future consideration.

[大灰狼1976]

@simora! Thank you for your reply. You're good man.


--Okami