Set initial filename before saving

[Pierre]

When a new workbook is created from a template the proposed (initial)
filename when saving the workbook is the "template name".xls. I would like
to have this proposed name (initial name) to be equal to cell content. Would
you please help me with the coding.

Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
'
' How do I run a macro every time a certain cell changes its value?
' Event called Worksheet_Change which is triggered when a
' value is entered (it will not fire when a formula result changes).


' If Intersect(Target, Range("A2")) Is Nothing Then
' Exit Sub
' Else
' Call Set_InitialFname
' End If

End Sub
----------

Sub Set_InitialFname()
'
' Propose a preset file name when the user will save the file
'
' Set the initial file name before saving
? =Range("A2").Value & ".xls"

End Sub

 

[StumpedAgain]

I think what you're looking for looks something like the following:

Sub Set_InitialFname()
'
' Propose a preset file name when the user will save the file
'
' Set the initial file name before saving
Dim initialname as String

initialname = Range("A2").Value & ".xls"

End Sub

You can then use 'initialname' in a variety of ways further along in your sub.

 

[Pierre]

Thanks StumpedAgain. But it doesn't work! I've typed "Allo" in Cell A2 (so
the sub is then executed) and it doesn't change the default filename when I
click on Save or SaveAs. In the SaveAs window the Filename by default is
still the Template Name.

 

[StumpedAgain]

I apologize I misunderstood your question. The following is the only way in
which I know to save using a cell reference:

ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\username\My
Documents\" & A2 & ".xls"

I am not aware of any way to change the default saveas name. Good luck!

 

[Pierre]

ActiveWorkbook.SaveAs Filename doesn't work well for my need. Any voluntrer?