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
>
>
|
Similar Threads
