Ultimate Control
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.
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
|