Stopping user saving unless condition met.

[S.U.S.S.]

Sorry to be back so soon.

I am looking to stop the user saving the sheet if certain fields are not complete.

The sheet has headers on line 4 and runs from A4 to M500

In the M cells there is a counta function to check the records are complete.

M4:M500 shows either

Complete - All records comlpete
Incomplete - something missing
Blank - nothing entered yet.

What i want to do is, if one of the cells in M shows incomplete not allow them to save the sheet until its complete.

I tried the folowing (was my attempt to modify another post)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Range("M5:M500") = ("Incomplete") Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If Range("M5:M500") = ("Complete") Or Range("M5:M500") = ("Blank") Then
    Cancel = False
    End If
End If
End Sub

Any help appreciated.

Thanks again.

[VBA Noob]

Maybe

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheets("Sheet1").Range("M5:M500") 'change to your sheet name    If WorksheetFunction.CountIf(Rng, "Incomplete") > 0 Or _
    WorksheetFunction.CountBlank(Rng) <> 0 Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If WorksheetFunction.CountIf(Rng, "Incomplete") = 0 And _
    WorksheetFunction.CountBlank(Rng) <> 0 Then
    Cancel = False
    End If
End If
End Sub

VBA Noob

[S.U.S.S.]

Maybe

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheets("Sheet1").Range("M5:M500") 'change to your sheet name    If WorksheetFunction.CountIf(Rng, "Incomplete") > 0 Or _
    WorksheetFunction.CountBlank(Rng) <> 0 Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If WorksheetFunction.CountIf(Rng, "Incomplete") = 0 And _
    WorksheetFunction.CountBlank(Rng) <> 0 Then
    Cancel = False
    End If
End If
End Sub

VBA Noob

Sorry think i may have not explained that as well as i could.

when i say "blank" above the cell shows the word blank due to the following IF statement.

=IF(L9=0,"Blank",IF(L9=9,"Complete", "Incomplete"))

tried changing the code to

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheets("ASC").Range("M5:M500") 'change to your sheet name
    If WorksheetFunction.CountIf(Rng, "Incomplete") > 0 Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If WorksheetFunction.CountIf(Rng, "Incomplete") = 0 Then
    Cancel = False
    End If
End If
End Sub

But sadly a little knowledge is dangerous and it didnt work!

Is the problem the fact the M cells are the product of an IF statement?

[VBA Noob]

Maybe

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheets("ASC").Range("M5:M500") 
    If WorksheetFunction.CountIf(Rng, "Incomplete") > 0 Or _
    WorksheetFunction.CountIf(Rng, "Blank") > 0 Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If WorksheetFunction.CountIf(Rng, "Incomplete") = 0 Then
    Cancel = False
    End If
End If
End Sub

VBA Noob

[S.U.S.S.]

Maybe

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheets("ASC").Range("M5:M500") 
    If WorksheetFunction.CountIf(Rng, "Incomplete") > 0 Or _
    WorksheetFunction.CountIf(Rng, "Blank") > 0 Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If WorksheetFunction.CountIf(Rng, "Incomplete") = 0 Then
    Cancel = False
    End If
End If
End Sub

VBA Noob

Sadly no, still allows saving even tho there are a few "Incomplete" records.

Thanks for your help tho.

Is it allowed to post a dump of the screen to show my sheet?

[VBA Noob]

Attach a sample file would be better

VBA Noob