Workbook_BeforeSave()

[bmm]

Hi,
How can I trap the file save as /save event and get the filename entered in
the dialogbox?
I tried to get the file name in "Workbook_BeforeSave() " event, but it
displays the dialog twice FileSave As dialog twice.
How can i avoid that dialog appearing twice

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename <> False Then
filesavename = filesavename
End If
Application.EnableEvents = True
Cancel = True
MsgBox filesavename
End Sub

 

[Tom Ogilvy]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename <> False Then
Thisworkbook.SaveAs FileName:=filesavename
End If
Application.EnableEvents = True
Cancel = True
End Sub

 

[bmm]

Thanks Tom,
this code works but when i try to save the file with the the existing file
name,
it asks if i want to replace the existing file, if i say No/ Cancel then it
gives me an Run time error "1004"
which says
"Method SaveAs of object '_Workbook' failed "
Can you please explain me why is it so?

Thanks once again

 

[Tom Ogilvy]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename <> False Then
On Error Resume Next
Application.DisplayAlerts = False
Thisworkbook.SaveAs FileName:=filesavename
Application.DisplayAlerts = True
On Error goto 0
End If
Application.EnableEvents = True
Cancel = True
End Sub

or

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename <> False Then
if lcase(thisworkbook.FullName) = lcase(filesavename) then
ThisWorkbook.Save
else
Application.DisplayAlerts = False
Thisworkbook.SaveAs FileName:=filesavename
Application.DisplayAlerts = True
End if
End If
Application.EnableEvents = True
Cancel = True
End Sub

Should avoid the error.