Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button (called
Save) placed on the sheet to generate a file name based on contents of cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once the
file is saved with this name & the user clicks on the Macro button (Save)
next time, it displays the Excel message, which alerts the user that the
file already exists & asks whether you want to replace it with choices of ,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way of
achieving what I am trying to do. Should this code be in a module or should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria

Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel, they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save button,
then you need to test if the file already exists, if it doesn't then use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once the
file is saved with this name & the user clicks on the Macro button (Save)
next time, it displays the Excel message, which alerts the user that the
file already exists & asks whether you want to replace it with choices of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button on the
worksheet, then users may bypass it by selecting "File", "Save or Save As".
In that case, probably I should disable the 'File/Save/SaveAs' from the menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or ?
BeforeClose event. This way, it will always be activated, but when I tried
it out, I have a feeling that this saves the file twice (increasing time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user that
the
file already exists & asks whether you want to replace it with choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if
the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Maria

The FileSave and FileSaveAs options will always be a possibility wherever
you place the code. So to prevent this you will need to disable these menu
options but do you?

If the code is placed in the workbook before close event then the file will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign the
macro to your 'Save' control button, users can press the button to save the
file, resave it etc., if they don't then the workbook close event kicks in
and saves it anyway. The only problem is the user saves it as another name
buth this does not affect the primary operation, just clutters the system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or ?
BeforeClose event. This way, it will always be activated, but when I tried
it out, I have a feeling that this saves the file twice (increasing time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving
them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user that
the
file already exists & asks whether you want to replace it with choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more elegant
way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Since you don't cancel the save that triggered the event, that will save the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save initiated by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign the
macro to your 'Save' control button, users can press the button to save
the
file, resave it etc., if they don't then the workbook close event kicks in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or ?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then
use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving
them
names on their own), I am using the following code in a macro button
(called
Save) placed on the sheet to generate a file name based on contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more elegant
way
of
achieving what I am trying to do. Should this code be in a module or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Thanks Tom, for pointing this out, I hope the original requestor benefits
from your insight
Cheers
Nigel

"Tom Ogilvy" wrote in message
...
Since you don't cancel the save that triggered the event, that will save
the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save initiated
by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign
the
macro to your 'Save' control button, users can press the button to save
the
file, resave it etc., if they don't then the workbook close event kicks
in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the
system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button
on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from
the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or
?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing
time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or
cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then
use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving
them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users
giving
them
names on their own), I am using the following code in a macro
button
(called
Save) placed on the sheet to generate a file name based on
contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as
"John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more
elegant
way
of
achieving what I am trying to do. Should this code be in a module
or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Hello Tom & Nigel:
I really appreciate your time & efforts. I followed the suggestions by Nigel
& comment by Tom but kind of lost track of the recommendations (sorry, I am
still a novice finding my way around with VBA!).

Tom, could I ask what your suggestions would be to address the problem. Does
your reply imply that I can use the BeforeClose event & then it would save
the workbook twice & not go on to an indefinite recursive loop?
Is there a way to achieve what I want yet save the file only once? (It is a
big file 1.5 mb & takes some time to save, so I would like to avoid saving
it twice)

Thanks a lot for your valuable suggestions.

--
Maria

"Tom Ogilvy" wrote in message
...
Since you don't cancel the save that triggered the event, that will save
the
file at least twice, maybe more as each save could trigger another
beforesave event (but fortunately doesn't - see below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.enableEvents = False
Cancel = True
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub

Would eliminate this type of recursive call and cancel the save initiated
by
the user.

as a simple test - this simplified but equivalent version:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
ThisWorkbook.Save
End Sub

fires twice on each user initiated save.

--
Regards,
Tom Ogilvy


Nigel wrote in message
...
Maria

The FileSave and FileSaveAs options will always be a possibility
wherever
you place the code. So to prevent this you will need to disable these
menu
options but do you?

If the code is placed in the workbook before close event then the file
will
either be created with the name you require or overwritten if it already
exists, it should not do it save it twice - place the following in
ThisWorkBook code. If you also put it in a standard module and assign
the
macro to your 'Save' control button, users can press the button to save
the
file, resave it etc., if they don't then the workbook close event kicks
in
and saves it anyway. The only problem is the user saves it as another
name
buth this does not affect the primary operation, just clutters the
system
with unwanted files.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (Dir("c:\My documents\Special Folder\"& Range("A1") & "- No " &
Range("A2") & ".xls") = "") Then
ThisWorkbook.SaveAs Filename:=("c:\My documents\Special Folder\" &
Range("A1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End If
End Sub

If you do decide to turn the file menu options off do not forget to set
them
on again before closing Excel.

Cheers
Nigel


"Maria" wrote in message
...
Hello Nigel:
Thanks a lot for your help. My second question is: where is the best
location to place this code. If I place it in a module with a button
on
the
worksheet, then users may bypass it by selecting "File", "Save or Save
As".
In that case, probably I should disable the 'File/Save/SaveAs' from
the
menu
bar?

2) The second option, should I place it in the Workbook BeforeSave or
?
BeforeClose event. This way, it will always be activated, but when I
tried
it out, I have a feeling that this saves the file twice (increasing
time
required for the operation unecessarily)

I would appreciate if you have any specific suggestions on the optimum
location for the code

Thanks a lot

--
Maria


"Nigel" wrote in message
...
Maria

Immediately before the SaveAs line place the following:

On Error Resume Next

This will prevent the Run time error if the user presses no or
cancel,
they
can still press Yes and overwrite the file.

If you want to overwrite the file whenever the user presses the save
button,
then you need to test if the file already exists, if it doesn't then
use
SaveAs (what you have already), if it does exist then use just save:

If (Dir("c:\My documents\Special Folder\" & Range("a1") & "- No " &
Range("A2") & ".xls" = "") Then
ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"
Else
ThisWorkbook.Save
End if

Cheers
Nigel

"Maria" wrote in message
...
Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to
enter
information & then store the files in a directory after giving
them
easy
to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users
giving
them
names on their own), I am using the following code in a macro
button
(called
Save) placed on the sheet to generate a file name based on
contents
of
cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:\My documents\Special Folder\" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as
"John
Doe -12345.xls" & saves it in the appropriate directory. However
once
the
file is saved with this name & the user clicks on the Macro button
(Save)
next time, it displays the Excel message, which alerts the user
that
the
file already exists & asks whether you want to replace it with
choices
of
,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an
Run
time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only
if
the
user is using the blank template for the first time. For all
subsequent
time, clicking on the macro should only save the file & not do a
saveas
features.

I am a novice at this, & am sure that there is a better/more
elegant
way
of
achieving what I am trying to do. Should this code be in a module
or
should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---