Shadow save

[Falakka]

I got a nice one to solve.

In a spreadsheet I made a form to be filled in by a user. After he filled in the form, he clicks on a button, assigned to a macro. The macro then prints out the form. This, off course, is not a hard thing to make.

What I want to achieve, is to save the file with a filename referred to a value in a cell, or even better, referred to two cells. This has to happen the same time when somebody uses the button to print the form. In this way, the user does not know that the file he prints, is secretly saved as well.

For example:
A1 ==> Date + Time ==> 05-06-08 16:44
A2 == > Username ==> John
A3-A10 ==> input

When John is done filling cells A3-A10, he clicks on the button which runs the macro to print the form and to save the form in the background with this generated name:

05-06-08 16:44 John.xls

Who can help me with this problem? I already read somewhere it can be done using VBA, but I want to know if there is a way without using VBA.

Yours faithfully,
Falakka

 

[The Dutchman]

Since you have a button already it's easy to put some code in there. You really cannot do it unless you have some VBA code. If it's a formula in cell B2, the date will change every time you open the file.

If the users name is in A2 and you want to put this combined date and name in B2 you could have the following code

Private Sub CommandButton1_Click()
Range("B2").value = format(now(),"mm-dd-yy") & " " & a2
End Sub

 

[Falakka]

Thnx for all the help. I still forgot something.

I want to save the file in a specified map, for example

c:\exceldocs\

So in the end by running the macro, I need to find a file like this

c:\exceldocs\05-05-08 Jonh Doe.xls

How do I do this?

 

[davie1982]

Sub CommandButton1()
Dim strLocation As String
strLocation = "C:\Exceldocs\"
Range("B2").Value = Format(Now(), "mm-dd-yy") & " " & a2
ActiveWorkbook.SaveAs (strLocation & Range("b2") & ".xls")
End Sub

 

[Falakka]

THNX all you guys for all the great help.
The thing I want to achieve is not perfect yet.

I see in the code(I don't know code, but I know formulas) that it says
Range("B2").Value = Format(Now(), "mm-dd-yy") & " " & a2
It saves as the date of the day you put in the data. I want to save it as the date in the cell where the user puts in the date.

I use the sheet to count a register, and the money gets counted after midnight, so if you save the file 00:33 06-05-08, it means the register is from 05-05-08. For that reason people fill in the date theirselves.

That's why I think that now() doesn't work the way I want it.

For example:
The date when filling in the form is 10-10-08
The date filled in on the form is a value in cell A1, 09-10-08.
The value in cell A2 is a name, filled in by the user, John Doe

I want to save the file as:
C:\Exceldocs\09-10-08 John Doe

Thnx for all the help, but if someone can fix this for me, I'm in heaven.4
Yours faithfully,
Falakka