Check File Name before Save Event
 

So John I am having trouble with this. When I first tried it I coul
not get it to work. Then it started working. I then created a ne
file with a new name and tried it. Problems getting the routine to ru
during save.

Try creating a new file called Test.xls. Insert the name into the cod
below and adust your Right() formula.

I can't seem to get it to fire when I save the file. It just goe
ahead and saves it without it calling the routine. I can't figure ou
why it does not call the routine.

Thanks



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

Dim FileName As String

If ThisWorkbook.Name = "Test.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, 8) = "Test.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 Su

--
ExcelMonke
-----------------------------------------------------------------------
ExcelMonkey's Profile: http://www.excelforum.com/member.php...nfo&userid=522
View this thread: http://www.excelforum.com/showthread.php?threadid=26427