I've done a fair amount of Visio VBA but very little with Excel and need
assistance.

I've written an Excel auto_open() macro that works fine. Among other things
it checks whether the user has a license to perform certain functions. I
would like the code to be compiled/ protected/ trusted so the user can't
circumvent it by setting macro security to high.

I've played with an .xla add-in but the auto_open() code only executes when
the add-in is loaded or unloaded. I know I can load or unload it from code
but I can't put the calling code in my xls/xlt because I would have the same
problem -- the user could set macro security to high and prevent the call to
the xla.

I'm sure my need isn't unique -- code that always runs when an xlt/xls is
opened and that can't be disabled by the user. Can someone point me in the
right direction?

Thanks,
Scott

Scott

Frtom help on "about digital signatures".


Microsoft Office 2003 uses Microsoft Authenticode technology to enable you to
digitally sign a file or a macro project by using a digital certificate. The
certificate used to create this signature confirms that the macro or document
originated from the signer, and the signature confirms that it has not been
altered. When you set the macro security level, you can run macros based on
whether they are digitally signed by a developer on your list of trusted
sources.

Where to get the digital certificates

You can obtain a digital certificate from a commercial certification authority,
such as VeriSign, Inc., or from your internal security administrator or
Information Technology (IT) professional. Or, you can create a digital signature
yourself using the Selfcert.exe tool.

Note Because a digital certificate you create yourself isn't issued by a formal
certification authority, macro projects signed by using such a certificate are
referred to as self-signed projects. Certificates you create yourself are
considered unauthenticated and will generate a warning in the Security Warning
box if the security level is set to High or Medium. Microsoft Office will only
trust a self-signed certificate on a computer that has the private key for that
certificate available (generally only the computer that actually created the
certificate, unless the private key is shared with other computers).


Gord Dibben MS Excel MVP

On Thu, 17 May 2007 20:41:00 -0700, Scott
wrote:

I've done a fair amount of Visio VBA but very little with Excel and need
assistance.

I've written an Excel auto_open() macro that works fine. Among other things
it checks whether the user has a license to perform certain functions. I
would like the code to be compiled/ protected/ trusted so the user can't
circumvent it by setting macro security to high.

I've played with an .xla add-in but the auto_open() code only executes when
the add-in is loaded or unloaded. I know I can load or unload it from code
but I can't put the calling code in my xls/xlt because I would have the same
problem -- the user could set macro security to high and prevent the call to
the xla.

I'm sure my need isn't unique -- code that always runs when an xlt/xls is
opened and that can't be disabled by the user. Can someone point me in the
right direction?

Thanks,
Scott

Scott,
A user can always set security to high prevents macros from running and
thankfully there is nothing you can do about that.
They can also set EnableEvents=False, so any Auto_Open, Workbook_Open code
will not run.

As for code in an xla, maybe ;
http://www.cpearson.com/excel/AppEvent.htm

You can however ensure that nothing (useful) is visible, apart from a sheet
saying "You must enable macros/events for this work.....", but you have to
work within the possible limitations of the user's Excel
environment/settings.

NickHK

"Scott" wrote in message
...
I've done a fair amount of Visio VBA but very little with Excel and need
assistance.

I've written an Excel auto_open() macro that works fine. Among other
things
it checks whether the user has a license to perform certain functions. I
would like the code to be compiled/ protected/ trusted so the user can't
circumvent it by setting macro security to high.

I've played with an .xla add-in but the auto_open() code only executes
when
the add-in is loaded or unloaded. I know I can load or unload it from code
but I can't put the calling code in my xls/xlt because I would have the
same
problem -- the user could set macro security to high and prevent the call
to
the xla.

I'm sure my need isn't unique -- code that always runs when an xlt/xls is
opened and that can't be disabled by the user. Can someone point me in the
right direction?

Thanks,
Scott

Thanks, Gord.

"Gord Dibben" wrote:

Scott

Frtom help on "about digital signatures".


Microsoft Office 2003 uses Microsoft Authenticode technology to enable you to
digitally sign a file or a macro project by using a digital certificate. The
certificate used to create this signature confirms that the macro or document
originated from the signer, and the signature confirms that it has not been
altered. When you set the macro security level, you can run macros based on
whether they are digitally signed by a developer on your list of trusted
sources.

Where to get the digital certificates

You can obtain a digital certificate from a commercial certification authority,
such as VeriSign, Inc., or from your internal security administrator or
Information Technology (IT) professional. Or, you can create a digital signature
yourself using the Selfcert.exe tool.

Note Because a digital certificate you create yourself isn't issued by a formal
certification authority, macro projects signed by using such a certificate are
referred to as self-signed projects. Certificates you create yourself are
considered unauthenticated and will generate a warning in the Security Warning
box if the security level is set to High or Medium. Microsoft Office will only
trust a self-signed certificate on a computer that has the private key for that
certificate available (generally only the computer that actually created the
certificate, unless the private key is shared with other computers).


Gord Dibben MS Excel MVP

On Thu, 17 May 2007 20:41:00 -0700, Scott
wrote:

I've done a fair amount of Visio VBA but very little with Excel and need
assistance.

I've written an Excel auto_open() macro that works fine. Among other things
it checks whether the user has a license to perform certain functions. I
would like the code to be compiled/ protected/ trusted so the user can't
circumvent it by setting macro security to high.

I've played with an .xla add-in but the auto_open() code only executes when
the add-in is loaded or unloaded. I know I can load or unload it from code
but I can't put the calling code in my xls/xlt because I would have the same
problem -- the user could set macro security to high and prevent the call to
the xla.

I'm sure my need isn't unique -- code that always runs when an xlt/xls is
opened and that can't be disabled by the user. Can someone point me in the
right direction?

Thanks,
Scott

Thanks, Nick.

A user can always set security to high prevents macros from running and
thankfully there is nothing you can do about that.

I understand and appreciate the reason for this.

However, what I'm trying to do is protect a commerical product by ensuring
that the user has purchased a valid license. In our Visio application, we did
that by building our special functions and our license checking code into a
COM add-in (COM add-ins are not affected by macro security settings). If the
add-in is properly registered with Visio and a license is present, our
intelligent shapes work as advertised. But if the COM add-in isn't registered
or has been disabled, our shapes cease to function. I'm trying to replicate
this mode of operation in Excel.

From various tips here and elsewhere, I've come to several conclusions:
-- I can digitally sign my code so it will run at any security setting other
than very high.
-- I can password protect my VBA project so the user can't see or alter the
code.
-- I can continue to check for a valid license in the auto_open() sub.
-- I can't prevent the user from setting macro security to vry high and
preventing my code from running.

Given the above:

-- If my code runs and there is a valid license present, my code will
provide its added value functions and the user (and I) will be happy.

-- If the user prevents my code from running, they will be able to open the
xls/xlt and view the spreadsheet, however, the special functions provided by
my code will not be available. So even in this case I guess I am "protected"
in a reasonable way.

Any flaws in my logic? Any other suggestions?