How can I make the default "save as" file name that appears when the
window dialog box opens reference a cell in the worksheet where I have
its name stored.

Hi gtslabs

You can use GetSaveAsFilename
I use the value of Sheets("Sheet1").Range("A1").Value

Sub Test()
Dim fname As Variant
fname = Application.GetSaveAsFilename(Sheets("Sheet1").Ran ge("A1").Value, _
fileFilter:="Excel Files (*.xls), *.xls")
If fname < False Then
ActiveWorkbook.SaveAs fname
Else
'do nothing
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"gtslabs" wrote in message ups.com...
How can I make the default "save as" file name that appears when the
window dialog box opens reference a cell in the worksheet where I have
its name stored.

An alternative is to trigger the Save-event, which doesn't require an extra
mouse-click which launches a sub...


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim fileName As String

'Don't do this on Save, only on SaveAs
If SaveAsUI = True Then

'Grab filename from Cell A1
fileName = ActiveSheet.Cells(1, 1).Value

'What if they forgot the extension?
If Right(fileName, 4) < ".xls" Then fileName = fileName & ".xls"

'The TextBox for the filename in the dialog
'is already highlighted (selected) so all
'you have to do is send the filename
Application.SendKeys fileName

End If

End Sub






"Ron de Bruin" schreef in bericht
...
Hi gtslabs

You can use GetSaveAsFilename
I use the value of Sheets("Sheet1").Range("A1").Value

Sub Test()
Dim fname As Variant
fname =
Application.GetSaveAsFilename(Sheets("Sheet1").Ran ge("A1").Value, _
fileFilter:="Excel Files (*.xls),
*.xls")
If fname < False Then
ActiveWorkbook.SaveAs fname
Else
'do nothing
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"gtslabs" wrote in message
ups.com...
How can I make the default "save as" file name that appears when the
window dialog box opens reference a cell in the worksheet where I have
its name stored.

This is what I need thanks, but I can not get it to launch. Where would
it go? in a separate module or the worksheet code? I tried both with
no luck.

Thanks
Steven


moon wrote:
An alternative is to trigger the Save-event, which doesn't require an extra
mouse-click which launches a sub...


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim fileName As String

'Don't do this on Save, only on SaveAs
If SaveAsUI = True Then

'Grab filename from Cell A1
fileName = ActiveSheet.Cells(1, 1).Value

'What if they forgot the extension?
If Right(fileName, 4) < ".xls" Then fileName = fileName & ".xls"

'The TextBox for the filename in the dialog
'is already highlighted (selected) so all
'you have to do is send the filename
Application.SendKeys fileName

End If

End Sub






"Ron de Bruin" schreef in bericht
...
Hi gtslabs

You can use GetSaveAsFilename
I use the value of Sheets("Sheet1").Range("A1").Value

Sub Test()
Dim fname As Variant
fname =
Application.GetSaveAsFilename(Sheets("Sheet1").Ran ge("A1").Value, _
fileFilter:="Excel Files (*.xls),
*.xls")
If fname < False Then
ActiveWorkbook.SaveAs fname
Else
'do nothing
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"gtslabs" wrote in message
ups.com...
How can I make the default "save as" file name that appears when the
window dialog box opens reference a cell in the worksheet where I have
its name stored.

Sorry for not mentioning that.
It should be in the Workbook-code.
Double click on ThisWorkbook to end up there.



"gtslabs" schreef in bericht
ps.com...
This is what I need thanks, but I can not get it to launch. Where would
it go? in a separate module or the worksheet code? I tried both with
no luck.

Thanks
Steven


moon wrote:
An alternative is to trigger the Save-event, which doesn't require an
extra
mouse-click which launches a sub...


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim fileName As String

'Don't do this on Save, only on SaveAs
If SaveAsUI = True Then

'Grab filename from Cell A1
fileName = ActiveSheet.Cells(1, 1).Value

'What if they forgot the extension?
If Right(fileName, 4) < ".xls" Then fileName = fileName & ".xls"

'The TextBox for the filename in the dialog
'is already highlighted (selected) so all
'you have to do is send the filename
Application.SendKeys fileName

End If

End Sub






"Ron de Bruin" schreef in bericht
...
Hi gtslabs

You can use GetSaveAsFilename
I use the value of Sheets("Sheet1").Range("A1").Value

Sub Test()
Dim fname As Variant
fname =
Application.GetSaveAsFilename(Sheets("Sheet1").Ran ge("A1").Value, _
fileFilter:="Excel Files
(*.xls),
*.xls")
If fname < False Then
ActiveWorkbook.SaveAs fname
Else
'do nothing
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"gtslabs" wrote in message
ups.com...
How can I make the default "save as" file name that appears when the
window dialog box opens reference a cell in the worksheet where I have
its name stored.