beforesave macro help

[Arran]

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

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[john]

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

 

[john]

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

 

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

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.