PDA

View Full Version : Solved: Application display alert question



PEV
09-09-2011, 01:37 PM
I have the below code as part of a large code. Everything does what it should be doing but i'm still getting the "save as" promt appearing. I would rather it didnt and simply put the newly renamed file on the user's desktop. I thought the Application.DisplayAlerts True/False would have done it not noooooo

Dim Fname As String
Cancel = True
Application.DisplayAlerts = False
ChDir "C:\Documents and Settings\All Users\Desktop\"
Fname = Application.GetSaveAsFilename(ActiveSheet.Range _("AA54").Value, fileFilter:="xls Files (*.xls), *.xls")
If Fname <> "False" Then
ActiveWorkbook.SaveAs Fname
Application.DisplayAlerts = True

What can i do to kill the Prompt?

frank_m
09-09-2011, 02:49 PM
If you do not want to see a File Name Browser, and if the file name is always based on the value in Range("AA54").Value and you do not want to be prompted when over writing the file, then perhaps something like this:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Fname As String

Application.DisplayAlerts = False

Fname = "C:\Documents and Settings\UserName\Desktop\" & ActiveSheet.Range("AA54").Value & ".xls"

ActiveWorkbook.SaveAs Fname

Application.DisplayAlerts = True

End Sub

PEV
09-12-2011, 08:45 AM
Muuuuuch better. Thanks Frank!