Arran,
my version seemed to work ok - give that try!
--
jb


"Arran" wrote:

Thank you for the quick responses but I wasn't clear enough, the code works
fine until I change the filename in the saveas dialog box. It saves the
workbook as the name I enter but then crashes excel and reopens the work book.

Any ideas?

I have also tried putting the code in a seperate module and calling (Run
"Macro Name") this in to action but it still crashes excel when the filename
is changed. If the filename already exists it saves fine.

"Jacob Skaria" wrote:

Try

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
End If
Application.EnableEvents = True
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"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