Creating Macro to Prompt for Save As

Microsoft Excel 2007
January 14, 2010 at 04:00:59
Specs: Windows 7
How to create MS Excel 2007 macro which
every time the file is launched, it will prompt
the user to “Save as"?

So that the file will not be overwritten.

Help me with the macro, please!

See More: Creating Macro to Prompt for Save As

Report •

January 14, 2010 at 06:14:59
This code is adapted from the example given in the VBA Help files for the GetSaveAsFilename amd SaveAs methods.

I put it inside the Workbook_Open event so that the Save As dialog box opens whenever the workbook is opened.

As written, it does not prevent the user from canceling the Save As, so that would have to be added if needed.

1 - Use Alt-F11 to open the VBA editor.
2 - Double-click the ThisWorkbook entry under the VBA Project list.
3 - Paste the following code into the pane that opens and save the file.

Private Sub Workbook_Open()
  fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Workbooks (*.xls), *.xls")
  If fileSaveName <> False Then
    Me.SaveAs Filename:=fileSaveName
  End If
End Sub

Report •
Related Solutions

Ask Question