On Monday, December 8, 2014 5:49:04 AM UTC-6, Jeff Owen wrote:
Hello all.

I'm looking to write something that will not allow the user to save the
sheet if things are not completed correctly. At the moment I have this
but it doesnt seem to work.

in G9 on sheet 1

=IF(ISBLANK(B9),"",IF(OR(ISBLANK(B9),ISBLANK(C9),I SBLANK(D9),ISBLANK(E9),ISBLANK(F9)),"Not
Complete","Complete"))

Which works fine. The issue is this......

Private Sub Workbook_BeforeSave(Cancel As Boolean)

If Sheet1.Range("G9").Value = "Not Complete" Then
MsgBox "Cannot save until ALL cells have been completed!"
Cancel = True
End If
End Sub

Any advice is greatly recieved.

Many thanks

Jeff




--
Jeff Owen
This should also take care of the careless spacebar

Dim c As Range
With Sheets("Sheet1")' most of us use application instead of worksheet.function
For Each c In .Range("b9:f9")
If Len(Application.Trim(c)) 0 Then mc = mc + 1
Next c
If mc 0 And mc < 5 Then
MsgBox "Cannot save until ALL cells have been completed!"
Cancel = True
application.goto .range("b9")

End If
End With
End Sub