Save As

Posted by lucky on January 21, 2002 8:51 AM

Hi all!
I wish to have a user prompted when they close a file to SaveAs " cell reference ". My attempt, using a UserForm and Command button was thus (that didn't work!):

Public Sub auto_close()
UserForm2.Show
End Sub

Private Sub CommandButton3_Click()
ActiveSheet.SaveAs (" & sheets ("home").range ("e6") & ")
End Sub

Any help/ideas would be appreciated!
Thanks again!

Posted by Juan Pablo G. on January 21, 2002 9:00 AM

Try

ActiveSheet.SaveAs Sheets("home").range("E6")

Juan Pablo G.

Posted by Richard Winfield on January 21, 2002 9:02 AM

This will save the file with a filename taken from a cell value without prompting the user unless a duplicate file name exists already. Don't know if this is exactly what you were looking for, but maybe it will help. This assumes that the user clicks "NO" when asked if you want to overwrite the file if it already exixts.

Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("d8").Value
On Error GoTo do_not_overwrite
ActiveSheet.SaveAs Filename:="C:\YOUR DIR\" & ThisFile & ".xls"

do_not_overwrite:
MsgBox "MESSAGE TO USER HERE."
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Posted by lucky on January 21, 2002 9:07 AM

thanks Juan, that works. Is it also possible for the user to have the Save As prompt box appear with Sheets("home").range("E6") in the file name box so the user can then decide which path (and change name if necessary)? i.e. as if they had gone File/Save As from the tool bar.
Thanks again.

Posted by Juan Pablo G. on January 21, 2002 9:12 AM

Use instead

Application.Dialogs(xlDialogSaveAs).Show

Juan Pablo G. .

: Try

Posted by lucky on January 21, 2002 9:21 AM

Thanks fellas, all good!

: Try