Save As Function in VBA

[davidgottlieb]

I have a form in excel. I have created a button that will save the file. What is supposed to happen is that the form is supposed to be saved as the "control number" which is in cell A3. How can I save a file that will reference a cell for the file name???

[Joacim Andersson]

Dim sFileName As String
sFileName = ActiveSheet.Range("C3").Value
ActiveWorkbook.SaveAs sFileName

Good luck!

[davidgottlieb]

for some reason that is not working. What's wrong?

[oetje]

This code should work:

Dim sFileName As String
sFileName = ActiveSheet.Range("A3").Value
ActiveWorkbook.SaveAs sFileName

[davidgottlieb]

Ok. I got it to work. It was a formula but I copied and pasted values. One problem. I am using the =NOW() formula for the control number in the Number format. I have it so it will display up to only 5 decimal places. However, when it saves the form, it saves it with like 10 decimal places. How can I keep that from happening? Also, when I used that code it doesn't come up with a dialog box for Save As. It just saves it. Help

[davidgottlieb]

That code works but, I need for the Save As dialog box to come up so the user can specify what folder to save it in. How do I do that?

[Joacim Andersson]

Sorry you can't have it both ways.
Either you pass a file name to the SaveAs method, and in that Excel saves the file without prompting, or you don't pass a file name to the method in which case Excel throghs up the Save As dialog so the user can choose a path and a name for the file.

Of course you could call SaveAs without the file name argument let the user set the path and the name and then you rename the file afterwards.

Or call up the "Browse for Folder" dialog so the user can only choose a path and not a name for the file.
See the tips section here at VB-World on how to call this dialog.

Best regards

[davidgottlieb]

I tried the Call SaveAs and it came up with an error. "Sub or Function not defined." what am I doing wrong?