automatic prompt for save as on open

Posted by naeblis on November 12, 2001 7:06 AM

I am trying to write a macro that will automatically prompt to save as when a spreadsheet is opened. I can get it to automatically save but I cannot get it to give a prompt so I can enter a new file name. Can anyone help?

Thanks, Naeblis

Posted by Barrie Davidson on November 12, 2001 7:31 AM

Try something like this:

Dim NewName As String
'your code
NewName = Application.GetSaveAsFilename
ThisWorkbook.SaveAs FileName:=NewName
'the rest of your code

Regards,
BarrieBarrie Davidson

Posted by Rick E on November 12, 2001 10:32 AM

Use Auto_Open and xlDialogSaveAs

Try this:

Sub Auto_Open()
Dim ck As Boolean
str1 = "Enter a new file name."
ck = Application.Dialogs(xlDialogSaveAs).Show(str1)
If ck = True Then
newName = ActiveWorkbook.Name
else
MsgBox "You Canceled the SaveAs request."
End If
End Sub

When the workbook is opened, the SaveAs dialog box is shown and a request to enter a new name displayed. If the dialog is "Canceled" then a "False" is returned, so you know the Cancel button was clicked.

Good luck