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

Hi,

maybe this but bear in mind that because the worksheet isn't specified this
works on the activesheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Range("D7"), Range("D9"), Range("D11"),
Range("G9"), _
Range("G7"), Range("G5")) < 6 Then
Cancel = True
MsgBox "Please complete all Mandatory Fields"
End If
End Sub



"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

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

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

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

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

Arran,
good that you have got it working but do take note of my point about
qualifying the range you are testing to a worksheet. If the sheet you think
you are checking is not the activesheet you will get incorrect result.
--
jb


"Arran" wrote:

> 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