Save As Macro

[Guy]

Office Basic 2003 - XP - Excel

I need a macro that will bring up the "Save As" window when button is
selected and to also insert a particular cell value in name field of "Save
As" window, then if possible to also save and exit all with this one macro.
I am a novice at this so a little extra explanation with it would be great.

I've been trying to use this macro that was posted but having trouble:

Sub SaveAs()
'Open the save as dialog box and show the value from A1
Dim myValue As String

myValue = ActiveSheet.Range("A1").Value
Dim fd As Dialog
Set fd = Application.Dialogs(xlDialogSaveAs)

If fd.Show(myValue) = False Then
'User pressed cancel, sheet not saved
'Add any additional code here if necessary
Else
'User has saved the file, update the data in range A1
'Run SetDate macro
SetDate
End If
End Sub


Thanks,
Guy

 

[Guy]

Update:
The cell value I wish to save with this macro is the current Date that is
inserted into the cell with the macro:

Sub SetDate4()
'Set cell A1 to the current date
ActiveSheet.Range("E2").Value = Date
End Sub

I get the "Save As" macro to partially work with:

Sub SaveAs4()
'Open the save as dialog box and show the value from E2
Dim myValue As String

myValue = ActiveSheet.Range("E2").Value
Dim fd As Dialog
Set fd = Application.Dialogs(xlDialogSaveAs)

If fd.Show(myValue) = False Then
'User pressed cancel, sheet not saved
'Add any additional code here if necessary
Else
'User has saved the file, update the data in range E2
End If
End Sub

It displays the "Save As" window with the date inserted into the name field,
which is what I want but the problem with this is that it puts the date in
formatted as 12/12/2008 and that cannot be saved. It needs to be formated as
12-12-08 *( - instead of / ). The cell on the sheet is formatted to
display 12-12-08 but when it is inserted into the "Save As" window it is
inserted as 12/12/2008 which cannot be saved.
Can someone help with this?

 

[Gord Dibben]

12/12/08 is ambiguous and will depend upon Regional Settings

Mine are dd/mm/yy

Sub SetDate4()
'Set cell E2 to the current date
ActiveSheet.Range("E2").Value = Format(Date, "dd-mm-yy")
End Sub

Works for me today(Dec 13th)


Gord Dibben MS Excel MVP