Date Filename Save as Dialog Macro Thingy!!!!!!

[Cobbcouk]

Hi there excel peeps, Hope you all are well.

I've got a bit of a problem, no one I know can help, and I can't find
the A team!!!!

PLEASE.

Can anyone give me the Visual Basic Code to open up a 'save as' dialog
box, with the filename as 'dd/mm/yy_sportsreturn', (dd/mm/yy being the
save date or a cell reference to a date), but give the user the ability
to save the file where they want to?

I've had a few goes, but i'm new to VBA and I can get the file to save
as a date formatted filename but I can't just get the box to open with
the correct filename, filetype(excel), and let the user put it where he
wants. It's getting a bit frustrating.

If i'm asking too much from excel/VBA can someone just give me a nudge,
i'll shut up and go do an ECDL course or something

Many Thanks in advance

Gaz

 

[Cobbcouk]

PS

ive had a look round the forums and can't find an answer!!!!!

:

 

[Ed]

(1) Open Excel, press Alt+F11, press F1, and look for the Help file for
BuiltInDialogs. GO from there and explore in Help anything that's
unfamiliar to you.

(2) A better search engine is Google Groups; use a few key words in groups
*excel*.

HTH
Ed

 

[NickHK]

Cobbcouk,
You need to look at "GetSaveAsFilename".

Dim FileName as string
Dim RetVal As Variant

FileName=Format(Now(), "dd/mm/yy") & "_sportsreturn.xls"
str = Application.GetSaveAsFilename(FileName)
'make sure the user did not change the filename
If str<>FileName Then
Msgbox "No, file must be called " & FileName
Exit sub
Else
'OK to save
Thisworkbook.SaveAs FileName
End If

You need to handle the case when the clicks also. Hint: RetVal=False

NickHK