Prompt for Save As Window through macro

H

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
 
B

Bernie Deitrick

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

Add the line

ActiveWorkbook.SaveAs fileSaveName


HTH,
Bernie
MS Excel MVP
 
M

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
 
M

Mike H

OOPS,

I meant to say

If fileSaveName <> False Then ActiveWorkbook.SaveAs fileSaveName

In case the user presses cancel.

Mike
 
H

h2fcell

Thank you so very much.

Mike H said:
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
 
O

Office_Novice

try somthing like ...

Option Explicit

Sub Human()
Dim FileName As String

FileName = "YourFileName"

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top