How do I stop people from saving a workbook as a particular name?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simmo - 30 Mar 2006 21:47 GMT

Hi,

can anyone help?

I want to stop users opening a particular workbook and then saving that
workbook as "master.xls".

So if they open the document called master.xls, if any save action is
invoked (i,e they click "save" or "save as") - they are prompted with a
message telling them to save as a different name (if they are about to
save it as master.xls).

If the autosave is on - is there anywhere I can use this to prompt them
to save as sat "dummy1.xls"? If not then, no matter. As long as they
cant save as "master.xls".

Does anybody know how to write the VBA code to do this? I know it needs
to go in the worksheet_Beforesave() event.

Many thanks,

Reply to this Message

Paul Berkowitz - 30 Mar 2006 22:05 GMT

On 3/30/06 12:47 PM, in article
1143751673.797511.171380@g10g2000cwb.googlegroups.com, "Simmo"
<newton2060@gmail.com> wrote:

> can anyone help?
>
[quoted text clipped - 12 lines]
> Does anybody know how to write the VBA code to do this? I know it needs
> to go in the worksheet_Beforesave() event.

Why don't you just save the master as a Template (.xlt), not as a Workbook?
Put it into a subfolder of Microsoft Office 2004/Templates - either My
Templates or another subfolder you make there. (Or you can designate another
folder for templates, not in Excel, but in Word/Preferences/File
Locations/User Templates.) Then tell your users to "open" it only via
File/Project Gallery/ in Excel, and in the My Templates or other directory
there. When they do that, a new unsaved workbook based on the master will
open as "Workbook 2" and they will have to give it a name when saving. It
will not alter the original master in any way. You just have to make sure
that they don't open the master template instead of a new workbook made from
it. Unfortunately, on the Mac, double-clicking the master will open the
original.

So here's maybe the _best_ solution. Save the master as STATIONERY (in the
Save As dialog, or just check "Stationery" in a Get Info for the file). Now,
no matter how they open it - double-clicking, File menu, anything - it will
make a new unsaved Workbook 2, etc. They have to give it a name when saving.
(In fact, it's quite awkward to make changes to the original when you
actually want to: you have to save as the original name in the original's
location, Save, and choose to Replace when asked.) The new workbooks are
"divorced" from their original, unlike documents made form templates, but
that's in fact exactly what you want here.

Signature

Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

Reply to this Message

JE McGimpsey - 30 Mar 2006 22:53 GMT

> The new workbooks are "divorced" from their original, unlike
> documents made form templates, but that's in fact exactly what you
> want here.

That would be the case anyway - XL workbooks never have any tie to any
template that they're created from.

Reply to this Message

Mac Forum / Applications / Excel / March 2006

How do I stop people from saving a workbook as a particular name?