Check Worksheet When Workbook Is Saved

[Guest]

Hi, could someone tell me how to do this in vba ?

When a user tries to save the workbook, for Sheet1 if any cell in column A
(A3 until the last row) equals "3" or column B (B3 until the last row)
equals "Calculus" then prevent the workbook from being saved & prompt a
msgbox if the corresponding cell in column C , F & H is blank.

Thanks !

Rgds,
Clare

 

[JW]

So, all of those conditions must be met, correct? If so, place this
in the ThisWorkbook module of the applicable workbook.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Dim lRow As Long
With Sheets("Sheet1")
lRow = .Cells.Find(what:="*", _
After:=.Cells(1, 1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 3 To lRow
If .Cells(i, 1).Text = "3" Or _
.Cells(i, 2).Text = "Calculus" Then
If IsEmpty(.Cells(i, 3)) And _
IsEmpty(.Cells(i, 6)) And _
IsEmpty(.Cells(i, 8)) Then
Cancel = True
MsgBox "Your message here"
Exit For
End If
End If
Next i
End With
End Sub

 

[Guest]

Sorry for late reply. I tested out the code & it worked well.

Some more questions on this code:
1) If I don't want to prevent the user from saving, but just prompt them
which row(s) that is not fulfilling the criteria, allow them to click "OK",
then prompt them to give them a choice to save the workbook or don't save and
continue working with the workbook.... how can this be done ?
2) Besides the criteria already in effect, if any cell in column A equals 1
AND the corresponding cell in column D is more than 500, then prompt the user
on which row(s) is not fulfilling the criteria, allow them to click "OK",
then prompt them to give them a choice to save the workbook or don't save and
continue working with the workbook.

Appreciate any help. Thanks!!