Prevent Save As

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CarlSprake - 22 Mar 2006 14:56 GMT

I am hoping that someone can help me with this problem.

Is there anyway to prevent a user from doing File - Save As? (preferably
using macros)

I am happy for them to make changes to a workbook, but do not want them to
be able to create different copies.

Thanks for your help

Reply to this Message

Martin - 22 Mar 2006 15:20 GMT

Go to the ThisWorkbook module and choose Workbook from the first dropdown at
the top of the module window (rather than General). Choose BeforeSave from
the righthand dropdown: this is a macro that will run automatically when
someone attempts to save the file. The following cancels the whole save
operation if the user chooses Save As:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
End Sub

> I am hoping that someone can help me with this problem.
>
[quoted text clipped - 5 lines]
>
> Thanks for your help

Reply to this Message

Dave Peterson - 22 Mar 2006 19:04 GMT

You won't be able to stop them.

Heck, they could go into Windows explorer and copy that file and put it anywhere
they want.

> I am hoping that someone can help me with this problem.
>
[quoted text clipped - 5 lines]
>
> Thanks for your help

Signature

Dave Peterson

Reply to this Message

Simon Lloyd - 23 Mar 2006 10:28 GMT

You could have the Auto_Open check the filepath if it is different t
the one hardcoded then Kill the application, that way any other copie
that are made and stored else where will be deleted when they ar
opened....................or something like that..........it's just
thought!

Regards,
Simo

Reply to this Message

Dave Peterson - 23 Mar 2006 13:26 GMT

And if macros are disabled (or the auto_open/workbook_open procedures are
disabled), then the user can still do what they want.

And macro security isn't too difficult to break--so even protecting the project
wouldn't stop any person really interested.

> You could have the Auto_Open check the filepath if it is different to
> the one hardcoded then Kill the application, that way any other copies
[quoted text clipped - 10 lines]
> Simon Lloyd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6708
> View this thread: http://www.excelforum.com/showthread.php?threadid=525252

Signature

Dave Peterson

Reply to this Message

Simon Lloyd - 23 Mar 2006 14:14 GMT

How about opening one workbook to call another, that way if macro'
aren't enabled it will never call the other workbook but if it doe
then the protection of the filepath check and the save as disable
should afford some better security than having it all in on
workbook.................anyway i dont know why i'm professing abou
the rights and wrongs because when it comes to programming im as thic
as the next guy!

Regards,

Simon

Reply to this Message

Dave Peterson - 23 Mar 2006 16:36 GMT

Yep. That's a common suggestion. But since the project protection of the
opening workbook can be broken, then the password to the real workbook can be
found.

Excel's security isn't really meant to protect intellectual property.

> How about opening one workbook to call another, that way if macro's
> aren't enabled it will never call the other workbook but if it does
[quoted text clipped - 13 lines]
> Simon Lloyd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6708
> View this thread: http://www.excelforum.com/showthread.php?threadid=525252

Signature

Dave Peterson

Reply to this Message

Simon Lloyd - 24 Mar 2006 11:58 GMT

Good job i'm not an intellectual then!

Regards,

Simon

Signature

Simon Lloyd

Reply to this Message

idyllicabyss@googlemail.com - 24 Mar 2006 13:28 GMT

In the past, I have created a new sheet to use as a splash screen and
included the words "This Document requires Macros to be enabled" or
similar on coloured cells cos it looks pretty. All other sheets are
hidden using

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Sheet2" Then
sh.Visible = 2
Else
sh.Visible = -1
End If
Next sh
End Sub

You need to run the code in reverse when opening and if you also add in
a line to close the workbook if the Filename and Path are not as
expected then it would be pretty tight. Be sure to use sheet protection
and VBA project protection and most people won't have the inclination
to hack into it.

Reply to this Message