Save it giving as a name the content of a cell

[Montse]

I wonder who could help me with this. I've got a spreadsheet called “Blank
Form†saved as a template. I want to attach a macro to a button in the
spreadsheet called “Save It†in a way that when someone clicks on it the Save
window opens up and in File name it writes the content of cell A1. The
content of the cell will be something like “NAT-NU-1-001†and it should ask
where to save it but give the name of the file by default.

Thank you

 

[Joel]

You need to first add a command button to worksheet. follow these directions

1) Open Control Toolbox toolbar: View Menu - Toolbars - Control Toolbox.
Trianle on toolbar toggle between enter/exit Design Mode. Placing mouse over
Triangle should say exit (which means you are in design mode). If it says
Enter, then press button once.
2) Press Command Button, then click on worksheet to add button. Resize
button if necessary.
3) Press Properties on Toolbar. Change Caption to "Save It".
4) Right Click Command button and select View Code.
5) Paste this code into VBA window between two lines that already exists.

filesavename = Application.GetSaveAsFilename( _
InitialFileName:= _
"c:\temp\" & Range("A1") & ".xls", _
FileFilter:= _
"Excel Files (*.xls), *.xls")
ThisWorkbook.SaveAs filesavename

6) Close VBA window
7) On toolbar, Press Triangle (exit design mode).
8) Button should now work.

 

[Montse]

Thank you Joel. Your reply was very helpful. I wonder if you could help me
with this related requirement. I would like to insert another button that
when clicked it sends the file as an attachment. Something similar to the
File - Sent To - Mail Recipient (as attachment). The problem is that if the
user didn't save the file previously to sending it as an attachement it gives
to the attached file the name False.xls (because I am working with a
template). What would be the code that Sends it as an attachment and if the
file hasn't been given a name yet, it assigns it the name as described in the
code that you gave me in your answer (name from cell A1).

I would really appreciate if you could help me with this.

Thank you,

 

[Joel]

I don't think you can do exactly what you want to do. You can't rename a
spreadsheet. You can only saveas the spreadsheet to change the name. You
could save the file in a temporary directory if you don't want to over-write
an existing file.

I don't know if your filename includes a path so I inluded two versions of
the code. One version strips the pathname the other doesn't.

An unsaved file doesn't contain the extension.xls. So if you get the
thisworkbook.name and it doesn't contain xls, then it is unsaved.

Sub test()

If InStr(ThisWorkbook.Name, "xls") > 0 Then
'code if saved
Else
'code if not saved
ThisWorkbook.Name = Range("A1").Text
End If

End Sub

Sub test()

If InStr(ThisWorkbook.Name, "xls") > 0 Then
'code if saved
Else
'code if not saved
NewName = Range("A1").Text
Do While InStr(NewName, "\") > 0
NewName = Mid(NewName, _
InStr(NewName, "\") + 1)
Loop
ThisWorkbook.SaveAs _
Filename:="c:\temp\" & NewName
End If


End Sub