Disabling macros is not an option!!

Hi folks

Office 97 & NT4

I've been handed a bit of a poisened chalice & like a fool I've taken a
sip...so here's the problem:

Esentially I need to prohibit printing a file unless a small number of
fields are completed. This was originally done in MS Word, but nothing stops
the users from choosing 'Disable Macros' and printing out as many copies as
they like.

I've come across XML macros that cannot be disabled, so I think Excel is my
only choice (yes?). What I'd like to do then is create a spreadsheet that
contains some text fields, which are validated and upon completion, prompt
the user to print. I can handle the validation & the final msgbox "print
now?" etc, but am completely stumped re. creating code from previous
versions of Excel.

Can anyone help me or suggest an alternative way to solve this problem? I
don't mind how it's done as long as users can't print a (partially or
totally) incomplete form - that's the nub of the issue

Ideally I'd like to use Word, and not use UserForms, but beggars can't be
choosers.

Thanks,
Paul

Harlan Grove just posted this to a different question. I didn't find it on
google (yet), so I'm gonna copy|paste his response:

===
If you really want to prevent users from doing certain things, you
gotta think nasty, something for which I'm better qualified than most
of the rest of you.

If macros are needed to ensure a workbook works properly, then include
do-nothing udfs returning VBA Empty variant values (equivalent to
what's returned by blank worksheet cells), and include them in *EVERY*
formula. If macros are disabled, so are udfs, in which case *ALL*
formulas would return #NAME?. If users want to print useless pages
filled with #NAME? errors with their own custom headers and footers, I
say let 'em until they blow through their paper and toner budgets.

So, when the only way to get useful results requires macros enabled,
then you can rely on event handlers. To prevent copying and pasting,
add code to the event handlers to make it difficult (though still not
impossible).

Note that even if copying from given workbooks were disabled, it'd
still be possible to pull data from such workbooks using external
reference links, e.g.,

='X:\Extremely\High\Security\[Document.xls]Well Protected'!A1

Basic rule of spreadsheet IP: if the file can be opened, *ANYTHING* in
it can be misused. Corollary: no spreadsheet, especially not Excel,
provides effective IP protection, so if you want to protect IP, don't
put it in a spreadsheet.

===


Password protect your

Paul Wagstaff wrote:
>
> Hi folks
>
> Office 97 & NT4
>
> I've been handed a bit of a poisened chalice & like a fool I've taken a
> sip...so here's the problem:
>
> Esentially I need to prohibit printing a file unless a small number of
> fields are completed. This was originally done in MS Word, but nothing stops
> the users from choosing 'Disable Macros' and printing out as many copies as
> they like.
>
> I've come across XML macros that cannot be disabled, so I think Excel is my
> only choice (yes?). What I'd like to do then is create a spreadsheet that
> contains some text fields, which are validated and upon completion, prompt
> the user to print. I can handle the validation & the final msgbox "print
> now?" etc, but am completely stumped re. creating code from previous
> versions of Excel.
>
> Can anyone help me or suggest an alternative way to solve this problem? I
> don't mind how it's done as long as users can't print a (partially or
> totally) incomplete form - that's the nub of the issue
>
> Ideally I'd like to use Word, and not use UserForms, but beggars can't be
> choosers.
>
> Thanks,
> Paul

--

Dave Peterson

Another approach that can be used separately or in conjunction with
Harlan's suggestion is to include a page that is essentially a splash
screen saying that macros are required. Save the document with all
pages hidden except for this splash screen. Write a ThisWorkbook
Workbook_Open event that will unhide the hidden pages and hide the
splash screen (it will only run if macros are enabled).

Jerry

Dave Peterson wrote:

> Harlan Grove just posted this to a different question. I didn't find it on
> google (yet), so I'm gonna copy|paste his response:
>
> ===
> If you really want to prevent users from doing certain things, you
> gotta think nasty, something for which I'm better qualified than most
> of the rest of you.
>
> If macros are needed to ensure a workbook works properly, then include
> do-nothing udfs returning VBA Empty variant values (equivalent to
> what's returned by blank worksheet cells), and include them in *EVERY*
> formula. If macros are disabled, so are udfs, in which case *ALL*
> formulas would return #NAME?. If users want to print useless pages
> filled with #NAME? errors with their own custom headers and footers, I
> say let 'em until they blow through their paper and toner budgets.
>
> So, when the only way to get useful results requires macros enabled,
> then you can rely on event handlers. To prevent copying and pasting,
> add code to the event handlers to make it difficult (though still not
> impossible).
>
> Note that even if copying from given workbooks were disabled, it'd
> still be possible to pull data from such workbooks using external
> reference links, e.g.,
>
> ='X:\Extremely\High\Security\[Document.xls]Well Protected'!A1
>
> Basic rule of spreadsheet IP: if the file can be opened, *ANYTHING* in
> it can be misused. Corollary: no spreadsheet, especially not Excel,
> provides effective IP protection, so if you want to protect IP, don't
> put it in a spreadsheet.
>
> ===
>
>
> Password protect your
>
> Paul Wagstaff wrote:
>
>>Hi folks
>>
>>Office 97 & NT4
>>
>>I've been handed a bit of a poisened chalice & like a fool I've taken a
>>sip...so here's the problem:
>>
>>Esentially I need to prohibit printing a file unless a small number of
>>fields are completed. This was originally done in MS Word, but nothing stops
>>the users from choosing 'Disable Macros' and printing out as many copies as
>>they like.
>>
>>I've come across XML macros that cannot be disabled, so I think Excel is my
>>only choice (yes?). What I'd like to do then is create a spreadsheet that
>>contains some text fields, which are validated and upon completion, prompt
>>the user to print. I can handle the validation & the final msgbox "print
>>now?" etc, but am completely stumped re. creating code from previous
>>versions of Excel.
>>
>>Can anyone help me or suggest an alternative way to solve this problem? I
>>don't mind how it's done as long as users can't print a (partially or
>>totally) incomplete form - that's the nub of the issue
>>
>>Ideally I'd like to use Word, and not use UserForms, but beggars can't be
>>choosers.
>>
>>Thanks,
>>Paul

Jerry W. Lewis wrote...
>Another approach that can be used separately or in conjunction with
>Harlan's suggestion is to include a page that is essentially a splash
>screen saying that macros are required. Save the document with all
>pages hidden except for this splash screen. Write a ThisWorkbook
>Workbook_Open event that will unhide the hidden pages and hide the
>splash screen (it will only run if macros are enabled).
....

The big question is whether unauthorized use would be unintentional or
willful. If the former, all these approaches would work. If the latter,
none are reliable. Since it's easy to eliminate workbook and worksheet
protection, it's easy to unhide worksheets and edit formulas manually.

Thanks for the replies. Basically I need to 'discourage' users from printing
out their own copies of this & I think I've worked out a rather clever
workaround - basically make all fonts vbWhite by default & protect the doc.
That way, if they 'disable' macros, all they're gettin is a blank piece of
A4!

nice 'n' sneaky?


Paul.



"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1115402187.149453.241430@o13g2000cwo.googlegroups.com...
> Jerry W. Lewis wrote...
> >Another approach that can be used separately or in conjunction with
> >Harlan's suggestion is to include a page that is essentially a splash
> >screen saying that macros are required. Save the document with all
> >pages hidden except for this splash screen. Write a ThisWorkbook
> >Workbook_Open event that will unhide the hidden pages and hide the
> >splash screen (it will only run if macros are enabled).
> ...
>
> The big question is whether unauthorized use would be unintentional or
> willful. If the former, all these approaches would work. If the latter,
> none are reliable. Since it's easy to eliminate workbook and worksheet
> protection, it's easy to unhide worksheets and edit formulas manually.
>

Paul

Sneaky? Yes.

Efective? No.

Protection is easily cracked.


Gord Dibben Excel MVP

On Fri, 06 May 2005 20:25:46 GMT, "Paul Wagstaff" <waggy@blueyonder.co.uk>
wrote:

>Thanks for the replies. Basically I need to 'discourage' users from printing
>out their own copies of this & I think I've worked out a rather clever
>workaround - basically make all fonts vbWhite by default & protect the doc.
>That way, if they 'disable' macros, all they're gettin is a blank piece of
>A4!
>
>nice 'n' sneaky?
>
>
>Paul.
>
>
>
>"Harlan Grove" <hrlngrv@aol.com> wrote in message
>news:1115402187.149453.241430@o13g2000cwo.googlegroups.com...
>> Jerry W. Lewis wrote...
>> >Another approach that can be used separately or in conjunction with
>> >Harlan's suggestion is to include a page that is essentially a splash
>> >screen saying that macros are required. Save the document with all
>> >pages hidden except for this splash screen. Write a ThisWorkbook
>> >Workbook_Open event that will unhide the hidden pages and hide the
>> >splash screen (it will only run if macros are enabled).
>> ...
>>
>> The big question is whether unauthorized use would be unintentional or
>> willful. If the former, all these approaches would work. If the latter,
>> none are reliable. Since it's easy to eliminate workbook and worksheet
>> protection, it's easy to unhide worksheets and edit formulas manually.
>>
>