More SaveAs trouble

[Shawn Shuler]

I jumped the gun thinking I had it figured out...

I'm trying to bulletproof a spreadsheet template against being overwritten
by coworkers who must use it. I have set it up to force them to save it as
a normal Excel spreadsheet under a different name any time the template is
activated. All works fine unless I choose NOT to overwrite an existing
file.

To be clear, the saveas dialog appears and I choose a file name that already
exists. I am then asked if I wish to overwrite the file. If I say 'Yes'
everything goes fine. However, choosing 'No' or 'Cancel' results in an
error.

Here's a simplified example of what I have so far:

Sub SaveAsX()
NameFile:
On Error GoTo SaveAsErrors
FileSaveName = "Test"
While FileSaveName = "Test"
FileSaveName = Application.GetSaveAsFilename(InitialFileName:= _
"C:\temp\Test", fileFilter:="Microsoft Excel Workbook (*.xls),
*.xls")
Wend
ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=-4143
Exit Sub

SaveAsErrors:
If Err.Number = 1004 Then GoTo NameFile
End Sub

If I choose 'No' or 'Cancel' the first time the error is handled fine, the
SaveAs dialog box appears again. However, if I choose either button again
the second time it creates the same error but it is not caught by the error
trap. Why is this. Help!

 

[Shawn Shuler]

GOT IT!! I really hate feeling like I have hit a brick wall, asking for
help, and then finding the answer. Seems to inspire me, though.