Macro or VBA help
 

I would like to add a button to my spreadsheet that
opens "save as" and then after the user saves it it
reopens the original file.
Can anyone help me get that going?

Thanks.

Macro or VBA help
 

Hi,

It is not necessary to close the file and re-open it. Using SaveCopyAs will
save a copy to disk whilst keeping the original open.

Try attaching the following to your button:

Sub MyCopyAs()
Dim fName As Variant, Res As Variant
Res = MsgBox("Save a copy of this file?", vbYesNo)
If Res = vbYes Then
fName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files Files (*.xls), *.xls")
If fName < False Then
ActiveWorkbook.SaveCopyAs Filename:=fName
End If
End If
End Sub

---
Regards,
Norman


Try attaching the following macro to your button

wrote in message
...
I would like to add a button to my spreadsheet that
opens "save as" and then after the user saves it it
reopens the original file.
Can anyone help me get that going?

Thanks.

Macro or VBA help
 

Here is some code to attach to the button


thisFile = ActiveWorkbook.Fullname
ans = MsgBox("Save file now?", vbYesNo)
If ans = vbYes Then
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If fileSaveName < False Then
Activworkbook.SaveAs Filename:=fileSaveName
Workbooks.Open filename:= thisFile
End If
End If



--
HTH

-------

Bob Phillips
wrote in message
...
I would like to add a button to my spreadsheet that
opens "save as" and then after the user saves it it
reopens the original file.
Can anyone help me get that going?

Thanks.

Macro or VBA help
 

Here's code that make that work:
Sub SaveMe()
Dim wkbk As String
' grab name of open workbook
wkbk = ActiveWorkbook.FullName
' use built-in dialog to SaveAs
Application.Dialogs(xlDialogSaveAs).Show
' close saved workbook
ActiveWorkbook.Close
' reopen original workbook
Application.Workbooks.Open (wkbk)
End Sub

I think I would put it in the WorkSheet module for the sheet that's going to
have the button on it. Create your button and assign this macro to it.

Ed

wrote in message
...
I would like to add a button to my spreadsheet that
opens "save as" and then after the user saves it it
reopens the original file.
Can anyone help me get that going?

Thanks.