Sdc,
code should be placed in ThisWorkbook
InstrRev only works with Excel2000 or newer.
warnings and/or exits should be amended to suit your needs.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sName$, sPath$
If SaveAsUI Then
Cancel = True
sPath = Application.GetSaveAsFilename( _
FileFilter:="Excel Workbooks (*.xls), *.xls")
If sPath = "False" Then
Exit Sub 'User cancelled
ElseIf sPath = ThisWorkbook.FullName Then
ThisWorkbook.Save
Else
sName = Mid(sPath, InStrRev(sPath, "\") + 1)
sPath = Left(sPath, InStrRev(sPath, "\"))
If sName < ThisWorkbook.Name Then
If vbOK = MsgBox( _
"Warning: will be saved to indicated path, " & _
"but file cannot be renamed", vbOKCancel) Then
If sPath = ThisWorkbook.Path & "\" Then
ThisWorkbook.Save
Else
On Error Resume Next 'need for cancel overwrite
Application.EnableEvents = False 'avoid triggering self
ThisWorkbook.SaveAs sPath & ThisWorkbook.Name
Application.EnableEvents = True
End If
End If
End If
End If
End If
End Sub
keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage:
http://members.chello.nl/keepitcool
"scrabtree23" wrote:
Is there a way I can have a message box alert someone,
when the file is opened, if they have changed the name of
the file? Basically, I don't want folks changing the name
of the file and want to warn them to change it back.
SDC