| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
beforesave macro help
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
beforesave macro help
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| beforesave | Excel Programming | |||
| BeforeSave macro | Excel Programming | |||
| Run macro with BeforeSave Event | Excel Programming | |||
| Trying to run macro on the BeforeSave event | Excel Programming | |||
| BeforeSave Sub | Excel Programming | |||
Linear Mode
