Save As Button

Posted by Becky on December 17, 2001 9:10 AM

I'm trying to set up a simple form sheet in Excel that my team members can easily access and use for our clients.

I'm trying to add a customized button to my spreadsheet that when pressed will send me automatically to the "save as" screen where I can save it as a new document (so that I don't override the other documents) and then automatically clear all the fields.

Anyone know how to do this?

Posted by Tom Urtis on December 17, 2001 9:17 AM

If you are only looking for the line of code that displays the Save As box, it is:

Application.Dialogs(xlDialogSaveAs).Show

If you are looking for something additionally, please repost.

HTH

Tom Urtis

Posted by Becky on December 17, 2001 9:58 AM

Thanks! Is there a way to set the macro so that it will not allow saving over the current document?

Posted by Tom Urtis on December 17, 2001 10:13 AM

Becky,

There are a couple different ways you can go with that, but I'm not sure how you are organizing the entire task. Bringing up the Saved As dialog box as you first posted would suggest to me that you likely want the user to manually choose from that point what and where to name the file. If the user chooses a file name already being used in that path, Excel would automatically pop up its own message and satisfy your concern right there.

On the other hand, if this is part of other code that you are continuing past this point, you can insert Error Handling code to exit the sub with a message box that the file already exists:

On Error Goto File_Exists
ActiveSheet.SaveAs Filename:="C:\Your\File\Path\" & Whatever & ".xls"
On Error Goto 0
'Rest of your macro
Exit Sub
File_Exists:
MsgBox "File name already exists, choose another"

Again, there are other ways depending on what kind of procedure you are running and how much or little involvement you want/need from your end user.

Hope this helps.

Tom Urtis

Thanks! Is there a way to set the macro so that it will not allow saving over the current document?

Posted by John on December 26, 2001 9:23 AM

Not sure if this will help, but you might try adding a new toolbar button for this command. If you go to View-Toolbars-Customize, select the Commands tab, then drag the "Save As" command to your toolbar. Next, right-click on the newly-created button to edit; then select "Change Button Image", to change the button to say, the diskette with the arrow. Don't exit the Customize toolbar dialog box yet, though. If you want to get rid of the "Save As" text description next to the button image that you've created, right-click on the button again, and select "Default Style", which will hide the text description of the button and only show the button image you've selected for this new toolbar command.

Hope that helps.

John:

Thanks! Is there a way to set the macro so that it will not allow saving over the current document?