Check Worksheet When Workbook Is Saved
 

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

Check Worksheet When Workbook Is Saved
 

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
Clare wrote:
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

Check Worksheet When Workbook Is Saved
 

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

"JW" wrote:

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
Clare wrote:
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