Problems with BeforeSave and Application.EnableEvents = False

Hello,

I learned in this newsgroup to use the BeforeSave event to force the
user to do specific things when he wants to save the workbook. Well,
this works great. But sometimes I get an error I can't avoid.

If the filename already exists I get the question whether I want to
replace the file. If I decide not to replace the file and choose the
button 'No' or 'Cancel' then I get the run- time error
'1004'.

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

fname = Application.GetSaveAsFilename

Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=fname
Application.EnableEvents = True

End Sub

Can anybody give me some hint?

do you want this alert. if you dont want try to introduce in the beginning

application.displayalerts=false
and later at the end
application.displayalerts=true

I have not checked this.

Sanne <s.buehner@gmx.de> wrote in message
news:1108123401.295706.129610@c13g2000cwb.googlegroups.com...
> Hello,
>
> I learned in this newsgroup to use the BeforeSave event to force the
> user to do specific things when he wants to save the workbook. Well,
> this works great. But sometimes I get an error I can't avoid.
>
> If the filename already exists I get the question whether I want to
> replace the file. If I decide not to replace the file and choose the
> button 'No' or 'Cancel' then I get the run- time error
> '1004'.
>
> Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>
> fname = Application.GetSaveAsFilename
>
> Application.EnableEvents = False
> ActiveWorkbook.SaveAs Filename:=fname
> Application.EnableEvents = True
>
> End Sub
>
> Can anybody give me some hint?
>

Hi Sanne

Application.DisplayAlerts = False

before you try to do a save, should work.

Regards

Andrew Bourke


Sanne wrote:
> Hello,
>
> I learned in this newsgroup to use the BeforeSave event to force the
> user to do specific things when he wants to save the workbook. Well,
> this works great. But sometimes I get an error I can't avoid.
>
> If the filename already exists I get the question whether I want to
> replace the file. If I decide not to replace the file and choose the
> button 'No' or 'Cancel' then I get the run- time error
> '1004'.
>
> Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>
> fname = Application.GetSaveAsFilename
>
> Application.EnableEvents = False
> ActiveWorkbook.SaveAs Filename:=fname
> Application.EnableEvents = True
>
> End Sub
>
> Can anybody give me some hint?
>

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName as Variant
Dim ans as Long
fname = Application.GetSaveAsFilename
if fName = False then exit sub
If dir(fname) <> "" then
ans = msgbox( fname & vbNewline & _
"Already exists. Do you want to replace it? ", _
vbYesNo)
if ans = vbNo then
cancel = true
exit sub
end if
End if
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=fname
Application.EnableEvents = True

End Sub


Would be another approach.

--
Regards,
Tom Ogilvy

"Sanne" <s.buehner@gmx.de> wrote in message
news:1108123401.295706.129610@c13g2000cwb.googlegroups.com...
> Hello,
>
> I learned in this newsgroup to use the BeforeSave event to force the
> user to do specific things when he wants to save the workbook. Well,
> this works great. But sometimes I get an error I can't avoid.
>
> If the filename already exists I get the question whether I want to
> replace the file. If I decide not to replace the file and choose the
> button 'No' or 'Cancel' then I get the run- time error
> '1004'.
>
> Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>
> fname = Application.GetSaveAsFilename
>
> Application.EnableEvents = False
> ActiveWorkbook.SaveAs Filename:=fname
> Application.EnableEvents = True
>
> End Sub
>
> Can anybody give me some hint?
>