Disabling macros is not an option!!

  • Thread starter Thread starter Paul Wagstaff
  • Start date Start date
P

Paul Wagstaff

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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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).
Click to expand...
....

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.
 
Paul

Sneaky? Yes.

Efective? No.

Protection is easily cracked.


Gord Dibben Excel MVP
 
Back
Top