Save As - Can I use VBA? to stop XL Sheet overwrite

[Guest]

and if so How???

Hi everyone,

I want to somehow protect a worksheet from being overwritten. My worksheet
has passwords to open and to modify, but I hadn't realised that it was
possible to overwrite it when another xls is saved using Saved as and my
worksheet's name entered. When I tried this out with dummy files I didn't
even get a message saying that this file exists and do I want to overwrite.

Is there a built in way of stopping this happening or can a macro stop it?

I am really hoping that someone can help

Thanking you in advance

 

[Guest]

I really don't think this can be stopped in the fashion you want. As I
understand it, someone may create a workbook and give it the name of the one
you want to remain untouched. They'll get the system warning that the "file
exists, overwrite?" and if they say yes, poof, it's done.

A macro could stop that, but the macro would have to be in the workbook they
created.

I'm wondering why you aren't getting the 'file exists, overwrite?" warning.
About the only way I know (someone else may know of a setting somewhere I'm
not thinking of) to do that is with a macro that does the Save As and sets a
parameter not to ask, just do it.

Seems like your best protection (as always) is to always have another backup
copy of any critical data file. In reality, this overwriting happens more
often than you might think - some people merrily just blaze through the "file
exists, overwrite?" prompt anyhow and only later realize what they've done.
This is the single most often cause of me having to go to our daily backup
files and retrieve and restore a 'lost' file - and it is operator error, not
system error that does it every time.

 

[Guest]

I'll offer a possible workaround macro that you could put into your workbook
to make recovery easier if someone does overwrite your file. Put this code
into the WORKBOOK's code segment. To get there quickly, right-click on the
little Excel icon immediately to the left of "File" in the menu toolbar and
choose [View Code] from the popup list - copy this code and paste it into
that and save the workbook.

What it does: it intercepts File | Save and even File | Save As (meaning
you won't be able to Save As with another name - you'd have to go into the
folder and rename it there) and even the click on the floppy icon for save,
and [Ctrl]+ actions. It looks at the current file name, and is written
presuming it ends with the standard ".xls" file extension. It then sticks
..bak between the original main part of the filename and the .xls and puts
..xls back onto it and saves it that way, then again saves it as the original
filename and exits. It puts the .bak.xls copy into the same folder that the
original file is in.

So if you started with this in a file named myWorkbook.xls and you end up
with 2 files
myWorkbook.bak.xls
and the latest copy of
myWorkbook.xls
saved to the same folder. The Cancel option is set to True to just abort
out of your requested Save/Save As operation, since it has already been done
for you by this routine.

At least this way, you can always fall back to the .bak.xls version, using
Windows explorer to delete the overwritten .xls version and copying the
..bak.xls version to the folder and renaming it properly.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'this will first save the workbook with a modified name as
'myWorkbook.bak.xls and then go ahead and
'save it with the original filename (myWorkbook.xls)
'when a save is performed

Dim OriginalName As String
Dim BackupName As String
OriginalName = ThisWorkbook.FullName
BackupName = Left(OriginalName, Len(OriginalName) - 4) & ".bak" & _
Right(OriginalName, 4)
Application.EnableEvents = False
Application.DisplayAlerts = False ' no "file exists" please
ActiveWorkbook.SaveAs Filename:=BackupName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=OriginalName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True ' turn them back on
Application.EnableEvents = True ' and these also
Cancel = True ' we did it once, why do it again

End Sub