Edit Work Sheet Whilst Msg Box Remains Open

[BENSON]

I have a similar question as posted by IW22 in this forum a few days ago.The code below displays a couple of Msg Boxes informing the user of some things that maybe should be changed.Is it possible for the Msg box to remain open while the work sheet is edited ?

THANKS

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
Dim msg2 As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 7 To 200

If i > 129 And i < 143 Then i = 143

If IsNumeric(.Cells(i, "J").Value) And _
IsNumeric(.Cells(i, "Q").Value) And _
.Cells(i, "Q").Value <> 0 Then

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.4 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
If .Cells(i, "n").Value > .Cells(i, "x").Value Then

msg2 = msg2 & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If


Next i

If msg <> "" Then

MsgBox "YOU MAY WANT TO CHECK THE BUILD TO ON THESE ITEMS" & vbNewLine & vbNewLine & msg
End If
If msg2 <> "" Then

MsgBox "THE ITEMS LISTED ARE NOT RECORDED ON STOCK SHEET" & vbNewLine & vbNewLine & msg2
End If
End With
End Sub

[JimmyTheHand]

I think it's not possible with messagebox. However, you can use

a modeless form comuflaged as a messagebox, or
the Excel Assistant. An example for the latter:

Sub ShowBalloon()
Application.Assistant.On = True
With Assistant.NewBalloon
.Mode = msoModeModeless
.Text = "my message"
.Button = msoButtonSetOK
.Callback = "RemoveBalloon"
.Show
End With
End Sub

Function RemoveBalloon(b As Balloon, Btn As Long, Bal_ID As Long)
b.Close
Application.Assistant.On = False
End Function