Hi...

I have an auto close routine which is fine. Except I need the file to name
itself from the value of cell A1 as it closes. The user has no option in
saving the file to any other name than what is in cell A1. eg. If cell A1 has
David Jones in it then the excel file will automatically save as David
Jones.xls. Also when the sheet closes I would like a message telling the user
the name of the file and also its location.

Grateful for any help...

Thanks

Gordon...

Gordon

Maybe this will give you a start

Sub Auto_Close()
Dim SfName As String
SfName = Worksheets("Sheet1").Range("A1").Value
With ThisWorkbook
.SaveAs Filename:=SfName
MsgBox "File saved as " & SfName & ".xls" & Chr(13) & _
"In location " & .Path, vbInformation + vbOKOnly
End With
End Sub

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


"Gordon" wrote in message
...
Hi...

I have an auto close routine which is fine. Except I need the file to name
itself from the value of cell A1 as it closes. The user has no option in
saving the file to any other name than what is in cell A1. eg. If cell A1
has
David Jones in it then the excel file will automatically save as David
Jones.xls. Also when the sheet closes I would like a message telling the
user
the name of the file and also its location.

Grateful for any help...

Thanks

Gordon...

Nick...

How can I specify within that code that the location for the file to be
saved should be c:/windows/desktop

As the current default its picking an obscure folder that I don't want to use.

Thanks in advance.

G



"Nick Hodge" wrote:

Gordon

Maybe this will give you a start

Sub Auto_Close()
Dim SfName As String
SfName = Worksheets("Sheet1").Range("A1").Value
With ThisWorkbook
.SaveAs Filename:=SfName
MsgBox "File saved as " & SfName & ".xls" & Chr(13) & _
"In location " & .Path, vbInformation + vbOKOnly
End With
End Sub

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


"Gordon" wrote in message
...
Hi...

I have an auto close routine which is fine. Except I need the file to name
itself from the value of cell A1 as it closes. The user has no option in
saving the file to any other name than what is in cell A1. eg. If cell A1
has
David Jones in it then the excel file will automatically save as David
Jones.xls. Also when the sheet closes I would like a message telling the
user
the name of the file and also its location.

Grateful for any help...

Thanks

Gordon...

Gordon

You could add the path to the file name and dispense with ThisWorkbook.Path

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


"Gordon" wrote in message
...
Nick...

How can I specify within that code that the location for the file to be
saved should be c:/windows/desktop

As the current default its picking an obscure folder that I don't want to
use.

Thanks in advance.

G



"Nick Hodge" wrote:

Gordon

Maybe this will give you a start

Sub Auto_Close()
Dim SfName As String
SfName = Worksheets("Sheet1").Range("A1").Value
With ThisWorkbook
.SaveAs Filename:=SfName
MsgBox "File saved as " & SfName & ".xls" & Chr(13) & _
"In location " & .Path, vbInformation + vbOKOnly
End With
End Sub

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


"Gordon" wrote in message
...
Hi...

I have an auto close routine which is fine. Except I need the file to
name
itself from the value of cell A1 as it closes. The user has no option
in
saving the file to any other name than what is in cell A1. eg. If cell
A1
has
David Jones in it then the excel file will automatically save as David
Jones.xls. Also when the sheet closes I would like a message telling
the
user
the name of the file and also its location.

Grateful for any help...

Thanks

Gordon...

Soz Nick...

I'm a dunce...I don't know how to do that...I've just crashed the whiole
thing trying. Could you resend code with alteration?

Cheers

G

"Nick Hodge" wrote:

Gordon

You could add the path to the file name and dispense with ThisWorkbook.Path

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


"Gordon" wrote in message
...
Nick...

How can I specify within that code that the location for the file to be
saved should be c:/windows/desktop

As the current default its picking an obscure folder that I don't want to
use.

Thanks in advance.

G



"Nick Hodge" wrote:

Gordon

Maybe this will give you a start

Sub Auto_Close()
Dim SfName As String
SfName = Worksheets("Sheet1").Range("A1").Value
With ThisWorkbook
.SaveAs Filename:=SfName
MsgBox "File saved as " & SfName & ".xls" & Chr(13) & _
"In location " & .Path, vbInformation + vbOKOnly
End With
End Sub

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


"Gordon" wrote in message
...
Hi...

I have an auto close routine which is fine. Except I need the file to
name
itself from the value of cell A1 as it closes. The user has no option
in
saving the file to any other name than what is in cell A1. eg. If cell
A1
has
David Jones in it then the excel file will automatically save as David
Jones.xls. Also when the sheet closes I would like a message telling
the
user
the name of the file and also its location.

Grateful for any help...

Thanks

Gordon...

Gordon

Something Like

Sub Auto_Close()
Dim SfName As String
SfName = "C:\Windows\Desktop\" & Worksheets("Sheet1").Range("A1").Value &
".xls"
With ThisWorkbook
.SaveAs Filename:=SfName
MsgBox "File saved as " & SfName
End With
End Sub

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


"Gordon" wrote in message
...
Soz Nick...

I'm a dunce...I don't know how to do that...I've just crashed the whiole
thing trying. Could you resend code with alteration?

Cheers

G

"Nick Hodge" wrote:

Gordon

You could add the path to the file name and dispense with
ThisWorkbook.Path

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


"Gordon" wrote in message
...
Nick...

How can I specify within that code that the location for the file to be
saved should be c:/windows/desktop

As the current default its picking an obscure folder that I don't want
to
use.

Thanks in advance.

G



"Nick Hodge" wrote:

Gordon

Maybe this will give you a start

Sub Auto_Close()
Dim SfName As String
SfName = Worksheets("Sheet1").Range("A1").Value
With ThisWorkbook
.SaveAs Filename:=SfName
MsgBox "File saved as " & SfName & ".xls" & Chr(13) & _
"In location " & .Path, vbInformation + vbOKOnly
End With
End Sub

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


"Gordon" wrote in message
...
Hi...

I have an auto close routine which is fine. Except I need the file
to
name
itself from the value of cell A1 as it closes. The user has no
option
in
saving the file to any other name than what is in cell A1. eg. If
cell
A1
has
David Jones in it then the excel file will automatically save as
David
Jones.xls. Also when the sheet closes I would like a message telling
the
user
the name of the file and also its location.

Grateful for any help...

Thanks

Gordon...