Hi,

I would like to know how to force a user to save a workbook upon closing,
with a new name, so that the original is never changed. I realise this can
be done by saving the file as a template, but I'd rather not have it as a
template as I have some other VBA procedures that go back and forth between
other workbooks, and if I use the template method I can't guarantee the file
name. I guess there might be a resolution here if I force a save to the file
name I'm after, but I wonder if that might be problematic if a file by that
name already exists........

Furthermore, once the file has been saved by a different name, what would
need to happen so the new file CAN be saved as its existing name.

I hope that's clear, but if not....
The file I never want changed is called "A".
When "A" is opened and some procedures are run, I then need the user to HAVE
TO save it under a new file and a name which he can decide (let's say "B").
When "B" is opened, any changes can be made to "B" and saved as usual.

Rob

You could force a save on open

Private Sub Workbook_Open()
Dim sFilename
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If sFilename < False Then
ThisWorkbook.SaveAs sFilename
Else
ThisWorkbook.Close False
End If
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Bob Phillips created this force event code.

"Rob" wrote:

Hi,

I would like to know how to force a user to save a workbook upon closing,
with a new name, so that the original is never changed. I realise this can
be done by saving the file as a template, but I'd rather not have it as a
template as I have some other VBA procedures that go back and forth between
other workbooks, and if I use the template method I can't guarantee the file
name. I guess there might be a resolution here if I force a save to the file
name I'm after, but I wonder if that might be problematic if a file by that
name already exists........

Furthermore, once the file has been saved by a different name, what would
need to happen so the new file CAN be saved as its existing name.

I hope that's clear, but if not....
The file I never want changed is called "A".
When "A" is opened and some procedures are run, I then need the user to HAVE
TO save it under a new file and a name which he can decide (let's say "B").
When "B" is opened, any changes can be made to "B" and saved as usual.

Rob

Thanks Sunday....,

However, it's not totally what I require, but it's a start. I'll have a
play with it and see. I don't really want the option to save presented on
opening, only if changes are made, and then to not allow it to be saved as
the same name as the file.
I suspect what I'm trying to do may be beyond the scope of Excel.

Rob

"Sunday88310" wrote in message
...
You could force a save on open

Private Sub Workbook_Open()
Dim sFilename
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If sFilename < False Then
ThisWorkbook.SaveAs sFilename
Else
ThisWorkbook.Close False
End If
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Bob Phillips created this force event code.

"Rob" wrote:

Hi,

I would like to know how to force a user to save a workbook upon closing,
with a new name, so that the original is never changed. I realise this
can
be done by saving the file as a template, but I'd rather not have it as a
template as I have some other VBA procedures that go back and forth
between
other workbooks, and if I use the template method I can't guarantee the
file
name. I guess there might be a resolution here if I force a save to the
file
name I'm after, but I wonder if that might be problematic if a file by
that
name already exists........

Furthermore, once the file has been saved by a different name, what would
need to happen so the new file CAN be saved as its existing name.

I hope that's clear, but if not....
The file I never want changed is called "A".
When "A" is opened and some procedures are run, I then need the user to
HAVE
TO save it under a new file and a name which he can decide (let's say
"B").
When "B" is opened, any changes can be made to "B" and saved as usual.

Rob