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
=---