Before Save Event is not working when called from another Procedure

[TW Bake]

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

 

[JLGWhiz]

Please post the code that fails.

 

[TW Bake]

I did, it's under my description of the problem .... help!!!

 

[kounoike]

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]

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







- 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!!