Ultimate Control

[Steven]

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

 

[Rick Rothstein \(MVP - VB\)]

I have a file that is set up so that on Before Save if it will cancel

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.

I agree about the backup; however, you do not need the FileSystemObject just
to change file permissions... use the built-in SetAttr Statement...

Private Sub Workbook_Open()
SetAttr ThisWorkbook.FullName, vbNormal
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
SetAttr ThisWorkbook.FullName, vbReadOnly
End Sub

The above (untested) should work fine.

Rick

 

[Steven]

Thank you for your responses. That is not what I am saying. What I want to
do is when I create a file, we will call it "File001.xls", in excel there
should be a property that says that only the creater of the file can a.)
"copy" over the file or b.) take a different file and save it over the file.

Now, from within the file File001.xls a user can save the file but could not
Save As to anything else. The ultimate key is that, for example, say a user
is in File002.xls and they try to do something like save File002.xls to
File001.xls then the system should say,

"You cannot save over File001.xls from another file...the creater of the
file has blocked the ability to overwrite the file from the outside. This
file can only be updated and saved by properly opening the file and working
within the file." The same would hold true if they tried to copy over the
file in Explorer or in the Excel dialog box or delete the file. If this
cannot be done I believe it is an overall weakness in excel that needs
changed.

Steven

 

[Tim Williams]

What you're asking for is an OS-level piece of functionality: Excel cannot
"watch" what folk do in Explorer when it's not running.

This is really a job for a document management system.

Tim