BENSON
07-09-2009, 09:37 PM
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
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