Macro for opening "Save As" dialogue box

[Olivia]

I want to create a macro in excel that opens the "Save
As" dialogue box so the user can save a copy of the data
they have entered in the spreadsheet.

When I record the macro to do this, and open the dialogue
box, the macro is "locked out" because you can't do any
work in the spreadsheet while the dialogue box is open.
You can only stop recording the macro when you close
the "Save as" dialogue box.

I have tried to assign a keyboard shortcut instead to get
the dialogue box open but cannot find in the help menu
how to create my own keyboard shortcuts. All it says is
that the two keyboard shortcuts are SHIFT + F12 or ALT +
SHIFT + F2 but neither of these work to get the save as
dialogue box displayed.

 

[Vasant Nanavati]

Hi Olivia:

Application.Dialogs(xlDialogSaveAs).Show

will open the SaveAs dialog. However, I suggest that you also look at the
GetSaveAsFilename method which allows you to have more control over the
process (e.g., if the user selects an invalid filename).

Regards,

Vasant.