Hi

I want to be able to force a user of a spreadsheet to save it in a standard
format using two of the cells contained within the spreadsheet as the save
name. Would anyone know of a macro that I could allow the user to run to do
this?

Thanks
Robin

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim nmSave As Range

Set nmSave = Sheets("Sheet1").Range("A1")

If nmSave = "" Then
MsgBox "There is no save value", vbOKOnly
Exit Sub
End If


ActiveWorkbook.SaveAs nmSave
End Sub

You will need to customize the entry points (sheet, cell, etc). Post back
if you need more help.



--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Robin1979" wrote:

Hi

I want to be able to force a user of a spreadsheet to save it in a standard
format using two of the cells contained within the spreadsheet as the save
name. Would anyone know of a macro that I could allow the user to run to do
this?

Thanks
Robin

Hello, thanks for the reply: I have it set against a button click with the
below code but I get a compile error - I presume due to something on the
first line being incorrect with the rest of the code??
Thanks

Sub Button30_Click()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim nmSave As Range

Set nmSave = Sheets("Home").Range("C7")

If nmSave = "" Then
MsgBox "There is no save value", vbOKOnly
Exit Sub
End If


ActiveWorkbook.SaveAs nmSave
End Sub

"PJFry" wrote:

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim nmSave As Range

Set nmSave = Sheets("Sheet1").Range("A1")

If nmSave = "" Then
MsgBox "There is no save value", vbOKOnly
Exit Sub
End If


ActiveWorkbook.SaveAs nmSave
End Sub

You will need to customize the entry points (sheet, cell, etc). Post back
if you need more help.



--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Robin1979" wrote:

Hi

I want to be able to force a user of a spreadsheet to save it in a standard
format using two of the cells contained within the spreadsheet as the save
name. Would anyone know of a macro that I could allow the user to run to do
this?

Thanks
Robin

Remove the:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

That piece tells Excel to save the file when it is closed. The advantage to
this method is that the user does not have to do anything. They just close
Excel and the file automatically saves with the names in the cells.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Robin1979" wrote:

Hello, thanks for the reply: I have it set against a button click with the
below code but I get a compile error - I presume due to something on the
first line being incorrect with the rest of the code??
Thanks

Sub Button30_Click()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim nmSave As Range

Set nmSave = Sheets("Home").Range("C7")

If nmSave = "" Then
MsgBox "There is no save value", vbOKOnly
Exit Sub
End If


ActiveWorkbook.SaveAs nmSave
End Sub

"PJFry" wrote:

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim nmSave As Range

Set nmSave = Sheets("Sheet1").Range("A1")

If nmSave = "" Then
MsgBox "There is no save value", vbOKOnly
Exit Sub
End If


ActiveWorkbook.SaveAs nmSave
End Sub

You will need to customize the entry points (sheet, cell, etc). Post back
if you need more help.



--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Robin1979" wrote:

Hi

I want to be able to force a user of a spreadsheet to save it in a standard
format using two of the cells contained within the spreadsheet as the save
name. Would anyone know of a macro that I could allow the user to run to do
this?

Thanks
Robin