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

[vba]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[/vba]

Any advice is greatly recieved.

Many thanks

Jeff

Hi Jeff,

Am Mon, 8 Dec 2014 11:29:40 +0000 schrieb Jeff Owen:

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

try:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Sheets("Sheet1")
If WorksheetFunction.CountA(.Range("B9:F9")) < 5 Then
MsgBox "Cannot save until ALL cells have been completed!"
Cancel = True
End If
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus

Thanks for the reply - Issue is now I am unable to save the blank template as there is nothing in the cells.
Is there a way of putting greater than 1 but less than 5 in the formula?

Hi Owen,

Am Mon, 8 Dec 2014 12:59:25 +0000 schrieb Jeff Owen:

Issue is now I am unable to save the blank
template as there is nothing in the cells.
Is there a way of putting greater than 1 but less than 5 in the formula?

try:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LCol As Long

With Sheets("Sheet1")
'Search for the last used column in row 5
LCol = .Cells(5, Columns.Count).End(xlToLeft).Column
'If the cell in row9 in the last used column is empty
'Saving is cancelled
If Len(.Cells(9, LCol)) = 0 And LCol 1 Then
MsgBox "Cannot save until ALL cells have been completed!"
Cancel = True
End If
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Unfortunatley not. Ihave tweaked your earlier formula though.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Sheets("Sheet1")
If WorksheetFunction.CountA(.Range("B9:F9")) < 5 And WorksheetFunction.CountA(.Range("B9:F9")) 1 Then
MsgBox "Cannot save until ALL cells have been completed!"
Cancel = True
End If
End With
End sub



how would I apply this to all the active sheets?
again massive thanks for your help!

Hi Owen,

Am Mon, 8 Dec 2014 19:20:33 +0000 schrieb Jeff Owen:

With Sheets("Sheet1")

change the line above to
With ActiveSheet


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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