cell value as a filename

[dunescratcher]

Hi all,


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

I want to use a cell value as a filename with the workbook to be save
in a specific folder but I want the Dialog box to open first with th
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’
be very grateful for any help at all

Many thanks in advance,

D

 

[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
(e-mail address removed)


"dunescratcher" <[email protected]>
wrote in message
news:[email protected]...

 

[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 a
trying to still generate the same file name but want to force a Save A
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,
D

 

[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 a
trying to still generate the same file name but want to force a Save A
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,
D

 

[dunescratcher]

Hi,

Thanks again. It wasn't exactly what I was after bit it set me on th
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,

D