macro to ask user what to save file as

  • Try this:

    ActiveWorkbook.SaveAs InputBox("Please enter file name")

    Of course, that saves in the default file directory. If you want to change that, you can look at the other arguments for the SaveAs Method.

    Hope it helps!

  • Hello,

    if you want exactly the same as the save as-function in Excel, you have to create a userform in Vba and put a commondialog control on it(rigth click on the toolbox - additional controls - microsoft commondialog control "6.0").

    Code behind a button:

    [vba]'Initialize commondialog-box
    frmFormulier.CommonDialog.DialogTitle = "Save as ..."
    frmFormulier.CommonDialog.InitDir = ActiveWorkbook.Path 'start-path
    frmFormulier.CommonDialog.Filter = "Excelfile|*.xls" 'Type of file
    frmFormulier.CommonDialog.Filename = "" 'Standard file name

    'Show type of commondialig-box open, save, ...

    'Save workbook
    Select Case frmFormulier.CommonDialog.Filename
    Case Is <> ""
    'There is a name filled in
    ActiveWorkbook.SaveAs frmFormulier.CommonDialog.Filename
    End Select[/vba]

    I have uploaded a small example.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!