On Jan 29, 7:19 pm, "kounoike" <kouno...@nowherembh.nifty.com> 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" <twbsoluti...@msn.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >I did, it's under my description of the problem .... help!!!
>
> > On Jan 26, 7:18 pm, JLGWhiz <JLGW...@discussions.microsoft.com> 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!!
|
Similar Threads
