How to make it work for "WHOLE" workbook
 

On Apr 4, 2:35*pm, test wrote:
On Apr 3, 9:16*pm, jasontferrell wrote:

Use this one also, and add the same code to this subroutine.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Thanks alot Jason. That works.

One more help. Below is an extract from my requirement. The code seems
to be working fine but I do not want the below code to run if the
column E value is NULL (Blank). This should be a exceptional case
where the code should skip blank values in column E. Can you please
advise what could be changed in the below code to reflect this.

Let me know if any more information is needed from my side. I am
currently using excel 2003

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MySheet As String, lastrow As Long
MySheet = "Sheet1"
lastrow = Sheets(MySheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets(MySheet).Range("E4:E" & lastrow)
For Each c In MyRange
Select Case UCase(c)
Case "FAIL", "OTHER"
If c.Offset(, 1).Value = "" Then
MsgBox "Invalid Comment at " & c.Offset(, 1).Address
Cancel = True
Exit For
End If
Case "SUCCESS"
If c.Offset(, 1).Value = "" Then
Cancel = False
End If
Case Else
MsgBox "Invalid value in " & c.Address
Cancel = True
Exit For
End Select
Next
End Sub

How to make it work for "WHOLE" workbook
 

On Apr 6, 1:17*pm, test wrote:
On Apr 4, 2:35*pm, test wrote:

On Apr 3, 9:16*pm, jasontferrell wrote:

Use this one also, and add the same code to this subroutine.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Thanks alot Jason. That works.

One more help. Below is an extract from my requirement. The code seems
to be working fine but I do not want the below code to run if the
column E value is NULL (Blank). This should be a exceptional case
where the code should skip blank values in column E. Can you please
advise what could be changed in the below code to reflect this.

Let me know if any more information is needed from my side. I am
currently using excel 2003

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MySheet As String, lastrow As Long
MySheet = "Sheet1"
lastrow = Sheets(MySheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets(MySheet).Range("E4:E" & lastrow)
For Each c In MyRange
* * Select Case UCase(c)
* * * * Case "FAIL", "OTHER"
* * * * If c.Offset(, 1).Value = "" Then
* * * * * * MsgBox "Invalid Comment at " & c.Offset(, 1).Address
* * * * * * Cancel = True
* * * * * * Exit For
* * * * End If
* * * * Case "SUCCESS"
* * * * If c.Offset(, 1).Value = "" Then
* * * * * * Cancel = False
* * * * End If
* * * * Case Else
* * * * * * MsgBox "Invalid value in " & c.Address
* * * * * * Cancel = True
* * * * * * Exit For
* * End Select
Next
End Sub

Ok I got it. A condition of IsEmpty(c.Value) in else case did the
trick. Thanks