Savename suggestion

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

I try to put my own filename suggestion into the SaveAs dialog.

I have tried something like this

strfilename = sSeets(1).Range("b1") & Sheets(1)Range("c1")
Application.GetSaveAsFilename strfilename

And it gets my name from the sheet allright.

But if i put it in the BeforeSave event the dialog is display on Save as
well as SaveAs. And using SaveAs the dialog is shown twice.

What can I do to put my own name suggestion into the standard FileSaveAs
dialog?

Jan
 
Try

strfilename = sSeets(1).Range("b1") & Sheets(1)Range("c1")
ActiveWorkbook.SaveAs Filename:= strfilename

in the BeforeClose event
 
Thanks Sandy, but you code saves the workbook. I just want the SaveAs dialog
displayed, with a default name of my own, so the user still will be able to
change it, if so pleases.

Jan
 
Hi Jan
You may be able to adapt this to your needs, I wanted a back-up copy with a
different name and path because existing forms are over written without
changing the name, thus messing up 2 forms.

Response = MsgBox(Prompt:="Would You Like to Create a Back-Up Copy?",
Buttons:=vbYesNoCancel + vbDefaultButton1, Title:="BACK-UP COPY")
If Response = vbYes Then
ActiveSheet.Unprotect
BookName = ActiveWorkbook.Name ' Cell outside of print area
Range("BckUp").Select ' Copy of calibration or recall date depending
- also outside print area, in mm-dd-yy format.
Selection = ClearContents
Selection = InputBox(Prompt:="Your Back-Up Form Name Will Be Like
the Window Below.", Title:="BACK-UP COPY", Default:=Range("BckUpDt").Text + "
" + (BookName))
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
If ActiveWorkbook.Saved = False Then ActiveWorkbook.Save
ChDir "\YOUR DIRECTORY\Current Year"
ActiveWorkbook.SaveCopyAs FileName:=Range("BckUp").Text
ChDir "\YOUR DIRECTORY\DEFALT PATH"

Please let me know if it helped and your modification. I may want to utilize
them elsewhere myself
Lou
 
Set cancel to true in the BeforeSave event, then disable events and perform
the save using the name returned from your prompt.
Now enable events again.
 
Thank you all. In used Tom's solution.

Jan

Tom Ogilvy said:
Set cancel to true in the BeforeSave event, then disable events and
perform
the save using the name returned from your prompt.
Now enable events again.
Click to expand...
 
Back
Top