beforesave macro help

  • Thread starter Thread starter Arran
  • Start date Start date
A

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