I have a file that is set up so that on Before Save if it will cancel
and tell the user that you cannot save this file to another location if
the user attempted a Save As.

Now my question is how do you keep a user from "copying or saving" over
a file with another file. Is this possible? I sure hope it is because
this definitely would lock down a file. It has been my experience that
excel files are always exposed to being copied over if a user has rights
to that directory. Is that correct? I hope not.

Thank you,

Steven

It's a nice question, but it's not a nice solution as far as I can see.

On any file, when you go to the right-click-properties dialog you can
tick the "Read-Only" attribute. That'll stop anyone accidentally using a
Saveas to clobber your file, but it does have the side-effect that when
you open the file it'll be read-only, so no-one can make any changes
either.

You could set the read-only property dynamically every time you open/
close the file, although it's a lot of effort. You need to reference
Microsoft Scripting Runtime (scrrun.dll) from tools, references.
Something like this:

Private Sub Workbook_Open()
Dim fso As FileSystemObject
Dim thisfile As File

Set fso = CreateObject("scripting.filesystemobject")
Set thisfile = fso.GetFile(ThisWorkbook.FullName)
thisfile.Attributes = Normal ' remove read-only setting

ThisWorkbook.ChangeFileAccess (xlReadWrite) ' open for editing

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save ' file has to save before it becomes readonly

Dim fso As FileSystemObject
Dim thisfile As File
Set fso = CreateObject("scripting.filesystemobject")
Set thisfile = fso.GetFile(ThisWorkbook.FullName)
thisfile.Attributes = Scripting.ReadOnly

Cancel = False
End Sub


Although I've lost the "would you like to save your changes" dialog, and
if someone really wanted to destroy your file, they still could. Far
easier is just to keep a backup.

Pip