T
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
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