How to make it work for "WHOLE" workbook

[test]

Greetings,

How can I make the below code work for the entire workbook. I mean for
now, if I try enter something which is an invalid value (as per code
below), and try to save and close it (by using close button from the
top of the workbook), it does not allow me to save/close which is what
I needed. But if I try to close from the main top right hand corner of
excel, the excel simply displays value of cannot save and closes
down.

What needs to be done (in below code) in order to prevent excel from
getting closed (no matter if I try to close from either the workbook
or either by closing the whole excel from top right hand side).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MySheet As String, lastrow As Long
MySheet = "Test"
lastrow = Sheets(MySheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets(MySheet).Range("A1:A" & lastrow)
For Each c In MyRange
Select Case UCase(c)
Case "FAIL", "OTHER"
If c.Offset(, 1).Value = "" Then
MsgBox "You must populate " & c.Offset(, 1).Address
Cancel = True
Exit For
End If
End Select
Next
End Sub

 

[jasontferrell]

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 

[test]

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Thanks alot Jason. That works.

 

[test]

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

 

[test]

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