file name as specified cell when save as or save is clicked

[lynn]

Can someone help me? I have a document I have created in MSExcel. It is
being used by multiple users. I would like them to be able to have them
access the form, fill in the information and then when they go to save it
have the default save as filename be a specified cell value from the
spreadsheet. I have written code that does the filename as specified value
but I can only get it to run when I Run Code from VBA or when I choose
Macro>Run. Or I can get it to loop consistently on file open. I want it to
run when the user clicks save or saveas. Thanks for your help.

 

[lynn]

I should have added the code i have writen so far:


Sub save_as()
savedir = Sheets("No Problem Found Report").Range("F10").Value
fname = Sheets("No Problem Found Report").Range("F10").Value
Application.Dialogs(xlDialogSaveAs).Show ("c:\" & savedir & "\" & fname)
End Sub

 

[dan dungan]

Would either of these work?




From: "Ron de Bruin" <[email protected]>
Date: Wed, 27 Sep 2006 16:53:02 +0200
Local: Wed, Sep 27 2006 6:53 am
Subject: Re: Changing default Save As name to a cell location.

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").Range
("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



From: "moon" <[email protected]>
Date: Wed, 27 Sep 2006 17:01:43 +0200
Local: Wed, Sep 27 2006 7:01 am
Subject: Re: Changing default Save As name to a cell location.

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" <[email protected]> schreef in bericht

From: "gtslabs" <[email protected]>
Date: 27 Sep 2006 08:26:33 -0700
Local: Wed, Sep 27 2006 7:26 am
Subject: Re: Changing default Save As name to a cell location.

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
From: "moon" <[email protected]>
Date: Wed, 27 Sep 2006 18:03:23 +0200
Local: Wed, Sep 27 2006 8:03 am
Subject: Re: Changing default Save As name to a cell location.

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

"gtslabs" <[email protected]> schreef in bericht
news:[email protected]...

 

[lynn]

I am getting an error on this portion of the script:

fname =
Application.GetSaveAsFilename(Sheets("NoProblemFoundReport").Range("F10").Value, _
fileFilter:="Excel Files"
(*.xls), *.xls")

On the second option I am getting an error on this portion -

If Right(fileName, 4) <> ".xls" Then fileName = fileName &
".xls"

So I don't know if either would work, however, I think I probably do want it
to run if save is clicked, the problem is, I have technician's using my form
that are not careful about the file name and keep saving over the original.
They need to be spoon fed so they can't screw it up.

 

[dan dungan]

What error are you getting?

 

[lynn]

The script I noted was red and before I even ran it I got the messages
Compile error: Expected: line number or label or statement or end of
statement. I was using a simpler code, that worked, I just need to know how
to activate it upon clicking the save as or save buttons:

This is the code that performs the function

Sub save_as()
savedir = Sheets("No Problem Found Report").Range("F10").Value
fname = Sheets("No Problem Found Report").Range("F10").Value
Application.Dialogs(xlDialogSaveAs).Show ("c:\" & savedir & "\" & fname)
End Sub

This works when I click run or run macro, but I need to activate it to run
when I select save or saveas button.

 

[dan dungan]

I don't know how to do that. I'm not sure, but I think you would need
to create your own save button.