Changing Save As... Filename Prompt Default to CELL value

[Andy]

I have a spreadsheet that contains a value which I would like to turn into the Filename Prompt in the Save As... Dialog box.

How do you do this?

 

[Dave Peterson]

Like in a macro???

Application.Dialogs(xlDialogSaveAs).Show _
arg1:=ActiveWorkbook.Worksheets("sheet1").Range("a1").Value & ".xls"

 

[Andy]

Thanks for the reply. I tried creating a Macro like...

Sub Filename_Prompt_Improver()
'
' Filename_Prompt_Improver Macro
' Macro recorded 28/06/2004
'
Application.Dialogs(xlDialogSaveAs).Show _
arg1:=ActiveWorkbook.Worksheets("Quotation").Range("J3").Value & ".xls"


End Sub

Nothing happenned. When I opened the SaveAs ... dialog box, there was still the [template's name].xls. BTW the file is .xlt if that makes any difference.

 

[Andy]

sorry...the save as dialog did appear but the filename prompt was the existing templatename.xlt

Thanks for the reply. I tried creating a Macro like...

Sub Filename_Prompt_Improver()
'
' Filename_Prompt_Improver Macro
' Macro recorded 28/06/2004
'
Application.Dialogs(xlDialogSaveAs).Show _
arg1:=ActiveWorkbook.Worksheets("Quotation").Range("J3").Value & ".xls"


End Sub

Nothing happenned. When I opened the SaveAs ... dialog box, there was still the [template's name].xls. BTW the file is .xlt if that makes any difference.

Like in a macro???

Application.Dialogs(xlDialogSaveAs).Show _
arg1:=ActiveWorkbook.Worksheets("sheet1").Range("a1").Value & ".xls"

 

[promethious]

I used the "Before Save" selection from the VBA editor and this was th
code.

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

Application.Dialogs(xlDialogSaveAs).Show _
arg1:=ActiveWorkbook.Worksheets("PO").Range("a10").Value & ".xls"

End Sub


When I select Save As from the file menu it correctly displays th
contents of cell A10 as the file name however after I save another Sav
As box opens wanting me to save again. Does anyone know of a way t
prevent this