Check File Name before Save Event

[ExcelMonkey]

I am distributing a file to clients and they are returning it to me.
want to send it out with the name "workbook1". When they return it t
me, I want to stop them from saving it under the same name. The cod
below works given that the file is already named "workbook1". Howeve
it also prevents the user from changing the name. Instead of using th
Name property as the test, I need to have the user attempt to save th
file and somehow pass the proposed name, that goes into the Save o
Save as filename box, to a variable and use this as the test tha
drives the Save Event.

Thanks


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)

Dim FileName As String

FileName = ThisWorkbook.Name

If FileName = "Workbook1.xls" Then
MsgBox ("You cannot save this file using this name?" & vbCr & _
"It is recommended that you save it using your name as a
extension" & vbCr & _
"to this name(i.e. Workbook1JohnSmith)")

Cancel = True

End If

End Su

 

[Jon Peltier]

The following BeforeSave procedure keeps asking until the user enters a valid file
name or cancels:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim FileName As String

If ThisWorkbook.Name = "Workbook1.xls" Then
Do
' returns path and name
FileName = Application.GetSaveAsFilename _
(Title:="Browse to a directory and enter a file name")

' check the name
If Right(FileName, 13) = "Workbook1.xls" Then
' don't use Workbook1.xls
MsgBox "That file name is not permitted." & vbCrLf & _
"Please select another file name.", vbExclamation, _
"Invalid File Name"
ElseIf Len(Dir(FileName)) > 0 Then
' duplicate name
MsgBox "A file with that name already exists." & vbCrLf & _
"Please select another file name.", vbExclamation, _
"File Exists"
ElseIf FileName = "False" Then
Exit Do
Else
Exit Do
End If
Loop

Cancel = True

If FileName <> "False" Then
Application.EnableEvents = False
ThisWorkbook.SaveAs FileName
Application.EnableEvents = False
End If
End If
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______