Prompt for Save As Window through macro

[h2fcell]

Hello,
I’m trying to create a macro that opens the file save as window with a
prefilled fill name and file format and allows me to save the file when I
click on the save button in the save as window.
The save as window opens, but when I click save the window closes and the
file is not saved.
Below is the code. Any suggestions are greatly appreciated.

Private Sub CommandButton2_Click()

Dim fileSaveName As String
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("B1").Value, _
FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

End Sub

 

[Bernie Deitrick]

You're only getting the name, not actually saving the workbook.

Add the line

ActiveWorkbook.SaveAs fileSaveName


HTH,
Bernie
MS Excel MVP

 

[Mike H]

Hi,

Try this

Dim fileSaveName As String
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("B1").Value, _
FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
If fileSaveName <> "" Then ActiveWorkbook.SaveAs fileSaveName

Mike

 

[Mike H]

OOPS,

I meant to say

If fileSaveName <> False Then ActiveWorkbook.SaveAs fileSaveName

In case the user presses cancel.

Mike

 

[h2fcell]

Thank you so very much.

Hi,

Try this

Dim fileSaveName As String
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("B1").Value, _
FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
If fileSaveName <> "" Then ActiveWorkbook.SaveAs fileSaveName

Mike

 

[Office_Novice]

try somthing like ...

Option Explicit

Sub Human()
Dim FileName As String

FileName = "YourFileName"

Application.Dialogs(xlDialogSaveAs).Show (FileName)
End Sub

 

[Chip Pearson]

If fileSaveName <> False Then ActiveWorkbook.SaveAs fileSaveName

In which case you need to declare fileSaveName as Variant, not String.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mike H]

Thanks, I forgot to alter that while correcting the error in my first post