Problems with BeforeSave and Application.EnableEvents = False

  • Thread starter Thread starter Sanne
  • Start date Start date
S

Sanne

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.
 
Hi Sanne

Application.DisplayAlerts = False

before you try to do a save, should work.

Regards

Andrew Bourke
 
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.
 
Back
Top