|
Before Save Event is not working when called from another Procedure
Hi All,
I've taken several stabs at this and am not getting anywhere. When I use the save button on the toolbar, the BeforeSave event below works as expected. However, when I envoke a save from another macro (btnSave), the event does not actually save when the filename is not the recommended name. When a macro button is pressed, this event should check if the file name matches the recommended name (ie Draft1.xls), if not (ie Draft2.xls) the user shoule be prompted with the SaveAs Dialog box. The user may or may not rename the file. When the user presses SAVE on the dialog box, the file should save ... but it does not. Any help would be appreciated. thanks, TW Baker -------------------------------------------------------- Sub btnSave() ThisWorkbook.Save End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft1.xls" '****RECOMMENDED NAME If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile '***This does not happen when save is called from another macro. Application.EnableEvents = True Cancel = True Else Cancel = True End If End If End Sub |
Before Save Event is not working when called from another Procedur
Please post the code that fails.
"TW Bake" wrote: Hi All, I've taken several stabs at this and am not getting anywhere. When I use the save button on the toolbar, the BeforeSave event below works as expected. However, when I envoke a save from another macro (btnSave), the event does not actually save when the filename is not the recommended name. When a macro button is pressed, this event should check if the file name matches the recommended name (ie Draft1.xls), if not (ie Draft2.xls) the user shoule be prompted with the SaveAs Dialog box. The user may or may not rename the file. When the user presses SAVE on the dialog box, the file should save ... but it does not. Any help would be appreciated. thanks, TW Baker -------------------------------------------------------- Sub btnSave() ThisWorkbook.Save End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft1.xls" '****RECOMMENDED NAME If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile '***This does not happen when save is called from another macro. Application.EnableEvents = True Cancel = True Else Cancel = True End If End If End Sub |
Before Save Event is not working when called from another Procedur
I did, it's under my description of the problem .... help!!!
On Jan 26, 7:18 pm, JLGWhiz wrote: Please post the code that fails. "TW Bake" wrote: Hi All, I've taken several stabs at this and am not getting anywhere. When I use the save button on the toolbar, the BeforeSave event below works as expected. However, when I envoke a save from another macro (btnSave), the event does not actually save when the filename is not the recommended name. When a macro button is pressed, this event should check if the file name matches the recommended name (ie Draft1.xls), if not (ie Draft2.xls) the user shoule be prompted with the SaveAs Dialog box. The user may or may not rename the file. When the user presses SAVE on the dialog box, the file should save ... but it does not. Any help would be appreciated. thanks, TW Baker -------------------------------------------------------- Sub btnSave() ThisWorkbook.Save End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft1.xls" '****RECOMMENDED NAME If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile '***This does not happen when save is called from another macro. Application.EnableEvents = True Cancel = True Else Cancel = True End If End If End Sub- Hide quoted text -- Show quoted text - |
Before Save Event is not working when called from another Procedur
i have no idea about why the code "ThisWorkbook.SaveAs vfile" in
Workbook_BeforeSave does not work when evoked from a macro. this is one example to work around, though i'm not sure if this is a right way and works in every cases. in standard module Sub btnSave() On Error GoTo errhandler ThisWorkbook.Save Application.DisplayAlerts = False Application.EnableEvents = False If ThisWorkbook.vfile = "" Then ThisWorkbook.Save Else ThisWorkbook.SaveAs ThisWorkbook.vfile End If Application.EnableEvents = True Exit Sub errhandler: Application.EnableEvents = True End Sub and in Thisworkbook module Public vfile 'this need to be declared here Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft.xls" '****RECOMMENDED NAME Application.DisplayAlerts = False If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile Application.EnableEvents = True Else vfile = "" End If Cancel = True Else vfile = ThisWorkbook.Name End If End Sub Regards keizi "TW Bake" wrote in message ups.com... I did, it's under my description of the problem .... help!!! On Jan 26, 7:18 pm, JLGWhiz wrote: Please post the code that fails. "TW Bake" wrote: Hi All, I've taken several stabs at this and am not getting anywhere. When I use the save button on the toolbar, the BeforeSave event below works as expected. However, when I envoke a save from another macro (btnSave), the event does not actually save when the filename is not the recommended name. When a macro button is pressed, this event should check if the file name matches the recommended name (ie Draft1.xls), if not (ie Draft2.xls) the user shoule be prompted with the SaveAs Dialog box. The user may or may not rename the file. When the user presses SAVE on the dialog box, the file should save ... but it does not. Any help would be appreciated. thanks, TW Baker -------------------------------------------------------- Sub btnSave() ThisWorkbook.Save End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft1.xls" '****RECOMMENDED NAME If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile '***This does not happen when save is called from another macro. Application.EnableEvents = True Cancel = True Else Cancel = True End If End If End Sub- Hide quoted text -- Show quoted text - |
Before Save Event is not working when called from another Procedur
On Jan 29, 7:19 pm, "kounoike" wrote:
i have no idea about why the code "ThisWorkbook.SaveAs vfile" in Workbook_BeforeSave does not work when evoked from a macro. this is one example to work around, though i'm not sure if this is a right way and works in every cases. in standard module Sub btnSave() On Error GoTo errhandler ThisWorkbook.Save Application.DisplayAlerts = False Application.EnableEvents = False If ThisWorkbook.vfile = "" Then ThisWorkbook.Save Else ThisWorkbook.SaveAs ThisWorkbook.vfile End If Application.EnableEvents = True Exit Sub errhandler: Application.EnableEvents = True End Sub and in Thisworkbook module Public vfile 'this need to be declared here Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft.xls" '****RECOMMENDED NAME Application.DisplayAlerts = False If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile Application.EnableEvents = True Else vfile = "" End If Cancel = True Else vfile = ThisWorkbook.Name End If End Sub Regards keizi "TW Bake" wrote in message ups.com... I did, it's under my description of the problem .... help!!! On Jan 26, 7:18 pm, JLGWhiz wrote: Please post the code that fails. "TW Bake" wrote: Hi All, I've taken several stabs at this and am not getting anywhere. When I use the save button on the toolbar, the BeforeSave event below works as expected. However, when I envoke a save from another macro (btnSave), the event does not actually save when the filename is not the recommended name. When a macro button is pressed, this event should check if the file name matches the recommended name (ie Draft1.xls), if not (ie Draft2.xls) the user shoule be prompted with the SaveAs Dialog box. The user may or may not rename the file. When the user presses SAVE on the dialog box, the file should save ... but it does not. Any help would be appreciated. thanks, TW Baker -------------------------------------------------------- Sub btnSave() ThisWorkbook.Save End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) tmpString = "Draft1.xls" '****RECOMMENDED NAME If ThisWorkbook.Name < tmpString Then vfile = Application.GetSaveAsFilename(tmpString) If vfile < False Then Application.EnableEvents = False ThisWorkbook.SaveAs vfile '***This does not happen when save is called from another macro. Application.EnableEvents = True Cancel = True Else Cancel = True End If End If End Sub- Hide quoted text -- Show quoted text -- Hide quoted text - - Show quoted text - Thanks! I'll give it a try. I'm also going to see about replacing the GetSaveAs with a GetOpen and see if that makes any difference. Thanks again!! |
| All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com