Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Before Save Event is not working when called from another Procedure
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default 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 -

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 126
Default 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 -


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default 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!!

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
after save event/procedure kev_06[_12_] Excel Programming 8 June 9th 06 08:09 PM
Event Procedure not working AD108 Excel Programming 4 April 13th 06 08:06 PM
procedure continuously being called nathan Excel Programming 9 January 19th 06 08:40 PM
Shared Workbook; Update-on-Save not working when called from code? Evan Excel Programming 0 February 4th 05 07:17 PM
Save Event - Determine if called by code or user David Sedberry Excel Programming 0 October 2nd 03 04:56 PM


All times are GMT +1. The time now is 07:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement