cell value as a filename

[dunescratcher]

Hi all,


An earlier thread(help required with using cells to generate a filename) nearly helped but not quite.

I want to use a cell value as a filename with the workbook to be saved in a specific folder but I want the Dialog box to open first with the filename in it so the user has to actually click on Save As.

So far, so bad: what I have got doesn’t work at all and I’d be very grateful for any help at all

Many thanks in advance,

DS

[Nick Hodge]

Not sure I understand you, but if you are trying to force a 'save as...'
opposed to a save then you can use the workbook_save() event

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI Then
'do what you want here as user pressed Save as...
Exit Sub
End If
'cancel the save if user pressed save
Cancel = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"dunescratcher" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all,
>
>
> An earlier thread(help required with using cells to generate a
> filename) nearly helped but not quite.
>
> I want to use a cell value as a filename with the workbook to be saved
> in a specific folder but I want the Dialog box to open first with the
> filename in it so the user has to actually click on Save As.
>
> So far, so bad: what I have got doesn’t work at all and I’d
> be very grateful for any help at all
>
> Many thanks in advance,
>
> DS
>
>
> --
> dunescratcher
> ------------------------------------------------------------------------
> dunescratcher's Profile:
> http://www.excelforum.com/member.php...o&userid=33114
> View this thread: http://www.excelforum.com/showthread...hreadid=529322
>

[dunescratcher]

Nick!

Thanks for the quick reply. I have already got:

Sub SaveAs1()
'
' SaveAs1 Macro
' Macro recorded 03/04/2006 by GB
'
' Keyboard Shortcut: Ctrl+e
'
ActiveWorkbook.SaveAs "C:\Windows\All users\desktop\" & Range("E2") & ".xls"

End Sub

...which directly saves the workbook under the name in "E2". I am trying to still generate the same file name but want to force a Save As with the name coming up in dialog box and the destination file also. User to click on Save As to complete.

Thanks for your input so far,
DS

[dunescratcher]

Nick!

Thanks for the quick reply. I have already got:

Sub SaveAs1()
'
' SaveAs1 Macro
' Macro recorded 03/04/2006 by GB
'
' Keyboard Shortcut: Ctrl+e
'
ActiveWorkbook.SaveAs "C:\Windows\All users\desktop\" & Range("E2") & ".xls"

End Sub

...which directly saves the workbook under the name in "E2". I am trying to still generate the same file name but want to force a Save As with the name coming up in dialog box and the destination file also. User to click on Save As to complete.

Thanks for your input so far,
DS

[dunescratcher]

Hi,

Thanks again. It wasn't exactly what I was after bit it set me on the right track:

What I wanted to do was this:

Application.Dialogs(xlDialogSaveAs).Show "C:\Windows\All users\bureau\" & Range("E2") & ".xls"

which works fine. Am already working on another stupîd question....

Thanks again,

DS