Hi John,

I have figured it out from some of the additional code you posted:

The following code now works fine:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents=False
If Range("G5") = "" Or Range("D7") = "" Or Range("G7") = "" _
Or Range("D9") = "" Or Range("G9") = "" Or Range("D11") = "" Then
Cancel = True
MsgBox "Please complete all Mandatory Fields"
Exit Sub
Else
Application.Dialogs(xlDialogSaveAs).Show
Cancel = True

End If

Application.EnableEvents = True

End Sub

"john" wrote:

it is considered good practice to always qualify the sheet the range refers to.
Another variation of the other posts.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set sh = Sheets("Sheet1")

Application.EnableEvents = False

If WorksheetFunction.CountA(sh.Range("G5,D7,G7,D9,G9, D11")) < 6 Then

Cancel = True

MsgBox "Please complete all Mandatory Fields"

Else

Application.Dialogs(xlDialogSaveAs).Show

Cancel = True

End If

Application.EnableEvents = True

End Sub

--
jb


"Arran" wrote:

Hi,

Can someone please tell me why the following code keeps crashing my excel?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("G5") = "" Or Range("D7") = "" Or Range("G7") = "" _
Or Range("D9") = "" Or Range("G9") = "" Or Range("D11") = "" Then
Cancel = True
MsgBox "Please complete all Mandatory Fields"
Exit Sub
Else
Application.Dialogs(xlDialogSaveAs).Show
End If
End Sub

Thank you in advance, any help on this will be greatly appreciated

Regards
Arran