Save not triggering Before_Save event
in [Programming]
|
Prev: Creating a Pivot Table via Macro - Errors on Recorded Code
Next: UDF: how to tell if optional *range* parameter is empty?
From: mooresk257 on 2 Jun 2010 17:25 I have a before_save event that runs when I click the save icon on the tool bar or through the menu. But if I have a command button with this code: Private Sub Save_Click() ThisWorkbook.Save End Sub the Before_save event is called but the files does not save. This is the code for the before_save event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As String If IsEmpty(Sheet1.Range("F3")) Then _ If IsEmpty(Sheet1.Range("F4")) Then Exit Sub If Not IsEmpty(Sheet1.Range("F3")) Then _ If IsEmpty(Sheet1.Range("F4")) Then _ Fname = Sheet1.Range("F3").Value If Not IsEmpty(Sheet1.Range("F4")) Then _ If IsEmpty(Sheet1.Range("F3")) Then _ Fname = Sheet1.Range("F4").Value If Not IsEmpty(Sheet1.Range("F3")) Then _ If Not IsEmpty(Sheet1.Range("F4")) Then _ Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value On Error GoTo DumpSub Application.EnableEvents = False Cancel = True Application.Dialogs(xlDialogSaveAs).Show Fname DumpSub: Application.EnableEvents = True End Sub Why would the before_save event work with menu and toolbar commands but not with a save event triggered through code? Thanks, Scott
From: Gary''s Student on 2 Jun 2010 19:09 You are setting: Cancel = True This may explain why no save is occurring. -- Gary''s Student - gsnu201003 "mooresk257" wrote: > I have a before_save event that runs when I click the save icon on the tool > bar or through the menu. But if I have a command button with this code: > > Private Sub Save_Click() > > ThisWorkbook.Save > > End Sub > > the Before_save event is called but the files does not save. > > This is the code for the before_save event: > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) > > Dim Fname As String > > If IsEmpty(Sheet1.Range("F3")) Then _ > If IsEmpty(Sheet1.Range("F4")) Then Exit Sub > > If Not IsEmpty(Sheet1.Range("F3")) Then _ > If IsEmpty(Sheet1.Range("F4")) Then _ > Fname = Sheet1.Range("F3").Value > > If Not IsEmpty(Sheet1.Range("F4")) Then _ > If IsEmpty(Sheet1.Range("F3")) Then _ > Fname = Sheet1.Range("F4").Value > > If Not IsEmpty(Sheet1.Range("F3")) Then _ > If Not IsEmpty(Sheet1.Range("F4")) Then _ > Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value > > On Error GoTo DumpSub > > Application.EnableEvents = False > Cancel = True > Application.Dialogs(xlDialogSaveAs).Show Fname > > DumpSub: > Application.EnableEvents = True > > End Sub > > Why would the before_save event work with menu and toolbar commands but not > with a save event triggered through code? > > Thanks, > > Scott
From: mooresk257 on 2 Jun 2010 23:28
Thanks for the reply - Nope - if "Cancel = True" is not there, when you click on cancel in the "Application.Dialogs(xlDialogSaveAs).Show Fname" it saves the workbook when you click cancel - it doesn't cancel the save. As I said, the code runs fine when I save through File>Save, or through the save toolbar button. I find this situation perplexing. "Gary''s Student" wrote: > You are setting: > > Cancel = True > > This may explain why no save is occurring. > -- > Gary''s Student - gsnu201003 > > > "mooresk257" wrote: > > > I have a before_save event that runs when I click the save icon on the tool > > bar or through the menu. But if I have a command button with this code: > > > > Private Sub Save_Click() > > > > ThisWorkbook.Save > > > > End Sub > > > > the Before_save event is called but the files does not save. > > > > This is the code for the before_save event: > > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) > > > > Dim Fname As String > > > > If IsEmpty(Sheet1.Range("F3")) Then _ > > If IsEmpty(Sheet1.Range("F4")) Then Exit Sub > > > > If Not IsEmpty(Sheet1.Range("F3")) Then _ > > If IsEmpty(Sheet1.Range("F4")) Then _ > > Fname = Sheet1.Range("F3").Value > > > > If Not IsEmpty(Sheet1.Range("F4")) Then _ > > If IsEmpty(Sheet1.Range("F3")) Then _ > > Fname = Sheet1.Range("F4").Value > > > > If Not IsEmpty(Sheet1.Range("F3")) Then _ > > If Not IsEmpty(Sheet1.Range("F4")) Then _ > > Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value > > > > On Error GoTo DumpSub > > > > Application.EnableEvents = False > > Cancel = True > > Application.Dialogs(xlDialogSaveAs).Show Fname > > > > DumpSub: > > Application.EnableEvents = True > > > > End Sub > > > > Why would the before_save event work with menu and toolbar commands but not > > with a save event triggered through code? > > > > Thanks, > > > > Scott |